Hello experts.
I have a query that is running fine with 11204 environment but after loading same database into 12102 database, query is performing really slow and consuming lot of CPU.
SELECT n.dest,
n.source,
n.RANK,
n.transmode,
n.transleadtime,
n.loadtime,
n.unloadtime,
n.transcal,
n.shipcal,
n.arrivcal,
n.orderreviewcal,
n.covduradjtolerance,
n.vendorminrule,
n.transmodeminrule,
n.loadtolerance,
n.loadbuildrule,
n.loadminimumrule,
n.loadbuildadjuptolerance,
n.loadbuildadjdowntolerance,
n.loadseqrule,
n.fwdbuyactivedur,
n.fwdbuyeffectpct,
n.autoapprovalsw,
n.projorderdur,
n.skuperpalletsw,
1
FROM NETWORK n
WHERE (n.source, n.dest, n.transmode) IN (SELECT pl.source,
pl.dest,
pl.transmode
FROM ProcessLane pl, sourcing s
WHERE pl.processID = :1
AND pl.batchnum = :2
AND pl.item = s.item
AND pl.source = s.source
AND pl.dest = s.dest
AND pl.transmode =
s.transmode
AND pl.ordergroup =
s.ordergroup
AND s.ordergroup = ' ')
UNION
SELECT UNIQUE n.dest,
n.source,
n.RANK,
n.transmode,
n.transleadtime,
n.loadtime,
n.unloadtime,
n.transcal,
n.shipcal,
n.arrivcal,
n.orderreviewcal,
n.covduradjtolerance,
n.vendorminrule,
n.transmodeminrule,
n.loadtolerance,
n.loadbuildrule,
n.loadminimumrule,
n.loadbuildadjuptolerance,
n.loadbuildadjdowntolerance,
n.loadseqrule,
n.fwdbuyactivedur,
n.fwdbuyeffectpct,
n.autoapprovalsw,
n.projorderdur,
n.skuperpalletsw,
0
FROM NETWORK n, processlane pl
WHERE n.dest = pl.dest
AND (n.source <> pl.source OR n.transmode <> pl.transmode)
AND PL.PROCESSID = :3
AND PL.BATCHNUM = :4
UNION
SELECT UNIQUE n.dest,
n.source,
n.RANK,
n.transmode,
n.transleadtime,
n.loadtime,
n.unloadtime,
n.transcal,
n.shipcal,
n.arrivcal,
n.orderreviewcal,
n.covduradjtolerance,
n.vendorminrule,
n.transmodeminrule,
n.loadtolerance,
n.loadbuildrule,
n.loadminimumrule,
n.loadbuildadjuptolerance,
n.loadbuildadjdowntolerance,
n.loadseqrule,
n.fwdbuyactivedur,
n.fwdbuyeffectpct,
n.autoapprovalsw,
n.projorderdur,
n.skuperpalletsw,
2
FROM NETWORK n
WHERE (n.source, n.dest, n.transmode) IN (SELECT pl.source,
pl.dest,
pl.transmode
FROM processlane pl
WHERE pl.processId = :5
AND pl.batchnum = :6
AND pl.item = ' '
AND pl.ordergroup = ' ');
Explain plan:
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | OMem | 1Mem | O/1/M |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 353K(100)| | | | |
| 1 | SORT UNIQUE | | 38466 | 5522K| 480M| 353K (1)| 00:00:28 | 974K| 974K| 310/0/0|
| 2 | UNION-ALL | | | | | | | | | |
|* 3 | HASH JOIN | | 2459K| 344M| | 2042 (5)| 00:00:01 | 1099K| 1099K| 310/0/0|
|* 4 | INDEX RANGE SCAN | PROCESSLANE_BATCH | 496 | 18848 | | 5 (0)| 00:00:01 | | | |
| 5 | TABLE ACCESS FULL | NETWORK | 28370 | 3019K| | 1959 (1)| 00:00:01 | | | |
| 6 | NESTED LOOPS | | 1 | 418 | | 1013 (1)| 00:00:01 | | | |
| 7 | NESTED LOOPS | | 2 | 418 | | 1013 (1)| 00:00:01 | | | |
| 8 | VIEW | VW_JF_SET$61B26582 | 2 | 618 | | 1011 (1)| 00:00:01 | | | |
| 9 | SORT UNIQUE | | 2 | 133 | | 1011 (1)| 00:00:01 | 2048 | 2048 | 310/0/0|
| 10 | UNION-ALL | | | | | | | | | |
| 11 | NESTED LOOPS SEMI | | 1 | 83 | | 1003 (1)| 00:00:01 | | | |
|* 12 | INDEX RANGE SCAN | PROCESSLANE_BATCH | 496 | 24800 | | 5 (0)| 00:00:01 | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID BATCHED| SOURCING | 8139 | 262K| | 3 (0)| 00:00:01 | | | |
|* 14 | INDEX RANGE SCAN | XIF4SOURCING | 1 | | | 2 (0)| 00:00:01 | | | |
|* 15 | TABLE ACCESS BY INDEX ROWID BATCHED | PROCESSLANE | 1 | 50 | | 8 (0)| 00:00:01 | | | |
|* 16 | INDEX RANGE SCAN | PROCESSLANE_LANE | 5 | | | 3 (0)| 00:00:01 | | | |
|* 17 | INDEX UNIQUE SCAN | NETWORK_PK | 1 | | | 0 (0)| | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | NETWORK | 1 | 109 | | 1 (0)| 00:00:01 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------
Explain plan from oracle 11204 database where query is running fine.
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4885 | 701K| 102 (20)| 00:00:01 |
| 1 | SORT UNIQUE | | 4885 | 701K| 102 (20)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN | | 57228 | 8215K| 41 (22)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PROCESSLANE_BATCH | 12 | 456 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | NETWORK | 28303 | 3012K| 36 (20)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 193 | 50 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 13 | 193 | 50 (0)| 00:00:01 |
| 8 | VIEW | VW_JF_SET$61B26582 | 13 | 1092 | 37 (0)| 00:00:01 |
| 9 | SORT UNIQUE | | 13 | 968 | 37 (0)| 00:00:01 |
| 10 | UNION-ALL | | | | | |
| 11 | NESTED LOOPS | | 12 | 924 | 28 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 12 | 924 | 28 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | PROCESSLANE_BATCH | 12 | 528 | 3 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | XIF4SOURCING | 1 | | 2 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| SOURCING | 1 | 33 | 3 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID | PROCESSLANE | 1 | 44 | 9 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | PROCESSLANE_LANE | 5 | | 3 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | NETWORK_PK | 1 | | 0 (0)| 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID | NETWORK | 1 | 109 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Please advice how can we change the execution plan to latter one to improve performance.
Note: All tables have same set of indexes and almost similar stats and histograms. Important DB parameters are also same.