Hello,
Database is version 11.2.0.3
I have a table, statistics are gathered on it, with average row length - 38 bytes. The rows in the table are 485,478.
I created second table from the first one and I created it empty.
I wanted to test how much undo blocks are used when rows are inserted from the original table to the new table using conventional insert.
I started new session and I selected for it from v$sesstat table:
SELECT *
FROM v$sesstat s
JOIN v$statname n
ON n.statistic# = s.statistic#
WHERE sid = &sid
AND LOWER(n.name) = 'undo change vector size';
At the beginning - before the insert is run - it show 0 rows for my session id - the session that will run the insert.
Then I ran it. Before committing the transaction I selected from v$transaction for the same SID:
SELECT t.used_ublk, t.*
FROM v$transaction t
WHERE t.addr = (SELECT taddr FROM v$session WHERE sid = &sid);
It showed 798 undo blocks that is 798 * 8192 (the block size of the db is 8192 bytes) = 6537216 bytes = 6.234375 Mb.
Then I selected from v$sesstat for the same session (the first query above). It showed in column VALUE: 22382668.
I'm not quite sure how to interpret this value. Does this column show for statistic 'undo change vector size' bytes or something else?
If it is bytes, then 22382668 / 1024 / 1024 is approx. 21.346 Mb. This is much bigger than the value from v$transaction - 6.234375 Mb. Can anyone explain why and what else eventually the VALUE in v$sesstat might represent in addition to the used undo blocks as it is quite bigger?
Thank you.