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

  BILLING_FREQ_ORDER, TCC.PARENT_METER_NUMBER


call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
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=0
time=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

Other related posts: