Re: TKPROF Output Help

amerar_at_iwc.net (Arthur) wrote in message news:<8b622eae.0411151137.bade798_at_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
Actually this looks like a pretty good plan considering the fact that the where clause has a function on every join condition column which under the RBO will disable the use of any index on that column.

Being that the RBO does not know what a function based index is then unless you can eliminate the rtrim's I do not see a lot of hope.

I suppose that the rtrim is because the tables are defined using char instead of varchar2.

One thing you might try depending on the unspecified version of Oracle is that since 7.3 the presence of any hint other than rule will cause the cost based optimzer to be invoked. You might try ordering the FROM clause in the best join order and try to see if you can hint the query into using hash joins instead of sort/merge.

HTH -- Mark D Powell -- Received on Mon Nov 15 2004 - 20:04:41 CST