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

Are you using distributed transactions to update the table ? (For a hint about
the presence of distributed transactions look at v$enqueue_stats for enqueues
of type 'DX').

If so then a query had to wait for
a distributed update to complete
its two-phase commit if the query
starts between the prepare and
commit and visits a block modified
by the distributed transaction.

In times of high load (especially
network load) this can slow things
down considerably.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 19th





"Steve B" <BigBoote66_at_hotmail.com> wrote in message
news:67bcf80a.0410121254.4e553c81_at_posting.google.com...


>

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

>