Hi there, need an advice on how to tune these selected SQL with higher elapse time, disk read and db file sequential read for further better performance
used in PL/SQL.
Currently, this PL/SQL took average about 25 min - 35 min to process ~ 33000 rows.
Let me know if further details required, thanks in advance.
database - 10 g.
== (1) ==
SELECT ohd.name,
ohd.type_code
INTO x_hold_code,
x_hold_type
FROM oe_hold_definitions ohd,
oe_hold_sources_all ohs,
oe_order_holds_all ooh
WHERE ooh.header_id = p_header_id
AND NVL(ooh.line_id , -1) = NVL(p_line_id, -1)
AND ooh.hold_source_id = ohs.hold_source_id
AND ohs.hold_id = ohd.hold_id
AND ooh.released_flag = 'N'
AND DECODE(ohd.type_code, 'SCHEDULING', 'SCHEDULING', 'X') = NVL(p_hold_type_code, 'X')
AND ohd.hold_id != pv_hold_id -- private variable to store constant value.
AND ROWNUM = 1;
TKPROF
SELECT OHD.NAME, OHD.TYPE_CODE
FROM
OE_HOLD_DEFINITIONS OHD, OE_HOLD_SOURCES_ALL OHS, OE_ORDER_HOLDS_ALL OOH
WHERE OOH.HEADER_ID = :B4 AND NVL(OOH.LINE_ID , -1) = NVL(:B3 , -1) AND
OOH.HOLD_SOURCE_ID = OHS.HOLD_SOURCE_ID AND OHS.HOLD_ID = OHD.HOLD_ID AND
OOH.RELEASED_FLAG = 'N' AND DECODE(OHD.TYPE_CODE, 'SCHEDULING',
'SCHEDULING', 'X') = NVL(:B2 , 'X') AND OHD.HOLD_ID != :B1 AND ROWNUM = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 66298 6.88 6.62 0 0 0 0
Fetch 66298 36.49 94.30 31630 729976 0 15283
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 132597 43.37 100.92 31630 729976 0 15283
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 173 (APPS) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
15283 COUNT STOPKEY (cr=729976 pr=31630 pw=0 time=95287111 us)
15283 NESTED LOOPS (cr=729976 pr=31630 pw=0 time=94732870 us)
26627 NESTED LOOPS (cr=676748 pr=31601 pw=0 time=93535378 us)
51645 TABLE ACCESS BY INDEX ROWID OE_ORDER_HOLDS_ALL (cr=473454 pr=25215 pw=0 time=74314275 us)
2077265 INDEX RANGE SCAN OE_ORDER_HOLDS_ALL_N1 (cr=206237 pr=3329 pw=0 time=14344310 us)(object id 41977)
26627 TABLE ACCESS BY INDEX ROWID OE_HOLD_SOURCES_ALL (cr=203294 pr=6386 pw=0 time=18998576 us)
51645 INDEX UNIQUE SCAN OE_HOLD_SOURCES_U1 (cr=151649 pr=2033 pw=0 time=7409386 us)(object id 41594)
15283 TABLE ACCESS BY INDEX ROWID OE_HOLD_DEFINITIONS (cr=53228 pr=29 pw=0 time=973600 us)
26627 INDEX UNIQUE SCAN OE_HOLDS_U1 (cr=26601 pr=5 pw=0 time=347192 us)(object id 41519)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
15283 COUNT (STOPKEY)
15283 NESTED LOOPS
26627 NESTED LOOPS
51645 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'OE_ORDER_HOLDS_ALL' (TABLE)
2077265 INDEX MODE: ANALYZED (RANGE SCAN) OF
'OE_ORDER_HOLDS_ALL_N1' (INDEX)
26627 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'OE_HOLD_SOURCES_ALL' (TABLE)
51645 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'OE_HOLD_SOURCES_U1' (INDEX (UNIQUE))
15283 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'OE_HOLD_DEFINITIONS' (TABLE)
26627 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'OE_HOLDS_U1'
(INDEX (UNIQUE))
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
gc current block 2-way 1923 0.00 1.61
db file sequential read 31630 0.07 48.22
gc cr grant 2-way 26318 0.01 12.99
latch: cache buffers chains 2 0.00 0.00
latch: KCL gc element parent latch 1 0.00 0.00
latch: cache buffers lru chain 1 0.00 0.00
latch free 234 0.00 0.03
== (2) ==
SELECT hl.state, hl.country
INTO x_to_state, x_to_country
from apps.hz_cust_site_uses_all hcsu,
apps.hz_cust_acct_sites_all hcas,
apps.hz_party_sites hps,
apps.hz_locations hl
where hcsu.site_use_id =x_ship_to_site_use_id
and hcsu.cust_acct_site_id = hcas.cust_acct_site_id
and hcas.party_site_id = hps.party_site_id
and hl.location_id = hps.location_id;
SELECT STATE, COUNTRY
FROM
APPS.HZ_CUST_ACCT_SITES_ALL HCAS, APPS.HZ_PARTY_SITES HPS ,
APPS.HZ_LOCATIONS HZL WHERE HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND
HPS.LOCATION_ID = HZL.LOCATION_ID AND CUST_ACCT_SITE_ID = (SELECT
CUST_ACCT_SITE_ID FROM APPS.HZ_CUST_SITE_USES_ALL WHERE SITE_USE_ID = :B1 )
TKPROF
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 33137 8.01 23.23 6707 132920 0 0
Fetch 33137 14.80 41.83 16127 331370 0 33137
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 66275 22.81 65.07 22834 464290 0 33137
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 173 (APPS) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
33137 NESTED LOOPS (cr=464290 pr=22834 pw=0 time=62916372 us)
33137 NESTED LOOPS (cr=364879 pr=17952 pw=0 time=50592119 us)
33137 TABLE ACCESS BY INDEX ROWID HZ_CUST_ACCT_SITES_ALL (cr=265468 pr=13105 pw=0 time=39910086 us)
33137 INDEX UNIQUE SCAN HZ_CUST_ACCT_SITES_U1 (cr=232331 pr=9751 pw=0 time=29071570 us)(object id 25124996)
33137 TABLE ACCESS BY INDEX ROWID HZ_CUST_SITE_USES_ALL (cr=132920 pr=6707 pw=0 time=20900111 us)
33137 INDEX UNIQUE SCAN HZ_CUST_SITE_USES_U1 (cr=99411 pr=3062 pw=0 time=7576200 us)(object id 25124976)
33137 TABLE ACCESS BY INDEX ROWID HZ_PARTY_SITES (cr=99411 pr=4847 pw=0 time=10528845 us)
33137 INDEX UNIQUE SCAN HZ_PARTY_SITES_U1 (cr=66274 pr=1969 pw=0 time=3967385 us)(object id 25124993)
33137 TABLE ACCESS BY INDEX ROWID HZ_LOCATIONS (cr=99411 pr=4882 pw=0 time=12156704 us)
33137 INDEX UNIQUE SCAN HZ_LOCATIONS_U1 (cr=66274 pr=1673 pw=0 time=4246752 us)(object id 25124992)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
33137 NESTED LOOPS
33137 NESTED LOOPS
33137 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'HZ_CUST_ACCT_SITES_ALL' (TABLE)
33137 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'HZ_CUST_ACCT_SITES_U1' (INDEX (UNIQUE))
33137 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'HZ_CUST_SITE_USES_ALL' (TABLE)
33137 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'HZ_CUST_SITE_USES_U1' (INDEX (UNIQUE))
33137 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'HZ_PARTY_SITES' (TABLE)
33137 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'HZ_PARTY_SITES_U1' (INDEX (UNIQUE))
33137 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'HZ_LOCATIONS' (TABLE)
33137 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'HZ_LOCATIONS_U1'
(INDEX (UNIQUE))
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
gc current block 2-way 2591 0.01 2.18
gc cr grant 2-way 14336 0.02 7.23
db file sequential read 22834 0.12 37.89
gc cr grant congested 2 0.00 0.00
latch: KCL gc element parent latch 1 0.00 0.00
latch: cache buffers chains 5 0.00 0.00
********************************************************************************
== (3) ==
SELECT data_string
FROM SEAOE_published_data -- this table has > 1 million records.
WHERE object_type = p_object_type
AND object_pk_id = p_object_id;
TKPROF
SELECT DATA_STRING
FROM
SEAOE_PUBLISHED_DATA WHERE OBJECT_TYPE = :B2 AND OBJECT_PK_ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 33109 2.36 2.32 0 0 0 0
Fetch 33109 21.64 172.64 25116 169087 0 33481
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 66219 24.00 174.97 25116 169087 0 33481
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 173 (APPS) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
33481 TABLE ACCESS BY INDEX ROWID SEAOE_PUBLISHED_DATA (cr=169087 pr=25116 pw=0 time=171727678 us)
33481 INDEX RANGE SCAN SEAOE_PUBLISHED_DATA_U1 (cr=135965 pr=4567 pw=0 time=42835829 us)(object id 29266250)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
33481 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'SEAOE_PUBLISHED_DATA' (TABLE)
33481 INDEX MODE: ANALYZED (RANGE SCAN) OF
'SEAOE_PUBLISHED_DATA_U1' (INDEX (UNIQUE))
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
gc cr grant 2-way 25115 0.01 13.09
db file sequential read 25116 0.66 143.45
latch: library cache 4 0.00 0.00
latch: KCL gc element parent latch 1 0.00 0.00
gc current block 2-way 26 0.00 0.02
gc cr grant congested 1 0.00 0.00
********************************************************************************