Hi We are using version 11.2.0.4.0 of oracle. we have below query which is running 3hrs+ in production, ia mtryin to tune this query. I ran it manually and captured the realtime monitor for same for around ~1hr and then killed the query so that it wont impact others. Here there are two key tables TDE and TFA used in this query which are range partitioned daily on column SDT and each partition holds ~150million records. Now the default execution path chosen by the optimizer went for scanning table TDE using index TDE_IX1, which is a composite index on column (DID,TDID,SID), column DID being not much unique values. and i see from real time monitor ,this query spends ~80% of the time on scanning this table TDE using index.this query needs to scan over two days data i.e two partitions ~300million records. i see there is big variation in expected and actual rows estimation for this table, i was wondering how can i fix this?
And then i thought if FTS on this big table TDE would be helpful and tried forcing that by hint and ran the query, i see from the real time monitoring plan that the query already 115k records inthat hour of execution. but the default path of execution had fetched ~36K over that hour of execution, so FULL scan of TDE look better. but i was trying to understand if i can fix the stats so that optimizer opts for the FTS on the big table TDE its own , without any hint as such? or it has to go via this hint and to make it more faster, i need to drive the FTS in paralle may be?
I also tried puttingt both the big table TDE and TFA as FULL and tried running the query, but i see it ran over ~45 mintes without resulting any records, so i killed that one.
Need experts view on this to make this query run faster.
SELECT TDE.ILOC ,
ST.VUM,
ST.MEI,
TDE.ANBR,
.....
FROM TDE,
TFA,
(SELECT DID, VUM
FROM TAB2
WHERE PMCD ='CC'
AND PRC = :B2
AND RDT = to_date(:B1,'DD-MON-RRRR hh24:mi:ss')
AND NVL (ADFLG, 'N') = 'Y') ST
WHERE (TFA.SDT >= to_date(:B9,'DD-MON-RRRR HH24:mi:ss') AND TFA.SDT < to_date(:B8,'DD-MON-RRRR hh24:mi:ss'))
AND (TDE.SDT >= to_date(:B9,'DD-MON-RRRR hh24:mi:ss') AND TDE.SDT < to_date(:B8,'DD-MON-RRRR hh24:mi:ss'))
AND (TDE.CRT_DT > to_date(:B7,'DD-MON-RRRR hh24:mi:ss') AND TDE.CRT_DT <= to_date(:B6,'DD-MON-RRRR hh24:mi:ss'))
AND TFA.OAR IN (SELECT ADCD FROM TAUAD)
AND TDE.TDID = TFA.TDID
AND TDE.SDT = TFA.SDT
AND TDE.DID = ST.DID
AND TDE.ACD IN ('AB','AC','AD','AW','AX')
AND TDE.MCD IN ('CC','DC')
AND NVL (TDE.ILOC, '1') IN (:B5, :B4)
AND NVL (TDE.DTM, '0') IN ('0', '1');
-- Realtime monitor for Default path of execution
Global Information
------------------------------
Status : EXECUTING
Execution Started : 04/26/2017 07:53:38
First Refresh Time : 04/26/2017 07:53:38
Last Refresh Time : 04/26/2017 09:08:43
Duration : 4507s
Fetch Calls : 2416
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :B2 | 1 | VARCHAR2(32) | BKK |
| :B1 | 2 | VARCHAR2(32) | 17-MAR-2017 |
| :B9 | 3 | VARCHAR2(32) | 16-MAR-2017 |
| :B8 | 4 | VARCHAR2(32) | 18-MAR-2017 |
| :B7 | 7 | VARCHAR2(32) | 16-MAR-2017 |
| :B6 | 8 | VARCHAR2(32) | 17-MAR-2017 |
| :B5 | 9 | VARCHAR2(32) | 1 |
| :B4 | 10 | VARCHAR2(32) | 2 |
========================================================================================================================
Global Stats
==============================================================================
| Elapsed | Cpu | IO | Concurrency | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
==============================================================================
| 4463 | 185 | 4278 | 0.00 | 2416 | 2M | 2M | 12GB |
==============================================================================
SQL Plan Monitoring Details (Plan Hash Value=1354753063)
===================================================================================================================================================================================================================
| 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 | | | | 4078 | +1 | 1 | 36226 | | | | |
| 1 | FILTER | | | | 4078 | +1 | 1 | 36226 | | | | |
| 2 | NESTED LOOPS | | 1 | 24M | 4078 | +1 | 1 | 36226 | | | | |
| 3 | NESTED LOOPS | | 1 | 24M | 4078 | +1 | 1 | 247K | | | | |
| 4 | NESTED LOOPS | | 1 | 24M | 4078 | +1 | 1 | 247K | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | TAb2 | 129 | 13367 | 3483 | +1 | 1 | 114 | 159 | 1MB | | |
| -> 6 | INDEX SKIP SCAN | TAb2_UN1 | 3536 | 9745 | 4507 | +1 | 1 | 4121 | 4576 | 36MB | 0.07 | db file sequential read (3) |
| 7 | PARTITION RANGE ITERATOR | | 1 | 183K | 4078 | +1 | 114 | 247K | | | | |
| -> 8 | TABLE ACCESS BY LOCAL INDEX ROWID | TDE | 1 | 183K | 4507 | +1 | 228 | 247K | 1M | 11GB | 89.10 | Cpu (86) |
| | | | | | | | | | | | | latch: object queue header operation (1) |
| | | | | | | | | | | | | db file sequential read (3756) |
| -> 9 | INDEX RANGE SCAN | TDE_IX1 | 558K | 4884 | 4507 | +1 | 228 | 4M | 37113 | 290MB | 2.41 | Cpu (9) |
| | | | | | | | | | | | | db file sequential read (95) |
| 10 | PARTITION RANGE AND | | 1 | 3 | 4078 | +1 | 247K | 247K | | | 0.05 | Cpu (2) |
| 11 | TABLE ACCESS BY LOCAL INDEX ROWID | TFA | 1 | 3 | 4078 | +1 | 247K | 247K | 121K | 944MB | 5.63 | Cpu (6) |
| | | | | | | | | | | | | db file sequential read (237) |
| 12 | INDEX RANGE SCAN | TFA_IX1 | 5 | 3 | 4078 | +1 | 247K | 247K | 55805 | 436MB | 2.74 | Cpu (8) |
| | | | | | | | | | | | | db file sequential read (110) |
| 13 | INDEX UNIQUE SCAN | TAUAD_PK | 1 | | 4078 | +1 | 247K | 36226 | | | | |
===================================================================================================================================================================================================================
Execution Plan by set autotrace traceonly explain
----------------------------------------------------------
Plan hash value: 1354753063
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 203 | 23M (1)| 79:19:50 | | |
|* 1 | FILTER | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 203 | 23M (1)| 79:19:50 | | |
| 3 | NESTED LOOPS | | 1 | 199 | 23M (1)| 79:19:50 | | |
| 4 | NESTED LOOPS | | 156 | 27924 | 23M (1)| 79:19:44 | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | TAb2 | 129 | 6966 | 13367 (1)| 00:02:41 | | |
|* 6 | INDEX SKIP SCAN | TAb2_UN1 | 3536 | | 9745 (1)| 00:01:57 | | |
| 7 | PARTITION RANGE ITERATOR | | 1 | 125 | 184K (1)| 00:36:53 | KEY | KEY |
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID| TDE | 1 | 125 | 184K (1)| 00:36:53 | KEY | KEY |
|* 9 | INDEX RANGE SCAN | TDE_IX1 | 558K| | 5661 (1)| 00:01:08 | KEY | KEY |
| 10 | PARTITION RANGE AND | | 1 | 20 | 3 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 11 | TABLE ACCESS BY LOCAL INDEX ROWID | TFA | 1 | 20 | 3 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 12 | INDEX RANGE SCAN | TFA_IX1 | 5 | | 3 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 13 | INDEX UNIQUE SCAN | TAUAD_PK | 1 | 4 | 0 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(:B8,'DD-MON-RRRR hh24:mi:ss')>TO_DATE(:B9,'DD-MON-RRRR HH24:mi:ss') AND TO_DATE(:B6,'DD-MON-RRRR
hh24:mi:ss')>TO_DATE(:B7,'DD-MON-RRRR hh24:mi:ss') AND TO_DATE(:B8,'DD-MON-RRRR hh24:mi:ss')>TO_DATE(:B9,'DD-MON-RRRR
hh24:mi:ss'))
5 - filter(NVL("ADFLG",'N')='Y')
6 - access("RDT"=TO_DATE(:B1,'DD-MON-RRRR hh24:mi:ss') AND "PMCD"='CC' AND "PRC"=:B2)
filter("PMCD"='CC' AND "PRC"=:B2 AND "RDT"=TO_DATE(:B1,'DD-MON-RRRR hh24:mi:ss'))
8 - filter(("TDE"."MCD"='CC' OR "TDE"."MCD"='DC') AND "TDE"."SDT"<TO_DATE(:B8,'DD-MON-RRRR hh24:mi:ss') AND
"TDE"."CRT_DT">TO_DATE(:B7,'DD-MON-RRRR hh24:mi:ss') AND "TDE"."CRT_DT"<=TO_DATE(:B6,'DD-MON-RRRR hh24:mi:ss') AND
("TDE"."ACD"='AB' OR "TDE"."ACD"='AC' OR "TDE"."ACD"='AD' OR "TDE"."ACD"='AW' OR "TDE"."ACD"='AX'
) AND (NVL("TDE"."ILOC",'1')=:B5 OR NVL("TDE"."ILOC",'1')=:B4) AND
"TDE"."SDT">=GREATEST(TO_DATE(:B9,'DD-MON-RRRR hh24:mi:ss'),TO_DATE(:B9,'DD-MON-RRRR HH24:mi:ss')) AND
(NVL("TDE"."DTM",'0')='0' OR NVL("TDE"."DTM",'0')='1'))
9 - access("TDE"."DID"="DID")
11 - filter("TDAU"."SDT"<TO_DATE(:B8,'DD-MON-RRRR hh24:mi:ss') AND "TDAU"."SDT">=GREATEST(TO_DATE(:B9,'DD-MON-RRRR
HH24:mi:ss'),TO_DATE(:B9,'DD-MON-RRRR hh24:mi:ss')) AND "TDE"."SDT"="TDAU"."SDT")
12 - access("TDE"."TDID"="TDAU"."TDID")
13 - access("TDAU"."OAR"="ADCD")
---- Realtime monitor with FULL(TDE)
SQL Monitoring Report
Global Information
------------------------------
Status : EXECUTING
Execution Started : 04/26/2017 08:05:05
First Refresh Time : 04/26/2017 08:05:05
Last Refresh Time : 04/26/2017 09:17:15
Duration : 4331s
Fetch Calls : 7653
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :B2 | 1 | VARCHAR2(32) | BKK |
| :B1 | 2 | VARCHAR2(32) | 17-MAR-2017 |
| :B9 | 3 | VARCHAR2(32) | 16-MAR-2017 |
| :B8 | 4 | VARCHAR2(32) | 18-MAR-2017 |
| :B7 | 7 | VARCHAR2(32) | 16-MAR-2017 |
| :B6 | 8 | VARCHAR2(32) | 17-MAR-2017 |
| :B5 | 9 | VARCHAR2(32) | 1 |
| :B4 | 10 | VARCHAR2(32) | 2 |
========================================================================================================================
Global Stats
==============================================================================
| Elapsed | Cpu | IO | Concurrency | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
==============================================================================
| 4025 | 721 | 3304 | 0.01 | 7653 | 53M | 3M | 99GB |
==============================================================================
SQL Plan Monitoring Details (Plan Hash Value=3362658732)
==============================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) | |
==============================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 3801 | +36 | 1 | 115K | | | 0.13 | Cpu (5) | |
| 1 | FILTER | | | | 3801 | +36 | 1 | 115K | | | | | |
| 2 | NESTED LOOPS | | 1 | 225M | 3801 | +36 | 1 | 115K | | | 0.03 | Cpu (1) | |
| 3 | NESTED LOOPS | | 1 | 225M | 3801 | +36 | 1 | 575K | | | | | |
| 4 | NESTED LOOPS | | 1 | 225M | 3801 | +36 | 1 | 1M | | | 0.08 | Cpu (3) | |
| 5 | NESTED LOOPS | | 1 | 225M | 3801 | +36 | 1 | 13M | | | 0.05 | Cpu (2) | |
| 6 | PARTITION RANGE ITERATOR | | 1 | 225M | 3801 | +36 | 1 | 13M | | | | | |
| -> 7 | TABLE ACCESS FULL | TDE | 1 | 225M | 4332 | +1 | 2 | 13M | 156K | 76GB | 32.04 | Cpu (260) | 50% |
| | | | | | | | | | | | | db file sequential read (1) | |
| | | | | | | | | | | | | direct path read (1009) | |
| 8 | PARTITION RANGE AND | | 1 | 3 | 3801 | +36 | 13M | 13M | | | 1.21 | Cpu (48) | |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID | TFA | 1 | 3 | 3801 | +36 | 13M | 13M | 3M | 21GB | 50.61 | Cpu (193) | |
| | | | | | | | | | | | | latch: object queue header operation (1) | |
| | | | | | | | | | | | | db file sequential read (1812) | |
| 10 | INDEX RANGE SCAN | TFA_IX1 | 5 | 3 | 3801 | +36 | 13M | 13M | 344K | 3GB | 15.04 | Cpu (147) | |
| | | | | | | | | | | | | db file sequential read (449) | |
| 11 | INDEX UNIQUE SCAN | TAUAD_PK | 1 | | 3801 | +36 | 13M | 1M | | | 0.45 | Cpu (18) | |
| -> 12 | INDEX UNIQUE SCAN | TAb2_UN1 | 1 | 1 | 4297 | +36 | 1M | 575K | 1393 | 11MB | 0.28 | Cpu (11) | |
| 13 | TABLE ACCESS BY INDEX ROWID | TAb2 | 1 | 2 | 3801 | +36 | 575K | 115K | 30 | 240KB | 0.10 | Cpu (4) | |
==============================================================================================================================================================================================================================
Execution Plan by set autotrace traceonly explain
----------------------------------------------------------
Plan hash value: 4016894561
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 203 | 225M (2)|752:09:36 | | |
|* 1 | FILTER | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 203 | 225M (2)|752:09:36 | | |
| 3 | NESTED LOOPS | | 1 | 199 | 225M (2)|752:09:36 | | |
|* 4 | HASH JOIN | | 156 | 27924 | 225M (2)|752:09:30 | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | TAb2 | 129 | 6966 | 13367 (1)| 00:02:41 | | |
|* 6 | INDEX SKIP SCAN | TAb2_UN1 | 3536 | | 9745 (1)| 00:01:57 | | |
| 7 | PARTITION RANGE ITERATOR | | 21847 | 2666K| 225M (2)|752:06:50 | KEY | KEY |
|* 8 | TABLE ACCESS FULL | TDE | 21847 | 2666K| 225M (2)|752:06:50 | KEY | KEY |
| 9 | PARTITION RANGE AND | | 1 | 20 | 3 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 10 | TABLE ACCESS BY LOCAL INDEX ROWID| TFA | 1 | 20 | 3 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 11 | INDEX RANGE SCAN | TFA_IX1 | 5 | | 3 (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 12 | INDEX UNIQUE SCAN | TAUAD_PK | 1 | 4 | 0 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(:B8,'DD-MON-RRRR hh24:mi:ss')>TO_DATE(:B9,'DD-MON-RRRR HH24:mi:ss') AND TO_DATE(:B6,'DD-MON-RRRR
hh24:mi:ss')>TO_DATE(:B7,'DD-MON-RRRR hh24:mi:ss') AND TO_DATE(:B8,'DD-MON-RRRR hh24:mi:ss')>TO_DATE(:B9,'DD-MON-RRRR
hh24:mi:ss'))
4 - access("TDE"."DID"="DID")
5 - filter(NVL("ADFLG",'N')='Y')
6 - access("RDT"=TO_DATE(:B1,'DD-MON-RRRR hh24:mi:ss') AND "PMCD"='CC' AND "PRC"=:B2)
filter("PMCD"='CC' AND "PRC"=:B2 AND "RDT"=TO_DATE(:B1,'DD-MON-RRRR hh24:mi:ss'))
8 - filter(("TDE"."MCD"='CC' OR "TDE"."MCD"='DC') AND "TDE"."SDT"<TO_DATE(:B8,'DD-MON-RRRR hh24:mi:ss') AND
"TDE"."CRT_DT">TO_DATE(:B7,'DD-MON-RRRR hh24:mi:ss') AND "TDE"."CRT_DT"<=TO_DATE(:B6,'DD-MON-RRRR hh24:mi:ss') AND
("TDE"."ACD"='AB' OR "TDE"."ACD"='AC' OR "TDE"."ACD"='AD' OR "TDE"."ACD"='AW' OR "TDE"."ACD"='AX'
) AND (NVL("TDE"."ILOC",'1')=:B5 OR NVL("TDE"."ILOC",'1')=:B4) AND
"TDE"."SDT">=GREATEST(TO_DATE(:B9,'DD-MON-RRRR hh24:mi:ss'),TO_DATE(:B9,'DD-MON-RRRR HH24:mi:ss')) AND
(NVL("TDE"."DTM",'0')='0' OR NVL("TDE"."DTM",'0')='1'))
10 - filter("TDAU"."SDT"<TO_DATE(:B8,'DD-MON-RRRR hh24:mi:ss') AND "TDAU"."SDT">=GREATEST(TO_DATE(:B9,'DD-MON-RRRR
HH24:mi:ss'),TO_DATE(:B9,'DD-MON-RRRR hh24:mi:ss')) AND "TDE"."SDT"="TDAU"."SDT")
11 - access("TDE"."TDID"="TDAU"."TDID")
12 - access("TDAU"."OAR"="ADCD")
------ Realtime monitor with both FULL(TDE) and FULL(TFA)
SQL Monitoring Report
Global Information
------------------------------
Status : EXECUTING
Execution Started : 04/26/2017 08:11:59
First Refresh Time : 04/26/2017 08:11:59
Last Refresh Time : 04/26/2017 08:55:19
Duration : 2602s
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :B2 | 1 | VARCHAR2(32) | BKK |
| :B1 | 2 | VARCHAR2(32) | 17-MAR-2017 |
| :B9 | 3 | VARCHAR2(32) | 16-MAR-2017 |
| :B8 | 4 | VARCHAR2(32) | 18-MAR-2017 |
| :B7 | 7 | VARCHAR2(32) | 16-MAR-2017 |
| :B6 | 8 | VARCHAR2(32) | 17-MAR-2017 |
| :B5 | 9 | VARCHAR2(32) | 1 |
| :B4 | 10 | VARCHAR2(32) | 2 |
========================================================================================================================
Global Stats
===================================================================
| Elapsed | Cpu | IO | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
===================================================================
| 2599 | 311 | 2224 | 64 | 20M | 312K | 152GB |
===================================================================
SQL Plan Monitoring Details (Plan Hash Value=3774041560)
======================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) | |
======================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | | |
| 1 | FILTER | | | | | | 1 | | | | | | |
| 2 | NESTED LOOPS | | 1 | 314M | | | 1 | | | | | | |
| 3 | NESTED LOOPS | | 1 | 314M | | | 1 | | | | | | |
| 4 | NESTED LOOPS | | 1 | 314M | 2343 | +91 | 1 | 0 | | | | | |
| 5 | NESTED LOOPS | | 1 | 314M | 2375 | +59 | 1 | 5 | | | | | |
| 6 | PARTITION RANGE ITERATOR | | 1 | 225M | 2337 | +59 | 1 | 5 | | | | | |
| -> 7 | TABLE ACCESS FULL | TDE | 1 | 225M | 2602 | +1 | 1 | 5 | 6922 | 3GB | 2.15 | Cpu (8) | 0% |
| | | | | | | | | | | | | direct path read (47) | |
| 8 | PARTITION RANGE AND | | 1 | 88M | 2343 | +91 | 5 | 5 | | | | | |
| -> 9 | TABLE ACCESS FULL | TFA | 1 | 88M | 2546 | +57 | 5 | 5 | 306K | 149GB | 97.85 | Cpu (336) | 80% |
| | | | | | | | | | | | | db file sequential read (1) | |
| | | | | | | | | | | | | direct path read (2170) | |
| 10 | INDEX UNIQUE SCAN | TAUAD_PK | 1 | | | | 5 | | | | | | |
| 11 | INDEX UNIQUE SCAN | TAb2_UN1 | 1 | 1 | | | | | | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | TAb2 | 1 | 2 | | | | | | | | | |
======================================================================================================================================================================================================