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:
sql_id — id of the statement whose plan is to be displayed. The default value is null, which means that the plan for the last statement in the current session will be displayed.
cursor_child_no — the number of the child cursor. The default value is 0
format — how much detail to display. The default value is ‘TYPICAL’.
I won’t go into details of all parameters (you can find a good description in a great post by Charles Hoopers or in online official Oracle documentation), I’ll just list a few most useful parameter values.
Very often, DBMS_XPLAN can be used like this:
SELECT * FROM TABLE(dbms_xplan.display(NULL, NULL, format=>'allstats last'));
which displays execution plans with I/O and memory statistics for all children of the last statement executed by the current session. This normally provides sufficient level of detail to troubleshoot a query. The “last” parameter makes sure that the stats will be displayed for the last execution only (the default value is to display cumulative stats for all executions).
In some cases, you may be unable to examine DBMS_XPLAN output immediately upon the completion of the SQL of interest. In this case you’ll need to specify sql_id of the statement, which you can look up in V$SQL:
SELECT sql_id FROM V$SQL WHERE sql_text like <pattern>,
where <pattern> is a part of the query text that uniquely identifies it. It’s convenient to use comments to label queries, e.g.:
SELECT /* myquery */ col1, col2, col3 FROM tab1
which makes it straightforward to find the query’s sql_id:
SELECT sql_id FROM V$SQL WHERE sql_text like 'SELECT /* myquery */%'
If memory and tempspace stats are of no interest, they can be omitted by replacing “allstats” with “iostats”.
By default, when rowsource stats are enabled (i.e. “iostats” or “allstats” format), partitioning information and costing is not displayed, but this can be changed by adding “partition” or “cost” switches to the format description, e.g.
SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'iostats partition cost'))
Occasionally, you may find useful the “peeked_binds” option which will show the value of peeked bind variables, if any. If you are interested in seeing what columns are picked from each table or index, you can do that using the “projection” option.
An option that can be very handy at times, is “advanced”, because it generates a set of hint that ensures that exactly that plan will be generated. E.g. if you are trying to understand why a SQL statement is behaving differently on different databases, an outline would help to force a certain plan that won’t be generated naturally, e.g. to see how it’s costed by the optimizer (or you can just use a stored outline to permanently lock down the desired plan).
Common problems producing the right DBMS_XPLAN output
1) Wrong timings
Sometimes, DBMS_XPLAN.display_cursor can show wrong values in A-time: the total can disagree with the actual waiting time, or for some parent operations elapsed time can be lower than the sum of elapsed_ time of the child operations etc. This can happen because of insufficient sampling frequency when using gather_plan_statistics hint. If that’s the case, one can fix the problem either by using ALTER SESSION SET STATISTICS_LEVEL=ALL instead of the hint or using _rowsource_statistics_sampfreq hidden parameter (see Jonathan Lewis’ post for details).
2) Rowsource stats are not displayed
First, make sure that you are looking at the right plan: scroll it down all the way. Sometimes, one can get confused by another child cursor (without rowsource stats). If that’s not the case, make sure that the hint is spelled correctly. Another possibility is that ALTER SESSION SET STATISTICS_LEVEL=ALL may have not worked properly if run from a GUI (like Toad). Sqlplus is more reliable for this.
3) Big performance impact of rowsource sampling
In most cases, rowsource sampling shouldn’t add a significant performance overhead (in my experience, the effect is normally within 10-15%). If it’s larger than this, then one can trying decreasing sampling frequency using _rowsource_statistics_sampfreq parameter, but before doing that it is a good idea to double-check that the difference in performance is really due to rowsource sampling and not something else. For example, it’s possible that the difference could be due to the fact that the query is run in a different client with different behavior and/or settings (e.g. the client may not fetch all rows or can fetch them in smaller portions because of different value of array_size parameter).
Oracle Diagnostician 