Forum Stats

  • 3,780,466 Users
  • 2,254,398 Discussions
  • 7,879,339 Comments

Discussions

Select from DBMS_XPLAN.DISPLAY_AWR and from DBA_HIST_SQL_PLAN does not show same stuff

Hello,

Have you already seen such a behavior ??:

SQL> select * from TABLE(dbms_xplan.display_awr('4qa15j68u10ws'));

 

SQL_ID 4qa15j68u10ws

--------------------

SELECT TO_CHAR(FILL.BGN_DT,:"SYS_B_0"),TO_CHAR(FILL.END_DT,:"SYS_B_1"),F

ILL.SETID,FILL.SCH_ADHOC_IND,FILL.SCHEDULE_ID,FILL.SCHED_HRS_TOTAL,FILL.

FG_HRSWEEK,FILL.FG_MAXHRSWEEK FROM PS_FG_HRSMONTH_VW FILL WHERE SETID =

:1 AND SCH_ADHOC_IND = :2 AND SCHEDULE_ID = :3 AND BGN_DT BETWEEN

TO_DATE(:4,:"SYS_B_2") AND TO_DATE(:5,:"SYS_B_3")

 

Plan hash value: 3925307117

 

-----------------------------------------------------------------------------------------------------------

| Id  | Operation                               | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                        |                 |       |       |     9 (100)|          |

|   1 |  VIEW                                   |                 |     1 |    70 |     9  (12)| 00:00:01 |

|   2 |   WINDOW SORT                           |                 |     1 |   126 |     9  (12)| 00:00:01 |

|   3 |    NESTED LOOPS                         |                 |     1 |   126 |     8   (0)| 00:00:01 |

|   4 |     NESTED LOOPS                        |                 |     1 |   118 |     6   (0)| 00:00:01 |

|   5 |      VIEW                               |                 |     1 |    80 |     3   (0)| 00:00:01 |

|   6 |       WINDOW BUFFER                     |                 |     1 |    41 |     3   (0)| 00:00:01 |

|   7 |        FILTER                           |                 |       |       |            |          |

|   8 |         TABLE ACCESS BY INDEX ROWID     | PS_SCH_DEFN_TBL |     1 |    41 |     3   (0)| 00:00:01 |

|   9 |          INDEX RANGE SCAN               | PS_SCH_DEFN_TBL |     1 |       |     2   (0)| 00:00:01 |

|  10 |      TABLE ACCESS BY INDEX ROWID BATCHED| PS_SCH_DEFN_DTL |     1 |    38 |     3   (0)| 00:00:01 |

|  11 |       INDEX RANGE SCAN                  | PS_SCH_DEFN_DTL |     1 |       |     2   (0)| 00:00:01 |

|  12 |     INDEX RANGE SCAN                    | PS_TL_DATES_TBL |     1 |     8 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------

 

Note

-----

   - this is an adaptive plan

 

SQL> SELECT ID ID_PLUS_EXP

  2       ,  PARENT_ID PARENT_ID_PLUS_EXP

  3       , LPAD(' ',2*(LEVEL-1))|| OPERATION||

  4              DECODE(OTHER_TAG,NULL,'','*')||

  5              DECODE(OPTIONS,NULL,'',' ('||OPTIONS||')')||

  6              DECODE(OBJECT_NAME,NULL,'',' OF '''||OBJECT_NAME||'''')||

  7              DECODE(OBJECT_TYPE,NULL,'',' ('||OBJECT_TYPE||')')||

  8              DECODE(ID,0,DECODE(OPTIMIZER,NULL,'',' Optimizer='||OPTIMIZER))||

  9              DECODE(COST,NULL,'',' (Cost='||COST||DECODE(CARDINALITY,NULL,'',' Card='||CARDINALITY)||DECODE(BYTES,NULL,'',' Bytes='||BYTES)||')') PLAN_PLUS_EXP

10    -- , OBJECT_NODE OBJECT_NODE_PLUS_EXP

11  FROM DBA_HIST_SQL_PLAN

12  START WITH ID=0

13  and sql_id = '4qa15j68u10ws' and plan_hash_value = 3925307117

14  CONNECT BY PRIOR ID=PARENT_ID

15  and sql_id = '4qa15j68u10ws' and plan_hash_value = 3925307117 order by id ,position;

 

  ID  PID PLAN_PLUS_EXP

---- ---- ------------------------------------------------------------------------------------------------------------------

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9)

   1    0   VIEW (Cost=9 Card=1 Bytes=70)

   2    1     WINDOW (SORT) (Cost=9 Card=1 Bytes=126)

   3    2       NESTED LOOPS (Cost=8 Card=1 Bytes=126)

   4    3         HASH JOIN (Cost=6 Card=1 Bytes=118)

   5    4           NESTED LOOPS (Cost=6 Card=1 Bytes=118)

   6    5             STATISTICS COLLECTOR

   7    6               VIEW (Cost=3 Card=1 Bytes=80)

   8    7                 WINDOW (BUFFER) (Cost=3 Card=1 Bytes=41)

   9    8                   FILTER

  10    9                     TABLE ACCESS (BY INDEX ROWID) OF 'PS_SCH_DEFN_TBL' (TABLE) (Cost=3 Card=1 Bytes=41)

  11   10                       INDEX (RANGE SCAN) OF 'PS_SCH_DEFN_TBL' (INDEX (UNIQUE)) (Cost=2 Card=1)

  12    5             TABLE ACCESS (BY INDEX ROWID BATCHED) OF 'PS_SCH_DEFN_DTL' (TABLE) (Cost=3 Card=1 Bytes=38)

  13   12               INDEX (RANGE SCAN) OF 'PS_SCH_DEFN_DTL' (INDEX (UNIQUE)) (Cost=2 Card=1)

  14    4           TABLE ACCESS (BY INDEX ROWID BATCHED) OF 'PS_SCH_DEFN_DTL' (TABLE) (Cost=3 Card=1 Bytes=38)

  15   14             INDEX (RANGE SCAN) OF 'PS_SCH_DEFN_DTL' (INDEX (UNIQUE)) (Cost=2 Card=1)

  16    3         INDEX (RANGE SCAN) OF 'PS_TL_DATES_TBL' (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=8)

What query should we trust ?

Thanks.

Sebino

Tagged:

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,844 Gold Crown
    Accepted Answer


    The reason why the two plans appear to be contradictory is because the plan is an adaptive plan as shown in the Note from the call to display_awr().

    In the second plan (which basically can't report the actual final choice plan of an adaptive plan) you can see the STATISTICS COLLECTOR at operation 6. Depending on the number of rows this statistics collector receives at RUN-TIME it would have selected one of two plans.

    One plan is for the nested loop (operation 5) to use operations 12 and 13 as the source for the 2nd table in the join; the other plan is to ignore lines 5, 12, 13 and to use the hash join (operation 4) using operations 14 and 15 to generate the 2nd table for the join.

    The report from the AWR tells you that the run-time choice was the nested loop.


    Regards

    Jonathan Lewis

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,844 Gold Crown
    Accepted Answer


    The reason why the two plans appear to be contradictory is because the plan is an adaptive plan as shown in the Note from the call to display_awr().

    In the second plan (which basically can't report the actual final choice plan of an adaptive plan) you can see the STATISTICS COLLECTOR at operation 6. Depending on the number of rows this statistics collector receives at RUN-TIME it would have selected one of two plans.

    One plan is for the nested loop (operation 5) to use operations 12 and 13 as the source for the 2nd table in the join; the other plan is to ignore lines 5, 12, 13 and to use the hash join (operation 4) using operations 14 and 15 to generate the 2nd table for the join.

    The report from the AWR tells you that the run-time choice was the nested loop.


    Regards

    Jonathan Lewis