Re: different physical access method because of disabling Automated Memory Management?
- From: Martin Berger <martin.a.berger@xxxxxxxxx>
- To: Tanel Poder <tanel@xxxxxxxxxx>
- Date: Sat, 3 Jan 2009 09:35:32 +0100
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:
manual:
=======
_small_table_threshold => 59
auto:
====
_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
--
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx
] 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).
Other related posts:
