RE: Excessive transaction tables consistent reads - undo records applied in 11.2.0.3
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <tanel@xxxxxxxxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Sat, 6 Oct 2012 14:38:52 -0400
Interesting. I'll need to check whether fts is subject to adaptive direct
read with that event on, with and without rownum limits.
Since we don't really care about the correctness (hmm, but we do care about
support) for driving block cleanouts, this may be a decent solution if
adaptive direct read can be made to not kick in. The same check that
adaptive direct read does not kick in is required for my index correlated to
recentness of update points at a row in the block query (though I think it
unlikely it uses adaptive direct read on a list of rowids, it certainly
might if it gets a relatively dense list with respect to block ids.)
I guess the list of rowids think is definitely supported, so if you have a
relevant index by which to fish for most of the recent blocks efficiently.
Likewise, if the recently modified blocks are scattered with respect to the
physical order of the table, the reverse read is less useful (though still
likely better than a forward read, since is likely there is a correlation of
quiescence and age, even if not absolute.)
Thanks for pointing out this event, Tanel. I suspect it will prove useful in
many cases, especially if it is a session capable setting (I haven't checked
yet).
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Tanel Poder
Sent: Thursday, October 04, 2012 10:55 PM
To: jonathan@xxxxxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Excessive transaction tables consistent reads - undo records
applied in 11.2.0.3
On Thu, Oct 4, 2012 at 8:37 AM, Administrator User <
jonathan@xxxxxxxxxxxxxxxxxx> wrote:
>
> Part of the problem, of course, is that Oracle starts a tablescan from
> the beginning while typical batch processing is all about changing the
> most recent data (i.e. the stuff at the end of the table). So the time
> you spend reading the first 350 MB may be letter the other session
> wreck the last 50MB. (This is a topic close to Mark Farnham's heart -
> why doesn't Oracle give us a "tablescan descending" hint.)#
>
>
If you don't mind occasionally getting wrong results (as this is
undocumented, unused and unsupported feature), you can use event 10460
"Perform backward tablescans for consistent read reduction" for backwards
full table scans ;-)
--
Tanel Poder
Blog - http://blog.tanelpoder.com
App - http://voic.ee
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
