RE: Update of Clobs *Performance*
- From: "Anthony Molinaro" <amolinaro@xxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 3 Nov 2004 19:18:22 -0500
Mark,
> if you continue adding to the size of the update between commits
performance will degrade.
Obviously.
a little common sense goes a long way.=20
I did not realize I had to qualify my responses with that :)
You make great points, and I'm afraid I was unclear.
I'm not against batch commits, they are much better that single commits.
My problem with even the batch commit solution though, is that it tends
to deter you from the real fix - to work in sets.=20
Look at the initial post... it should be a single update or an array
update.
By saying, "think about a commit counter within the loop..."=20
you're suggesting (maybe unintentially) it's ok to loop,=20
when in fact (in this case), it's not.
Regarding the initial post, once the index is added and correct where
clause used,
the 2 things that will improve performance (of writes) most is the
nologging storage
param of the lob and doing either one update or array updates.=20
Regards,
Anthony
=20
=20
-----Original Message-----
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]=20
Sent: Wednesday, November 03, 2004 4:18 PM
To: Anthony Molinaro; oracle-l@xxxxxxxxxxxxx
Subject: RE: Update of Clobs *Performance*
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Anthony Molinaro
Sent: Wednesday, November 03, 2004 3:35 PM
To: Mark W. Farnham; oracle-l@xxxxxxxxxxxxx
Subject: RE: Update of Clobs *Performance*
Mark,
>there is a good chance it will blow up later as the tables grow.
Kidding right?
<< not kidding at all. I've seen plenty of toy monolith designs blow up
<< when unleashed on big or growing data. "But we haven't changed the
programs at all!"
You bring up a good point, but I disagree. My reasons are:
1. one commit will outperform the counter.
<< That is something you can measure, but you can't possibly know the
answer without testing. << There is a cost to accumulation of UNDO, and
generally you reach a plateau where the cost << of extra commits is
balanced against the cost overhead of accumulated UNDO and then if <<
you continue adding to the size of the update between commits
performance will degrade. << Sometimes it is even worthwhile to measure
to make sure you pick a commit frequency from << the plateau.
2. if the table is so huge that a single commit is a problem
then he shouldn't be looping one by one in the first place << Feel
free to send him a set update example. 3. undo is a little different
here, these are lobs - pctversion << Unrestricted size of UNDO
accumulated uncommitted is still the issue
The idea of a counter is useful if you want "partial loads".
If half get in and the rest fail, and that's what you want, and you have
a reliable point where you can restart, then cool. Otherwise, I really
disagree.=3D20
<< The idea of a counter is useful if you want a job that will work
regardless of the growth << of your data. Clearly his process is
restartable, but it would indeed do the entire job << again. The data
model presented, however, does not lend itself to having a restart point
<< unless you would trust select to always return rows in the same
order. Then I suppose you << could scan off the n rows previously
updated before entering the update loop. Of course << someone may have
added a row since the previous update was attempted and ASSM may have <<
stuck it anywhere. << Of course you're free to disagree, but you might
find it enlightening to try some timed << tests of monolithic commits
versus commit counters. Of course you'll need to be sure to << test
array updates versus array updates or row by row versus row by row to
get valid << results. << << Regards, << mwf
- Anthony
-----Original Message-----
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]=3D20
Sent: Wednesday, November 03, 2004 3:17 PM
To: Anthony Molinaro; oracle-l@xxxxxxxxxxxxx
Subject: RE: Update of Clobs *Performance*
Putting in a counter, picking a reasonable size, commit'n and reset'n
the counter when you hit the limit is usually useful.
Even if a single commit for the whole table will work for him now, there
is a good chance it will blow up later as the tables grow. It's pretty
likely committing each row is unreasonable, but committing monoliths is
a recipe for future problems and driving UNDO out of cache without need.
I recommend avoiding monolithic commits unless there is a hard
requirement for reversibility (rollback) and avoiding a program
architecture that drives a need for monolithic commits is up there with
the golden mean as far as I'm concerned.
mwf
-----Original Message-----
From: Anthony Molinaro [mailto:amolinaro@xxxxxxxx]
Sent: Wednesday, November 03, 2004 3:05 PM
To: mwf@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Update of Clobs *Performance*
In regard to: >>>>>>>> Even better, just commit once at the end...
-----Original Message-----
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Wednesday, November 03, 2004 3:00 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Update of Clobs *Performance*
create index why_full_scan_all_my_clobs_for_each_one_row_update on
tableB(tabB_num)
change your where clause to where tabB_num =3D3D to_number(v_id)
Think about a commit counter within the loop less than the entire table.
Maybe 1000 or 10000?
Regards,
mwf
<snip>
Procedure
declare
v_clob varchar2(32500);
v_id varchar(10);
cursor cont_rep_clob is
select tabA_char, tabA_clob
from Table_A;
begin
open cont_rep_clob;
loop
fetch cont_rep_clob into v_id, v_clob;
exit when cont_rep_clob%NOTFOUND;
update Table_B
set tabB_clob =3D3D v_clob
where to_char(tabB_num) =3D3D v_id;
commit;
end loop;
close cont_rep_clob;
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
