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