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.

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:

• Cost - provides the anticipated number of rows, size in MB, and associated cost of each execution step;
• Distribution - query distribution information is useful for tuning Parallel queries;
• Predicates - provides predicate info in the bottom part of the form. Click on the step of the execution plan you are interested; the bottom window automatically scrolls to show predicate info in the visible area;
• Projections - lists columns which data will be collected and carried over to the next execution step;
• Outl+Bind - shows Outline data and Peeked bind variables extracted from OTHER_XML column; (see also Peeked Bind Variables decoded from BIND_DATA column)
• QB Name - shows Query Block names. These names are valuable when you need to specify hints for a specific block;
• Exec Stats - select this option if data from SQL Area v$sql view should be presented for the examined SQL.
• Pipeline - alternative view of plan showing pipelined operations in a linear fashion. See more about this mode in the article Alternative "Pipelined" Presentation of the Query Execution Plan;
• Wrap SQL Text - select this option to wrap SQL text with long lines.
• Exec Stats - select this option to show actual execution statistics (if available) on each step of execution. See also "SQL Plan Statistics" section below.
• Last Execution Only - only last execution statistics will be presented;
• Expense Path By Elapsed Time - Highlights with yellow color steps having largest contribution in total Elapsed time. It makes sense to examine highlighted steps first because this is where execution spent most of the time;
• Expense Path By CR Gets - Highlights with yellow color steps having largest contribution in total Gets. If you are trying to reduce CPU usage by this query, it makes sense to examine highlighted steps first because this is where execution cumulated most of gets. Gets usually well correlate to the CPU usage;
• Workarea - shows workarea memory usage statistics.

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:

• If the step in the Plan tree is related to the table or index, you can get more details on the object by right-clicking on the desired line and selecting "Report" menu item.
• Text from the Plan tree view can be copied onto the clipboard by right-clicking on the desired line and selecting "Open in Editor" menu item. Then select what you need and copy into the clipboard.

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):

• Ela(cum) - Elapsed time in seconds cumulated so far including this step (v$sql_plan_statistics.LAST_ELAPSED_TIME);
• Ela(this step) - Elapsed time of this step in percentage of total execution time (delta LAST_ELAPSED_TIME / total_elapsed);
• Starts - Number of times this operation has been started (v$sql_plan_statistics.LAST_STARTS);
• Rows - Number of rows produced by the row source per a single operation start (LAST_OUTPUT_ROWS / LAST_STARTS);
• Gets(CR) - Number of buffers retrieved in consistent mode, per a single operation start (LAST_CR_BUFFER_GETS / LAST_STARTS);
• Gets(CU) - Number of buffers retrieved in consistent mode, per a single operation start (LAST_CU_BUFFER_GETS / LAST_STARTS);
• Reads - Number of physical disk reads performed by the operation per a single operation start (LAST_DISK_READS / LAST_STARTS);

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 - Type of operation using the work area; in the example above - SORT operation. (v$sql_workarea.OPERATION_TYPE);
• Policy - Sizing policy for this work area MANUAL or AUTO; in the example the policy is AUTO. (v$sql_workarea.POLICY);
• Est Mem Opti - Estimated size required by this work area to execute the operation completely in memory (optimal execution). Derived from either optimizer statistics or previous executions. (v$sql_workarea.ESTIMATED_OPTIMAL_SIZE);
• 1pass - Estimated size required by this work area to execute the operation in a single pass. (v$sql_workarea.ESTIMATED_ONEPASS_SIZE);
• Last Used - For last execution of the cursor: Memory used by this work area (72KB); Degree of parallelism of this operation (4); Whether this work area was using OPTIMAL, ONE PASS, or MULTI-PASS mode - OPTIMAL in the example. v$sql_workarea.LAST_MEMORY_USED, LAST_DEGREE, LAST_EXECUTION);
• O+1+M - Number of times this work area ran in optimal mode (44) + One-pass (0) + Multi-pass (0) = Total executions (44). (v$sql_workarea.OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS, TOTAL_EXECUTIONS);
• AvgTime(s) - Average time this work area was active (1.36 sec). (v$sql_workarea.ACTIVE_TIME);
• Last/Max TempSeg - Temporary segment size created in the last instantiation of this work area / Maximum temporary segment size created by an instantiation of this work area. This info is not shown if this work area did not spill to disk. (v$sql_workarea.LAST_TEMPSEG_SIZE, MAX_TEMPSEG_SIZE).

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:

• When examining plan statistics, pay attention to how many rows were actually returned in the operation and compare with the estimated number of rows. If this discrepancy is significant, you may investigate what caused the Oracle optimizer to miscalculate the cardinality. You may also check statistics on the table/index to find if they are fresh.
• If you find an operation with a large spike in number of rows produced, try to re-arrange the query by pushing the most selective operations ahead of the problem operation.
• Pay attention to the number of operation starts. If you see full scans (TABLE ACCESS - full) with starts >1 per execution, this is a red flag. Same may be true for the HASH JOIN, MERGE JOIN, SORT, and HASH operations.