Re: What creates the most volume of REDO entries: INSERT, UPDATE, or DELETE?
From: joel garry <joel-garry_at_home.com>
Date: Fri, 17 Jul 2009 14:50:08 -0700 (PDT)
Message-ID: <2d1fd20e-9cae-4d43-8e30-52bb65f0bcd1_at_d9g2000prh.googlegroups.com>
On Jul 17, 12:39 pm, dana <dana_at_w..._at_yahoo.com> wrote:
> Which of these 3 operations creates the most volume of total REDO log
> file entries; not REDO log *records*: INSERT, UPDATE, or DELETE? A
> colleague of mine says INSERTS add more to the redo log file. His
> rationale? the redo log contains all changes to the database; and an
> entire row takes the most redo log file space to store and re-play if
> the database goes down.
>
> In practice, it seems like DELETEs create larger REDO log files; have
> seen servers crash when archive redo log disk space runs out from
> running DELETE FROMs instead of TRUNCATEs; when the same raw number of
> INSERT operations doesn't seem to cause the same problem.
>
> The only thing I can come up with: for UNDO, DELETE operations create
> the most UNDO volume because they store the "before" image of a row.
> INSERT operations create the least UNDO volume because they store only
> a ROWID. Seems that UNDO records are mirrored in the redo log files.
> Is this why large numbers of INSERTS seem to be less redo logfile
> intensive than the same number of DELETEs?
>
> Wondering if I'm missing something about a symmetry or asymmetry
> between INSERTs and DELETEs and the volume of space they fill in the
> redo log files; vs. redo records only.
>
> Thanks.
>
> Dana
Date: Fri, 17 Jul 2009 14:50:08 -0700 (PDT)
Message-ID: <2d1fd20e-9cae-4d43-8e30-52bb65f0bcd1_at_d9g2000prh.googlegroups.com>
On Jul 17, 12:39 pm, dana <dana_at_w..._at_yahoo.com> wrote:
> Which of these 3 operations creates the most volume of total REDO log
> file entries; not REDO log *records*: INSERT, UPDATE, or DELETE? A
> colleague of mine says INSERTS add more to the redo log file. His
> rationale? the redo log contains all changes to the database; and an
> entire row takes the most redo log file space to store and re-play if
> the database goes down.
>
> In practice, it seems like DELETEs create larger REDO log files; have
> seen servers crash when archive redo log disk space runs out from
> running DELETE FROMs instead of TRUNCATEs; when the same raw number of
> INSERT operations doesn't seem to cause the same problem.
>
> The only thing I can come up with: for UNDO, DELETE operations create
> the most UNDO volume because they store the "before" image of a row.
> INSERT operations create the least UNDO volume because they store only
> a ROWID. Seems that UNDO records are mirrored in the redo log files.
> Is this why large numbers of INSERTS seem to be less redo logfile
> intensive than the same number of DELETEs?
>
> Wondering if I'm missing something about a symmetry or asymmetry
> between INSERTs and DELETEs and the volume of space they fill in the
> redo log files; vs. redo records only.
>
> Thanks.
>
> Dana
You're missing the concept of change vectors. The redo is the difference between the block and the new block. If you only change a varchar2(1), not much redo.
See:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/onlineredo.htm#sthref857
http://orainternals.wordpress.com/2008/09/11/how-to-find-objects-creating-nologging-changes/
metalink Note: 1031381.6 or
http://advait.wordpress.com/2008/01/02/dumping-redo-log-file-information-oracle-database-10g/
Google around, there are explanations by people like Tom Kyte somewhere that compare how much redo generated. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7036069421885 isn't what I was looking for, but a start.
jg
-- _at_home.com is bogus. http://www3.signonsandiego.com/stories/2009/jul/17/1m17hacker221630-hotline-ucsd-patients-swamped/?metro&zIndex=133482Received on Fri Jul 17 2009 - 16:50:08 CDT