Re: Help me read my statspack report

From: <emdproduction_at_hotmail.com>
Date: 14 Dec 2006 09:45:07 -0800
Message-ID: <1166118307.072987.27310@79g2000cws.googlegroups.com>

Jonathan Lewis wrote:
> <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

Johathan,

Thanks very much for your help. I really appreciated. I just want to make sure my suspicion is correct or not.

In our system, we have a large "cached buffers chain" contention, I used your method on internet, and they all points at a few DB blocks on 2 tables. I wonder is it something in the application desing that cause the slowness, do you think we need to upgrade our hardware can solve this problem?

archive process latch                82    0.0             0
0
cache buffer handles            212,771    0.0    0.0      0
0
cache buffers chains        162,172,226    0.7    0.0      0
4,170,498    0.0
cache buffers lru chain          30,906    0.1    0.1      0

6,132,286 0.1

Latch Name                       Requests      Misses      Sleeps
Sleeps 1->4
-------------------------- -------------- ----------- -----------
------------
cache buffers chains          162,172,226   1,075,291       2,068
0/0/0/0/0
library cache                   7,413,632      44,110         130
43981/128/1/
                                                                  0/0
library cache pin               4,203,089      16,818           9
16809/9/0/0/
                                                                  0
row cache objects              21,365,569      16,195           2
16193/2/0/0/
                                                                  0
row cache enqueue latch        21,358,822      14,016           1
14015/1/0/0/
                                                                  0
shared pool                     4,320,950      10,965         306
10660/304/1/
                                                                  0/0

Received on Thu Dec 14 2006 - 11:45:07 CST