Re: very inefficient lob updates

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> 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.

Changing PCTVERSION seems to have a very large effect on the distribution of consistent reads, but a much lower effect on the average number. The "exceptional" executions are much more common with higher PCTVERSION, but each one is less severe.

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

Thanks, will do.

Xho

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

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB