Re: Tuning question - low cpu_time, high elapsed_time - what gives?

Thanks very much for the recommendation - yours has been the most useful advice I've seen here. I got the book and have been applying what I've learned. I've since done some analysis and discovered that the problem seems to stem from the fact that every once in a while, a "db file sequential read" step in my queries takes an inordinate amount of time for a single block of data fetched - sometimes 3 seconds, sometimes 30 seconds! I don't really know how to find out what is causing the problem. I have described it further here, in case you have anything to add:

http://groups.google.com/groups?hl=en&lr=&safe=off&threadm=clkpc3%24uon%241%40lust.ihug.co.nz&prev=/groups%3Fhl%3Den%26lr%3D%26safe%3Doff%26group%3Dcomp.databases.oracle.server

-Steve

bdurrett_at_diamonddata.com (Bobby Durrett) wrote in message news:<1903bd23.0410150918.560970ec_at_posting.google.com>...
> > I've done traces before, but always from within the session that I'm
> > tracing, and only for the purposes of query tuning - looking at
> > minimizing i/o or playing with query plans. Using traces for resource
> > contention are new to me. Is it possible to enable tracing for
> > session A from another session B? Also, the problem occurs somewhat
> > sporadically; I assume I can't enable tracing after the fact, so if I
> > want to capture this behavior, I'll need to be tracing the sessions
> > a-priori. I have pool of sessions that exhibit this behavior - about
> > 20, and I'm a little nervous about what kind of impact it's going to
> > have on our production system to have 20 sessions tracing at 10086 -
> > is this a valid concern?
> >
> > Finally, once I do have a trace file that shows the statement and the
> > pain it was experiencing, and it shows that it suffered from some kind
> > of wait XXX, will the trace file show me the other process that was
> > causing the block, or will it just show the wait.
> >
> > I realize these are complex questions - do you have a resource you
> > could point me at (book, URL) that could help me out with this?
> >
>
> All of this is in the book "Optimizing Oracle Performance" by Cary
> Millsap/Jeff Holt.
>
> http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_perf.html
> is a free article that is about the same topic.
>
> You can turn on trace in another session. You might want to try just
> tracing one session in your production system and see what impact it
> has. Maybe you can catch it when things are slow.
>
> The waits in the trace file (not the output from tkprof) have some
> more information about what it waits on. But what I think you will
> really gain is an understanding of the kind of thing it is waiting on.
> Is it disk? Is it a lock? Once you find the name of the wait event
> you can research that particular event and see what it means.
>
> Anyway, the book I mention does a much better job of explaining this.
> Also, it is relatively short and inexpensive.
>
> - Bobby
Received on Tue Oct 26 2004 - 15:23:16 CDT