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

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<ckicc7$4tk$1_at_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.
> >

Steve,

Please note that the kernel tries to detect unusual circumstances such as disruptions with the lock manager; if a resource is held X or SSX. Another is node death. Locks are validated during a lengthy cleanup.

What make these calls expensive (i.e. getting completion status from within a blocking request -- probably conflicting) is that info is not available on the local node.

Amjad Daoud
http://www.omlet.org Received on Thu Oct 14 2004 - 02:44:50 CDT