Re: Horrible Query Performance, Simple Query -- Performance Tuning Help Needed

"WhiteDog" <cchenoweth56_at_msn.com> wrote in message news:1145546326.070848.162850_at_g10g2000cwb.googlegroups.com...
> Here is the HDD_TRAN_2000 Function:
>
> SELECT representation INTO rep
> FROM rsform, rsform_context
> WHERE rsform.rsform_id = rsform_context.rsform_id
> AND rsform_context.context_ncid = 2000
> AND rsform.ncid = ncid_to_translate
> AND rsform_context.preferred_score = 0
> AND rownum = 1;
> RETURN rep;
>
>
> I'll run the trace with those events and post later today.
>

The calls to the function would probably account for the discrepancy in your tkprof output:

<quote>
total 64 0.60 27.20 3857 8167 0 907

Rows Row Source Operation

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

    907 SORT ORDER BY (cr=38786 pr=5695 pw=0 time=33005420 us)

    907 NESTED LOOPS (cr=8166 pr=3856 pw=0 time=21419454 us)

</quote>

Total Physicals = 3857
Total Consistent = 8167

These match the figures for pr= cr = in the NESTED LOOPs line.

The excess 30,600 cr and 1,840 pr will probably be reported in the totals for the SQL run by the function.

There is still an accounting problem with time, though - as the nested loop shows 21.4 seconds, with the sort we get to 33 seconds, but the total only shows 27.2 seconds. But the rest may have been spent in the 64 round-trips to client, I guess.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html