Where AWR can’t help: identifying top-SQL in absence of cursor sharing

Many database performance problems can be resolved via a top-down approach: you look at database performance globally (using statspack or AWR report) and then you work your way down to specific SQL consuming most of resources. This approach breaks when the application is not using bind variables. The reason for that is that performance reports depend on sql_id to identify a statement, and in absence of cursor sharing a single SQL statement has as many sql_id’s as there are combinations of literal parameters. So if your heaviest query is something like


SELECT * FROM T1 WHERE X=:B1,

then you’ll see in in AWR top-SQL lists, but if it’s

SELECT * FROM T1 WHERE X="some literal value",

then at best you’ll see a bunch of similar statements in the lists, each responsible for a small fraction of the workload. There is also a good chance that your heaviest SQL won’t show up in those lists at all, if it’s overshadowed by frequently run statements with unchanging parameters (or using binds).

Continue reading “Where AWR can’t help: identifying top-SQL in absence of cursor sharing” →

Histograms for strongly skewed columns

On a recent OTN thread, I learned a nice trick by J. Lewis that allows to circumvent certain problems with histograms.

Histograms were designed to solve the problem of estimating cardinality for skewed columns (i.e. where some values occur much more frequently than the others). For columns with low number of distinct values (NDV) Oracle collects a frequency histogram, which can be thought of as a set of two one-dimensional arrays: one containing all possible values, the other containing their frequency (i.e. how many rows have this value). However, if sample size is small, then Oracle can miss rare values, and they won’t be reflected in the histogram. As a result, the cardinality estimates for those values will be wrong (depending on version Oracle will either set it to either 1 or to half of the frequency for the rarest value found). A detailed explanation of the issues with examples can be found in blog posts by J. Lewis and R. Geist.

Continue reading “Histograms for strongly skewed columns” →

DBMS_XPLAN.DISPLAY_CURSOR

In this post, I continue on the topic of examining SQL plans. I will talk about one DBMS_XPLAN function, DISPLAY_CURSOR (because it’s probably the most useful one when troubleshooting ongoing performance issues, and also because other functions have a lot of similiarity to it). I will discuss frequently used options and some common problems.

Preparation

As already mentioned in my previous post on the subject, using DBMS_XPLAN to display rowsource stats requires a bit of preparation. Namely, one needs to either set STATISTICS_LEVEL parameter to ALL (can be done on the session level), or use gather_plan_statistics hint in the query, and then run the query.

Usage DBMS_XPLAN.display_cursor

Once the statement is executed, the plan with row source statistics can be obtained in a convenient format using DBMS_XPLAN.display_cursor.

DBMS_XPLAN.display_cursor takes three parameters, all of which are optional:

Continue reading “DBMS_XPLAN.DISPLAY_CURSOR” →