Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
Hi,
I have a query that run's from application in multiple iterations and process data. Suddenly query execution becomes very slow and it won't finish until invalidate the current cursor. After invalidating cursor, it generates new plan and again start processing fast.
It's happening because data condition seems to be getting changed and existing plan does not work well. What is best approach to change the query plan so that it always take better plan.
I'm trying to introduce some hint. Please suggest if it works.
Hint:/*+ordered use_nl(sourcing_dt RELX_NETWORK) index(RELX_SKU XIF7SKU) */
Note: I'm planning to generate baseline to fix plan. If above hint solution does not work.
Query:
INSERT INTO PROCESSTAB (PROCESSID,
BATCHNUM,
ITEM,
SOURCE,
DEST,
TRANSMODE,
ORDERGROUP)
SELECT /*+ ordered use_nl(SOURCING_DT NETWORK_DT) */
DISTINCT :processid,
:batchnum,
SOURCING_DT.ITEM,
SOURCING_DT.SOURCE,
SOURCING_DT.DEST,
SOURCING_DT.TRANSMODE,
SOURCING_DT.ORDERGROUP
FROM SOURCING_DT,
NETWORK_DT RELX_NETWORK,
SKU_DT RELX_SKU
WHERE ( RELX_NETWORK.SOURCE LIKE 'V%'
AND RELX_NETWORK.DEST LIKE 'D%'
AND RELX_SKU.UDC_ITEM_STATUS <> 9)
AND SOURCING_DT.DEST = RELX_NETWORK.DEST
AND SOURCING_DT.SOURCE = RELX_NETWORK.SOURCE
AND SOURCING_DT.TRANSMODE = RELX_NETWORK.TRANSMODE
AND RELX_NETWORK.SOURCE = RELX_SKU.LOC
AND SOURCING_DT.source = :source
AND SOURCING_DT.dest = :dest
AND SOURCING_DT.transmode = :transmode
AND SOURCING_DT.ORDERGROUP = :blank;
Bad plan:
Plan hash value: 611541756
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | O/1/M |
------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 13 (100)| | | | |
| 1 | LOAD TABLE CONVENTIONAL | PROCESSTAB | | | | | | | |
| 2 | VIEW | VW_DIS_4 | 1 | 536 | 13 (8)| 00:00:01 | | | |
| 3 | SORT UNIQUE NOSORT | | 1 | 75 | 13 (8)| 00:00:01 | | | |
|* 4 | FILTER | | | | | | | | |
| 5 | MERGE JOIN CARTESIAN | | 1 | 75 | 12 (0)| 00:00:01 | | | |
| 6 | MERGE JOIN CARTESIAN| | 1 | 65 | 3 (0)| 00:00:01 | | | |
|* 7 | INDEX RANGE SCAN | SOURCING_PERF1 | 1 | 39 | 3 (0)| 00:00:01 | | | |
| 8 | BUFFER SORT | | 1 | 26 | 0 (0)| | 73728 | 73728 | |
|* 9 | INDEX UNIQUE SCAN | NETWORK_PK | 1 | 26 | 0 (0)| | | | |
| 10 | BUFFER SORT | | 47 | 470 | 12 (0)| 00:00:01 | 549K| 408K| 396/0/0|
|* 11 | INDEX SKIP SCAN | SKU_PERF1 | 47 | 470 | 9 (0)| 00:00:01 | | | |
------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$EBCE75F1
2 - SEL$1 / VW_DIS_4@SEL$EBCE75F1
3 - SEL$1
7 - SEL$1 / SOURCING_DT@SEL$1
9 - SEL$1 / RELX_NETWORK@SEL$1
11 - SEL$1 / RELX_SKU@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_dsdir_usage_control' 0)
OPT_PARAM('_optimizer_adaptive_plans' 'false')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_gather_feedback' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$EBCE75F1")
OUTLINE_LEAF(@"INS$1")
OUTLINE(@"SEL$1")
FULL(@"INS$1" "PROCESSTAB"@"INS$1")
NO_ACCESS(@"SEL$EBCE75F1" "VW_DIS_4"@"SEL$EBCE75F1")
INDEX(@"SEL$1" "SOURCING_DT"@"SEL$1" ("SOURCING_DT"."SOURCE" "SOURCING_DT"."DEST" "SOURCING_DT"."TRANSMODE"
"SOURCING_DT"."ORDERGROUP" "SOURCING_DT"."ITEM"))
INDEX(@"SEL$1" "RELX_NETWORK"@"SEL$1" ("NETWORK_DT"."SOURCE" "NETWORK_DT"."TRANSMODE" "NETWORK_DT"."DEST"))
INDEX_SS(@"SEL$1" "RELX_SKU"@"SEL$1" ("SKU_DT"."UDC_ITEM_STATUS" "SKU_DT"."LOC"))
LEADING(@"SEL$1" "SOURCING_DT"@"SEL$1" "RELX_NETWORK"@"SEL$1" "RELX_SKU"@"SEL$1")
USE_MERGE_CARTESIAN(@"SEL$1" "RELX_NETWORK"@"SEL$1")
USE_MERGE_CARTESIAN(@"SEL$1" "RELX_SKU"@"SEL$1")
PARTIAL_JOIN(@"SEL$1" "RELX_SKU"@"SEL$1")
END_OUTLINE_DATA
*/
Good plan:
Plan hash value: 3367406455
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | OMem | 1Mem | O/1/M |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | 231 (100)| | | | |
| 1 | LOAD TABLE CONVENTIONAL | PROCESSTAB | | | | | | | | |
| 2 | VIEW | VW_DIS_9 | 1 | 536 | | 231 (8)| 00:00:01 | | | |
| 3 | HASH UNIQUE | | 1 | 75 | | 231 (8)| 00:00:01 | 1885K| 1161K| 1256/0/0|
| 4 | NESTED LOOPS SEMI | | 1 | 75 | | 230 (8)| 00:00:01 | | | |
|* 5 | HASH JOIN | | 1 | 65 | | 227 (8)| 00:00:01 | 3767K| 1371K| 1256/0/0|
| 6 | VIEW | VW_DTP_08749D0B | 44940 | 1141K| | 212 (7)| 00:00:01 | | | |
| 7 | SORT UNIQUE | | 44940 | 1141K| 1600K| 212 (7)| 00:00:01 | 3525K| 739K| 1256/0/0|
|* 8 | INDEX FAST FULL SCAN | NETWORK_PK | 44940 | 1141K| | 28 (15)| 00:00:01 | | | |
| 9 | VIEW | VW_DTP_98A89098 | 1 | 39 | | 13 (8)| 00:00:01 | | | |
| 10 | SORT UNIQUE | | 1 | 39 | | 13 (8)| 00:00:01 | 1186K| 508K| 80/0/0|
|* 11 | INDEX RANGE SCAN | SOURCING_PERF1 | 1716 | 66924 | | 12 (0)| 00:00:01 | | | |
|* 12 | TABLE ACCESS BY INDEX ROWID BATCHED| SKU_DT | 3522K| 33M| | 3 (0)| 00:00:01 | | | |
|* 13 | INDEX RANGE SCAN | XIF7SKU | 1 | | | 2 (0)| 00:00:01 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2766B822
2 - SEL$9FA9D248 / VW_DIS_9@SEL$2766B822
3 - SEL$9FA9D248
6 - SEL$33235787 / VW_DTP_08749D0B@SEL$08749D0B
7 - SEL$33235787
8 - SEL$33235787 / RELX_NETWORK@SEL$1
9 - SEL$44516A63 / VW_DTP_98A89098@SEL$98A89098
10 - SEL$44516A63
11 - SEL$44516A63 / SOURCING_DT@SEL$1
12 - SEL$9FA9D248 / RELX_SKU@SEL$1
13 - SEL$9FA9D248 / RELX_SKU@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_dsdir_usage_control' 0)
OPT_PARAM('_optimizer_adaptive_plans' 'false')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_gather_feedback' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$33235787")
OUTLINE_LEAF(@"SEL$44516A63")
OUTLINE_LEAF(@"SEL$9FA9D248")
PLACE_DISTINCT(@"SEL$1" "RELX_NETWORK"@"SEL$1")
PLACE_DISTINCT(@"SEL$1" "SOURCING_DT"@"SEL$1")
OUTLINE_LEAF(@"SEL$2766B822")
OUTLINE_LEAF(@"INS$1")
OUTLINE(@"SEL$08749D0B")
OUTLINE(@"SEL$98A89098")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$9FA9D248")
PLACE_DISTINCT(@"SEL$1" "RELX_NETWORK"@"SEL$1")
PLACE_DISTINCT(@"SEL$1" "SOURCING_DT"@"SEL$1")
FULL(@"INS$1" "PROCESSTAB"@"INS$1")
NO_ACCESS(@"SEL$2766B822" "VW_DIS_9"@"SEL$2766B822")
NO_ACCESS(@"SEL$9FA9D248" "VW_DTP_08749D0B"@"SEL$08749D0B")
NO_ACCESS(@"SEL$9FA9D248" "VW_DTP_98A89098"@"SEL$98A89098")
INDEX_RS_ASC(@"SEL$9FA9D248" "RELX_SKU"@"SEL$1" ("SKU_DT"."LOC"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$9FA9D248" "RELX_SKU"@"SEL$1")
LEADING(@"SEL$9FA9D248" "VW_DTP_08749D0B"@"SEL$08749D0B" "VW_DTP_98A89098"@"SEL$98A89098" "RELX_SKU"@"SEL$1")
USE_HASH(@"SEL$9FA9D248" "VW_DTP_98A89098"@"SEL$98A89098")
USE_NL(@"SEL$9FA9D248" "RELX_SKU"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$9FA9D248")
PARTIAL_JOIN(@"SEL$9FA9D248" "RELX_SKU"@"SEL$1")
INDEX_FFS(@"SEL$33235787" "RELX_NETWORK"@"SEL$1" ("NETWORK_DT"."SOURCE" "NETWORK_DT"."TRANSMODE" "NETWORK_DT"."DEST"))
INDEX(@"SEL$44516A63" "SOURCING_DT"@"SEL$1" ("SOURCING_DT"."SOURCE" "SOURCING_DT"."DEST" "SOURCING_DT"."TRANSMODE" "SOURCING_DT"."ORDERGROUP"
"SOURCING_DT"."ITEM"))
END_OUTLINE_DATA
*/