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

1 02 2010

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

Actions

Information

Date : February 1, 2010
Categories : Performance

One response

5 03 2010: Blogroll Report 29/01/2009 – 05/02/2010 « Coskan’s Approach to Oracle (05:00:19) :

[…] 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 […]

Reply

	Charles Hooper on Favorite Free Applications to…
	Zach on Favorite Free Applications to…
	Charles Hooper on Favorite Free Applications to…
	Zach on Favorite Free Applications to…
	Charles Hooper on Huge Presentation – Work…
	Dan on Huge Presentation – Work…
	Charles Hooper on Review of Synology DS415+, How…
	Matthias Clausen on Review of Synology DS415+, How…
	Charles Hooper on _SMALL_TABLE_THRESHOLD Paramet…
	mohd atif on _SMALL_TABLE_THRESHOLD Paramet…
	Charles Hooper on Free ANSI SQL to Oracle Specif…
	Kalin Agrawal on Free ANSI SQL to Oracle Specif…
	Charles Hooper on Large Page Support or Not…
	vaurob on Large Page Support or Not…
	Locating Oracle… on Finding a New Home for a Clien…

Charles Hooper's Oracle Notes