February 1, 2010
(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)
In the earlier blog article, we saw how to:
- 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):
EXEC STATSPACK.SNAP(i_snap_level=>7)
host sleep 300
EXEC STATSPACK.SNAP(i_snap_level=>7)
host sleep 300
EXEC STATSPACK.SNAP(i_snap_level=>7)
host sleep 300
EXEC STATSPACK.SNAP(i_snap_level=>7)
While the PERSTAT user is running the above script, the other session will run the following script several times (at least 2 times per Statspack capture):
SPOOL BindTestPlans.txt
SET AUTOTRACE OFF
ALTER SESSION SET STATISTICS_LEVEL='ALL';
ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS';
ALTER SYSTEM FLUSH SHARED_POOL;
VARIABLE N1 NUMBER
VARIABLE N2 NUMBER
EXEC :N1:=1
EXEC :N2:=1
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
EXEC :N2:=100
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
EXEC :N2:=1000
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
EXEC :N2:=10000
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
EXEC :N2:=100000
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS_1';
VARIABLE N1 NUMBER
VARIABLE N2 NUMBER
EXEC :N1:=1
EXEC :N2:=1
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
EXEC :N2:=100
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
EXEC :N2:=1000
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
EXEC :N2:=10000
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
EXEC :N2:=100000
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS_10';
VARIABLE N1 NUMBER
VARIABLE N2 NUMBER
EXEC :N1:=1
EXEC :N2:=1
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
EXEC :N2:=100
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
EXEC :N2:=1000
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
EXEC :N2:=10000
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
EXEC :N2:=100000
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS_1000';
VARIABLE N1 NUMBER
VARIABLE N2 NUMBER
EXEC :N1:=1
EXEC :N2:=1
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
EXEC :N2:=100
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
EXEC :N2:=1000
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
EXEC :N2:=10000
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
EXEC :N2:=100000
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
T3.C1,
SUBSTR(T1.C2,1,10) T1_C2,
SUBSTR(T2.C2,1,10) T2_C2
FROM
T3,
T2,
T1
WHERE
T1.C1=T3.C1
AND T1.C1=T2.C1
AND T1.C1 BETWEEN :N1 AND :N2;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
SPOOL OFF
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):
SPOOL MultiplePlans.txt
SELECT
SQL_ID,
COUNT(DISTINCT PLAN_HASH_VALUE) C
FROM
PERFSTAT.STATS$SQL_PLAN_USAGE
GROUP BY
SQL_ID
HAVING
COUNT(DISTINCT PLAN_HASH_VALUE)>1;
SPOOL OFF
The above SQL statement produced the following output:
SQL_ID C
------------- ----------
asc81p5ttbyf4 2
94bv1jwkzcc38 2
77z8bh7b87r1s 4
ga9j9xk5cy9s0 2
ddnbt67ftu9ds 2
0fr8zhn4ymu3v 2
cvn54b7yz0s8u 2
gjm43un5cy843 2
ff9zaqp3qfnah 2
gx4mv66pvj3xz 2
cvq22z77c8fww 3
a13qjgdbjcurr 2
1gu8t96d0bdmu 2
2x7j98pyk8ggm 2
8p3pacrbngv80 2
c6awqs517jpj0 2
cds8ys5zwkrpk 2
0u02w6ryqkg66 2
39m4sx9k63ba2 2
7ng34ruy5awxq 4
9dq71tc7vasgu 2
53saa2zkr6wc3 2
bsa0wjtftg3uw 2
cn5wthk738x0x 2
f3g84j69n0tjh 2
9nv4n7b54ph9a 2
0g5sb3zpqzss8 2
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):
SET LINESIZE 150
SET PAGESIZE 10000
SPOOL StatspackPlan.txt
SELECT /*+ ORDERED */
T.*
FROM
(SELECT DISTINCT
PLAN_HASH_VALUE
FROM
PERFSTAT.STATS$SQL_PLAN_USAGE
WHERE
SQL_ID='7ng34ruy5awxq'
ORDER BY
PLAN_HASH_VALUE) SPU,
TABLE(DBMS_XPLAN.DISPLAY(
'PERFSTAT.STATS$SQL_PLAN',
NULL,
'TYPICAL -PREDICATE -NOTE',
'PLAN_HASH_VALUE='||SPU.PLAN_HASH_VALUE)) T;
SPOOL OFF
The output for the above SQL statement looks like this:
------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN OUTER | |
| 2 | SORT JOIN | |
| 3 | NESTED LOOPS OUTER | |
| 4 | TABLE ACCESS CLUSTER | IND$ |
| 5 | INDEX UNIQUE SCAN | I_OBJ# |
| 6 | TABLE ACCESS BY INDEX ROWID| IND_STATS$ |
| 7 | INDEX UNIQUE SCAN | I_IND_STATS$_OBJ# |
| 8 | SORT JOIN | |
| 9 | VIEW | |
| 10 | SORT GROUP BY | |
| 11 | TABLE ACCESS CLUSTER | CDEF$ |
| 12 | INDEX UNIQUE SCAN | I_COBJ# |
------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | SORT ORDER BY | | 2 | 374 | 7 (43)| 00:00:01 |
| 2 | HASH JOIN OUTER | | 2 | 374 | 6 (34)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 2 | 288 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS CLUSTER | IND$ | 2 | 184 | 2 (0)| 00:00:01 |
| 5 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| IND_STATS$ | 1 | 52 | 0 (0)| |
| 7 | INDEX UNIQUE SCAN | I_IND_STATS$_OBJ# | 1 | | 0 (0)| |
| 8 | VIEW | | 1 | 43 | 3 (34)| 00:00:01 |
| 9 | SORT GROUP BY | | 1 | 15 | 3 (34)| 00:00:01 |
| 10 | TABLE ACCESS CLUSTER | CDEF$ | 1 | 15 | 2 (0)| 00:00:01 |
| 11 | INDEX UNIQUE SCAN | I_COBJ# | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | SORT GROUP BY | | 2 | 342 | 5 (20)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 2 | 342 | 4 (0)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 2 | 312 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS CLUSTER | IND$ | 2 | 184 | 2 (0)| 00:00:01 |
| 5 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| IND_STATS$ | 1 | 64 | 0 (0)| |
| 7 | INDEX UNIQUE SCAN | I_IND_STATS$_OBJ# | 1 | | 0 (0)| |
| 8 | TABLE ACCESS CLUSTER | CDEF$ | 1 | 15 | 1 (0)| 00:00:01 |
| 9 | INDEX UNIQUE SCAN | I_COBJ# | 1 | | 0 (0)| |
----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | SORT ORDER BY | | 2 | 374 | 7 (43)| 00:00:01 |
| 2 | HASH JOIN OUTER | | 2 | 374 | 6 (34)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 2 | 288 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS CLUSTER | IND$ | 2 | 184 | 2 (0)| 00:00:01 |
| 5 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | IND_STATS$ | 1 | 52 | 0 (0)| |
| 7 | INDEX UNIQUE SCAN | I_IND_STATS$_OBJ# | 1 | | 0 (0)| |
| 8 | VIEW | | 1 | 43 | 3 (34)| 00:00:01 |
| 9 | SORT GROUP BY | | 1 | 15 | 3 (34)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| CDEF$ | 1 | 15 | 2 (0)| 00:00:01 |
| 11 | INDEX RANGE SCAN | I_CDEF2 | 4 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
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):
SET LINESIZE 150
SET PAGESIZE 10000
SPOOL StatspackPlan.txt
SELECT /*+ ORDERED */
T.*
FROM
(SELECT DISTINCT
PLAN_HASH_VALUE
FROM
PERFSTAT.STATS$SQL_PLAN_USAGE
WHERE
SQL_ID='77z8bh7b87r1s'
ORDER BY
PLAN_HASH_VALUE) SPU,
TABLE(DBMS_XPLAN.DISPLAY(
'PERFSTAT.STATS$SQL_PLAN',
NULL,
'TYPICAL -PREDICATE -NOTE',
'PLAN_HASH_VALUE='||SPU.PLAN_HASH_VALUE)) T;
SPOOL OFF
The above output the following:
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1257 (100)| |
| 1 | FILTER | | | | | |
| 2 | HASH JOIN | | 13 | 8762 | 1257 (1)| 00:00:16 |
| 3 | TABLE ACCESS BY INDEX ROWID | T2 | 1000 | 138K| 4 (0)| 00:00:01 |
| 4 | INDEX RANGE SCAN | SYS_C0017308 | 1000 | | 3 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | | | | |
| 6 | NESTED LOOPS | | 13 | 3458 | 1253 (1)| 00:00:16 |
| 7 | TABLE ACCESS FULL | T3 | 1000 | 5000 | 1227 (1)| 00:00:15 |
| 8 | INDEX UNIQUE SCAN | SYS_C0017307 | 1 | | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 261 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 15215 (100)| |
| 1 | FILTER | | | | | | |
| 2 | HASH JOIN | | 32260 | 12M| | 15215 (1)| 00:03:03 |
| 3 | TABLE ACCESS FULL | T3 | 100K| 488K| | 10214 (1)| 00:02:03 |
| 4 | MERGE JOIN | | 32260 | 12M| | 4999 (1)| 00:01:00 |
| 5 | TABLE ACCESS BY INDEX ROWID | T1 | 100K| 24M| | 3897 (1)| 00:00:47 |
| 6 | INDEX RANGE SCAN | SYS_C0017307 | 100K| | | 191 (1)| 00:00:03 |
| 7 | SORT JOIN | | 32260 | 4473K| 9752K| 1103 (1)| 00:00:14 |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 32260 | 4473K| | 80 (0)| 00:00:01 |
| 9 | INDEX RANGE SCAN | SYS_C0017308 | 32260 | | | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10217 (100)| |
| 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 12 | 4896 | 10217 (1)| 00:02:03 |
| 4 | NESTED LOOPS | | 12 | 3192 | 10216 (1)| 00:02:03 |
| 5 | TABLE ACCESS FULL | T3 | 100K| 488K| 10214 (1)| 00:02:03 |
| 6 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 261 | 2 (0)| 00:00:01 |
| 7 | INDEX UNIQUE SCAN | SYS_C0017307 | 1 | | 1 (0)| 00:00:01 |
| 8 | INDEX UNIQUE SCAN | SYS_C0017308 | 1 | | 0 (0)| |
| 9 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 142 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 10897 (100)| |
| 1 | FILTER | | | | | | |
| 2 | HASH JOIN | | 9998 | 3983K| | 10897 (1)| 00:02:11 |
| 3 | TABLE ACCESS FULL | T3 | 10000 | 50000 | | 10214 (1)| 00:02:03 |
| 4 | HASH JOIN | | 9999 | 3935K| 2432K| 682 (1)| 00:00:09 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 16130 | 2236K| | 41 (0)| 00:00:01 |
| 6 | INDEX RANGE SCAN | SYS_C0017308 | 16130 | | | 29 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| T1 | 10000 | 2548K| | 392 (0)| 00:00:05 |
| 8 | INDEX RANGE SCAN | SYS_C0017307 | 10000 | | | 21 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
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:
SQL_ID 77z8bh7b87r1s, child number 0
-------------------------------------
SELECT T3.C1, SUBSTR(T1.C2,1,10) T1_C2, SUBSTR(T2.C2,1,10) T2_C2
FROM T3, T2, T1 WHERE T1.C1=T3.C1 AND T1.C1=T2.C1 AND T1.C1
BETWEEN :N1 AND :N2
Plan hash value: 1056058413
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.08 | 37173 |
|* 1 | FILTER | | 1 | | 1 |00:00:00.08 | 37173 |
| 2 | NESTED LOOPS | | 1 | | 1 |00:00:00.08 | 37173 |
| 3 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.08 | 37172 |
| 4 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.08 | 37169 |
|* 5 | TABLE ACCESS FULL | T3 | 1 | 1 | 1 |00:00:00.08 | 37165 |
| 6 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 4 |
|* 7 | INDEX UNIQUE SCAN | SYS_C0017307 | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 8 | INDEX UNIQUE SCAN | SYS_C0017308 | 1 | 1 | 1 |00:00:00.01 | 3 |
| 9 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 1 |00:00:00.01 | 1 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
5 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
7 - access("T1"."C1"="T3"."C1")
filter(("T1"."C1"<=:N2 AND "T1"."C1">=:N1))
8 - access("T1"."C1"="T2"."C1")
filter(("T2"."C1">=:N1 AND "T2"."C1"<=:N2))
Note
-----
- dynamic sampling used for this statement (level=2)
-
SQL_ID 77z8bh7b87r1s, child number 1
-------------------------------------
SELECT T3.C1, SUBSTR(T1.C2,1,10) T1_C2, SUBSTR(T2.C2,1,10) T2_C2
FROM T3, T2, T1 WHERE T1.C1=T3.C1 AND T1.C1=T2.C1 AND T1.C1
BETWEEN :N1 AND :N2
Plan hash value: 2222254696
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.08 | 37382 | | | |
|* 1 | FILTER | | 1 | | 1000 |00:00:00.08 | 37382 | | | |
|* 2 | HASH JOIN | | 1 | 998 | 1000 |00:00:00.08 | 37382 | 1452K| 1452K| 1476K (0)|
|* 3 | TABLE ACCESS FULL | T3 | 1 | 1000 | 1000 |00:00:00.07 | 37164 | | | |
|* 4 | HASH JOIN | | 1 | 999 | 1000 |00:00:00.01 | 218 | 830K| 830K| 1277K (0)|
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1000 | 1000 |00:00:00.01 | 42 | | | |
|* 6 | INDEX RANGE SCAN | SYS_C0017308 | 1 | 1000 | 1000 |00:00:00.01 | 4 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1000 | 1000 |00:00:00.01 | 176 | | | |
|* 8 | INDEX RANGE SCAN | SYS_C0017307 | 1 | 1000 | 1000 |00:00:00.01 | 71 | | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
2 - access("T1"."C1"="T3"."C1")
3 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
4 - access("T1"."C1"="T2"."C1")
6 - access("T2"."C1">=:N1 AND "T2"."C1"<=:N2)
8 - access("T1"."C1">=:N1 AND "T1"."C1"<=:N2)
Note
-----
- dynamic sampling used for this statement (level=2)
-
SQL_ID 77z8bh7b87r1s, child number 2
-------------------------------------
SELECT T3.C1, SUBSTR(T1.C2,1,10) T1_C2, SUBSTR(T2.C2,1,10) T2_C2
FROM T3, T2, T1 WHERE T1.C1=T3.C1 AND T1.C1=T2.C1 AND T1.C1
BETWEEN :N1 AND :N2
Plan hash value: 2222254696
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10000 |00:00:00.14 | 39279 | | | |
|* 1 | FILTER | | 1 | | 10000 |00:00:00.14 | 39279 | | | |
|* 2 | HASH JOIN | | 1 | 9998 | 10000 |00:00:00.14 | 39279 | 1452K| 1452K| 1795K (0)|
|* 3 | TABLE ACCESS FULL | T3 | 1 | 10000 | 10000 |00:00:00.08 | 37164 | | | |
|* 4 | HASH JOIN | | 1 | 9999 | 10000 |00:00:00.04 | 2115 | 3436K| 948K| 3949K (0)|
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 16130 | 10000 |00:00:00.01 | 391 | | | |
|* 6 | INDEX RANGE SCAN | SYS_C0017308 | 1 | 16130 | 10000 |00:00:00.01 | 20 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10000 | 10000 |00:00:00.01 | 1724 | | | |
|* 8 | INDEX RANGE SCAN | SYS_C0017307 | 1 | 10000 | 10000 |00:00:00.01 | 686 | | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
2 - access("T1"."C1"="T3"."C1")
3 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
4 - access("T1"."C1"="T2"."C1")
6 - access("T2"."C1">=:N1 AND "T2"."C1"<=:N2)
8 - access("T1"."C1">=:N1 AND "T1"."C1"<=:N2)
Note
-----
- dynamic sampling used for this statement (level=2)
-
SQL_ID 77z8bh7b87r1s, child number 3
-------------------------------------
SELECT T3.C1, SUBSTR(T1.C2,1,10) T1_C2, SUBSTR(T2.C2,1,10) T2_C2
FROM T3, T2, T1 WHERE T1.C1=T3.C1 AND T1.C1=T2.C1 AND T1.C1
BETWEEN :N1 AND :N2
Plan hash value: 736968136
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100K|00:00:01.02 | 58272 | | | |
|* 1 | FILTER | | 1 | | 100K|00:00:01.02 | 58272 | | | |
|* 2 | HASH JOIN | | 1 | 32260 | 100K|00:00:00.97 | 58272 | 3838K| 1862K| 5510K (0)|
|* 3 | TABLE ACCESS FULL | T3 | 1 | 100K| 100K|00:00:00.09 | 37164 | | | |
| 4 | MERGE JOIN | | 1 | 32260 | 100K|00:00:00.68 | 21108 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 100K| 100K|00:00:00.22 | 17215 | | | |
|* 6 | INDEX RANGE SCAN | SYS_C0017307 | 1 | 100K| 100K|00:00:00.06 | 6844 | | | |
|* 7 | SORT JOIN | | 100K| 32260 | 100K|00:00:00.29 | 3893 | 30M| 1975K| 26M (0)|
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 32260 | 100K|00:00:00.06 | 3893 | | | |
|* 9 | INDEX RANGE SCAN | SYS_C0017308 | 1 | 32260 | 100K|00:00:00.02 | 189 | | | |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
2 - access("T1"."C1"="T3"."C1")
3 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
6 - access("T1"."C1">=:N1 AND "T1"."C1"<=:N2)
7 - access("T1"."C1"="T2"."C1")
filter("T1"."C1"="T2"."C1")
9 - access("T2"."C1">=:N1 AND "T2"."C1"<=:N2)
Note
-----
- dynamic sampling used for this statement (level=2)
-
SQL_ID 77z8bh7b87r1s, child number 4
-------------------------------------
SELECT T3.C1, SUBSTR(T1.C2,1,10) T1_C2, SUBSTR(T2.C2,1,10) T2_C2
FROM T3, T2, T1 WHERE T1.C1=T3.C1 AND T1.C1=T2.C1 AND T1.C1
BETWEEN :N1 AND :N2
Plan hash value: 1056058413
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.07 | 37411 |
|* 1 | FILTER | | 1 | | 100 |00:00:00.07 | 37411 |
| 2 | NESTED LOOPS | | 1 | | 100 |00:00:00.07 | 37411 |
| 3 | NESTED LOOPS | | 1 | 1 | 100 |00:00:00.07 | 37311 |
| 4 | NESTED LOOPS | | 1 | 1 | 100 |00:00:00.07 | 37291 |
|* 5 | TABLE ACCESS FULL | T3 | 1 | 1 | 100 |00:00:00.07 | 37171 |
| 6 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 1 | 100 |00:00:00.01 | 120 |
|* 7 | INDEX UNIQUE SCAN | SYS_C0017307 | 100 | 1 | 100 |00:00:00.01 | 20 |
|* 8 | INDEX UNIQUE SCAN | SYS_C0017308 | 100 | 1 | 100 |00:00:00.01 | 20 |
| 9 | TABLE ACCESS BY INDEX ROWID | T2 | 100 | 1 | 100 |00:00:00.01 | 100 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
5 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
7 - access("T1"."C1"="T3"."C1")
filter(("T1"."C1"<=:N2 AND "T1"."C1">=:N1))
8 - access("T1"."C1"="T2"."C1")
filter(("T2"."C1">=:N1 AND "T2"."C1"<=:N2))
Note
-----
- dynamic sampling used for this statement (level=2)
-
SQL_ID 77z8bh7b87r1s, child number 5
-------------------------------------
SELECT T3.C1, SUBSTR(T1.C2,1,10) T1_C2, SUBSTR(T2.C2,1,10) T2_C2
FROM T3, T2, T1 WHERE T1.C1=T3.C1 AND T1.C1=T2.C1 AND T1.C1
BETWEEN :N1 AND :N2
Plan hash value: 1056058413
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.08 | 39469 |
|* 1 | FILTER | | 1 | | 1000 |00:00:00.08 | 39469 |
| 2 | NESTED LOOPS | | 1 | | 1000 |00:00:00.08 | 39469 |
| 3 | NESTED LOOPS | | 1 | 4 | 1000 |00:00:00.08 | 38469 |
| 4 | NESTED LOOPS | | 1 | 4 | 1000 |00:00:00.08 | 38350 |
|* 5 | TABLE ACCESS FULL | T3 | 1 | 1000 | 1000 |00:00:00.08 | 37231 |
| 6 | TABLE ACCESS BY INDEX ROWID| T1 | 1000 | 1 | 1000 |00:00:00.01 | 1119 |
|* 7 | INDEX UNIQUE SCAN | SYS_C0017307 | 1000 | 1 | 1000 |00:00:00.01 | 119 |
|* 8 | INDEX UNIQUE SCAN | SYS_C0017308 | 1000 | 1 | 1000 |00:00:00.01 | 119 |
| 9 | TABLE ACCESS BY INDEX ROWID | T2 | 1000 | 1 | 1000 |00:00:00.01 | 1000 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
5 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
7 - access("T1"."C1"="T3"."C1")
filter(("T1"."C1"<=:N2 AND "T1"."C1">=:N1))
8 - access("T1"."C1"="T2"."C1")
filter(("T2"."C1">=:N1 AND "T2"."C1"<=:N2))
Note
-----
- dynamic sampling used for this statement (level=2)
-
SQL_ID 77z8bh7b87r1s, child number 6
-------------------------------------
SELECT T3.C1, SUBSTR(T1.C2,1,10) T1_C2, SUBSTR(T2.C2,1,10) T2_C2
FROM T3, T2, T1 WHERE T1.C1=T3.C1 AND T1.C1=T2.C1 AND T1.C1
BETWEEN :N1 AND :N2
Plan hash value: 1056058413
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10000 |00:00:00.21 | 63553 |
|* 1 | FILTER | | 1 | | 10000 |00:00:00.21 | 63553 |
| 2 | NESTED LOOPS | | 1 | | 10000 |00:00:00.20 | 63553 |
| 3 | NESTED LOOPS | | 1 | 3 | 10000 |00:00:00.17 | 53553 |
| 4 | NESTED LOOPS | | 1 | 3 | 10000 |00:00:00.14 | 50692 |
|* 5 | TABLE ACCESS FULL | T3 | 1 | 6668 | 10000 |00:00:00.09 | 37831 |
| 6 | TABLE ACCESS BY INDEX ROWID| T1 | 10000 | 1 | 10000 |00:00:00.04 | 12861 |
|* 7 | INDEX UNIQUE SCAN | SYS_C0017307 | 10000 | 1 | 10000 |00:00:00.02 | 2861 |
|* 8 | INDEX UNIQUE SCAN | SYS_C0017308 | 10000 | 1 | 10000 |00:00:00.02 | 2861 |
| 9 | TABLE ACCESS BY INDEX ROWID | T2 | 10000 | 1 | 10000 |00:00:00.02 | 10000 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
5 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
7 - access("T1"."C1"="T3"."C1")
filter(("T1"."C1"<=:N2 AND "T1"."C1">=:N1))
8 - access("T1"."C1"="T2"."C1")
filter(("T2"."C1">=:N1 AND "T2"."C1"<=:N2))
Note
-----
- dynamic sampling used for this statement (level=2)
-
SQL_ID 77z8bh7b87r1s, child number 7
-------------------------------------
SELECT T3.C1, SUBSTR(T1.C2,1,10) T1_C2, SUBSTR(T2.C2,1,10) T2_C2
FROM T3, T2, T1 WHERE T1.C1=T3.C1 AND T1.C1=T2.C1 AND T1.C1
BETWEEN :N1 AND :N2
Plan hash value: 1056058413
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100K|00:00:01.45 | 316K|
|* 1 | FILTER | | 1 | | 100K|00:00:01.45 | 316K|
| 2 | NESTED LOOPS | | 1 | | 100K|00:00:01.39 | 316K|
| 3 | NESTED LOOPS | | 1 | 3 | 100K|00:00:01.07 | 216K|
| 4 | NESTED LOOPS | | 1 | 9 | 100K|00:00:00.76 | 180K|
|* 5 | TABLE ACCESS FULL | T3 | 1 | 100K| 100K|00:00:00.16 | 43822 |
| 6 | TABLE ACCESS BY INDEX ROWID| T1 | 100K| 1 | 100K|00:00:00.46 | 136K|
|* 7 | INDEX UNIQUE SCAN | SYS_C0017307 | 100K| 1 | 100K|00:00:00.20 | 36565 |
|* 8 | INDEX UNIQUE SCAN | SYS_C0017308 | 100K| 1 | 100K|00:00:00.17 | 36565 |
| 9 | TABLE ACCESS BY INDEX ROWID | T2 | 100K| 1 | 100K|00:00:00.18 | 100K|
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
5 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
7 - access("T1"."C1"="T3"."C1")
filter(("T1"."C1"<=:N2 AND "T1"."C1">=:N1))
8 - access("T1"."C1"="T2"."C1")
filter(("T2"."C1">=:N1 AND "T2"."C1"<=:N2))
Note
-----
- dynamic sampling used for this statement (level=2)
-
SQL_ID 77z8bh7b87r1s, child number 8
-------------------------------------
SELECT T3.C1, SUBSTR(T1.C2,1,10) T1_C2, SUBSTR(T2.C2,1,10) T2_C2
FROM T3, T2, T1 WHERE T1.C1=T3.C1 AND T1.C1=T2.C1 AND T1.C1
BETWEEN :N1 AND :N2
Plan hash value: 1056058413
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.07 | 37173 |
|* 1 | FILTER | | 1 | | 1 |00:00:00.07 | 37173 |
| 2 | NESTED LOOPS | | 1 | | 1 |00:00:00.07 | 37173 |
| 3 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.07 | 37172 |
| 4 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.07 | 37169 |
|* 5 | TABLE ACCESS FULL | T3 | 1 | 1 | 1 |00:00:00.07 | 37165 |
| 6 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 4 |
|* 7 | INDEX UNIQUE SCAN | SYS_C0017307 | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 8 | INDEX UNIQUE SCAN | SYS_C0017308 | 1 | 1 | 1 |00:00:00.01 | 3 |
| 9 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 1 |00:00:00.01 | 1 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
5 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
7 - access("T1"."C1"="T3"."C1")
filter(("T1"."C1"<=:N2 AND "T1"."C1">=:N1))
8 - access("T1"."C1"="T2"."C1")
filter(("T2"."C1">=:N1 AND "T2"."C1"<=:N2))
Note
-----
- dynamic sampling used for this statement (level=2)
-
SQL_ID 77z8bh7b87r1s, child number 9
-------------------------------------
SELECT T3.C1, SUBSTR(T1.C2,1,10) T1_C2, SUBSTR(T2.C2,1,10) T2_C2
FROM T3, T2, T1 WHERE T1.C1=T3.C1 AND T1.C1=T2.C1 AND T1.C1
BETWEEN :N1 AND :N2
Plan hash value: 300618627
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.07 | 37298 | | | |
|* 1 | FILTER | | 1 | | 100 |00:00:00.07 | 37298 | | | |
|* 2 | HASH JOIN | | 1 | 13 | 100 |00:00:00.07 | 37298 | 711K| 711K| 1272K (0)|
| 3 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
|* 4 | INDEX RANGE SCAN | SYS_C0017308 | 1 | 100 | 100 |00:00:00.01 | 3 | | | |
| 5 | NESTED LOOPS | | 1 | | 100 |00:00:00.07 | 37291 | | | |
| 6 | NESTED LOOPS | | 1 | 13 | 100 |00:00:00.07 | 37191 | | | |
|* 7 | TABLE ACCESS FULL | T3 | 1 | 100 | 100 |00:00:00.07 | 37171 | | | |
|* 8 | INDEX UNIQUE SCAN | SYS_C0017307 | 100 | 1 | 100 |00:00:00.01 | 20 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 1 | 100 |00:00:00.01 | 100 | | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:N1<=:N2)
2 - access("T1"."C1"="T2"."C1")
4 - access("T2"."C1">=:N1 AND "T2"."C1"<=:N2)
7 - filter(("T3"."C1"<=:N2 AND "T3"."C1">=:N1))
8 - access("T1"."C1"="T3"."C1")
filter(("T1"."C1"<=:N2 AND "T1"."C1">=:N1))
Note
-----
- dynamic sampling used for this statement (level=2)
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:
Name
------------------------------
UNBOUND_CURSOR
SQL_TYPE_MISMATCH
OPTIMIZER_MISMATCH
OUTLINE_MISMATCH
STATS_ROW_MISMATCH
LITERAL_MISMATCH
FORCE_HARD_PARSE
EXPLAIN_PLAN_CURSOR
BUFFERED_DML_MISMATCH
PDML_ENV_MISMATCH
INST_DRTLD_MISMATCH
SLAVE_QC_MISMATCH
TYPECHECK_MISMATCH
AUTH_CHECK_MISMATCH
BIND_MISMATCH
DESCRIBE_MISMATCH
LANGUAGE_MISMATCH
TRANSLATION_MISMATCH
BIND_EQUIV_FAILURE
INSUFF_PRIVS
INSUFF_PRIVS_REM
REMOTE_TRANS_MISMATCH
LOGMINER_SESSION_MISMATCH
INCOMP_LTRL_MISMATCH
OVERLAP_TIME_MISMATCH
EDITION_MISMATCH
MV_QUERY_GEN_MISMATCH
USER_BIND_PEEK_MISMATCH
TYPCHK_DEP_MISMATCH
NO_TRIGGER_MISMATCH
FLASHBACK_CURSOR
ANYDATA_TRANSFORMATION
INCOMPLETE_CURSOR
TOP_LEVEL_RPI_CURSOR
DIFFERENT_LONG_LENGTH
LOGICAL_STANDBY_APPLY
DIFF_CALL_DURN
BIND_UACS_DIFF
PLSQL_CMP_SWITCHS_DIFF
CURSOR_PARTS_MISMATCH
STB_OBJECT_MISMATCH
CROSSEDITION_TRIGGER_MISMATCH
PQ_SLAVE_MISMATCH
TOP_LEVEL_DDL_MISMATCH
MULTI_PX_MISMATCH
BIND_PEEKED_PQ_MISMATCH
MV_REWRITE_MISMATCH
ROLL_INVALID_MISMATCH
OPTIMIZER_MODE_MISMATCH
PX_MISMATCH
MV_STALEOBJ_MISMATCH
FLASHBACK_TABLE_MISMATCH
LITREP_COMP_MISMATCH
PLSQL_DEBUG
LOAD_OPTIMIZER_STATS
ACL_MISMATCH
FLASHBACK_ARCHIVE_MISMATCH
LOCK_USER_SCHEMA_FAILED
REMOTE_MAPPING_MISMATCH
LOAD_RUNTIME_HEAP_FAILED
HASH_MATCH_FAILED
PURGED_CURSOR
BIND_LENGTH_UPGRADEABLE
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)?
CN USOOSLFEBPISTABDLTBIIRLIOEMUTNFAITDLDBPCSCPTMBMROPMFLPLAFLRLHPB
-- ---------------------------------------------------------------
0 NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNYNNNNNNNN
1 NNNNNNNNNNNNNNNNNNYNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN
2 NNNNNNNNNNNNNNNNNNYNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN
3 NNNNNNNNNNNNNNNNNNYNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN
4 NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNYNNNNNYNNNNNNNN
5 NNNNNNNNNNNNNNNNNNYNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN
6 NNNNNNNNNNNNNNNNNNYNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN
7 NNNNNNNNNNNNNNNNNNYNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN
8 NNNNNNNNNNNNNNNNNNYNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN
9 NNNNNNNNNNNNNNNNNNYNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN
From the above, the reasons are: L (LOAD_OPTIMIZER_STATS), B (BIND_EQUIV_FAILURE), O (OPTIMIZER_MODE_MISMATCH).
Recent Comments