DB version: 11.2.0.4
The below query runs in less than 7 seconds if I do the count only for 10 days. ie
OH.CREATED >= to_date ('01/04/2018 00:00:00', 'dd/mm/yyyy hh24:mi:ss') -1/3
AND
OH.CREATED <= to_date('10/04/2018 23:59:59', 'dd/mm/yyyy hh24:mi:ss') -1/3
But, the query never finish executing if I do the count for 30 days ie.
OH.CREATED >= to_date ('01/04/2018 00:00:00', 'dd/mm/yyyy hh24:mi:ss') -1/3
AND
OH.CREATED <= to_date('30/04/2018 23:59:59', 'dd/mm/yyyy hh24:mi:ss') -1/3
They both use different execution plans with different SQL_IDs. How can I improve the performance of count query for 30 days ?
Row source execution plan generated using /*+ GATHER_PLAN_STATISTICS */ included below.
The query
========
SELECT count (DISTINCT OH.ROW_ID)
FROM
SIEBEL.S_ORDER OH,
SIEBEL.S_ORDER_ITEM OLI,
SIEBEL.S_ORDER_DTL DTL,
SIEBEL.S_ORDER_ITEM RLI,
SIEBEL.S_PROD_INT PRMPRD,
SIEBEL.S_PROD_INT OLIPRD,
SIEBEL.S_VOD VOD1,
SIEBEL.S_ISS_OBJ_DEF OBJDEF,
SIEBEL.S_VOD VOD2
WHERE
OH.ROW_ID = OLI.ORDER_ID
AND OH.ROW_ID = DTL.PAR_ROW_ID
AND OLI.PROD_ID = OLIPRD.ROW_ID
AND OLIPRD.CFG_MODEL_ID = VOD1.OBJECT_NUM
AND VOD1.ROW_ID = OBJDEF.VOD_ID
AND OBJDEF.PAR_VOD_ID= VOD2.OBJECT_NUM
AND VOD2.VOD_NAME IN ('FUP ICC','FUP Revamp ICC','Components and Network ICC','Volume Top Up ICC','Data Max Up ICC')
AND RLI.ROW_ID = OLI.ROOT_ORDER_ITEM_ID
AND RLI.PROMOTION_ID = PRMPRD.ROW_ID
AND OH.CREATED >= to_date ('01/04/2018 00:00:00', 'dd/mm/yyyy hh24:mi:ss') -1/3
AND OH.CREATED <= to_date('30/04/2018 23:59:59', 'dd/mm/yyyy hh24:mi:ss') -1/3
AND OH.X_ORDER_SUB_TYPE = 'Add/Remove/Modify VAS'
AND OH.STATUS_CD = 'Complete'
AND OLI.ACTION_CD = 'Add'
AND ( nvl(OLI.COMPLETED_DT, OLI.DB_LAST_UPD)- nvl(DTL.SUBMIT_DT, OH.CREATED)) * 86400 <= 300 ;
The bad plan (for 30 days) where the query never finishes executing
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'allstats last +cost'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID b233x2fsg2qk1, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ count (DISTINCT OH.ROW_ID) FROM
SIEBEL.S_ORDER OH, SIEBEL.S_ORDER_ITEM OLI, SIEBEL.S_ORDER_DTL DTL,
SIEBEL.S_ORDER_ITEM RLI, SIEBEL.S_PROD_INT PRMPRD, SIEBEL.S_PROD_INT
OLIPRD, SIEBEL.S_VOD VOD1, SIEBEL.S_ISS_OBJ_DEF OBJDEF, SIEBEL.S_VOD
VOD2 WHERE OH.ROW_ID = OLI.ORDER_ID AND OH.ROW_ID = DTL.PAR_ROW_ID AND
OLI.PROD_ID = OLIPRD.ROW_ID AND OLIPRD.CFG_MODEL_ID = VOD1.OBJECT_NUM
AND VOD1.ROW_ID = OBJDEF.VOD_ID AND OBJDEF.PAR_VOD_ID= VOD2.OBJECT_NUM
AND VOD2.VOD_NAME IN ('FUP ICC','FUP Revamp ICC','Components and
Network ICC','Volume Top Up ICC','Data Max Up ICC') AND RLI.ROW_ID =
OLI.ROOT_ORDER_ITEM_ID AND RLI.PROMOTION_ID = PRMPRD.ROW_ID AND
OH.CREATED >= to_date ('01/04/2018 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
-1/3 AND OH.CREATED <= to_date('30/04/2018 23:59:59', 'dd/mm/yyyy
hh24:mi:ss') -1/3 AND OH.X_ORDER_SUB_TYPE = 'Add/Remove/Modify VAS'
AND OH.STATUS_CD = 'Complete' AND OLI.ACTION_CD = 'Add' AND (
nvl(OLI.COMPLETED_DT, OLI.DB_LAST_UPD)- nv
Plan hash value: 3682714848
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 19129 (100)| 0 |00:00:00.01 | 0 | 0 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | 0 |00:00:00.01 | 0 | 0 | | | |
| 2 | VIEW | VW_DAG_0 | 1 | 69 | 19129 (1)| 0 |00:00:00.01 | 0 | 0 | | | |
| 3 | HASH GROUP BY | | 1 | 69 | 19129 (1)| 0 |00:00:00.01 | 0 | 0 | 750K| 750K| |
| 4 | NESTED LOOPS | | 1 | 69 | 19128 (1)| 1 |00:01:01.23 | 41572 | 27644 | | | |
| 5 | NESTED LOOPS | | 1 | 69 | 19127 (1)| 1 |00:01:01.23 | 41570 | 27644 | | | |
| 6 | NESTED LOOPS | | 1 | 69 | 19125 (1)| 1 |00:01:01.23 | 41564 | 27644 | | | |
| 7 | NESTED LOOPS | | 1 | 1372 | 19070 (1)| 1 |00:01:01.23 | 41559 | 27644 | | | |
| 8 | NESTED LOOPS | | 1 | 221K| 12424 (1)| 1194 |00:04:38.05 | 105K| 75506 | | | |
| 9 | NESTED LOOPS | | 1 | 7 | 4 (0)| 13 |00:00:00.03 | 60 | 5 | | | |
| 10 | NESTED LOOPS | | 1 | 9 | 3 (0)| 13 |00:00:00.03 | 37 | 5 | | | |
| 11 | NESTED LOOPS | | 1 | 9 | 2 (0)| 13 |00:00:00.03 | 8 | 3 | | | |
| 12 | INLIST ITERATOR | | 1 | | | 1 |00:00:00.01 | 3 | 0 | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| S_VOD | 1 | 5 | 1 (0)| 1 |00:00:00.01 | 3 | 0 | | | |
|* 14 | INDEX RANGE SCAN | S_VOD_U1 | 1 | 5 | 1 (0)| 1 |00:00:00.01 | 2 | 0 | | | |
| 15 | TABLE ACCESS BY INDEX ROWID | S_ISS_OBJ_DEF | 1 | 2 | 1 (0)| 13 |00:00:00.03 | 5 | 3 | | | |
|* 16 | INDEX RANGE SCAN | S_ISS_OBJ_DEF_M1 | 1 | 75 | 1 (0)| 13 |00:00:00.01 | 2 | 0 | | | |
| 17 | TABLE ACCESS BY INDEX ROWID | S_VOD | 13 | 1 | 1 (0)| 13 |00:00:00.01 | 29 | 2 | | | |
|* 18 | INDEX UNIQUE SCAN | S_VOD_P1 | 13 | 1 | 1 (0)| 13 |00:00:00.01 | 16 | 0 | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | S_PROD_INT | 13 | 1 | 1 (0)| 13 |00:00:00.01 | 23 | 0 | | | |
|* 20 | INDEX RANGE SCAN | S_PROD_INT_F9 | 13 | 1 | 1 (0)| 13 |00:00:00.01 | 15 | 0 | | | |
|* 21 | TABLE ACCESS BY INDEX ROWID | S_ORDER_ITEM | 13 | 29646 | 1774 (1)| 1194 |00:03:26.28 | 105K| 75501 | | | |
|* 22 | INDEX RANGE SCAN | S_ORDER_ITEM_F1 | 13 | 186K| 6 (0)| 88610 |00:00:00.49 | 338 | 308 | | | |
|* 23 | TABLE ACCESS BY INDEX ROWID | S_ORDER | 1194 | 1 | 1 (0)| 1 |00:00:02.05 | 4672 | 422 | | | |
|* 24 | INDEX UNIQUE SCAN | S_ORDER_P1 | 1194 | 1 | 1 (0)| 1194 |00:00:00.58 | 3587 | 18 | | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | S_ORDER_DTL | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 5 | 0 | | | |
|* 26 | INDEX RANGE SCAN | S_ORDER_DTL_U1 | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 4 | 0 | | | |
|* 27 | TABLE ACCESS BY INDEX ROWID | S_ORDER_ITEM | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 6 | 0 | | | |
|* 28 | INDEX UNIQUE SCAN | S_ORDER_ITEM_P1 | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 4 | 0 | | | |
|* 29 | INDEX UNIQUE SCAN | S_PROD_INT_P1 | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 2 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
14 - access(("VOD2"."VOD_NAME"='Components and Network ICC' OR "VOD2"."VOD_NAME"='Data Max Up ICC' OR "VOD2"."VOD_NAME"='FUP ICC' OR "VOD2"."VOD_NAME"='FUP
Revamp ICC' OR "VOD2"."VOD_NAME"='Volume Top Up ICC'))
16 - access("OBJDEF"."PAR_VOD_ID"="VOD2"."OBJECT_NUM")
18 - access("VOD1"."ROW_ID"="OBJDEF"."VOD_ID")
20 - access("OLIPRD"."CFG_MODEL_ID"="VOD1"."OBJECT_NUM")
21 - filter("OLI"."ACTION_CD"='Add')
22 - access("OLI"."PROD_ID"="OLIPRD"."ROW_ID")
23 - filter(("OH"."CREATED">=TO_DATE(' 2018-03-31 16:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "OH"."X_ORDER_SUB_TYPE"='Add/Remove/Modify VAS' AND
"OH"."STATUS_CD"='Complete' AND "OH"."CREATED"<=TO_DATE(' 2018-04-30 15:59:59', 'syyyy-mm-dd hh24:mi:ss')))
24 - access("OH"."ROW_ID"="OLI"."ORDER_ID")
25 - filter((NVL("OLI"."COMPLETED_DT","OLI"."DB_LAST_UPD")-NVL("DTL"."SUBMIT_DT","OH"."CREATED"))*86400<=300)
26 - access("OH"."ROW_ID"="DTL"."PAR_ROW_ID")
27 - filter("RLI"."PROMOTION_ID" IS NOT NULL)
28 - access("RLI"."ROW_ID"="OLI"."ROOT_ORDER_ITEM_ID")
29 - access("RLI"."PROMOTION_ID"="PRMPRD"."ROW_ID")
74 rows selected.
The good plan (for 10 days) which executes in less than 7 seconds
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID d43165mz8h40u, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ count (DISTINCT OH.ROW_ID) FROM
SIEBEL.S_ORDER OH, SIEBEL.S_ORDER_ITEM OLI, SIEBEL.S_ORDER_DTL DTL,
SIEBEL.S_ORDER_ITEM RLI, SIEBEL.S_PROD_INT PRMPRD, SIEBEL.S_PROD_INT
OLIPRD, SIEBEL.S_VOD VOD1, SIEBEL.S_ISS_OBJ_DEF OBJDEF, SIEBEL.S_VOD
VOD2 WHERE OH.ROW_ID = OLI.ORDER_ID AND OH.ROW_ID = DTL.PAR_ROW_ID AND
OLI.PROD_ID = OLIPRD.ROW_ID AND OLIPRD.CFG_MODEL_ID = VOD1.OBJECT_NUM
AND VOD1.ROW_ID = OBJDEF.VOD_ID AND OBJDEF.PAR_VOD_ID= VOD2.OBJECT_NUM
AND VOD2.VOD_NAME IN ('FUP ICC','FUP Revamp ICC','Components and
Network ICC','Volume Top Up ICC','Data Max Up ICC') AND RLI.ROW_ID =
OLI.ROOT_ORDER_ITEM_ID AND RLI.PROMOTION_ID = PRMPRD.ROW_ID AND
OH.CREATED >= to_date ('01/04/2018 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
-1/3 AND OH.CREATED <= to_date('10/04/2018 23:59:59', 'dd/mm/yyyy
hh24:mi:ss') -1/3 AND OH.X_ORDER_SUB_TYPE = 'Add/Remove/Modify VAS'
AND OH.STATUS_CD = 'Complete' AND OLI.ACTION_CD = 'Add' AND (
nvl(OLI.COMPLETED_DT, OLI.DB_LAST_UPD)- nv
Plan hash value: 4029838665
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 18175 (100)| 1 |00:00:06.72 | 2408K| | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:06.72 | 2408K| | | |
| 2 | VIEW | VW_DAG_0 | 1 | 28 | 18175 (1)| 8991 |00:00:06.72 | 2408K| | | |
| 3 | HASH GROUP BY | | 1 | 28 | 18175 (1)| 8991 |00:00:06.72 | 2408K| 2403K| 2262K| 1338K (0)|
| 4 | NESTED LOOPS | | 1 | 28 | 18174 (1)| 21988 |00:00:12.02 | 2408K| | | |
| 5 | NESTED LOOPS | | 1 | 28 | 18173 (1)| 21988 |00:00:11.95 | 2387K| | | |
| 6 | NESTED LOOPS | | 1 | 564 | 18151 (1)| 22148 |00:00:11.77 | 2320K| | | |
|* 7 | HASH JOIN | | 1 | 564 | 18134 (1)| 22148 |00:00:11.51 | 2225K| 1888K| 1888K| 772K (0)|
| 8 | INLIST ITERATOR | | 1 | | | 5 |00:00:00.01 | 13 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID | S_VOD | 5 | 5 | 1 (0)| 5 |00:00:00.01 | 13 | | | |
|* 10 | INDEX RANGE SCAN | S_VOD_U1 | 5 | 5 | 1 (0)| 5 |00:00:00.01 | 8 | | | |
|* 11 | HASH JOIN | | 1 | 1005K| 18128 (1)| 298K|00:00:04.05 | 2225K| 1973K| 1857K| 2351K (0)|
| 12 | VIEW | index$_join$_008 | 1 | 16108 | 3 (34)| 15560 |00:00:00.02 | 169 | | | |
|* 13 | HASH JOIN | | 1 | | | 15560 |00:00:00.02 | 169 | 1937K| 1823K| 2096K (0)|
| 14 | INDEX FAST FULL SCAN | S_ISS_OBJ_DEF_M1 | 1 | 16108 | 1 (0)| 15560 |00:00:00.01 | 76 | | | |
| 15 | INDEX FAST FULL SCAN | S_ISS_OBJ_DEF_M2 | 1 | 16108 | 1 (0)| 16128 |00:00:00.01 | 93 | | | |
|* 16 | HASH JOIN | | 1 | 497K| 18120 (1)| 148K|00:00:06.40 | 2224K| 1595K| 1595K| 1840K (0)|
| 17 | VIEW | index$_join$_007 | 1 | 8504 | 3 (34)| 8509 |00:00:00.01 | 107 | | | |
|* 18 | HASH JOIN | | 1 | | | 8509 |00:00:00.01 | 107 | 1541K| 1541K| 1840K (0)|
| 19 | INDEX FAST FULL SCAN | S_VOD_P1 | 1 | 8504 | 1 (0)| 8509 |00:00:00.01 | 46 | | | |
| 20 | INDEX FAST FULL SCAN | S_VOD_U2 | 1 | 8504 | 1 (0)| 8509 |00:00:00.01 | 61 | | | |
|* 21 | HASH JOIN | | 1 | 497K| 18114 (1)| 148K|00:00:06.31 | 2224K| 1509K| 1509K| 1840K (0)|
| 22 | VIEW | index$_join$_006 | 1 | 7031 | 3 (34)| 7036 |00:00:00.01 | 131 | | | |
|* 23 | HASH JOIN | | 1 | | | 7036 |00:00:00.01 | 131 | 1456K| 1456K| 1840K (0)|
| 24 | INDEX FAST FULL SCAN | S_PROD_INT_F9 | 1 | 7031 | 1 (0)| 7036 |00:00:00.01 | 38 | | | |
| 25 | INDEX FAST FULL SCAN | S_PROD_INT_M16 | 1 | 7031 | 1 (0)| 7036 |00:00:00.01 | 93 | | | |
| 26 | NESTED LOOPS | | 1 | 588K| 18108 (1)| 148K|00:00:06.21 | 2224K| | | |
| 27 | NESTED LOOPS | | 1 | 588K| 18108 (1)| 859K|00:00:04.13 | 1066K| | | |
|* 28 | TABLE ACCESS BY INDEX ROWID| S_ORDER | 1 | 138K| 8409 (1)| 80283 |00:00:02.54 | 823K| | | |
|* 29 | INDEX RANGE SCAN | CX1_S_ORDER_X | 1 | 871K| 29 (0)| 796K|00:00:00.24 | 4695 | | | |
|* 30 | INDEX RANGE SCAN | S_ORDER_ITEM_U1 | 80283 | 14 | 1 (0)| 859K|00:00:00.66 | 243K| | | |
|* 31 | TABLE ACCESS BY INDEX ROWID | S_ORDER_ITEM | 859K| 4 | 1 (0)| 148K|00:00:02.22 | 1157K| | | |
|* 32 | TABLE ACCESS BY INDEX ROWID | S_ORDER_ITEM | 22148 | 1 | 1 (0)| 22148 |00:00:00.19 | 95716 | | | |
|* 33 | INDEX UNIQUE SCAN | S_ORDER_ITEM_P1 | 22148 | 1 | 1 (0)| 22148 |00:00:00.10 | 64494 | | | |
|* 34 | TABLE ACCESS BY INDEX ROWID | S_ORDER_DTL | 22148 | 1 | 1 (0)| 21988 |00:00:00.23 | 66566 | | | |
|* 35 | INDEX RANGE SCAN | S_ORDER_DTL_U1 | 22148 | 1 | 1 (0)| 22148 |00:00:00.11 | 57612 | | | |
|* 36 | INDEX UNIQUE SCAN | S_PROD_INT_P1 | 21988 | 1 | 1 (0)| 21988 |00:00:00.04 | 21289 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("OBJDEF"."PAR_VOD_ID"="VOD2"."OBJECT_NUM")
10 - access(("VOD2"."VOD_NAME"='Components and Network ICC' OR "VOD2"."VOD_NAME"='Data Max Up ICC' OR "VOD2"."VOD_NAME"='FUP ICC' OR
"VOD2"."VOD_NAME"='FUP Revamp ICC' OR "VOD2"."VOD_NAME"='Volume Top Up ICC'))
11 - access("VOD1"."ROW_ID"="OBJDEF"."VOD_ID")
13 - access(ROWID=ROWID)
16 - access("OLIPRD"."CFG_MODEL_ID"="VOD1"."OBJECT_NUM")
18 - access(ROWID=ROWID)
21 - access("OLI"."PROD_ID"="OLIPRD"."ROW_ID")
23 - access(ROWID=ROWID)
28 - filter(("OH"."X_ORDER_SUB_TYPE"='Add/Remove/Modify VAS' AND "OH"."STATUS_CD"='Complete'))
29 - access("OH"."CREATED">=TO_DATE(' 2018-03-31 16:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "OH"."CREATED"<=TO_DATE(' 2018-04-10 15:59:59',
'syyyy-mm-dd hh24:mi:ss'))
30 - access("OH"."ROW_ID"="OLI"."ORDER_ID")
31 - filter("OLI"."ACTION_CD"='Add')
32 - filter("RLI"."PROMOTION_ID" IS NOT NULL)
33 - access("RLI"."ROW_ID"="OLI"."ROOT_ORDER_ITEM_ID")
34 - filter((NVL("OLI"."COMPLETED_DT","OLI"."DB_LAST_UPD")-NVL("DTL"."SUBMIT_DT","OH"."CREATED"))*86400<=300)
35 - access("OH"."ROW_ID"="DTL"."PAR_ROW_ID")
36 - access("RLI"."PROMOTION_ID"="PRMPRD"."ROW_ID")
85 rows selected.