Re: UNTO TBS behavior in 9i
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: veeeraman@xxxxxxxxx
- Date: Sat, 27 Jan 2007 14:53:32 +0800
Answers Indented.
At 06:32 AM Saturday, Ram Raman wrote:
This has evolved into an interesting discussion. I have more questions:
"If you are doing multiple commits during the insert run, each
committed batch remains in UNDO for the duration of 2 hours. You
should reduce UNDO_RETENTION."
If that is the case, when the current INSERT process needs space it
should be able to grab the existing space in UNDO TBS. It should
overwrite it even though it is within the UNDO_RETENTION period
because oracle has run out of space in UNDO TBS. Correct?
===> Normally, undo is retained and Oracle will attempt to
get an expired extent for a new transaction / growing transaction
and if it cannot find an available extent, it will
auto-extend the datafile if possible. If it _cannot_ get an extent
and cannot extend the undo datafile, then, yes, it will begin
to overwrite extents that were used by committed transactions. That
is the situation Oracle does not
actually guarantee that _all_ transactions will be retained
for the specified UNDO_RETENTION period. [and a third session may
get an ORA-1555 because the second session has overwrriten
committed undo of a first session].
In your case , if the datafile could not autoextend, then
the session itself would have been allowed to overwrite it's previous undo
--- because every fresh transaction is independent of each
other [ie undo_retention is for transactions, not sessions] {and a case
where you could have a single sesssion executing DML in the
database and yet reporting ORA-1555s}.
"You will RECONSTRUCT a consistent version of The block by applying
all "undo vectors" to a current or a consistent version of the block."
If I understand this correctly, this means when an older un-updated
version of the block is needed oracle gets the current updated
version from memory or disk. Then it applies the undo vectors from
the UNDO tbs. If it was a case of DELETE transaction which deleted
say 6 rows out of 10 in a block, to produce a consistent version
oracle brings the current version of the block which has 4 rows and
then applies the change vectors from the UNDO tbs to 'INSERT' the
deleted 6 rows to produce a consistent version? Does this mean Oracle
stores the 6 deleted rows in UNDO tbs? Or it just stores the ROWIDs
in UNDO and selects the old values from the 'deleted spots' in the
data block which are still not overwritten?
===> Undo for a DELETE is actually the whole row that was
deleted. {in order to Rollback a DELETE, Oracle has to perform an INSERT
=> of the whole row, with all column values}
Another clarification: You have mentioned the vectors are applied to
a current or consistent version of the block. I can understand the
current version, but why take a consistent version and then apply
changes again?
===> Because the same block may have been updated at
different times by different sessions [transactions, actually] --
each one having
=> updated a different row. Therefore, you would have
multiple versions of the same block -- ergo, Oracle must get a
=> _consistent_ version to be able to read the required
rows as of the required SCN. Since undo is actually change vectors
=> [ie corresponding insert/update/delete statements], it
can reapply the changes to the required row {of course, if multiple
=> transactios have committed different record updates, in
order to actually rollback the whole block to disk, Oracle has to check
=> committed and uncommitted rows ! -- but in the case of a
query that wants to read a consistent version of a row, Oracle
=> doesn't have to reapply all changes to all rows in the
block, only to the rows required by that query}.
Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantoracledba.blogspot.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
