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

Thanks for your advice - I believe I see where you're coming from. I'm not familiar with the Oracle-L email list - can you give me a URL?

I have a few ideas as to what statements may be causing the problem - even under "smooth sailing" time windows, I still see the total "enqueue" time in the statspack report as showing up as a rather high percentage of the time under the top 5 Timed Events (501 seconds in a 10 minute period). Unfortunately, it may not be possible to deal with them without a major system redesign. However, I'm curious regarding this:

I understand how DML can get blocked by another DML statement, but how can a SELECT statement become blocked? We're not doing any logic where we perform full table exclusive locks, and in experiments I've done, updating a table with a bitmap index doesn't block another session that is doing a select from that table.

I may not be able to improve the performance of my DML statements, but if I can figure out what resource they're acquiring that is locking out my SELECTS, I can deal with the equeue problem that way.

-S

"Ana C. Dent" <anacedent_at_hotmail.com> wrote in message news:<Xns9580497EA3794SunnySD_at_68.6.19.6>...
> BigBoote66_at_hotmail.com (Steve B) wrote in
> news:67bcf80a.0410120608.7906bf18_at_posting.google.com:
>
> > "Ana C. Dent" <anacedent_at_hotmail.com> wrote in message
> > news:<Xns957FB795CB741SunnySD_at_68.6.19.6>...
> >> enable event 10046 at level 8
> >> run the resultant trace file thru TKPROF.
> >> TKPROF's output will reveal the culprit SQL statement(s).
> >> Then tune the ones where a large discrepency between
> >> elapsed & CPU time exists.
> >>
> >> You can search the achives of the ORACLE-L email list
> >> to complete instructions on how to do all this
> >
> > I'm not sure I understand your advice. We know which sql statements
> > are the slow ones - I left them out of the output above for space
> > purposes. However, the same statement that runs slowly (with the
> > large elapsed time but favorable statistics) will run just fine if you
> > re-execute it at a later date (with physical i/o being the same), so
> > the statement is already "tuned". I believe the real problem is some
> > kind of resource contention, and I'm looking for a way of identifying
> > the resource that is causing the slowdown.
> >
> > -Steve
> >
>
> Visit http://asktom.oracle.com
> do a keyword search on "enqueue wait".
Received on Tue Oct 12 2004 - 15:54:00 CDT