RE: Need help with tuning SQL statement
Date: Tue, 26 Feb 2008 10:08:22 -0500
Message-ID: <0684DA55864E404F8AD2E2EBDFD557DABE0ECE@JAXMSG01.crowley.com>
It is true that the number of times a statement is execute is important,
add up. I wonder why the data isn't in memory. Sorry, but I'm not
much of a tuning guy.
Joel Patterson
Database Administrator
joel.patterson_at_crowley.com
x72546
904 727-2546
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hameed, Amir
Sent: Tuesday, February 26, 2008 9:41 AM
To: oracle-l_at_freelists.org
Subject: Need help with tuning SQL statement
Folks,
I have a trace file that contains a few statements that are taking a
long time to complete. I am going to paste TKPROF o/p from one statement
here (other statements have similar behavior). This is an Oracle
11.5.9/10.2.0.2 system running on Sun Solaris9. From what I can tell by
looking at the "db file sequential read" wait statistics, 700713 I/Os
were performed in 3130.18 seconds which indicates that the average I/O
time was ~ 4.46 ms and is quite good. Is the issue here related to the
fact that the statement was executed too many times (117382)? Would
using bulk-fetch feature help in reducing the elapsed time here?
Any help will be appreciated.
Thanks
SELECT OKL.CLE_ID LINE_ID, OKL.ID SUBLINE_ID, OKL.START_DATE
LINE_START_DATE,
DECODE (ORB.ATTRIBUTE11, 'MO', 1, 'Q', 2, 'BA', 3, 'YR', 4, 99 )
BILLING_FREQ_ORDER, DECODE (ORB.ATTRIBUTE11, 'MO', ' ', 'Q', 'P',
'BA', 'S',
'YR', 'A', NVL (SUBSTR (ORB.ATTRIBUTE11, 1, 10), 'N/A') )
BILLING_FREQ
FROM
OKC_K_HEADERS_B OKH, OKC_K_LINES_B OKL, OKC_K_ITEMS OKI,
OKC_RULE_GROUPS_B
ORG, OKC_RULES_B ORB, TXRCC0_CS_COUNTERS_MP TCC WHERE OKL.ID =
OKI.CLE_ID
AND OKH.ID = OKL.DNZ_CHR_ID AND OKL.CLE_ID = ORG.CLE_ID AND
OKI.OBJECT1_ID1
- TO_CHAR (TCC.COUNTER_ID) AND OKI.JTOT_OBJECT1_CODE = 'OKX_COUNTER' AND OKH.CONTRACT_NUMBER = :B2 AND TCC.INSTANCE_COUNTER_GROUP_ID = :B1 AND ORG.ID = ORB.RGP_ID AND ORB.RULE_INFORMATION_CATEGORY = 'QRE' ORDER BY
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 117382 15.08 14.67 0 0 0 0
Fetch 117382 377.05 3485.69 700713 5941451 0 140578
------- ------ -------- ---------- ---------- ---------- ----------
total 234765 392.13 3500.37 700713 5941451 0 140578
Misses in library cache during parse: 1
Misses in library cache during execute: 2
Optimizer mode: ALL_ROWS
Parsing user id: 173 (APPS) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
4761 SORT ORDER BY (cr=203532 pr=21787 pw=0 time=126450991 us)
4761 TABLE ACCESS BY INDEX ROWID OKC_RULES_B (cr=203532 pr=21787
pw=0 time=126955746 us)
13288 NESTED LOOPS (cr=199055 pr=19266 pw=0 time=272743522 us)
4761 NESTED LOOPS (cr=184936 pr=18429 pw=0 time=96277230 us) 4761 HASH JOIN (cr=166034 pr=15515 pw=0 time=80863467 us) 5030 NESTED LOOPS (cr=154719 pr=15467 pw=0 time=76459237 us) 5030 NESTED LOOPS (cr=134726 pr=12232 pw=0 time=65209837 us) 37844 TABLE ACCESS BY INDEX ROWID TXRCC0_CS_COUNTERS_MP (cr=46240 pr=9639 pw=0 time=66473907 us) 37844 INDEX RANGE SCAN IXRTC0_COUNTER_GRP_ID_N1 (cr=11421 pr=1416 pw=0 time=9663661 us)(object id 9849161) 5030 TABLE ACCESS BY INDEX ROWID OKC_K_ITEMS (cr=88486 pr=2593 pw=0 time=13838836 us) 5030 INDEX RANGE SCAN OKC_K_ITEMS_N2 (cr=83469 pr=480 pw=0 time=3066136 us)(object id 114433) 5030 TABLE ACCESS BY INDEX ROWID OKC_K_LINES_B (cr=19993 pr=3235 pw=0 time=10801289 us) 5030 INDEX UNIQUE SCAN OKC_K_LINES_B_U1 (cr=14802 pr=1123 pw=0 time=4495583 us)(object id 85466) 3766 TABLE ACCESS BY INDEX ROWID OKC_K_HEADERS_B (cr=11315 pr=48 pw=0 time=357535 us) 3766 INDEX RANGE SCAN OKC_K_HEADERS_B_U2 (cr=7548 pr=2 pw=0 time=114972 us)(object id 85479) 4761 TABLE ACCESS BY INDEX ROWID OKC_RULE_GROUPS_B (cr=18902 pr=2914 pw=0 time=15269149 us) 4761 INDEX RANGE SCAN OKC_RULE_GROUPS_B_N2 (cr=14141 pr=1305 pw=0 time=6744772 us)(object id 108871) 4761 INDEX RANGE SCAN OKC_RULES_B_N4 (cr=14119 pr=837 pw=0time=6817338 us)(object id 108857)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
4761 SORT (ORDER BY)
4761 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'OKC_RULES_B' (TABLE)
13288 NESTED LOOPS
4761 NESTED LOOPS
4761 HASH JOIN
5030 NESTED LOOPS
5030 NESTED LOOPS
37844 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'TXRCC0_CS_COUNTERS_MP' (TABLE)
37844 INDEX MODE: ANALYZED (RANGE SCAN) OF
'IXRTC0_COUNTER_GRP_ID_N1' (INDEX)
5030 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'OKC_K_ITEMS' (TABLE)
5030 INDEX MODE: ANALYZED (RANGE SCAN) OF
'OKC_K_ITEMS_N2' (INDEX)
5030 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'OKC_K_LINES_B' (TABLE)
5030 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'OKC_K_LINES_B_U1' (INDEX (UNIQUE))
3766 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'OKC_K_HEADERS_B' (TABLE)
3766 INDEX MODE: ANALYZED (RANGE SCAN) OF
'OKC_K_HEADERS_B_U2' (INDEX (UNIQUE))
4761 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'OKC_RULE_GROUPS_B' (TABLE)
4761 INDEX MODE: ANALYZED (RANGE SCAN) OF
'OKC_RULE_GROUPS_B_N2' (INDEX)
4761 INDEX MODE: ANALYZED (RANGE SCAN) OF 'OKC_RULES_B_N4'
(INDEX)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
- Waited ----------
db file sequential read 700713 1.46 3130.18 latch: cache buffers chains 11 0.00 0.00 latch: shared pool 2 0.00 0.00 latch free 3 0.02 0.02 latch: row cache objects 1 0.00 0.00
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 26 2008 - 09:08:22 CST