Re: 10046 trace: 'latch free' then 'db file seq. read' waits

Mladen Gogala wrote:
> hpuxrac wrote:
> > Mladen Gogala wrote:
> >> On Wed, 14 Jun 2006 00:07:40 -0700, Spendius wrote:
> >>
> >>> Argh... Forgot to say I'm dealing with an 8i DB, for which
> >>> SQL> select name from v$latch where latch#=66;
> >>> NAME
> >>> ---------------------
> >>> cache buffers chains
> >> You need more cache buffer chains.
> >
> > Conceivably possible but more likely it's bad SQL or some kind of
> > strange optimizer derived access pattern.
> >
> > What's the point in taking a wild stab in the dark?
>
>
> No stabs in the dark here. Common wisdom tells me that if he's
> waiting for buffer chains, it's prudent to create more of them.
> That, actually, is all that I can judge about.

Actually I wouldn't recommend judging anything here without knowing first if the OP has collected properly scoped diagnostic data in the 10046 trace and if the OP has put that trace through a resource profiler.

Manually scanning lines in a trace file might give some hints but no more than that.

What if the event that was detected manually by looking at selected lines in the trace file only accounts for a small percentage of the overall response time.

Egor developed a nice free clone of the hotsos resource profiler orasrp. Even though he is no longer supporting it if you google around you can still find some sites to download it from.

Using a resource profiler you can drill into problem statements and look at the SQL involved with the actual execution plan, etc.

Advocating fixing and changing oracle parameters without looking in depth first at what is occuring and why is not a repeatable process. To see that kind of approach advocated on cdos is something that can be avoided, please. Received on Thu Jun 15 2006 - 08:39:48 CDT