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

Explain Plan

The Explain Plan window queries either the v$sql_plan view or the Plan table and presents data in a tree view. When this window is called from the Session Details, Activity Explorer, SQL Area etc, it is provided with the SQL address, hash value, SQL ID (10g), plan hash value (if available), cursor child number (if available) from the calling window. In that case, it takes the plan from the v$sql_plan using supplied values. Once the SQL text is edited in this window, it is not represented in the SQL area anymore. The SQL statement is then explained through the "explain plan" command using Plan table. The source of the plan is indicated in the upper part of this window.

Single session history

Controls and Viewing options.

The "Current schema" edit box defines a schema name that will be used for table names where a schema is not indicated. Note: you may not be able to get the Plan if the Oracle account used by Lab128 does not have corresponding privileges to the database objects involved.

The list of child cursors associated with this parent cursor can be seen and selected from drop-down list. When available, SQL plan hash value is also shown in this list. Just a reminder: Oracle calculates Plan Hash value with the hash function taken from the execution plan; if execution plans are the same then hash values are the same. Roughly, Plan Hash value can be thought of as execution plan's ID.

The check boxes at the top customize the level of details shown in this form:

In some cases, the Explain Plan can be used to see if the SQL statement is executable. If SQL has problems, an Oracle error will be presented and the editor cursor will be placed at the problem spot. If SQL is OK, you will see an execution plan.

Few hints that may be useful:

SQL Plan Statistics.

The SQL plan statistics view v$sql_plan_statistics provides the actual statistics for each operation in the plan. In some cases this info can be a good alternative to 10046 traces. By default, Oracle doesn't gather plan statistics. You can enable plan statistics at a system or session level. Beware of high CPU load caused by this option. You probably shouldn't enable plan statistics at system level on a busy OLTP system or on a system with a high rate of SQL executions. Enabling this feature at a session level has a much smaller effect on the measured system. Unfortunately, it is not possible to set this parameter in someone's session; this is a big limitation of this method. In order to enable SQL plan statistics:

at system level: alter system set statistics_level=all;
at session level: alter session set statistics_level=all;

Interpreting Plan statistics. Once the SQL plan statistics option has been enabled and the queries have executed, the v$sql_plan_statistics view should contain data. It provides two sets of values: summed values for all executions and values for the last execution. We recommend using "Last Execution Only" check box to show only one set and simplify the output. Once you get familiar with presented data, you can also include total values by unchecking the box. Make sure the "Plan Stats" box is checked; otherwise no data will be shown. Here is an example of data for one operation in the plan (only statistics part shown; last execution only):

step statistics: Ela(cum),s: 10.3 Ela(this step): 56%; Starts: 1; Rows: 853,740; Gets(CR): 44,221 this step: 0%; Gets(CU): 2; Reads: 5


Note that statistics are shown only if they have non-zero values. Depending on the query, you may see more statistics. When percentage (elapsed time, for example) exceeds 10%, it is shown in red. When it exceeds 50%, it is shown in bold red. This helps to find the most expensive steps.

See also "SQL Plan Statistics, Pstack examples" for a practical example how to troubleshoot SQL and interpret data shown in the plan.

Interpreting Workarea Memory Usage Statistics. Execution of some queries requires large memory allocations. The memory usage statistics is reported in v$sql_workarea view. To show this data check the "Workarea" box described above. Here is an example of workarea usage:

Workarea Op/Policy: SORT/AUTO; Est Mem Opti/1pass: 72.0 KB/72.0 KB; Last Used: 72.0 KB*4/OPTIMAL; O+1+M: 44+0+0=44; AvgTime(s): 1.36;


Plan Statistics - Expense Path.

The path from the root node of the Plan tree to the expensive steps is highlighted with yellow. This is helpful when there are many UNION ALL operations in the query - the path shows which subquery is most expensive. Here is explanation how it works. From programming point of view, each node in the tree is roughly a function. The root node is in the upper left corner, it corresponds to a function which calls all other lower level functions. The low-level functions can call even lower level functions. This goes on until they finish their work and return. The execution finally returns to the root function. Once a single function finishes, it adds its execution time and other counters to the total values of the corresponding node. The parent node values include their child nodes values. For example, if function A calls function B; function B spends 6 seconds and returns; function A has its own 2 seconds. Then node B will have 6 seconds of elapsed time, and node A - 8 seconds. The plan statistics reported in v$sql_plan_statistics contain these cumulative numbers. Knowing the cumulative elapsed time, we can traverse nodes and find a path which was contributing the most to the execution time. Alternatively, number of gets can be used instead of elapsed time. Use "Expense Path by CR Gets" check box described above for that option,

Some hints on SQL plan statistics that can be useful: