Re: Undo Generation
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 29 Jun 2007 17:42:00 +0100
I believe you are using 10g.
There's an optimisation for undo and redo in 10g
which aims to minimise the number of records
(vectors for redo) on array processing.
For inserts, all inserts made at the same time to
a single block can be packed into a undo record
(ditto redo vector).
For delete, every row deleted has to generate a
separate undo record (redo vector).
For updates there is an odd mix (which may also be
true for inserts, except that I haven't looked closely
enough). The number of undo records can be between
1 and the number of rows updated. I think this depends
on the number of rows which can be updated 'in situ' -
so your 9 suggests that several rows had to be
transferred into the block free space for the update
to take place. If you had done
set sal = sal + 1
you may have found fewer undo records.
(I guess I ought to test whether an insert that causes
deleted rows to be cleared out and the free space to
be coalesced results in many more undo records, or just
one or two extra - or maybe one per deleted row cleared).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
Date: Thu, 28 Jun 2007 23:13:39 +0530
From: "DBA Deepak" <oracle.tutorials@xxxxxxxxx>
Subject: Undo Generation
Hi Gurus,
Have a simple doubt. I did an experiment to know which DML statement
generates the Most/Least amount of UNDO for the same no. of records.
Steps:
===========================================================
SQL> conn scott/tiger
Connected.
SQL> create table myemp as select * from emp;
Table created.
SQL> delete myemp;
14 rows deleted.
SQL> select used_ublk,used_urec from v$session s,v$transaction t where
s.taddr=t.addr and s.username='SCOTT';
USED_UBLK USED_UREC
---------- ----------
2 14
SQL> rollback;
Rollback complete.
SQL> update myemp set sal=sal*1.1;
14 rows updated.
USED_UBLK USED_UREC
---------- ----------
1 9
SQL> rollback;
Rollback complete.
SQL> insert into myemp select * from emp;
14 rows created.
USED_UBLK USED_UREC
---------- ----------
1 1
SQL> rollback;
Rollback complete.
=================================================================
Please note that there was only one session connected to the database as
SCOTT user.
My question is why the do the UNDO generated for DELTE and UPDATE DML
statements differ? Because Oracle has to keep undo information of the same
no. of records in both the cases.
Please help me on this.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
