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 
and I am not aware of another way to do this other than query the x$ tables.