Re: transaction tables consistent reads - undo records applied
From: Steve Howard <stevedhoward_at_gmail.com>
Date: Sat, 26 Jun 2010 06:10:30 -0700 (PDT)
Message-ID: <358e8dc4-9e65-4c4e-9101-8871d8ae5730_at_y4g2000yqy.googlegroups.com>
On Jun 26, 2:05 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "joel garry" <joel-ga..._at_home.com> wrote in message
>
> news:29c017c9-7c3a-40db-b422-1b1f2d861431_at_i9g2000prn.googlegroups.com...
> ]On Jun 25, 9:37 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> ]wrote:
> ]>
> ]> 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.)
> ]
>
> By the time I'd written this much, I'd forgotten that he'd added the note
> about the index - but it doesn't really make any difference (a) to the
> explanation or (b) to the concept in the solution - except that you
> can put in an "index_desc()" hint and that might be enough to help.
> It depends on the how the query is written, what index it uses, and
> the distribution of the changed data.
>
> ]>
> ]> 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.
>
> The error is "just" the same as a traditional 1555 problem when it gets
> that far so a "large enough" undo retention should stop the 1555 - but
> that won't stop the amount of work it takes. Thinking about initrans is
> a good idea - but that won't have any effect either because the problem
> is the number of backward steps that have to be taken and the value of
> initrans only eliminates the first few (i.e. a few relating to the size of
> INITRANS).
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
Date: Sat, 26 Jun 2010 06:10:30 -0700 (PDT)
Message-ID: <358e8dc4-9e65-4c4e-9101-8871d8ae5730_at_y4g2000yqy.googlegroups.com>
On Jun 26, 2:05 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "joel garry" <joel-ga..._at_home.com> wrote in message
>
> news:29c017c9-7c3a-40db-b422-1b1f2d861431_at_i9g2000prn.googlegroups.com...
> ]On Jun 25, 9:37 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> ]wrote:
> ]>
> ]> 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.)
> ]
>
> By the time I'd written this much, I'd forgotten that he'd added the note
> about the index - but it doesn't really make any difference (a) to the
> explanation or (b) to the concept in the solution - except that you
> can put in an "index_desc()" hint and that might be enough to help.
> It depends on the how the query is written, what index it uses, and
> the distribution of the changed data.
>
> ]>
> ]> 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.
>
> The error is "just" the same as a traditional 1555 problem when it gets
> that far so a "large enough" undo retention should stop the 1555 - but
> that won't stop the amount of work it takes. Thinking about initrans is
> a good idea - but that won't have any effect either because the problem
> is the number of backward steps that have to be taken and the value of
> initrans only eliminates the first few (i.e. a few relating to the size of
> INITRANS).
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
Thanks much , Jonathan (and Joel).
I found a comment by you several months ago similar to this that I always go back to. I can't tell you how many times I would go for a cup of coffee with someone and mention that link and the fact that I would like to send you a note for clarification, but just never did.
http://www.orafaq.com/usenet/comp.databases.oracle.server/2008/02/01/0022.htm
I really appreciate you taking the time to respond, as this has been driving me nutty.
Let me take some time to digest what you posted and then I will respond.
Thanks,
Steve Received on Sat Jun 26 2010 - 08:10:30 CDT