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

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

You where right, there was a difference:

manual:

auto:

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

--
Martin Berger
http://berxblog.blogspot.com



>

> 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

> http://blog.tanelpoder.com

>

>

> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org 

> ] 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).

>

>







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




application/pkcs7-signature attachment: smime.p7s