Hi, we are using version - 11.2.0.4.0 of oracle. We have below query which is running long and then i found couple of issue. In the first place as this query was having filter on the partition column(T_DATE) but was still not partitioned pruned, so i found it was because it was being compared with another column(LESTS) which is having data type time-stamp and oracle is doing implicit conversion putting an internal function during run-time, so to fix this i have manually converted the time-stamp data type to date in the WHERE clause, and now i see its getting partitioned pruned but still the estimations are differing TAB1_ADD lot and table TAB1_ADD was going for index scan while its actually supposed to do full scan, so i am wondering why its happening, even the estimation are in higher side(~812million) even its wrong, then why its going for index scan of table TAB1_ADD?
I then tried driving the query with FULL hint for both big tables TAB1 and TAB1_ADD as the records were coming out to be all from the specific partitions and are >40million+ , so full partition scan is actually required one. Then i see the query ran for ~2hrs but was still not completing. So is ther anyway i can do something about making it complete faster at this scenario?
Then i tried removing the control table from the query , and just pass the hard coded dates to the filter for T_DATE and now i see the estimations are quite good and optimizer is actually opting for Full partition scan for both the big tables without any additional hints. But yes this query was running for ~1hr and i hope it will run longer too.
So i have mainly three doubts and need experts view 1) Why the query going for index scan even the estimation is higher in main query? 2) Even after forcing the query full partition scan , where is the current time getting spent so that it exceeds ~1hr+ for completion? 3)If presence of function(to_char/to_date) is actually making the estimation went wrong in first case and so it needs to be separately evaluated separately and passed to this query to make the optimizer take correct decision?
Here both table TAB1 and TAB1_ADD both are daily range partitioned on column T_DATE. And both contains same volume of data and related to each other , just the columns are different. And table TAB2 is TAB1_ADD control table holding one record just to satisfy the execution criteria. The partition which i am querying is actually holding total ~50million record in both the table TAB1 and TBA1_ADD. i have published real time monitor and the predicate section for all the three plans. Index TAB1_ADD_IX1 is composite index on column (t_date,col2_Indicator, col3).
SQL Text
------------------------------
SELECT TAB1.MNBER,
TAB1.SIN,
TAB1.TNBR,
TAB1_ADD.HIADR
FROM TAB1_ADD TAB1_ADD, TAB1 TAB1,
TAB2 TAB2
WHERE TAB1_ADD.T_DATE > to_date(to_char(TAB2.LESTS,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') AND TAB1_ADD.T_DATE < TAB2.STS
AND TAB2.WEID = 2
AND NVL (UPPER (TAB2.LSCD), 'F') = 'S'
AND TAB1.T_DATE = TAB1_ADD.T_DATE
AND TAB1.SIN = TAB1_ADD.SIN
AND TAB1.DSNBR = TAB1_ADD.DSNBR;
Global Information
------------------------------
Status : EXECUTING
Execution Started : 10/19/2017 05:13:28
First Refresh Time : 10/19/2017 05:13:28
Last Refresh Time : 10/19/2017 07:50:21
Duration : 9416s
Global Stats
======================================================================================
| Elapsed | Cpu | IO | Concurrency | Buffer | Read | Read | Write | Write |
| Time(S) | Time(S) | Waits(S) | Waits(S) | Gets | Reqs | Bytes | Reqs | Bytes |
======================================================================================
| 9499 | 446 | 9053 | 0.04 | 18M | 4M | 29GB | 3456 | 837MB |
======================================================================================
SQL Plan Monitoring Details (Plan Hash Value=918620286)
==================================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(S) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | | | (%) | (# samples) |
==================================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | | | | | |
| 1 | HASH JOIN | | 822M | 242M | 9412 | +2 | 1 | 0 | | | 3456 | 837MB | 116M | 971M | 0.39 | Cpu (23) |
| | | | | | | | | | | | | | | | | direct path write temp (13) |
| 2 | PART JOIN FILTER CREATE | :BF0000 | 812M | 17M | 9412 | +2 | 1 | 18M | | | | | | | 0.21 | Cpu (19) |
| -> 3 | NESTED LOOPS | | 812M | 17M | 9412 | +2 | 1 | 18M | | | | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | TAB2 | 1 | 1 | 1 | +2 | 1 | 1 | 1 | 8192 | | | | | | |
| -> 5 | INDEX UNIQUE SCAN | TAB2_PK | 1 | 1 | 9412 | +2 | 1 | 1 | 1 | 8192 | | | | | | |
| -> 6 | PARTITION RANGE ITERATOR | | 812M | 17M | 9412 | +2 | 1 | 18M | | | | | | | | |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB1_ADD | 812M | 17M | 9412 | +2 | 2 | 18M | 3M | 26GB | | | | | 89.33 | Cpu (276) |
| | | | | | | | | | | | | | | | | latch: object queue header operation (2) |
| | | | | | | | | | | | | | | | | db file sequential read (7990) |
| -> 8 | INDEX RANGE SCAN | TAB1_ADD_IX1 | 62M | 166K | 9414 | +2 | 2 | 18M | 323K | 2GB | | | | | 10.08 | Cpu (37) |
| | | | | | | | | | | | | | | | | db file sequential read (896) |
| 9 | PARTITION RANGE JOIN-FILTER | | 21G | 168M | | | | | | | | | | | | |
| 10 | TABLE ACCESS FULL | TAB1 | 21G | 168M | | | | | | | | | | | | |
==================================================================================================================================================================================================================================
Execution Plan
----------------------------------------------------------
Plan hash value: 918620286
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 824M| 84G| | 253M (2)|290:56:03 | | |
|* 1 | HASH JOIN | | 824M| 84G| 60G| 253M (2)|290:56:03 | | |
| 2 | PART JOIN FILTER CREATE | :BF0000 | 813M| 51G| | 29M (1)| 33:16:42 | | |
| 3 | NESTED LOOPS | | 813M| 51G| | 29M (1)| 33:16:42 | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | TAB2 | 1 | 24 | | 1 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | TAB2_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE ITERATOR | | 813M| 33G| | 29M (1)| 33:16:42 | KEY | KEY |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB1_ADD | 813M| 33G| | 29M (1)| 33:16:42 | KEY | KEY |
|* 8 | INDEX RANGE SCAN | TAB1_ADD_IX1 | 102M| | | 275K (1)| 00:18:57 | KEY | KEY |
| 9 | PARTITION RANGE JOIN-FILTER | | 20G| 807G| | 167M (2)|192:37:59 |:BF0000|:BF0000|
| 10 | TABLE ACCESS FULL | TAB1 | 20G| 807G| | 167M (2)|192:37:59 |:BF0000|:BF0000|
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TAB1"."T_DATE"="TAB1_ADD"."T_DATE" AND "TAB1"."SIN"="TAB1_ADD"."SIN" AND
"TAB1"."DSNBR"="TAB1_ADD"."DSNBR")
4 - filter(NVL(UPPER("TAB2"."LSCD"),'F')='TAB2')
5 - access("TAB2"."WEID"=2)
8 - access("TAB1_ADD"."T_DATE">TO_DATE(TO_CHAR(INTERNAL_FUNCTION("TAB2"."LESTS"),'DD-MON-YYYY
HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') AND "TAB1_ADD"."T_DATE"<"TAB2"."STS")
***************************************** By Adding FULL Hint to both the table TAB1 and TAB1_ADD
SELECT/*+FULL(TAB1_ADD) FULL(TAB1)*/ TAB1.MNBER,
TAB1.SIN,
TAB1.TNBR,
TAB1_ADD.HIADR
FROM TAB1_ADD TAB1_ADD, TAB1 TAB1,
TAB2 TAB2
WHERE TAB1_ADD.T_DATE > to_date(to_char(TAB2.LESTS,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') AND TAB1_ADD.T_DATE < TAB2.STS
AND TAB2.WEID = 2
AND NVL (UPPER (TAB2.LSCD), 'F') = 'S'
AND TAB1.T_DATE = TAB1_ADD.T_DATE
AND TAB1.SIN = TAB1_ADD.SIN
AND TAB1.DSNBR = TAB1_ADD.DSNBR;
Global Information
------------------------------
Status : EXECUTING
Execution Started : 10/19/2017 06:25:52
First Refresh Time : 10/19/2017 06:25:52
Last Refresh Time : 10/19/2017 08:22:52
Duration : 7021s
Fetch Calls : 143649
Global Stats
==========================================================================================================
| Elapsed | Cpu | IO | Application | Other | Fetch | Buffer | Read | Read | Write | Write |
| Time(S) | Time(S) | Waits(S) | Waits(S) | Waits(S) | Calls | Gets | Reqs | Bytes | Reqs | Bytes |
==========================================================================================================
| 1166 | 521 | 599 | 0.02 | 46 | 144K | 6M | 46546 | 44GB | 11077 | 3GB |
==========================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2230454738)
========================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(S) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | | | (%) | (# samples) | |
========================================================================================================================================================================================================================
| -> 0 | SELECT STATEMENT | | | | 6556 | +468 | 1 | 2M | | | | | | | 0.34 | Cpu (4) | |
| -> 1 | HASH JOIN | | 822M | 423M | 6556 | +468 | 1 | 2M | 1233 | 299MB | 11092 | 3GB | 671M | 3G | 10.09 | Cpu (70) | |
| | | | | | | | | | | | | | | | | direct path write temp (47) | |
| 2 | PART JOIN FILTER CREATE | :BF0000 | 812M | 198M | 589 | +468 | 1 | 41M | | | | | | | 1.81 | Cpu (21) | |
| 3 | NESTED LOOPS | | 812M | 198M | 1055 | +2 | 1 | 41M | | | | | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | TAB2 | 1 | 1 | 1 | +2 | 1 | 1 | 1 | 8192 | | | | | | | |
| 5 | INDEX UNIQUE SCAN | TAB2_PK | 1 | 1 | 1053 | +2 | 1 | 1 | 1 | 8192 | | | | | | | |
| 6 | PARTITION RANGE ITERATOR | | 812M | 198M | 589 | +468 | 1 | 41M | | | | | | | | | |
| 7 | TABLE ACCESS FULL | TAB1_ADD | 812M | 198M | 1055 | +2 | 2 | 41M | 40795 | 39GB | | | | | 81.55 | Cpu (488) | |
| | | | | | | | | | | | | | | | | direct path read (458) | |
| -> 8 | PARTITION RANGE JOIN-FILTER | | 21G | 168M | 5964 | +1060 | 1 | 14M | | | | | | | | | |
| -> 9 | TABLE ACCESS FULL | TAB1 | 21G | 168M | 5968 | +1056 | 1 | 14M | 4518 | 4GB | | | | | 6.21 | Cpu (10) | 0% |
| | | | | | | | | | | | | | | | | direct path read (62) | |
========================================================================================================================================================================================================================
Execution Plan
----------------------------------------------------------
Plan hash value: 2230454738
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 824M| 84G| | 423M (2)|485:15:46 | | |
|* 1 | HASH JOIN | | 824M| 84G| 60G| 423M (2)|485:15:46 | | |
| 2 | PART JOIN FILTER CREATE | :BF0000 | 813M| 51G| | 198M (3)|227:36:25 | | |
| 3 | NESTED LOOPS | | 813M| 51G| | 198M (3)|227:36:25 | | |
|* 4 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 24 | | 1 (0)| 00:00:01 | | |
|* 5 | INDEX UNIQUE SCAN | TAB2_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE ITERATOR | | 813M| 33G| | 198M (3)|227:36:25 | KEY | KEY |
|* 7 | TABLE ACCESS FULL | TAB1_ADD | 813M| 33G| | 198M (3)|227:36:25 | KEY | KEY |
| 8 | PARTITION RANGE JOIN-FILTER | | 20G| 807G| | 167M (2)|192:37:59 |:BF0000|:BF0000|
| 9 | TABLE ACCESS FULL | TAB1 | 20G| 807G| | 167M (2)|192:37:59 |:BF0000|:BF0000|
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TAB1"."T_DATE"="TAB1_ADD"."T_DATE" AND "TAB1"."SIN"="TAB1_ADD"."SIN" AND
"TAB1"."DSNBR"="TAB1_ADD"."DSNBR")
4 - filter(NVL(UPPER("TAB2"."LSCD"),'F')='TAB2')
5 - access("TAB2"."WEID"=2)
7 - filter("TAB1_ADD"."T_DATE">TO_DATE(TO_CHAR(INTERNAL_FUNCTION("TAB2"."LESTS"),'DD-MON-YYYY
HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') AND "TAB1_ADD"."T_DATE"<"TAB2"."STS")
**************************** By Removing Control Table from the query
SELECT TAB1.MNBER,
TAB1.SIN,
TAB1.TNBR,
TAB1_ADD.HIADR
FROM TAB1_ADD TAB1_ADD, TAB1 TAB1
WHERE A.T_DATE > to_date('17-OCT-2017 21:58:00' ,'DD-MON-YYYY HH24:MI:SS')AND A.T_DATE < to_date('18-OCT-2017 21:58:00' ,'DD-MON-YYYY HH24:MI:SS')
AND TAB1.T_DATE = TAB1_ADD.T_DATE
AND TAB1.SIN = TAB1_ADD.SIN
AND TAB1.DSNBR = TAB1_ADD.DSNBR;
Global Information
------------------------------
Status : EXECUTING
Execution Started : 10/19/2017 07:29:55
First Refresh Time : 10/19/2017 07:30:05
Last Refresh Time : 10/19/2017 08:23:42
Duration : 3229s
Fetch Calls : 59450
Global Stats
============================================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read | Write | Write |
| Time(S) | Time(S) | Waits(S) | Waits(S) | Calls | Gets | Reqs | Bytes | Reqs | Bytes |
============================================================================================
| 591 | 92 | 488 | 12 | 59450 | 4M | 31662 | 29GB | 9285 | 2GB |
============================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2440603386)
===================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(S) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | | | (%) | (# samples) | |
===================================================================================================================================================================================================================
| -> 0 | SELECT STATEMENT | | | | 2991 | +239 | 1 | 892K | | | | | | | 0.50 | Cpu (3) | |
| -> 1 | HASH JOIN | | 41M | 2M | 2991 | +239 | 1 | 892K | 1385 | 335MB | 9285 | 2GB | 659M | 2G | 16.86 | Cpu (59) | |
| | | | | | | | | | | | | | | | | direct path read temp (3) | |
| | | | | | | | | | | | | | | | | direct path write temp (39) | |
| 2 | PART JOIN FILTER CREATE | :BF0000 | 41M | 691K | 300 | +239 | 1 | 41M | | | | | | | 1.50 | Cpu (9) | |
| 3 | PARTITION RANGE ITERATOR | | 41M | 691K | 300 | +239 | 1 | 41M | | | | | | | | | |
| 4 | TABLE ACCESS FULL | TAB1 | 41M | 691K | 537 | +2 | 2 | 41M | 26879 | 26GB | | | | | 72.45 | Cpu (37) | 100% |
| | | | | | | | | | | | | | | | | direct path read (397) | |
| -> 5 | PARTITION RANGE AND | | 41M | 1M | 2686 | +544 | 1 | 5M | | | | | | | | | |
| -> 6 | TABLE ACCESS FULL | TAB1_ADD | 41M | 1M | 2692 | +538 | 1 | 5M | 2756 | 3GB | | | | | 8.68 | Cpu (5) | 7% |
| | | | | | | | | | | | | | | | | direct path read (47) | |
===================================================================================================================================================================================================================
Execution Plan
----------------------------------------------------------
Plan hash value: 2440603386
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40M| 3353M| | 1925K (2)| 02:12:32 | | |
|* 1 | HASH JOIN | | 40M| 3353M| 2105M| 1925K (2)| 02:12:32 | | |
| 2 | PART JOIN FILTER CREATE | :BF0000 | 40M| 1637M| | 690K (2)| 00:47:32 | | |
| 3 | PARTITION RANGE ITERATOR| | 40M| 1637M| | 690K (2)| 00:47:32 | 507 | 508 |
|* 4 | TABLE ACCESS FULL | TAB1 | 40M| 1637M| | 690K (2)| 00:47:32 | 507 | 508 |
| 5 | PARTITION RANGE AND | | 41M| 1720M| | 1018K (2)| 01:10:08 |KEY(AP)|KEY(AP)|
|* 6 | TABLE ACCESS FULL | TAB1_ADD | 41M| 1720M| | 1018K (2)| 01:10:08 |KEY(AP)|KEY(AP)|
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TAB1"."T_DATE"="TAB1_ADD"."T_DATE" AND "TAB1"."SIN"="TAB1_ADD"."SIN" AND
"TAB1"."DSNBR"="TAB1_ADD"."DSNBR")
4 - filter("TAB1"."T_DATE">TO_DATE(' 2017-10-17 21:58:00', 'syyyy-mm-dd hh24:mi:ss') AND
"TAB1"."T_DATE"<TO_DATE(' 2017-10-18 21:58:00', 'syyyy-mm-dd hh24:mi:ss'))
6 - filter("TAB1_ADD"."T_DATE">TO_DATE(' 2017-10-17 21:58:00', 'syyyy-mm-dd hh24:mi:ss') AND
"TAB1_ADD"."T_DATE"<TO_DATE(' 2017-10-18 21:58:00', 'syyyy-mm-dd hh24:mi:ss'))