Re: Help me read my statspack report

<emdproduction_at_hotmail.com> wrote in message news:1166088247.249368.171390_at_f1g2000cwa.googlegroups.com...
>
>> You are starving the CPU (see top 5 events, always start there), by
>> issuing way too much sorts (see 'sort (rows)'), too many chained rows
>> (see 'table fetch continued row') and way way too much full table scans
>> (see 'tablescans (short tables)' and 'tablescans (long tables)'
>> You must have inefficient and untuned sql. You can track down the SQL
>> starving your system, by running statspack(5).
>>
>> --
>> Sybrand Bakker
>
> Thanks. But I think we had too much index scan as well, did you see we
> have a high logical reads?
>
> Also, could you comment on
> ======
> Execute to Parse %: 76.61 Latch Hit %: 99.49
> Parse CPU to Parse Elapsd %: 97.32 % Non-Parse CPU: 92.36
>
> Shared Pool Statistics Begin End
> ------ ------
> Memory Usage %: 94.04 94.02
> % SQL with executions>1: 28.31 26.99
> % Memory for SQL w/exec>1: 26.11 25.70
> ==========
> Does it mean bind variable is not used here?
>

If I have this correctly, you want help to diagnose the problem, so long as the help tells you that the problem is what you want it to be ?

Sybrand made some very sensible observations - you are doing an extraordinary amount of tablescanning

    table fetch by rowid                       5,500,640
    table scan rows gotten                 8,963,725,778

If it takes just 500 nanoseconds to examine and discard every row, that's 4,300 CPU seconds of the 5,200 you report. So your first suspicion should be inefficient SQL.

Of course, it is possible that the perceived slowness has nothing to do with the CPU burn, maybe it's wait time - and 515 seconds of your wait time is tablescans (or index fast full scans) so you need to check for inefficient SQL (again) in the critical path of the jobs which are "slow as hell"

The figures suggest that your parse costs are not terribly significant at present (on average).

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

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

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