Oracle Undo size: 10g 10.2.0.3 vs 9i 9.2.0.6
Posted by Arul Ramachandran on December 19, 2007
Does 10g R2 generate more undo than 9i R2?
While trying to inspect the undo details for an UPDATE statement using v$transaction in my previous post (this was tested in Oracle 10.2.0.3) , I found something strange. While the UPDATE statement updated only 100,000 rows, the column USED_UREC from v$transaction displayed 200,000 rows. I was curious to see the behavior in 9i.
Here is what I saw from v$transaction in 9i:
SQL_TEXT RECORDS BLOCKS KBYTES
---------------------------------------- ---------- ---------- ----------
update t1 set name = rpad('y', 30, 'y' ) 100000 1516 12128
RECORDS field is the alias for column USED_UREC and it shows only 100,000 records. BLOCKS field is the alias for column USED_UBLK shows 1516 blocks, which is about half of what [3078 blocks] I saw in 10G (see previous post). Does this mean 10GR2 generates twice as much undo?
As a next step, I did a tkprof for both tests.
On 9.2.0.6, tkprof shows the following:
update t1 set name = rpad('y', 30, 'y' )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.23 4.84 0 675 103151 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ------- ---------- ---------- ----------
total 2 1.23 4.84 0 675 103151 100000
There is nothing unusual here.
On 10.2.0.3, tkprof shows the following:
update t1 set name = rpad('a', 30, 'a' )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 2.07 2.72 0 724 205112 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ------- ---------- ---------- ----------
total 2 2.07 2.72 0 724 205112 100000
Here the value of current mode gets is twice that of 9.2.0.6. Why so?
For both cases 9i and 10g, table T1 did not have any indexes.
Digging further to check if the recursive sql’s had any effect, from the tkprof and the trace, notice another difference:
On 9.2.0.6
select file#
from
file$ where ts#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ------- ---------- ---------- ----------
Parse 12 0.00 0.00 0 0 0 0
Execute 12 0.00 0.00 0 0 0 0
Fetch 36 0.00 0.00 0 48 0 24
------- ------ -------- ---------- ------- ---------- ---------- ----------
total 60 0.00 0.00 0 48 0 24
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS BY INDEX ROWID FILE$
2 INDEX RANGE SCAN I_FILE2 (object id 42)
On 10.2.0.3
select file#
from
file$ where ts#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ------- ---------- ---------- ----------
Parse 23 0.00 0.00 0 0 0 0
Execute 23 0.00 0.00 0 0 0 0
Fetch 46 0.00 0.00 0 92 0 23
------- ------ -------- ---------- ------- ---------- ---------- ----------
total 92 0.00 0.00 0 92 0 23
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL FILE$ (cr=4 pr=0 pw=0 time=44 us)
Note the number of executions in 10G has doubled for this sql and it does a table scan on FILE$.
Does the change in the access path of this recursive SQL lend to the increase in the current mode gets? and does that impact the undo size? at this point, I am really puzzled 
Jonathan Lewis said
This is a surprising result as 10g has an optimisation for updates that reduces the number of undo records needed. If you do several updates to a block at the same time (e.g. updating several rows in the block without leaving the block to update the indexes) then all the changes for the block can be recorded in a single undo record.
Since your rows are about 40 bytes, you should have about 200 per 8Kb block, which means about 5,000 blocks in the table – so I would expect to see about 5,000 records in the 10.2.0.3 test. (And this is what I got when I repeated your test – although I got 200,000 when I recreated the table as an index organized table with both columns in the primary key).
Have you installed your database with any special adjustments to the spfile ?
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk
Arul Ramachandran said
Hi Jonathan,
Thanks for the response.
It’s been a while and I am at a different work site now, I do not have access to the specific details related to this. IIRC, there were no special adjustments to the spfile, I recall the database was set up for Automatic Undo, the tablespace for the table was in LMT (possibly with ASSM).
The only thing I am not sure is, I might have set up the database for supplemental logging as I was testing some stuff related to Oracle Streams.
Regards,
Arul
Carl Bruneau said
Hi Arul,
On my side I tested how much UNDO is generated for an UPDATE statement (updating a single integer column).
Running these queries:
SELECT to_char(sysdate, ‘HH24:MI:SS’), b.used_urec, b.used_ublk
2 FROM v$session a inner join v$transaction b ON a.saddr = b.ses_addr
3* WHERE a.sid = 1081
Give us back:
41867461 undo records used and 529654 undo block used.
AND:
SELECT to_char(sysdate, ‘HH24:MI:SS’), a.sid, b.name, a.value
2 FROM v$sesstat a inner join v$statname b ON a.statistic# = b.statistic#
3 WHERE a.statistic# = 208 AND
4 a.sid = 1081;
Give us back:
4216598256 for the value of “undo change vector size”.
How a table of 2.2GB that uses 292224 blocks can require about 4GB of undo space to be updated (and 529654 undo block)?
We have tested on 10G R2 and on 11G with both using automatic UNDO management.
Best regards.
Carl