Re: different physical access method because of disabling Automated Me

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Mon, 5 Jan 2009 12:23:41 +0100
Message-ID: <cd8f74560901050323h20a4fa4dof4af6521a651139d_at_mail.gmail.com>



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_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
> 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_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).
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 05 2009 - 05:23:41 CST

Original text of this message