Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Tuning question - low cpu_time, high elapsed_time - what gives?

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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 13 Oct 2004 04:53:27 +0000 (UTC)
Message-ID: <ckicc7$4tk$1@hercules.btinternet.com>

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.
>
Received on Tue Oct 12 2004 - 23:53:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US