RE: different physical access method because of disabling Automated Me
Date: Mon, 5 Jan 2009 13:15:59 -0000
Btw, There's also event 10365 for tracing adaptive direct read decisions, but I haven't tested it, so don't know if it's helpful.
10365, 00000, "turn on debug information for adaptive direct reads"
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Martin Berger
Sent: 05 January 2009 11:24
Subject: Re: different physical access method because of disabling Automated Me
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,
On Sun, Jan 4, 2009 at 18:29, Yong Huang <yong321_at_yahoo.com> 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
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.
> 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:
> _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?
> best regards,
> Martin Berger
> > 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
> > ] 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).