Re: different physical access method because of disabling Automated Memory Management?
Date: Sat, 3 Jan 2009 09:35:32 +0100
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?
-- Martin Berger http://berxblog.blogspot.comReceived on Sat Jan 03 2009 - 02:35:32 CST
> 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: 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).
- application/pkcs7-signature attachment: smime.p7s