SEO

#1 (**permalink**) 02-26-2008, 02:55 AM

emdproduct...@hotmail.com wrote:
> Jonathan Lewis wrote:
> > <emdproduction@hotmail.com> wrote in message
> > news:1166088247.249368.171390@f1g2000cwa.googlegro ups.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

#2 (**permalink**) 02-26-2008, 02:55 AM

>
> 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

Sybrand,

Thanks very much for your help.

But from Johnathan's previous response to a user's help request about
"cache buffer chains", he instructed us to use x$bh in combine with
dba_extent, we can see which db block is the "hot spot".

And, I run the statspack level5 as you instructed, got a lot of sql,
but all the Top SQL (disk read or buffer gets) seems to respond fairly
quickly(<20 sec).

But if I do

SQL> select count(*) from v$sql where executions < 2;

COUNT(*)
----------
7868

SQL> select count(*) from v$sql ;

COUNT(*)
----------
11786

I can not attempt to think setting the cursor_sharing=force will help.

#3 (**permalink**) 02-26-2008, 02:55 AM

emdproduction@hotmail.com wrote:
> >
> > 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
>
> Sybrand,
>
> Thanks very much for your help.
>
> But from Johnathan's previous response to a user's help request about
> "cache buffer chains", he instructed us to use x$bh in combine with
> dba_extent, we can see which db block is the "hot spot".
>
> And, I run the statspack level5 as you instructed, got a lot of sql,
> but all the Top SQL (disk read or buffer gets) seems to respond fairly
> quickly(<20 sec).
>
> But if I do
>
> SQL> select count(*) from v$sql where executions < 2;
>
> COUNT(*)
> ----------
> 7868
>
> SQL> select count(*) from v$sql ;
>
> COUNT(*)
> ----------
> 11786
>
> I can not attempt to think setting the cursor_sharing=force will help.

And from v$ses_event, when I sort by timed_out column, the top 10 are
"latch free".

#4 (**permalink**) 02-26-2008, 02:56 AM

On Dec 14, 7:21 pm, emdproduct...@hotmail.com wrote:
> > 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 DBASybrand,
>
> Thanks very much for your help.
>
> But from Johnathan's previous response to a user's help request about
> "cache buffer chains", he instructed us to use x$bh in combine with
> dba_extent, we can see which db block is the "hot spot".
>
> And, I run the statspack level5 as you instructed, got a lot of sql,
> but all the Top SQL (disk read or buffer gets) seems to respond fairly
> quickly(<20 sec).
>
> But if I do
>
> SQL> select count(*) from v$sql where executions < 2;
>
> COUNT(*)
> ----------
> 7868
>
> SQL> select count(*) from v$sql ;
>
> COUNT(*)
> ----------
> 11786
>
> I can not attempt to think setting the cursor_sharing=force will help.

Ok, I see you have a distinct preference for symptom fighting, and
following your own interpretations, instead of accepting advice from
others.
One might want to ask why you posted this at all, as you already know
the solution.
Yet I must warn you: you are using wrong criteria. It doesn't tell
anything the top 5 sql executes in less than 20 seconds, what matters
is the number of buffers it uses.
Changing cursor_sharing to force also seldomly fixes anything, it makes
sure the patient suffers less from terminal cancer, yet he still
suffers from cancer.
So if you want to continue to follow your own path, I won't block you,
I just won't respond to any of your future posts.

--
Sybrand Bakker
Senior Oracle DBA

#5 (**permalink**) 02-26-2008, 02:56 AM

> Ok, I see you have a distinct preference for symptom fighting, and
> following your own interpretations, instead of accepting advice from
> others.
> One might want to ask why you posted this at all, as you already know
> the solution.
> Yet I must warn you: you are using wrong criteria. It doesn't tell
> anything the top 5 sql executes in less than 20 seconds, what matters
> is the number of buffers it uses.
> Changing cursor_sharing to force also seldomly fixes anything, it makes
> sure the patient suffers less from terminal cancer, yet he still
> suffers from cancer.
> So if you want to continue to follow your own path, I won't block you,
> I just won't respond to any of your future posts.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

Do not take me wrong. I really appreciate your help and took your
advice to look for the TOP SQL from day one.

#6 (**permalink**) 02-26-2008, 02:56 AM

> Ok, I see you have a distinct preference for symptom fighting, and
> following your own interpretations, instead of accepting advice from
> others.
> One might want to ask why you posted this at all, as you already know
> the solution.
> Yet I must warn you: you are using wrong criteria. It doesn't tell
> anything the top 5 sql executes in less than 20 seconds, what matters
> is the number of buffers it uses.
> Changing cursor_sharing to force also seldomly fixes anything, it makes
> sure the patient suffers less from terminal cancer, yet he still
> suffers from cancer.
> So if you want to continue to follow your own path, I won't block you,
> I just won't respond to any of your future posts.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

Do not take me wrong. I really appreciate your help and took your
advice to look for the TOP SQL from day one. And I will let you know
the result after we examine all the expensive SQLs.

Thanks

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Re: Help me read my statspack report