Read consistency overhead

SQL performance can degrade for many reasons, some of most common are:
- plan changes
- data skewness
- low caching efficiency
- data growth
- contention.

All these factors are relatively well known. A somewhat less common, although not exceptionally rare scenario, is read consistency overhead due to concurrent (more...)

Managing expectations

In this blog, I mostly write about performance issues (and tools to resolve them). I’d like to make a post about performance non-issues for a change. I think it might be interesting for two reasons. One, if not identified early, non-issues can consume lots of resources in terms of developers’ (more...)

Is CPU usage 100% really okay?

Even the great ones do misspeak from time to time. And when they do, this can lead to a terrible mess in people’s heads.

Here is one example:

Are you at 100% utilization? If not, you haven’t accomplished your job yet. You cannot
put CPU in the bank and save (more...)

ASH basics

Introduction

Active session history (ASH), available since 10g, is one of the greatest performance troubleshooting tools available for Oracle databases. And one of most underutilized, too. In this article I will give some basic guidelines regarding ASH usage, and will list some of my favorite ASH queries.

ASH is a (more...)

Global Hints

Occasionally I encounter a situation when I need to affect a part of the plan that corresponds to a view, e.g.:

select *
from
(
   select v.x, x.y
   from v
) q
where q.x = 1

Such situations are resolved using global hints. Oracle offers (more...)

Tuning Analytic Functions

In general, tuning analytic functions (and more generally, all sort operations) is rather difficult. While for most poorly performing queries it’s relatively straightforward to gain some improvements  by applying “eliminate early” principle one way or another, for slow sort operations it’s rarely applicable. Usually options are limiting to rewriting a (more...)

“Snapshot too old” as a performance issue in disguise

There exists a fair amount of confusion around one of the most famous Oracle errors — ORA-01555 (“snapshot too old”). The difficulty is probably coming from the fact that ORA-01555 is related to internal workings of undo generation and usage in an Oracle database, which a rather complex. Because of that, when this error on their system, inexperienced DBAs and developers often either start looking at UNDO-related parameters, rollback segment sizes, etc., or start looking for any suspicious activity, which they later link to excessive undo “consumption” or “generation”.

In many cases, however, things are much simpler. ORA-01555 is (more...)