Hi Yuong, thank you for the informations, I created a tracefile using alter *SESSION SET EVENTS '10357 trace name context forever, level 1';* and run the testcase. (the tracefile is available at http://berx.at/traces/BERX2_ora_1087.trc) Unfortunately this trace only shows *what *kind of IO is done (and how), but not *why*. Such as 10046 shows only what kind of waits are generated, but 10053 shows why a dedicated execution plan is choosen). thanks for all your effort, Martin On Sun, Jan 4, 2009 at 18:29, Yong Huang <yong321@xxxxxxxxx> wrote: > I checked the parameter difference using a crude method: > --spool query against x$kspp% to a.lst > alter system set memory_target = 0 scope = spfile; > alter system set sga_target = 0 scope = spfile; > alter system set shared_pool_size = 536870912 scope = spfile; > startup force > ---spool query against x$kspp% to c.lst > !diff a.lst c.lst | egrep -v '^---|^[0-9]' > > There're 29 parameters in the diff output. I think most are db > cache size related. I was expecting _serial_direct_read to change > from false to true, or a change of _adaptive_direct_read, but > they're not there. Compared to Martin's database, my > statistics_level is left at typical and Oracle version is > slightly older, but I doubt they make any difference. > > My result is at > http://yong321.freeshell.org/oranotes/tmp2.txt > > To trace direct path I/O, you can set event 10357 at level 1. K Gopal's > "Oracle Wait Interface" has an example on p.127. > > Yong Huang > > > 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). > > > > -- > //www.freelists.org/webpage/oracle-l > > >