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

  • From: "Tanel Poder" <tanel@xxxxxxxxxx>
  • To: <martin.a.berger@xxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 2 Jan 2009 13:09:26 -0000

Hi Martin,
 
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: