Re: transaction tables consistent reads - undo records applied

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 26 Jun 2010 07:05:31 +0100
Message-ID: <kdKdna1BHZ40BbjRnZ2dnUVZ7rKdnZ2d_at_bt.com>

"joel garry" <joel-garry_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 Lewis
http://jonathanlewis.wordpress.com

Received on Sat Jun 26 2010 - 01:05:31 CDT