Hi Tuning Experts,
We have a function which is rarely used but takes around 2hours to complete and per AWR a query takes around 20mins for single execution. Below is the query and its explain plan. I am not sure what could be cause of issue from explain plan and is it possible to convert/generate this plan in Tree form to understand better?
SELECT /*+ FIRST_ROWS ORDERED USE_NL(IT1) USE_MERGE(RPE) INDEX(IT1 IT1P1) INDEX(RPE RPEP1) */
IT1.OCHD OCHD,
RPE.RPRD PCHD,
IT1.TYPE TYPE,
IT1.YR YR,
IT1.PR PR,
IT1.WK WK,
IT1.DATE DATE,
RPE.MULT * NVL (IT1.RTL, 0) RTL,
RPE.MULT * NVL (IT1.CST, 0) CST,
RPE.MULT * NVL (IT1.ST_OUT, 0) ST_OUT,
RPE.MULT * NVL (IT1.QTY, 0) QTY,
NVL (IT1.CUR, ' ') CUR,
IT1.IN_QTY,
IT1.IN_RTL,
IT1.IN_CST
FROM PRV, IT1, RPE
WHERE PRV.PCHD = :B2
AND IT1.OCHD = PRV.OCHD
AND IT1.PCHD = PRV.PCHD
AND IT1.TYPE = 'T'
AND RPE.RTYPE = :B1
AND RPE.RCHD = IT1.PCHD
UNION ALL
SELECT /*+ FIRST_ROWS ORDERED USE_NL(IT3) USE_MERGE(RPE) INDEX(IT3 IT3P1) INDEX(RPE RPEP1) */
IT3.OCHD OCHD,
RPE.RPRD PCHD,
IT3.TYPE TYPE,
IT3.YR YR,
IT3.PR PR,
IT3.WK WK,
IT3.DATE DATE,
RPE.MULT * NVL (IT3.RTL, 0) RTL,
RPE.MULT * NVL (IT3.CST, 0) CST,
RPE.MULT * NVL (IT3.ST_OUT, 0) ST_OUT,
RPE.MULT * NVL (IT3.QTY, 0) QTY,
NVL (IT3.CUR, ' ') CUR,
IT3.IN_QTY,
IT3.IN_RTL,
IT3.IN_CST
FROM PLV, IT3, RPE
WHERE PLV.PCHD = :B2
AND IT3.OCHD = PLV.OCHD
AND IT3.PCHD = PLV.PCHD
AND IT3.TYPE = 'T'
AND RPE.RTYPE = :B1
AND RPE.RCHD = IT3.PCHD
ORDER BY OCHD,
PCHD,
TYPE,
YR,
PR,
WK
Plan hash value: 3672387809
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1824 | 229K| 3982K (1)| 00:02:36 | | |
| 1 | SORT ORDER BY | | 1824 | 229K| 3982K (1)| 00:02:36 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | MERGE JOIN | | 11 | 1441 | 166K (1)| 00:00:07 | | |
| 4 | NESTED LOOPS | | 314 | 27946 | 166K (1)| 00:00:07 | | |
| 5 | NESTED LOOPS | | 368 | 27946 | 166K (1)| 00:00:07 | | |
| 6 | NESTED LOOPS | | 368 | 11040 | 21 (0)| 00:00:01 | | |
| 7 | MERGE JOIN CARTESIAN | | 1 | 22 | 3 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | PRV1 | 1 | 13 | 2 (0)| 00:00:01 | | |
| 9 | BUFFER SORT | | 1 | 9 | 1 (0)| 00:00:01 | | |
| 10 | TABLE ACCESS BY INDEX ROWID | PRS | 1 | 9 | 1 (0)| 00:00:01 | | |
|* 11 | INDEX UNIQUE SCAN | PRSP1 | 1 | | 0 (0)| 00:00:01 | | |
|* 12 | TABLE ACCESS FULL | ORS | 368 | 2944 | 18 (0)| 00:00:01 | | |
| 13 | PARTITION RANGE ALL | | 1 | | 452 (0)| 00:00:01 | 1 | 226 |
|* 14 | INDEX RANGE SCAN | IT1P1 | 1 | | 452 (0)| 00:00:01 | 1 | 226 |
| 15 | TABLE ACCESS BY LOCAL INDEX ROWID | IT1 | 1 | 59 | 453 (0)| 00:00:01 | 1 | 1 |
|* 16 | SORT JOIN | | 1 | 42 | 4 (25)| 00:00:01 | | |
| 17 | TABLE ACCESS BY INDEX ROWID BATCHED| RPE | 1 | 42 | 3 (0)| 00:00:01 | | |
|* 18 | INDEX RANGE SCAN | RPEP1 | 1 | | 2 (0)| 00:00:01 | | |
| 19 | MERGE JOIN | | 1813 | 228K| 3815K (1)| 00:02:30 | | |
| 20 | NESTED LOOPS | | 50822 | 4317K| 3815K (1)| 00:02:30 | | |
| 21 | NESTED LOOPS | | 71566 | 4317K| 3815K (1)| 00:02:30 | | |
| 22 | MERGE JOIN CARTESIAN | | 6506 | 190K| 119 (0)| 00:00:01 | | |
| 23 | MERGE JOIN CARTESIAN | | 1 | 22 | 3 (0)| 00:00:01 | | |
|* 24 | INDEX RANGE SCAN | PRV1 | 1 | 13 | 2 (0)| 00:00:01 | | |
| 25 | BUFFER SORT | | 1 | 9 | 1 (0)| 00:00:01 | | |
|* 26 | TABLE ACCESS BY INDEX ROWID | PRS | 1 | 9 | 1 (0)| 00:00:01 | | |
|* 27 | INDEX UNIQUE SCAN | PRSP1 | 1 | | 0 (0)| 00:00:01 | | |
| 28 | BUFFER SORT | | 6511 | 52088 | 118 (0)| 00:00:01 | | |
| 29 | TABLE ACCESS BY INDEX ROWID | ORS | 6511 | 52088 | 116 (0)| 00:00:01 | | |
|* 30 | INDEX RANGE SCAN | ORSI5 | 6511 | | 20 (0)| 00:00:01 | | |
| 31 | PARTITION RANGE ALL | | 11 | | 579 (0)| 00:00:01 | 1 | 193 |
|* 32 | INDEX RANGE SCAN | IT3P1 | 11 | | 579 (0)| 00:00:01 | 1 | 193 |
| 33 | TABLE ACCESS BY LOCAL INDEX ROWID | IT3 | 8 | 456 | 590 (0)| 00:00:01 | 1 | 1 |
|* 34 | SORT JOIN | | 1 | 42 | 4 (25)| 00:00:01 | | |
| 35 | TABLE ACCESS BY INDEX ROWID BATCHED| RPE | 1 | 42 | 3 (0)| 00:00:01 | | |
|* 36 | INDEX RANGE SCAN | RPEP1 | 1 | | 2 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("PRE"."PCHD"=TO_NUMBER(:B2))
11 - access("PRS"."PCHD"=TO_NUMBER(:B2))
12 - filter("PRS"."PLI">1 OR NVL("ORS"."OLI",99)>1)
14 - access("IT1"."OCHD"="ORS"."OCHD" AND
"IT1"."PCHD"=TO_NUMBER(:B2) AND "IT1"."TYPE"='T')
16 - access("RPE"."RCHD"="IT1"."PCHD")
filter("RPE"."RCHD"="IT1"."PCHD")
18 - access("RPE"."RTYPE"=:B1 AND "RPE"."RCHD"=TO_NUMBER(:B2))
24 - access("PRE"."PCHD"=TO_NUMBER(:B2))
26 - filter("PRS"."PLI"=1)
27 - access("PRS"."PCHD"=TO_NUMBER(:B2))
30 - access("ORS"."OLI"=1)
32 - access("IT3"."OCHD"="ORS"."OCHD" AND
"IT3"."PCHD"=TO_NUMBER(:B2) AND "IT3"."TYPE"='T')
34 - access("RPE"."RCHD"="IT3"."PCHD")
filter("RPE"."RCHD"="IT3"."PCHD")
36 - access("RPE"."RTYPE"=:B1 AND "RPE"."RCHD"=TO_NUMBER(:B2))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
68 rows selected.
Note:
1. PRV view definition
CREATE OR REPLACE FORCE VIEW PRV
(
PCHD,
OCHD
) AS
SELECT /*+
ORDERED USE_NL(PRS) USE_MERGE(ORS)
INDEX(PRS PRSP1)
INDEX(PRE PRV1)
*/
PRE.pchd, ORS.ochd
FROM PRE, PRS, ORS
WHERE PRS.pchd = PRE.pchd
AND (PRS.pid > 1 OR NVL (ORS.oid, 99) > 1);
2. PLV view definition
CREATE OR REPLACE FORCE VIEW PLV
(
PCHD,
OCHD
) AS
SELECT /*+
ORDERED USE_NL(PRS) USE_MERGE(ORS)
INDEX(PRS PRSP1)
INDEX(PRE PREP1)
*/
PRE.PCHD, ORS.OCHD
FROM PRE, PRS, ORS
WHERE PRS.PCHD = PRE.PCHD
AND PRS.pid = 1
AND ORS.oid = 1;
3. Table IT1
had num_rows 919,049,343
has PARTITION BY RANGE (yr, pr, wk)
4. Table IT3
has num_rows 2,903,600,902
PARTITION BY RANGE (yr, pr, wk)
5. Table RPE has num_rows 264
TABLE ORS has num_rows 6878
Table PRS has num_rows 154715
Table PRE has num_rows 200
Thanks,
Imran.