Re: Help me read my statspack report

On Dec 14, 7:21 pm, emdproduct..._at_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