TKPROF Output Help

From: Arthur <amerar_at_iwc.net>
Date: 15 Nov 2004 11:37:13 -0800
Message-ID: <8b622eae.0411151137.bade798@posting.google.com>

Hi All,

I am having a bit of trouble getting this query to run faster. The system I am working on is using the RBO. I cannot change that. Here is all my output, any pointers would be great.

Thanks.

SELECT T1."PL_EXCP_CODE" "c1", T2."TEXT" "c2", T3."TXTA_2" "c3", COUNT(T1."RECD_CODE") "c6"
FROM csiowner.pbms T1, csiowner.msgs T2, csiowner.tabm T3 WHERE (RTRIM(T1."PL_EXCP_CODE")=RTRIM(T2."MSGS_NUMBER") OR

       RTRIM(T1."WC_EXCP_CODE")=RTRIM(T2."MSGS_NUMBER")) AND
       SUBSTR(T1."PLANNER_BUYER_ID",1 ,1)=RTRIM(T3."RECORD_KEY") AND
       T3."RECORD_TYPE"='PLNG'

GROUP BY T1.PL_EXCP_CODE, T2."TEXT", T3."TXTA_2"

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch       19    829.14     853.59       2447     139764         13  
      256

------- ------ -------- ---------- ---------- ---------- ----------

total       21    829.14     853.59       2447     139764         13  
      256

Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 5

Rows Row Source Operation

-------  ---------------------------------------------------

    256 SORT GROUP BY
  49007 MERGE JOIN
  97091 SORT JOIN

  97090     NESTED LOOPS
   6936      TABLE ACCESS FULL MSGS
  97090      TABLE ACCESS BY INDEX ROWID TABM
 104025       INDEX RANGE SCAN (object id 63660)
  49007    FILTER
339863545     SORT JOIN
  55548      TABLE ACCESS FULL PBMS

Received on Mon Nov 15 2004 - 13:37:13 CST