Hi
Iam fairly new to oracle and i was given a query that takes a long time which is used to fetch some numbers associated with each info of an order, to be tuned.
<
select A.LS201_SEC_SEQ_NO,CHR(B.LS201_SEC_CODE+64)||'.'||B.LS201_SEC_NAME LS201_SEC_NAME
,CURSOR(
SELECT LEVEL,
G.LS202_SUBSEC_SEQ_NO,
G.LS300_CLA_SEQ_NO,
G.LS406_CLA_NUM
,CURSOR
( SELECT B.LS302_EDL_NO
FROM LSDB302_CLA_EDL_NO B
WHERE G.LS300_CLA_SEQ_NO = B.LS300_CLA_SEQ_NO
AND G.LS301_VERSION_NO = B.LS301_VERSION_NO
) NO
,DECODE((SELECT COUNT(*)
FROM LSDB305_CLA_COMP H
WHERE H.LS300_CLA_SEQ_NO = G.LS300_CLA_SEQ_NO),0,
REGEXP_REPLACE (SUBSTR(G.LS301_CLA_DESC,1,
DECODE(INSTR(G.LS301_CLA_DESC,'<br />')-1,
-1,(DECODE(INSTR(G.LS301_CLA_DESC,CHR(13)||CHR(10)),
0,LENGTH(G.LS301_CLA_DESC),
INSTR(G.LS301_CLA_DESC,CHR(13)||CHR(10)))),
INSTR(G.LS301_CLA_DESC,'<br />')-1)
) || DECODE(INSTR(G.LS301_CLA_DESC,'<p>'),1,
DECODE(INSTR(G.LS301_CLA_DESC,'<br />'),0,
'','</p>'),''),'<[^>]+>',NULL)
) CLA_DESC
,CURSOR
(SELECT I.LS140_COMP_SEQ_NO,I.LS140_COMP_NAME
,DECODE(I.LS191_COMP_SOURCE_CODE,
'O',
'Y',
'N') ORDER_COMP_FLAG
FROM LSDB140_COMP I,
LSDB305_CLA_COMP J
WHERE I.LS140_COMP_SEQ_NO = J.LS140_COMP_SEQ_NO
AND J.LS300_CLA_SEQ_NO = G.LS300_CLA_SEQ_NO
)COMPONENTS
FROM (
SELECT D.LS201_SEC_SEQ_NO,
D.LS202_SUBSEC_CODE,
D.LS202_SUBSEC_SEQ_NO,
E.LS300_CLA_SEQ_NO,
E.LS406_CLA_NUM,
K.LS301_CLA_DESC,
E.LS301_VERSION_NO,
E.LS193_CLA_HRCHY_SEQ_NO,
E.LS406_ORDR_BY_CODE
FROM LSDB406_ORDR_CLA E,
LSDB300_CLA C,
LSDB202_MDL_SUBSEC D,
LSDB301_CLA_VER K
WHERE E.LS400_ORDR_KEY=<b>2305</b>
AND E.LS150_DATA_LOC_TYPE_CODE=<b>'W'</b>
AND E.LS300_CLA_SEQ_NO=C.LS300_CLA_SEQ_NO
AND C.LS202_SUBSEC_SEQ_NO=D.LS202_SUBSEC_SEQ_NO
AND E.LS300_CLA_SEQ_NO = K.LS300_CLA_SEQ_NO
AND E.LS301_VERSION_NO = K.LS301_VERSION_NO
AND E.LS406_CLA_DEL_FLAG = 'N'
) G ,
LSDB307_CLA_HRCHY H
WHERE H.LS300_CHILD_CLA_SEQ_NO = G.LS300_CLA_SEQ_NO
AND EXISTS
(
SELECT F.LS300_CLA_SEQ_NO
FROM LSDB302_CLA_EDL_NO F
WHERE F.LS300_CLA_SEQ_NO = G.LS300_CLA_SEQ_NO
AND F.LS301_VERSION_NO = G.LS301_VERSION_NO
)
AND G.LS201_SEC_SEQ_NO=B.LS201_SEC_SEQ_NO
START WITH H.LS300_PARENT_CLA_SEQ_NO IS NULL
CONNECT BY H.LS300_PARENT_CLA_SEQ_NO = PRIOR H.LS300_CHILD_CLA_SEQ_NO
ORDER BY B.LS201_SEC_CODE,G.LS202_SUBSEC_CODE,G.LS193_CLA_HRCHY_SEQ_NO,G.LS406_ORDR_BY_CODE) CLAUSES
FROM LSDB411_ORDR_SEC A,LSDB201_MDL_SEC B
WHERE A.LS400_ORDR_KEY= <b>2305</b>
AND A.LS150_DATA_LOC_TYPE_CODE=<b>'W'</b>
AND A.LS201_SEC_SEQ_NO=B.LS201_SEC_SEQ_NO
ORDER BY B.LS201_SEC_CODE;
/>
when i did a trace i get
statistics:
270 Recursive calls
1304 db block gets
54012 consistent gets
0 physical reads
0 redo size
9317 bytes sent via SQL*NET
2572 bytes received via SQL*NET
11 SQL*NET rountrips
161 sorts (memory)
0 sorts (disk)
16 rows processed
This is part of a packaged procedure with ref cursor as 'out' parameter and the order key and type code as 'in' parameter(bold ones).
i did a gather table stats and i get this
SQL_ID 8q5rxcng9u04s, child number 0
-------------------------------------
select /*+ gather_plan_statistics */
A.LS201_SEC_SEQ_NO,CHR(B.LS201_SEC_CODE+64)||'.'||B.LS201_SEC_NAME
LS201_SEC_NAME ,CURSOR( SELECT
LEVEL, G.LS202_SUBSEC_SEQ_NO,
G.LS300_CLA_SEQ_NO,
G.LS406_CLA_NUM ,CURSOR
( SELECT B.LS302_EDL_NO FROM
LSDB302_CLA_EDL_NO B WHERE
G.LS300_CLA_SEQ_NO = B.LS300_CLA_SEQ_NO
AND G.LS301_VERSION_NO = B.LS301_VERSION_NO
) EDLNO ,DECODE((SELECT COUNT(*)
FROM LSDB305_CLA_COMP H
WHERE H.LS300_CLA_SEQ_NO = G.LS300_CLA_SEQ_NO),0,
REGEXP_REPLACE
(SUBSTR(G.LS301_CLA_DESC,1,
DECODE(IN
Plan hash value: 2369088999
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 | | | |
|* 1 | INDEX RANGE SCAN | PK_LSDB302_CLA_EDL_NO | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 2 | SORT AGGREGATE | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 3 | INDEX RANGE SCAN | PK_LSDB305_CLA_COMP | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 4 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 5 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 6 | INDEX RANGE SCAN | PK_LSDB305_CLA_COMP | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 7 | INDEX UNIQUE SCAN | PK_LSDB140_COMP | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | LSDB140_COMP | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 9 | SORT ORDER BY | | 0 | 92 | 0 |00:00:00.01 | 0 | 214K| 214K| |
|* 10 | FILTER | | 0 | | 0 |00:00:00.01 | 0 | | | |
|* 11 | CONNECT BY NO FILTERING WITH START-WITH| | 0 | | 0 |00:00:00.01 | 0 | | | |
|* 12 | HASH JOIN | | 0 | 86 | 0 |00:00:00.01 | 0 | 740K| 740K| |
| 13 | VIEW | | 0 | 86 | 0 |00:00:00.01 | 0 | | | |
| 14 | NESTED LOOPS | | 0 | 86 | 0 |00:00:00.01 | 0 | | | |
| 15 | NESTED LOOPS | | 0 | 86 | 0 |00:00:00.01 | 0 | | | |
|* 16 | HASH JOIN | | 0 | 86 | 0 |00:00:00.01 | 0 | 940K| 940K| |
|* 17 | HASH JOIN | | 0 | 86 | 0 |00:00:00.01 | 0 | 998K| 998K| |
|* 18 | TABLE ACCESS BY INDEX ROWID | LSDB406_ORDR_CLA | 0 | 86 | 0 |00:00:00.01 | 0 | | | |
|* 19 | INDEX RANGE SCAN | PK_LSDB406_ORDR_CLA | 0 | 92 | 0 |00:00:00.01 | 0 | | | |
| 20 | TABLE ACCESS FULL | LSDB300_CLA | 0 | 24508 | 0 |00:00:00.01 | 0 | | | |
| 21 | TABLE ACCESS FULL | LSDB202_MDL_SUBSEC | 0 | 4269 | 0 |00:00:00.01 | 0 | | | |
|* 22 | INDEX UNIQUE SCAN | PK_LS301_CLA_VER | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 23 | TABLE ACCESS BY INDEX ROWID | LSDB301_CLA_VER | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 24 | INDEX FAST FULL SCAN | INDX_LSDB307_CLA_HRCHY | 0 | 22732 | 0 |00:00:00.01 | 0 | | | |
| 25 | SORT ORDER BY | | 1 | 3 | 16 |00:00:00.01 | 15 | 2048 | 2048 | 2048 (0)|
| 26 | NESTED LOOPS | | 1 | 3 | 16 |00:00:00.01 | 15 | | | |
| 27 | TABLE ACCESS FULL | LSDB201_MDL_SEC | 1 | 466 | 466 |00:00:00.01 | 6 | | | |
|* 28 | INDEX UNIQUE SCAN | PK_LSDB411_ORDR_SEC | 466 | 1 | 16 |00:00:00.01 | 9 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."LS300_CLA_SEQ_NO"=:B1 AND "B"."LS301_VERSION_NO"=:B2)
3 - access("H"."LS300_CLA_SEQ_NO"=:B1)
6 - access("J"."LS300_CLA_SEQ_NO"=:B1)
7 - access("I"."LS140_COMP_SEQ_NO"="J"."LS140_COMP_SEQ_NO")
10 - filter(( AND "G"."LS201_SEC_SEQ_NO"=:B1))
11 - access("H"."LS300_PARENT_CLA_SEQ_NO"=PRIOR NULL)
filter("H"."LS300_PARENT_CLA_SEQ_NO" IS NULL)
12 - access("H"."LS300_CHILD_CLA_SEQ_NO"="G"."LS300_CLA_SEQ_NO")
16 - access("C"."LS202_SUBSEC_SEQ_NO"="D"."LS202_SUBSEC_SEQ_NO")
17 - access("E"."LS300_CLA_SEQ_NO"="C"."LS300_CLA_SEQ_NO")
18 - filter("E"."LS406_CLA_DEL_FLAG"='N')
19 - access("E"."LS150_DATA_LOC_TYPE_CODE"='W' AND "E"."LS400_ORDR_KEY"=2305)
22 - access("E"."LS300_CLA_SEQ_NO"="K"."LS300_CLA_SEQ_NO" AND "E"."LS301_VERSION_NO"="K"."LS301_VERSION_NO")
28 - access("A"."LS150_DATA_LOC_TYPE_CODE"='W' AND "A"."LS400_ORDR_KEY"=2305 AND "A"."LS201_SEC_SEQ_NO"="B"."LS201_SEC_SEQ_NO")
is it because of the consistent gets happening frequently?
or any other thoughts?
The process I generally follow when tuning queries like this, is:
1) remove the scalar queries, so its just a "standard" query
=> how does that run. If it runs fine, then the impact if in the subordinate queries
=> if it does not run fine, focus efforts there
2) introduce scalar queries back in one at a time
=> tune as per normal
3) If the scalars are being run excessively (tens of thousands of times), consider moving them to an inline view or lateral clause (depending on version) to get a join style operation rather than repeated executions, eg
select col1, col2,
( select sum(blah) from x where colx = t.col3 )
from t
could be changed to:
select col1, col2, tot
from t
( select colx, sum(blah) tot
from x
group by colx
) tx
where tx.colx = t.col3