Re: different physical access method because of disabling Automated Memory Management?

From: Martin Berger <>
Date: Sat, 3 Jan 2009 09:35:32 +0100
Message-Id: <>

Hi Tanel,

I checked _small_table_treshold (for some reasons it was not mentioned in the 10053 trace.)

You where right, there was a difference:


_small_table_threshold => 59


_small_table_threshold => 498

So I adjusted my 'manual'-testcase, run it again - but still 'direct path read'.

Does anyone knows which subsystem takes the decision which kind of IO, and how to trace this?

best regards,

Martin Berger

> Since 11g, Oracle can automatically choose to do a serial direct
> path read for full segment scans.
> It looks like one condition for serial direct read is that the
> segment scanned is larger than 5 * _small_table_threshold, but there
> must be other variables involved as well as my tests with changing
> _small_table_threshold were not always consistent.
> I suspect this feature is gonna cause some trouble in the future...
> Serial direct path reads (with asynch IO prefetching) do make sense
> in a DW / reporting environment, but there are plenty of OLTP
> environments which do lots of full table scanning (yeah, often due
> inappropriate design, but that doesn't change the problem)
> --
> Tanel Poder
> From: [
> ] On Behalf Of Martin Berger
> Sent: 02 January 2009 11:16
> To: oracle-l
> Subject: different physical access method because of disabling
> Automated Memory Management?
> Hi List,
> once again I'm coming up with a behaviour of Oracle rdbms which I
> can not explain to myselve. So I'm asking here for help.
> If you are not interrested in theoretical discussions about how
> oracle heuristics might work, please excuse this email and stop here.
> All others are warmly welcome to read and reply ;-)
> My monitoring:
> =============
> the same statement (select rowid from test.t_keep) is executed
> different only because memory_target, sga_target and
> shared_pool_size are different. (In addition, db_cache_size and
> db_keep_cache_size might be of any interrest).
> With automatic memory management the DB uses 'db file sequential
> read' and fills up the (keep) buffer cache, with manual memory
> parameters it uses 'direct path read' (only 4 'db file sequential
> read' at the beginning).

  • application/pkcs7-signature attachment: smime.p7s
Received on Sat Jan 03 2009 - 02:35:32 CST

Original text of this message