emdproduct..._at_hotmail.com wrote:
> 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
Generally speaking you should resolve the issue, instead of curing
symptoms.
Just upgrading the hardware seldomly helps.
Cache buffer chain problems are usually the result of tuning a database
by increasing the buffer cache, instead of tuning inefficient
statements.
--
Sybrand Bakker
Senior Oracle DBA
Received on Thu Dec 14 2006 - 11:55:19 CST
