RE: Excessive transaction tables consistent reads - undo records applied in 11.2.0.3

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Sat, 6 Oct 2012 14:38:52 -0400
Message-ID: <00cd01cda3f1$d6438f10$82caad30$_at_rsiz.com>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tanel Poder
Sent: Thursday, October 04, 2012 10:55 PM To: jonathan_at_jlcomp.demon.co.uk
Cc: oracle-l_at_freelists.org
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_at_jlcomp.demon.co.uk> 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


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Sat Oct 06 2012 - 20:38:52 CEST