Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> very inefficient lob updates
(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 30GBReceived on Mon Apr 26 2004 - 13:20:09 CDT
![]() |
![]() |