Contents Lab128 - Tools for Advanced Oracler Tuning and Monitoring. Reference Guide.

SQL Plan Statistics

Plan statistics have tremendous value and are potentially a good alternative to 10046 traces. SQL Plan Statistics can be seen from v$sql_plan_statistics view. Oracle doesn't collect plan statistics until the 'statistics_level' parameter is changed to ALL. This parameter can be changed at a system or session level. Also, the /*+ gather_plan_statistics */ hint will collect plan statistics for a specific statement. Typically, it is prohibitively expensive to set "statistics_level=all" at a system level because there is high CPU overhead in this mode. But it is OK to set this parameter at a session level and execute specific SQL statement in this mode, even in a production environment. Lab128 can set this parameter for you in the session opened by SLQ Central. Of course, executing SQL statement with the hint is also a good practice but will require you to manually put the hint into SQL text.

Executing query with SQL plan statistics collection enabled.

There is a "Enable Plan Stats" check box in the lower data result section of SQL Central. Then checked, it will execute "alter session set statistics_level=all". If unchecked, the "alter session set statistics_level=typical" will be executed, disabling plan statistics collection.

Once Plan Statistics collection is enabled, simply execute the SQL statement you need to troubleshoot. After it finished, click on the "Last SQL" active link to see the execution plan with plan statistics overlaid. When called this way, the Explain Plan window has the "Plan Stats" check box automatically checked. Please see Explain Plan for more details about available options.

If you use gather_plan_statistics hint to collect plan statistics, open Explain Plan window the same way by clicking on "Last SQL" link.

Examining SQL execution plan statistics.

Now you can examine the plan statistics. For a quick reference, see the Interpreting plan statistics section of the Explain Plan. For a more detailed discussion, see "SQL Plan Statistics, Pstack examples". After become familiar with plan statistics, it should be absolutely clear where the problem is. No guess work, it's all based on numbers.

Troubleshooting "running forever" queries.

This technique can be used for troubleshooting "running forever" queries. Run the SQL statement for some time, and then simply stop the query by pressing the "Cancel" button. The beauty of plan statistics is that they are reported even after interrupted execution. In this case, the numbers reported are the numbers collected thus far before the query was interrupted. Even 10-15 seconds of execution can be enough for the query to get to the steps where it was running for a very long time and they can easily be identified.

Note. In "statistics_level=all" mode SQL statements are executed 5-8 times longer due to the collection overhead and high CPU usage. This actually helps for short queries as it increases elapsed time and makes it easier to track down where time was spent. But for long queries it can be a problem. Fortunately, you can cancel execution and still get very actionable information (see previous paragraph). Because it takes longer to execute, you should not time or benchmark queries in this mode; therefore uncheck the "Enable Plan Stats" box before benchmarking. Also when you are running a script or executing a query, which you are not interested in troubleshooting, uncheck this box for faster execution.