Mohamed Houri’s Oracle Notes

August 21, 2015

Cardinality Feedback: a practical case

Filed under: Oracle — hourim @ 5:36 pm

Here it is an interesting case of cardinality feedback collected from an 11.2.0.3 running system. A simple query against a single table has a perfect first execution response time with, according to the human eyes, a quite acceptable difference between Oracle cardinality estimates and actual rows as shown below:

SELECT 
   tr_id
FROM 
    t1 t1
WHERE 
     t1.t1_col_name= 'GroupID'
AND  t1.t1_col_value= '6276931'
AND EXISTS(SELECT 
               1 
            FROM  
                t1 t2
            WHERE t1.tr_id   = t2.tr_id
            AND   t2.t1_col_name= 'TrRangeOrder'
            AND   t2.t1_col_value= 'TrOrderPlace'
           );

SQL_ID  8b3tv5uh8ckfb, child number 0
-------------------------------------

Plan hash value: 1066392926
--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |      1 |        |      1 |00:00:00.14 |
|   1 |  NESTED LOOPS SEMI           |                         |      1 |      1 |      1 |00:00:00.14 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1                      |      1 |      1 |      6 |00:00:00.07 |
|*  3 |    INDEX RANGE SCAN          | IDX_T1_NAME_VALUE       |      1 |      1 |      6 |00:00:00.03 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1                      |      6 |      1 |      1 |00:00:00.07 |
|*  5 |    INDEX UNIQUE SCAN         | T1_PK                   |      6 |      1 |      6 |00:00:00.07 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."T1_COL_NAME"='GroupID' AND "T1"."T1_COL_VALUE"='6276931')
   4 - filter("T2"."T1_COL_VALUE"='TrOrderPlace')
   5 - access("T1"."TR_ID"="T2"."TR_ID" AND 
               "T2"."T1_COL_NAME"='TrRangeOrder')

And here it is the second dramatic execution plan and response time due, this time, to cardinality feedback optimisation:


SQL_ID  8b3tv5uh8ckfb, child number 1
-------------------------------------
Plan hash value: 3786385867
----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                    | Starts | E-Rows | A-Rows |   A-Time   |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         |      1 |        |      1 |00:10:40.14 |
|   1 |  NESTED LOOPS                  |                         |      1 |        |      1 |00:10:40.14 |
|   2 |   NESTED LOOPS                 |                         |      1 |      1 |    787K|00:09:31.00 |
|   3 |    SORT UNIQUE                 |                         |      1 |      1 |    787K|00:02:44.83 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1                      |      1 |      1 |    787K|00:02:41.58 |
|*  5 |      INDEX RANGE SCAN          | IDX_T1_NAME_VALUE       |      1 |      1 |    787K|00:00:36.46 |
|*  6 |    INDEX UNIQUE SCAN           | T1_PK                   |    787K|      1 |    787K|00:06:45.25 |
|*  7 |   TABLE ACCESS BY INDEX ROWID  | T1                      |    787K|      1 |      1 |00:05:00.24 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T2"."T1_COL_NAME"='TrRangeOrder' AND "T2"."T1_COL_VALUE"='TrOrderPlace')
   6 - access("T1"."TR_ID"="T2"."TR_ID" AND "T1"."T1_COL_NAME"='GroupID')
   7 - filter("T1"."T1_COL_VALUE"='6276931')

Note
-----
   - cardinality feedback used for this statement

There is no real noticeable difference between actual and estimated rows in the first run of the query (E-Rows =1 versus A-Rows = 6) which implies a new re-optimisation. But Oracle did it and marked the child cursor n°0 candidate for a cardinality feedback:

SQL> select
       sql_id
      ,child_number
      ,use_feedback_stats
    from
      v$sql_shared_cursor
    where
      sql_id = '8b3tv5uh8ckfb';

SQL_ID        CHILD_NUMBER U
------------- ------------ -
8b3tv5uh8ckfb            0 Y

The bad news however with this Oracle decision is that we went from a quasi-instantaneous response time to a catastrophic 10 min. In the first plan the always suspicious estimated ‘’1’’ cardinality is not significantly far from actual rows (6), so why then Oracle has decided to re-optimize the first cursor? It might be “possible” that when Oracle rounds up its cardinality estimation to 1 for a cursor that has been previously monitored for cardinality feedback, it flags somewhere that this cursor is subject to a re-optimization during its next execution whatever the actual rows will be (close to 1 or not)?

Fortunately, this second execution has also been marked for re-optimisation:

SQL> select
       sql_id
      ,child_number
      ,use_feedback_stats
    from
      v$sql_shared_cursor
    where
      sql_id = '8b3tv5uh8ckfb';

SQL_ID        CHILD_NUMBER U
------------- ------------ -
8b3tv5uh8ckfb            0 Y
8b3tv5uh8ckfb            1 Y

And the third execution of the query produces the following interesting execution plan

SQL_ID  8b3tv5uh8ckfb, child number 2
-------------------------------------

Plan hash value: 1066392926
--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |      1 |        |      1 |00:00:00.01 |
|   1 |  NESTED LOOPS SEMI           |                         |      1 |      1 |      1 |00:00:00.01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1                      |      1 |      6 |      6 |00:00:00.01 |
|*  3 |    INDEX RANGE SCAN          | IDX_T1_NAME_VALUE       |      1 |      6 |      6 |00:00:00.01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1                      |      6 |      1 |      1 |00:00:00.01 |
|*  5 |    INDEX UNIQUE SCAN         | T1_PK                   |      6 |      1 |      6 |00:00:00.01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."T1_COL_NAME"='GroupID' AND "T1"."T1_COL_VALUE"='6276931')
   4 - filter("T2"."T1_COL_VALUE"='TrOrderPlace')
   5 - access("T1"."TR_ID"="T2"."TR_ID" AND
              "T2"."T1_COL_NAME"='TrRangeOrder')

Note
-----
   - cardinality feedback used for this statement   

Oracle is back to its first execution plan. The new estimations coincide perfectly with the actuals so that Oracle decided to stop monitoring this cursor with cardinality feedback as shown below:

SQL> select
       sql_id
      ,child_number
      ,use_feedback_stats
    from
      v$sql_shared_cursor
    where
      sql_id = '8b3tv5uh8ckfb';

SQL_ID        CHILD_NUMBER U
------------- ------------ -
8b3tv5uh8ckfb            0 Y
8b3tv5uh8ckfb            1 Y
8b3tv5uh8ckfb            2 N

Several questions come to my mind at this stage of the investigation:

  1.  What are the circumstances for which Oracle marks a cursor for cardinality feedback optimisation?
  2. How Oracle decides that E-Rows are significantly different from A-Rows and henceforth a cursor re-optimization will be done? In other words is E-Rows =1 significantly different from A-Rows =6? Or does that suspicious cardinality 1 participate in Oracle decision to re-optimize a cursor monitored with cardinality feedback?

Let’s try to answer the first question. There is only one unique table involved in this query with two conjunctive predicates. The two predicate columns have the following statistics

SQL> select
        column_name
       ,num_distinct
       ,density
       ,histogram
     from 
	    all_tab_col_statistics
     where
        table_name = 'T1'
     and
       column_name in ('T1_COL_NAME','T1_COL_VALUE');

COLUMN_NAME     NUM_DISTINCT    DENSITY HISTOGRAM
--------------- ------------ ---------- ---------------
T1_COL_NAME           103     4,9781E-09 FREQUENCY
T1_COL_VALUE      14833664   ,000993049  HEIGHT BALANCED

The presence of histograms, particularly the HEIGHT BALANCED, on these two columns participates strongly in the Oracle decision to monitor the cursor for cardinality feedback. In order to be sure of it I decided to get rid of histograms from both columns and re-query again:

SQL> select
        column_name
       ,num_distinct
       ,density
       ,histogram
     from 
	    all_tab_col_statistics
     where
        table_name = 'T1'
     and
       column_name in ('T1_COL_NAME','T1_COL_VALUE');

COLUMN_NAME     NUM_DISTINCT    DENSITY HISTOGRAM
--------------- ------------ ---------- ---------
T1_COL_NAME           103    ,009708738 NONE
T1_COL_VALUE      15477760   6,4609E-08 NONE

The new cursor is not anymore monitored with the cardinality feedback as shown below:

SQL_ID  fakc7vfbu1mam, child number 0
-------------------------------------

Plan hash value: 739349168
--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |      1 |        |      1 |00:02:00.68 |
|*  1 |  HASH JOIN SEMI              |                         |      1 |      6 |      1 |00:02:00.68 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1                      |      1 |      6 |      6 |00:00:00.01 |
|*  3 |    INDEX RANGE SCAN          | IDX_T1_NAME_VALUE       |      1 |      6 |      6 |00:00:00.01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T1                      |      1 |      6 |    787K|00:02:00.14 |
|*  5 |    INDEX RANGE SCAN          | IDX_T1_NAME_VALUE       |      1 |      6 |    787K|00:00:12.36 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."TR_ID"="T2"."TR_ID")
   3 - access("T1"."T1_COL_NAME"='GroupID' AND "T1"."T1_COL_VALUE"='6276931')
   5 - access("T2"."T1_COL_NAME"='TrRangeOrder' AND "T2"."T1_COL_VALUE"='TrOrderPlace')

   SQL> select
         sql_id
        ,child_number
        ,use_feedback_stats
    from
       v$sql_shared_cursor
    where
        sql_id = 'fakc7vfbu1mam';

SQL_ID        CHILD_NUMBER U
------------- ------------ -
fakc7vfbu1mam            0 N --> cursor not re-optimisable 

Without histograms on the two columns Oracle has not monitored the query for cardinality feedback. Unfortunately getting rid of histogram was not an option accepted by the client nor changing this packaged query to force the optimizer not unnesting the EXISTS subquery with its parent query as far as the later is always generating a couple of rows that will not hurt performance when filtered with the EXIST subquery. Attaching a SQL Profile has also been discarded because several copies of the same query are found in the packaged application which would have necessitated a couple of extra SQL Profiles.

The last option that remains at my hands was to collect extended statistics so that Oracle will be able to get accurate estimations and henceforth will stop using cardinality feedback

SQL> SELECT
       dbms_stats.create_extended_stats
       (ownname   => user
       ,tabname   => 'T1'
       ,extension => '(T1_COL_NAME,T1_COL_VALUE)'
      )
  FROM dual;

DBMS_STATS.CREATE_EXTENDED_STATS(
---------------------------------
SYS_STUE3EBVNLB6M1SYS3A07$LD52

SQL> begin
      dbms_stats.gather_table_stats
            (user
           ,'T1'
           ,method_opt    => 'for columns SYS_STUE3EBVNLB6M1SYS3A07$LD52 size skewonly'
           ,cascade       => true
           ,no_invalidate => false
            );
    end;
    /

SQL> select
       column_name
      ,num_distinct
      ,density
      ,histogram
    from all_tab_col_statistics
    where
        table_name = 'T1'
    and column_name in ('T1_COL_NAME','T1_COL_VALUE', 'SYS_STUE3EBVNLB6M1SYS3A07$LD52');

COLUMN_NAME                    NUM_DISTINCT    DENSITY HISTOGRAM
------------------------------ ------------ ---------- ---------------
SYS_STUE3EBVNLB6M1SYS3A07$LD52     18057216 ,000778816 HEIGHT BALANCED
T1_COL_NAME                          103    4,9781E-09 FREQUENCY
T1_COL_VALUE                     14833664   ,000993049 HEIGHT BALANCED


SQL_ID  dn6p58b9b6348, child number 0
-------------------------------------
Plan hash value: 1066392926
--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |      1 |        |      1 |00:00:00.01 |
|   1 |  NESTED LOOPS SEMI           |                         |      1 |      3 |      1 |00:00:00.01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1                      |      1 |      3 |      6 |00:00:00.01 |
|*  3 |    INDEX RANGE SCAN          | IDX_T1_NAME_VALUE       |      1 |      3 |      6 |00:00:00.01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1                      |      6 |    832K|      1 |00:00:00.01 |
|*  5 |    INDEX UNIQUE SCAN         | T1_PK                   |      6 |      1 |      6 |00:00:00.01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."T1_COL_NAME"='GroupID' AND "T1"."T1_COL_VALUE"='6276931')
   4 - filter("T2"."T1_COL_VALUE"='TrOrderPlace')
   5 - access("T1"."TR_ID"="T2"."TR_ID" AND
              "T2"."T1_COL_NAME"='TrRangeOrder')

SQL> select
       sql_id
      ,child_number
      ,use_feedback_stats
    from
      v$sql_shared_cursor
    where
      sql_id = 'dn6p58b9b6348';
   
SQL_ID        CHILD_NUMBER U
------------- ------------ -
dn6p58b9b6348            0 N

This time, for E-Rows = 3 and A-Rows =6, Oracle decided that there is no significant difference between cardinality estimates and the actual rows so that the cursor is not anymore subject to cardinality feedback optimization.

You might have pointed out that I have forced the Extended Statistics column to have histogram. Otherwise the cardinality feedback will kicks off. In fact I have conducted several experiments to see when the cardinality feedback occurs and when not depending on the existence or the absence of the column group extension, its type of statistics and the statistics that have been gathered on the underlying two columns predicates:
cardinality feedback

 

9 Comments »

  1. Hi Mohamed,

    Does it imply that cardinality feedback kicks off only in presence of histograms?

    Thanks,
    Abbas

    Comment by Abu Fazal Md Abbas — August 21, 2015 @ 6:22 pm | Reply

    • Abbas

      As mentioned by Stefan there a couple of reasons for which Oracle decides to put a cursor
      under cardinality feedback monitoring. And as per regards to your question about histogram and
      cardinality feedback, you can see in my tests that when I’ve get rid of histogram
      from both columns cardinality feedback didn’t kicks in. it is Oracle perception about the existing possibility
      of cardinality underestimations that drive the monitoring of cardinality feedback or not. That’s said,
      monitoring a cursor with cardinality feedback is not a guaranty that the cursor will be re-optimized at
      its next execution. This re-optimisation happens when a monitored cursor has produced a significant difference between
      the initial estimation and the actual rows. And the entire article purpose is to question which difference
      Oracle can consider as significant and which not?

      I have played with this example for several input bind variables and as matter of example I have
      observed that cardinality feedback didn’t kicked in for the following case:

      SQL_ID d9qytywbquvms, child number 0
      -------------------------------------
      Plan hash value: 1066392926
      -------------------------------------------------------------------------------------------
      | Id  | Operation                    | Name                    | Starts | E-Rows | A-Rows |
      -------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT             |                         |      1 |        |      2 |
      |   1 |  NESTED LOOPS SEMI           |                         |      1 |      1 |      2 |
      |   2 |   TABLE ACCESS BY INDEX ROWID| T1                      |      1 |      1 |      2 |
      |*  3 |    INDEX RANGE SCAN          | IDX_T1_NAME_VALUE       |      1 |      1 |      2 |
      |*  4 |   TABLE ACCESS BY INDEX ROWID| T1                      |      2 |  35621 |      2 |
      |*  5 |    INDEX UNIQUE SCAN         | PK_T1                   |      2 |      1 |      2 |
      -------------------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
         3 - access("T1"."T1_COL_NAME"='IDGroup' AND "T1"."T1_COL_VALUE"='18102')
         4 - filter("T2"."T1_COL_VALUE"='Zywullaaa')
         5 - access("T1"."T1_ID"="T2"."T1_ID" AND "T2"."T1_COL_NAME"='Xajjalalal')
       
      SQL> select
        2         sql_id
        3        ,child_number
        4        ,use_feedback_stats
        5      from
        6        v$sql_shared_cursor
        7      where
        8        sql_id =
        9  'd9qytywbquvms';
       
      SQL_ID         CHILD_NUMBER U
      ------------- ------------ -
      d9qytywbquvms            0 N
      

      And kicked off for the following case:

       
      -- A-Rows = 3
      SQL_ID gmy1rr9b0z9kf, child number 0
      -------------------------------------
       
      Plan hash value: 1066392926
      -------------------------------------------------------------------------------------------
      | Id  | Operation                    | Name                    | Starts | E-Rows | A-Rows |
      -------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT             |                         |      1 |        |      1 |
      |   1 |  NESTED LOOPS SEMI           |                         |      1 |      1 |      1 |
      |   2 |   TABLE ACCESS BY INDEX ROWID| T1                      |      1 |      1 |      3 |
      |*  3 |    INDEX RANGE SCAN          | IDX_T1_NAME_VALUE       |      1 |      1 |      3 |
      |*  4 |   TABLE ACCESS BY INDEX ROWID| T1                      |      3 |  35621 |      1 |
      |*  5 |    INDEX UNIQUE SCAN         | PK_T1                   |      3 |      1 |      3 |
      -------------------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
         3 - access("T1"."T1_COL_NAME"='IDGroup' AND "T1"."T1_COL_VALUE"='1000087')
         4 - filter("T2"."T1_COL_VALUE"='Zywullaaa')
         5 - access("T1"."T1_ID"="T2"."T1_ID" AND"T2"."T1_COL_NAME"='Xajjalalal')
       
      SQL> select
        2       sql_id
        3      ,child_number
        4      ,use_feedback_stats
        5  from
        6      v$sql_shared_cursor
        7  where
        8      sql_id = 'gmy1rr9b0z9kf';
       
      SQL_ID         CHILD_NUMBER U
      ------------- ------------ -
      gmy1rr9b0z9kf            0 Y
      

      There are two article you can read to deepen your understanding of cardinality feedback:

      http://www.vldb.org/pvldb/1/1454178.pdf
      https://blogs.oracle.com/optimizer/entry/cardinality_feedback

      Best regards

      Comment by hourim — August 22, 2015 @ 8:28 am | Reply

  2. Hi Mohamed,
    how did the OPT_ESTIMATE hints look like in the cases without the extended statistic? Unfortuantely these hints are only populated in the 10053 trace (in case of cardinality feedback).

    @Abbas
    MOS ID #1344937.1 answers your question. At present Statistics Feedback monitoring may be enabled in the following cases:
    – Tables with no statistics where dynamic sampling is not used
    – Multiple conjunctive or disjunctive filter predicates on a table and no extended statistics
    – Predicates containing complex operators that the optimizer cannot accurately compute selectivity estimates for.

    In some cases, there are other techniques available to improve estimation; for instance, dynamic sampling or multi-column statistics allow the optimizer to more accurately estimate selectivity of conjunctive predicates. In cases where these techniques apply, Statistics Feedback is not enabled. However, if multi-column statistics are not present for the relevant combination of columns, the optimizer can fall back on Statistics Feedback.

    Regards
    Stefan

    Comment by Stefan Koehler — August 21, 2015 @ 7:02 pm | Reply

    • Hi Stefan

      I have generated one 10053 trace file for the upper part of the query only whithout Extended Stats (because this part of the query was subject to cardinality feedback as well).This trace file corresponds to the second execution and I pointed out in this trace file that, Indeed, Oracle used opt_estimate with card (6)
      and stopped monitoring the query at its second execution. I will try to get the 10053 trace file at the second execution of the entire query next week
      when back to client site

      Best regards

      Comment by hourim — August 22, 2015 @ 8:38 am | Reply

  3. Thank you Mohamed and Stefan for the links and clarification.

    @Mohamed
    I am trying to understand the calculation/algorithm based on which Oracle decides the estimated and actual cardinality differs significantly. What I had understood is Oracle treats the estimated cardinality “1” as a suspicious case and it always participates in re-optimization (monitoring). However, looks like that is not the case as Oracle did not monitor the cursor in the 1st example you have demonstrated in the comment section.

    Please share if you happened to find any clue from Optimizer trace with respect to this calculation?

    Regards,
    Abbas

    Comment by Abu Fazal Md Abbas — August 22, 2015 @ 1:13 pm | Reply

  4. Hi

    Even though in your test case without histogram no cardinality feedback is used, it doesn’t matter that it is always “switched off” when no histograms are involved. An example is when table functions are involved. Another example, actually a test case, is shown below (executed in 12.1.0.2).

    Best,
    Chris

    SQL> CREATE TABLE t1 (id, n1, n2, pad)
      2  AS
      3  SELECT rownum, mod(rownum,797), mod(rownum,797), lpad('*',100,'*')
      4  FROM dual
      5  CONNECT BY level <1e4;
     execute dbms_stats.gather_table_stats(user,'t1')
    
    SQL> SELECT column_name, histogram
      2  FROM user_tab_col_statistics
      3  WHERE table_name = 'T1';
    
    COLUMN_NAME                    HISTOGRAM
    ------------------------------ ---------------
    ID                             NONE
    N1                             NONE
    N2                             NONE
    PAD                            NONE
    
    SQL> CREATE TABLE t2 (id, n1, n2, pad)
      2  AS
      3  SELECT rownum, mod(rownum,797), mod(rownum,797), lpad('*',100,'*')
      4  FROM dual
      5  CONNECT BY level<1e4;
    
      execute dbms_stats.gather_table_stats(user,'t2')
    
    SQL> SELECT column_name, histogram
      2  FROM user_tab_col_statistics
      3  WHERE table_name = 'T2';
    
    COLUMN_NAME                    HISTOGRAM
    ------------------------------ ---------------
    ID                             NONE
    N1                             NONE
    N2                             NONE
    PAD                            NONE
    
    SQL> SELECT *
      2  FROM t1, t2
      3  WHERE t1.id = t2.id
      4  AND t1.n1 = 6
      5  AND t1.n2 = 6;
    
            ID         N1         N2 PAD                ID         N1         N2 PAD
    ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
             6          6          6 **********          6          6          6 **********
           803          6          6 **********        803          6          6 **********
          1600          6          6 **********       1600          6          6 **********
          2397          6          6 **********       2397          6          6 **********
          3194          6          6 **********       3194          6          6 **********
          3991          6          6 **********       3991          6          6 **********
          4788          6          6 **********       4788          6          6 **********
          5585          6          6 **********       5585          6          6 **********
          6382          6          6 **********       6382          6          6 **********
          7179          6          6 **********       7179          6          6 **********
          7976          6          6 **********       7976          6          6 **********
          8773          6          6 **********       8773          6          6 **********
          9570          6          6 **********       9570          6          6 **********
    
    SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=&gt;'report'));
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------
    
    SQL_ID  21fnza3nxta7h, child number 0
    -------------------------------------
    SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.n1 = 6 AND t1.n2 = 6
    
    Plan hash value: 1838229974
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |       |    98 (100)|          |
    |*  1 |  HASH JOIN         |      |     1 |   226 |    98   (0)| 00:00:01 |
    |*  2 |   TABLE ACCESS FULL| T1   |     1 |   113 |    49   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL| T2   | 10000 |  1103K|    49   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("T1"."ID"="T2"."ID")
       2 - filter(("T1"."N1"=6 AND "T1"."N2"=6))
    
    
    Reoptimized plan:
    -----------------
    This cursor is marked for automatic reoptimization.  The plan that is
    expected to be chosen on the next execution is displayed below.
    
    Plan hash value: 1838229974
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |    13 |  2938 |    98   (0)| 00:00:01 |
    |*  1 |  HASH JOIN         |      |    13 |  2938 |    98   (0)| 00:00:01 |
    |*  2 |   TABLE ACCESS FULL| T1   |    13 |  1469 |    49   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL| T2   | 10000 |  1103K|    49   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("T1"."ID"="T2"."ID")
       2 - filter("T1"."N1"=6 AND "T1"."N2"=6)
    

    Comment by Christian Antognini — August 25, 2015 @ 7:55 pm | Reply

    • Hi Chris

      Thanks a lot for your comment and the example you’ve supplied which I have also tested in 12.1.0.1.0

      I have also re-formatted your example (by adding <1e4) which has disappeared because of the less than operator

      Best regards

      Comment by hourim — August 27, 2015 @ 12:40 pm | Reply

  5. Mohamed thank you for your very interesting post.

    @Christian,

    What is the 12c optimization technic used in your example? is it Statistics feedback or SQL Plan Directives? Or both?

    regards,
    Ahmed

    Comment by ahmed aangour — August 26, 2015 @ 8:40 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: