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: very inefficient lob updates

Re: very inefficient lob updates

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 27 Apr 2004 16:53:24 +0000 (UTC)
Message-ID: <c6m364$ns3$1@sparta.btinternet.com>

Just a guess, that you may be able to
test if you have a repeatable data set.

Possibly the odd few special cases appear when the LOB segment gets to the PCTVERSION and does something to decide which old lobs have to be cleaned out and overwritten.

If you can re-run your data set with a couple of extreme values for PCTVERSION, you might spot some difference in the timing of the strange few rows.

Since you are on 9.2.0.2, you could also download the patch notes for 9.2.0.5 and see if there is anything in the bug list about fixes for LOBs.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

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

April 2004 Iceland  http://www.index.is/oracleday.php
June  2004      UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar

<ctcgag_at_hotmail.com> wrote in message
news:20040426142009.915$n7_at_newsreader.com...

> (Sorry for the wrap on previous version of post)
> I'm doing a lob update based on primary key, and I get an exorbitant
number
> of consistent gets per execution, 1161 on average:
>
> update a_table set clob_column=:p1
> where
> the_pk=:p2
>
> call count cpu elapsed disk query current rows
> ----- ------ ----- ---------- ---------- ---------- ---------- ----------
> Parse 1 0.00 0.00 0 0 0 0
> Execute 1719 18.92 19.94 0 1995884 74385 1719
> Fetch 0 0.00 0.00 0 0 0 0
> ----- ------ -------- -------- --------- ---------- ---------- ---------
> total 1720 18.92 19.95 0 1995884 74385 1719
>
> Misses in library cache during parse: 1
> Misses in library cache during execute: 2
> Optimizer goal: CHOOSE
> Parsing user id: 66
>
> All the clobs are small enough to fit into either one or two 8K db blocks.
> They are approximately the same size as the clobs they are replacing.
> What on earth could it be doing that needs that many reads to update a
> small clob? By looking at the trace files, the typical value per
execution
> is 13 cr (which is more inline with what I expect) but about 10 of the
> executions have over 100,000 cr each. All of the updates were part of one
> (maybe two) transaction, and there was very little other activity at the
> time. The explain plan showed a simple primary key look-up on the table.
>
> Is this normal expected behaviour for clobs?
>
> 9.2.0.2.0 on SunOS 5.8
>
> Thanks,
>
> Xho
>
> --
> -------------------- http://NewsReader.Com/ --------------------
> Usenet Newsgroup Service $9.95/Month 30GB
Received on Tue Apr 27 2004 - 11:53:24 CDT

Original text of this message

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