We are using Version -11.2.0.4 of oracle. I see one query is having multiple execution path , and one of the recent execution path(plan-2 below) was taking longer to execute. This query uses a table TAB1 which is daily range partitioned on column T_DT but that partition key column is not getting used in the WHERE clause but still i see the optimizer is intelligently transforming to a partition scan in three different chunks/batches which is bit surprising for me. i was expecting it to use global stats but it seems its referring local partition stats. We had few of the partitions having zero stats recently and so the plan changed from plan-1 to plan-2 as mentioned below. It went for full scanning few of the latest partitions considering that those were actually holding zero records which was not the reality and ran longer.
I was thinking it will utilize the global stats and will reach-out to some plan as mentioned below (plan-3), which i see it has used in past in some occasion. But still i was not able to understand if my understanding is correct on this?Is this type of transformation is possible where optimizer will use partitioned level stats rather global stats? or I am missing something here?
Also i see in past in AWR history, the query was using same plan-2 (which shows it full scans few of the partitions) since last 4-5days back(mostly since we have the stats were zero on the latest partitions) but i see it was completing in faster time then(in ~1minute) wondering how? And now suddenly from yesterday its started getting slowing down(~5minutes+), wondering how is that possible?
sql_query:
----------
SELECT MAX (CASE WHEN CD = 'A' THEN 'Y' ELSE 'N' END),
MAX (CASE WHEN CD = 'B' THEN 'Y' ELSE 'N' END)
FROM TAB1
WHERE SNO = :B2 AND C1 = :B1 AND CD IN ('B', 'A');
*****************PLAN-1********************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
SQL Execution ID : 16777216
Execution Started : 04/25/2018 05:27:30
First Refresh Time : 04/25/2018 05:27:30
Last Refresh Time : 04/25/2018 05:27:30
Duration : .142856s
Module/Action : SQL*Plus/-
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.14 | 0.05 | 0.09 | 0.00 | 1 | 1764 | 52 | 416KB |
===========================================================================
SQL Plan Monitoring Details (Plan Hash Value=2227119587)
=========================================================================================================================================================
| 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 | +0 | 1 | 1 | | | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | | | |
| 2 | VIEW | VW_TE_2 | 3 | 716 | | | 1 | | | | | |
| 3 | UNION-ALL | | | | | | 1 | | | | | |
| 4 | CONCATENATION | | | | | | 1 | | | | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 333 | | | 1 | | | | | |
| 6 | INDEX RANGE SCAN | TAB1_PK | 1 | 333 | | | 369 | | 52 | 416KB | | |
| 7 | PARTITION RANGE ITERATOR | | 1 | 361 | | | 1 | | | | | |
| 8 | INDEX RANGE SCAN | TAB1_PK | 1 | 361 | | | 401 | | | | | |
| 9 | PARTITION RANGE ITERATOR | | 1 | 22 | | | 1 | | | | | |
| 10 | INDEX RANGE SCAN | TAB1_PK | 1 | 22 | | | 24 | | | | | |
=========================================================================================================================================================
Execution Plan
----------------------------------------------------------
Plan hash value: 2227119587
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 716 (1)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 24 | | | | |
| 2 | VIEW | VW_TE_2 | 4 | 8 | 716 (1)| 00:00:03 | | |
| 3 | UNION-ALL | | | | | | | |
| 4 | CONCATENATION | | | | | | | |
| 5 | PARTITION RANGE ITERATOR| | 1 | 32 | 333 (1)| 00:00:02 | 426 | 794 |
|* 6 | INDEX RANGE SCAN | TAB1_PK | 1 | 32 | 333 (1)| 00:00:02 | 426 | 794 |
| 7 | PARTITION RANGE ITERATOR| | 2 | 64 | 361 (1)| 00:00:02 | 1 | 401 |
|* 8 | INDEX RANGE SCAN | TAB1_PK | 2 | 64 | 361 (1)| 00:00:02 | 1 | 401 |
| 9 | PARTITION RANGE ITERATOR | | 1 | 32 | 22 (0)| 00:00:01 | 402 | 425 |
|* 10 | INDEX RANGE SCAN | TAB1_PK | 1 | 32 | 22 (0)| 00:00:01 | 402 | 425 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("SNO"=TO_NUMBER(:B2) AND "TAB1"."T_DT">=TO_DATE(' 2018-04-12
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C1"=:B1 AND "TAB1"."T_DT"<TO_DATE('
2019-04-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("C1"=:B1 AND ("CD"='B' OR "CD"='A'))
8 - access("SNO"=TO_NUMBER(:B2) AND "C1"=:B1 AND
"TAB1"."T_DT"<TO_DATE(' 2018-03-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("C1"=:B1 AND ("CD"='B' OR "CD"='A') AND
(LNNVL("TAB1"."T_DT">=TO_DATE(' 2018-04-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR
LNNVL("TAB1"."T_DT"<TO_DATE(' 2019-04-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))))
10 - access("SNO"=TO_NUMBER(:B2) AND "TAB1"."T_DT">=TO_DATE(' 2018-03-19
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C1"=:B1 AND "TAB1"."T_DT"<TO_DATE('
2018-04-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("C1"=:B1 AND ("CD"='B' OR "CD"='A'))
Statistics
----------------------------------------------------------
22 recursive calls
0 db block gets
1764 consistent gets
52 physical reads
0 redo size
325 bytes sent via SQL*Net to client
361 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
*************PLAN-2****************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Execution Started : 04/25/2018 05:12:35
First Refresh Time : 04/25/2018 05:12:35
Last Refresh Time : 04/25/2018 05:26:44
Duration : 849s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 1
Global Stats
=======================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
=======================================================================================================
| 849 | 134 | 690 | 1.73 | 0.01 | 23 | 1 | 9M | 121K | 70GB |
=======================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2163925400)
==============================================================================================================================================================================
| 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 | +849 | 1 | 1 | | | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +849 | 1 | 1 | | | | |
| 2 | VIEW | VW_TE_1 | 3 | 532K | | | 1 | | | | | |
| 3 | UNION-ALL | | | | | | 1 | | | | | |
| 4 | CONCATENATION | | | | | | 1 | | | | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 531K | | | 1 | | | | | |
| 6 | TABLE ACCESS FULL | TAB1 | 1 | 531K | 836 | +2 | 369 | 0 | 120K | 69GB | 98.56 | enq: KO - fast object checkpoint (1) |
| | | | | | | | | | | | | Cpu (160) |
| | | | | | | | | | | | | db file sequential read (124) |
| | | | | | | | | | | | | direct path read (537) |
| 7 | PARTITION RANGE ITERATOR | | 1 | 361 | | | 1 | | | | | |
| 8 | INDEX RANGE SCAN | TAB1_PK | 1 | 361 | 12 | +838 | 401 | 0 | 1603 | 13MB | 1.44 | db file sequential read (12) |
| 9 | PARTITION RANGE ITERATOR | | 1 | 22 | | | 1 | | | | | |
| 10 | INDEX RANGE SCAN | TAB1_PK | 1 | 22 | | | 24 | | 96 | 768KB | | |
==============================================================================================================================================================================
Elapsed: 00:14:09.32
Execution Plan
----------------------------------------------------------
Plan hash value: 2163925400
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 531K (10)| 00:36:36 | | |
| 1 | SORT AGGREGATE | | 1 | 2 | | | | |
| 2 | VIEW | VW_TE_1 | 4 | 8 | 531K (10)| 00:36:36 | | |
| 3 | UNION-ALL | | | | | | | |
| 4 | CONCATENATION | | | | | | | |
| 5 | PARTITION RANGE ITERATOR| | 1 | 32 | 531K (10)| 00:36:34 | 426 | 794 |
|* 6 | TABLE ACCESS FULL | TAB1 | 1 | 32 | 531K (10)| 00:36:34 | 426 | 794 |
| 7 | PARTITION RANGE ITERATOR| | 2 | 64 | 361 (1)| 00:00:02 | 1 | 401 |
|* 8 | INDEX RANGE SCAN | TAB1_PK | 2 | 64 | 361 (1)| 00:00:02 | 1 | 401 |
| 9 | PARTITION RANGE ITERATOR | | 1 | 32 | 22 (0)| 00:00:01 | 402 | 425 |
|* 10 | INDEX RANGE SCAN | TAB1_PK | 1 | 32 | 22 (0)| 00:00:01 | 402 | 425 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("TAB1"."T_DT">=TO_DATE(' 2018-04-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "C1"=:B1 AND "SNO"=TO_NUMBER(:B2) AND ("CD"='B' OR "CD"='A') AND
"TAB1"."T_DT"<TO_DATE(' 2019-04-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
8 - access("SNO"=TO_NUMBER(:B2) AND "C1"=:B1 AND
"TAB1"."T_DT"<TO_DATE(' 2018-03-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("C1"=:B1 AND ("CD"='B' OR "CD"='A') AND
(LNNVL("TAB1"."T_DT">=TO_DATE(' 2018-04-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR
LNNVL("TAB1"."T_DT"<TO_DATE(' 2019-04-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))))
10 - access("SNO"=TO_NUMBER(:B2) AND "TAB1"."T_DT">=TO_DATE(' 2018-03-19
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C1"=:B1 AND "TAB1"."T_DT"<TO_DATE('
2018-04-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("C1"=:B1 AND ("CD"='B' OR "CD"='A'))
Statistics
----------------------------------------------------------
16 recursive calls
1 db block gets
9339237 consistent gets
9109931 physical reads
1510896 redo size
325 bytes sent via SQL*Net to client
361 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
******************PLAN-3**************************
Plan hash value: 1298442127
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 716 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 24 | | | | |
| 2 | PARTITION RANGE ALL| | 1 | 24 | 716 (1)| 00:00:03 | 1 | 794 |
| 3 | INDEX RANGE SCAN | TAB1_PK | 1 | 24 | 716 (1)| 00:00:03 | 1 | 794 |
---------------------------------------------------------------------------------------------------