Execution Plans – What is the Plan, and Where Do I Find It 2

Display the actual (TYPICAL) execution plan for a SQL statement with estimated cardinality and costs.

Display the actual (ALLSTATS LAST) execution plan for a SQL statement using the GATHER_PLAN_STATISTICS hint to compare the estimated cardinality with the actual number of rows returned from each operation in the plan, and obtain the actual final execution time (timing of the intermediate steps might not be close to actual time).

Display the actual (ALLSTATS LAST) execution plan for a SQL statement with the STATISTICS_LEVEL parameter set to ALL at the session level to obtain the final execution time and close estimates for each of the intermediate steps (in some cases, a significant increase in execution time was experienced).

Compare the changes in the actual execution plan when changing the OPTIMIZER_MODE parameter between ALL_ROWS, FIRST_ROWS_1, and CHOOSE. Note that setting the OPTIMIZER_MODE to FIRST_ROWS_1 may have caused the optimizer to under-estimate the number of rows that will be returned, as shown in the execution plan.

Display a possibly correct execution plan with SQL*Plus’ AUTOTRACE and with EXPLAIN PLAN, and explore some of the problems that might be encountered when relying on those plans.

See the effects of bind peeking on an execution plan.

Retrieve the execution plan by setting event 10132.

Read the execution plan directly from a 10046 trace file.

Process the trace file with TKPROF, and one of the potential problems caused by telling TKPROF to generate explain plans.

So, what is left? Well, the previous blog article in this series used the nearly four year old Oracle Database 10.2.0.2. It might be interesting to look at Oracle Database 11.2.0.1, but more specifically using Statspack in that Oracle release to capture the execution plans. If you read the documentation for Oracle Database, you might be lead to believe that Statspack disappeared with Oracle 10.1.0.1 and was replaced with AWR reports (using AWR requires an additional cost license, while using Statspack does not). Statspack is still very much alive in Oracle Database 11.2.0.1, and has actually improved from what was offered in earlier releases. A Statspack snapshot captured at level 6 or greater will capture excution plans – not necessarily all execution plans for all SQL statement (the criteria for what is captured can be adjusted), but quite a few with the default settings. This blog article will use the tables that were created in the previous blog article to retrieve execution plans captured by Statspack

We will need two SQL*Plus sessions, the first connected as the PERFSTAT user (the owner of Statspack data), and the second connected as the owner of the tables created in the previous blog article. The PERFSTAT user will execute the following script, which will collect a Statspack level 7 snapshot every 5 minutes (there is no sleep command on Windows, but it is possible to create a VBS script to perform the same task on the Windows platform, as happens when the sleep command is called on Unix/Linux – see chapter 8 of the “Expert Oracle Practices” book for the VBS script):

The above script changes the OPTIMIZER_MODE and executes the same SQL statement with different bind variable values. Once a couple of Statspack snapshots have been collected, we can see if any of the SQL statements in the Statspack snapshots have different execution plans with a SQL statement like the following (note that it probably would be wise to specify a range of Statspack snapshots, rather than looking at all snapshots):

Interesting – we have a couple of SQL statements with 2 execution plans, one with 3 execution plans, and two with 4 execution plans. Our SQL statement did not constrain the time periods to specific Statspack snapshots, so some of those execution plans may be very old. Let’s take a look at the execution plans for one of those SQL statements (note that we are excluding the Predicate Information section and the Note section from the execution plans since those sections are not captured in a Statspack snapshot):

Well, that is not the execution plan for the SQL statement that we executed, but it is interesting that one of Oracle’s internal SQL statements generated 4 different execution plans (one of which appears to have been created with the RULE optimizer mode, indicated by the abscence of the Cost column). Let’s try again (note that the SQL_ID of interest was output in the DBMS_XPLAN output, but assume that we did not have that SQL_ID and did not want to query V$SQLAREA or V$SQL):

That looks like the execution plans for our query, but were there only 4 different execution plans for our query (adaptive cursor sharing should have taken effect)? Well, there were more than 4 child cursors created, but only 4 distinct execution plans. Interesting. In the above (reading from the top plan), we have the following join operations:

Nested Loops (between a table and an index for another table), Nested Loops, Hash Join

Sort – Merge Join, Hash Join

Nested Loops, Nested Loops, Nested Loops

Hash Join, Hash Join

The cost-based optimizer is quite clever. OK, it is neat that Statspack is able to capture the execution plans, but without the predicate information, the execution plan information is a bit limited. So, let’s take a look at the DBMS_XPLAN output that was captured during the run of our script, outputting the plan only when a new execution plan (or child cursor) is created:

Notice that the Note section states that dynamic sampling at level 2 was performed – it does not state that it was only performed on table T2 (because statistics were not collected on that table).

It might be fun to take a look at V$SQL_SHARED_CURSOR for this SQL_ID. The columns in that view tell why a child cursor was created, the following was generated with the DESC V$SQL_SHARED_CURSOR command:

Oracle Database 11.2.0.1 certainly has a lot of potential reasons established for creating additional child cursors. So, what is the reason for the child cursors in our test (at least the ones that are still in the library cache after executing the script several times)?

Actions

Information

One response

[…] plans Charles Hooper-Execution Plans – What is the Plan, and Where Do I Find It? Charles Hooper-Execution Plans – What is the Plan, and Where Do I Find It 2? Charles Hooper-Execution Plans – What is the Plan, and Where Do I Find It 3? 20-Faulty quotes […]

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: