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

very inefficient lob updates

From: <ctcgag_at_hotmail.com>
Date: 26 Apr 2004 18:20:09 GMT
Message-ID: <20040426142009.915$n7@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 Mon Apr 26 2004 - 13:20:09 CDT

Original text of this message

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