Hi, we have just been migrated from oracle version from 11.2.0.3 to 11.2.0.4 . We see a query execution path deviation previously doing partition pruning suddenly started scanning all the partition, so want to get the cause behind same. Here table TAB1 is collection Table type(i dont see this in dba_tables rather i see in DBA_OBJECT_TABLES), this is hourly range partitioned on column "hr". and there does exist a direct filter in the query on column hr. I do see difference in predicate section of both the execution, so wanted to know why its happening, and how we can fix this from root or is it some bug? I have published here both the execution plan and real time monitor with both the optimizer version hint.
***********************************************with OFE 11.2.0.3*****************************************************
select /*+optimizer_features_enable('11.2.0.3') MONITOR*//*test1*/:etid, row_number() over (order by ant),
a.oid, :hr from TAB2_VW a, TAB1 tab1 where
tab1.etid = :etid and tab1.gvl = 0 and tab1.hr = :hr and tab1.rowid = a.rowid;
Execution Plan
----------------------------------------------------------
Plan hash value: 3715020418
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 16 (7)| 00:00:01 | | |
| 1 | WINDOW SORT | | 1 | 48 | 16 (7)| 00:00:01 | | |
| 2 | PARTITION LIST SINGLE | | 1 | 48 | 15 (0)| 00:00:01 | KEY | KEY |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB1 | 1 | 48 | 15 (0)| 00:00:01 | KEY | KEY |
|* 4 | INDEX RANGE SCAN | IDX_TAB1 | 99 | | 1 (0)| 00:00:01 | KEY | KEY |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("SYS_NC_TYPEID$"=HEXTORAW('0122') )
4 - access("etid"=TO_NUMBER(:etid) AND "gvl"=0)
Global Information
------------------------------
Status : DONE (ALL ROWS)
Execution Started : 07/26/2017 16:37:57
First Refresh Time : 07/26/2017 16:37:57
Last Refresh Time : 07/26/2017 16:37:57
Duration : .451926s
Program : sqlplus.exe
Fetch Calls : 1
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 0.45 | 0.41 | 0.02 | 0.02 | 1 | 585 | 6 | 49152 |
===========================================================================
SQL Plan Monitoring Details (Plan Hash Value=3715020418)
=======================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
=======================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | |
| 1 | WINDOW SORT | | 1 | 5 | | | 1 | | | | | |
| 2 | PARTITION LIST SINGLE | | 1 | 4 | | | 1 | | | | | |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB1 | 1 | 4 | 1 | +0 | 1 | 0 | | | | |
| 4 | INDEX RANGE SCAN | IDX_TAB1 | 1 | 3 | 1 | +0 | 1 | 50 | 2 | 16384 | | |
=======================================================================================================================================================================
***********************************************with OFE 11.2.0.4*****************************************************
select /*+optimizer_features_enable('11.2.0.4')*/:etid, :sortid, row_number() over (order by ant),
a.oid, :hr from TAB2_VW a, TAB1 tab1 where
tab1.etid = :etid and tab1.gvl = 0 and tab1.hr = :hr and tab1.rowid = a.rowid;
Execution Plan
----------------------------------------------------------
Plan hash value: 1710480983
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 72 | 443 (3)| 00:00:02 | | |
| 1 | WINDOW SORT | | 1 | 72 | 443 (3)| 00:00:02 | | |
|* 2 | HASH JOIN | | 1 | 72 | 442 (3)| 00:00:02 | | |
| 3 | PARTITION LIST SINGLE | | 25 | 600 | 15 (0)| 00:00:01 | KEY | KEY |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB1 | 25 | 600 | 15 (0)| 00:00:01 | KEY | KEY |
|* 5 | INDEX RANGE SCAN | IDX_TAB1 | 99 | | 1 (0)| 00:00:01 | KEY | KEY |
| 6 | PARTITION LIST ALL | | 899 | 43152 | 427 (3)| 00:00:02 | 1 | 24 |
| 7 | VIEW | | 899 | 43152 | 427 (3)| 00:00:02 | | |
|* 8 | TABLE ACCESS FULL | TAB1 | 899 | 386K| 427 (3)| 00:00:02 | 1 | 24 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("tab1".ROWID="A".ROWID)
5 - access("tab1"."etid"=TO_NUMBER(:etid) AND "tab1"."gvl"=0)
8 - filter("SYS_NC_TYPEID$"=HEXTORAW('0122') )
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Execution Started : 07/26/2017 16:39:29
First Refresh Time : 07/26/2017 16:39:29
Last Refresh Time : 07/26/2017 16:42:58
Duration : 209s
Program : sqlplus.exe
Fetch Calls : 1
Global Stats
==========================================================================================
| Elapsed | Cpu | IO | Concurrency | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
==========================================================================================
| 208 | 181 | 13 | 0.06 | 14 | 1 | 11M | 10113 | 640MB |
==========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1710480983)
=============================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
=============================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | | |
| 1 | WINDOW SORT | | 1 | 432 | | | 1 | | | | | | |
| 2 | HASH JOIN | | 1 | 431 | 208 | +2 | 1 | 0 | | | 1M | | |
| 3 | PARTITION LIST SINGLE | | 1 | 4 | 1 | +2 | 1 | 50 | | | | | |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB1 | 1 | 4 | 1 | +2 | 1 | 50 | | | | | |
| 5 | INDEX RANGE SCAN | IDX_TAB1 | 1 | 3 | 1 | +2 | 1 | 50 | 2 | 16384 | | | |
| 6 | PARTITION LIST ALL | | 899 | 427 | 208 | +2 | 1 | 3M | | | | | |
| 7 | VIEW | | 899 | 427 | 208 | +2 | 24 | 3M | | | | 19.21 | Cpu (39) |
| 8 | TABLE ACCESS FULL | TAB1 | 899 | 427 | 208 | +2 | 24 | 3M | 10108 | 640MB | | 80.79 | Cpu (147) |
| | | | | | | | | | | | | | db file scattered read (9) |
| | | | | | | | | | | | | | db file sequential read (8) |
=============================================================================================================================================================================================