How much UNDO does a SQL use?

If you wish to know how much UNDO does a DML statement use, here is a method.

First, let’s create a test table to populate 100,000 rows.

SQL> create table t1 ( id number(6), name varchar2(30) );

Table created.

SQL> create sequence seq;

Sequence created.

SQL> insert into t1 select seq.nextval, rpad(‘x’,30, ‘x’) from test where rownum < 100001;

100000 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

COUNT(*)
———-
100000

The dynamic view v$transaction provides details of active transactions in the database ; the column USED_UBLK provides the number of undo blocks consumed by a transaction; the column USED_UREC provides the number of undo records used.

col sql_text format a40
set lines 130
select sq.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks, (t.USED_UBLK*8192/1024) KBytes from v$transaction t,
v$session s,
v$sql sq
where t.addr = s.taddr
and s.sql_id = sq.sql_id
and s.username = ‘<user>’
/

Let’s update the table using

SQL> update t1 set name = rpad(‘y’, 30, ‘y’) ;

100000 rows updated.

and run the v$ script from another window to get the output below:

SQL_TEXT                                    RECORDS     BLOCKS     KBYTES
---------------------------------------- ---------- ---------- ----------
update t1 set name = rpad('y', 30, 'y')      200000       3078      24624

Using v$transaction provides an insight to the active transactions on the system and the resources used by those transactions.

This tells the update dml used 3078 blocks or 24Mb of undo. But, look at USED_UREC column – it shows 200,000 records when I updated only 100,000 records.. this seems strange… this is not what I’ve seen before.. I need to check more on this .. stay tuned…

---

Possibly related posts: (automatically generated)

• Different flavors of Explain Plan – Part I
• Uncommitted transactions
• Show currently executing SQL
• Unexpected Side Effect of Table Shrinking