Re: transaction tables consistent reads - undo records applied
Date: Fri, 25 Jun 2010 13:31:45 -0700 (PDT)
Message-ID: <29c017c9-7c3a-40db-b422-1b1f2d861431_at_i9g2000prn.googlegroups.com>
On Jun 25, 9:37 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> This happens when your query hits a block that was updated
> "a long time" in the past by a transaction that has committed
> but not been cleaned out.
>
> Your query can tell that it is a committed transaction because
> the ITL entry for the transaction points to transaction table slot
> (in an undo segment header block) that has been re-used for
> a newer transaction. (Part of the transaction id is the "transaction
> sequence number", which is counting the number of times a transaction
> slot has been used).
>
> Your query therefore needs to know WHEN the transaction committed,
> so that it can decide whether or not it's supposed to see the new version
> or the old version of the row. (If the transaction committed before the
> query then the query doesn't need to know exactly when the transaction
> committed, if it started after the query then it has to be rolled back -
> and it's possible that the "snapshot too old" is the result of the data
> rollback
> than the transaction table rollback.)
>
> To find out when the transaction committed, your query copies the undo
> segment header block and starts rolling it back. The number of times this
> happens is recorded as:
> "transaction tables consistent read rollbacks"
>
> To perform the rollback, your query will read the transaction control block
> (another part of the undo segment header) which contains a number of
> important
> details - including the first undo block address of the most recent
> transaction
> to use that undo segment header. This undo block address will hold the
> first
> record of that transaction *** - which include information about the
> PREVIOUS
> state of the transaction control block. By using this undo record your
> query
> can take the undo segment header block backwards in time by one step -
> at which point it reads the older version of the transaction control block
> and
> repeats the process until it reaches the point where the transaction slot
> it's
> interested in has been taken back to the correct sequence number (or a
> change
> has taken the undo segment header block back to a point in time before the
> start of the query). Each record it reads in this process is counted in
> the
> "transaction tables consistent reads - undo records applied"
>
> (*** This is why the block you dumped had nothing to do with your table.)
>
> The trouble with your requirement is that we really need to do a backwards
> tablescan - because it's probably the data near the end of the table that
> is
> changing while you are "wasting" time reading all the data from the start
> of
> the table.
Excellent explanation, but I lost you here. He says plan says doing a range scan, for 1% of the table? (Maybe you hadn't seen subsequent post yet, where he mentions a fetch suddenly exhibiting the characteristics you describe.)
>
> Unfortunately there is no such hint - but if it's really critical, you
> could write
> some code to scan the table one extent at a time in reverse order.
This cleaning makes perfect sense, but I'm wondering if there is some administrative tuning like adjusting undo size or retention or some fiddling with initrans? Sounds critical if it's interrupting data extraction. I'm wondering if the mysterious translation of xmltype from a column might be a problem here. Steve, how exactly are you inserting and accessing this column?
jg
-- _at_home.com is bogus. snake oil 2.0 http://www.gapingvoidgallery.com/product_info.php?products_id=1614Received on Fri Jun 25 2010 - 15:31:45 CDT