Binay,
from what i understand, these type of block reads
relate to read consistancy .. meaning that if oracle
wants to read block x but it finds that it is dirty ,
it reads from the rollback segments to give you the
point in time snapshot as it existed at the time when
you had first started the query.
e.g. u fire query at 12.00 and if at 12.05, someone
changes a block and commits. Now at 12.10 your query
is still executing and it requests the above block,
oracle will attempt to read it from the rbs , therby
resulting in a consistent read.
a current mode read on the other is a direct read off
of the buffer cache.
correct me if i am wrong here guys?
Thx
Deepak
- BINAY.KUMAR_at_ponl.com wrote:
> Hi Deepak,
> Can you elaborate what is consistent gets
>
>
>
> -----
> Eric,
>
> here's a high level from my understanding on this
> issue:
>
> Buffer gets {also called Logical IO's}
> These happen as oracle scans blocks of data in the
> buffercache(in-mem scans). Many people believe that
> since these are memory reads, they are inexpensive.
> I
> have seen the contrary in many cases and have seen
> that these are the ones which take the most cpu
> clycles, therby making your system CPU Bound.
> Resolution of this is to tune your sql by having it
> use better access paths (indexes). Also consider
> de-norming in ordr to avoid too many joins
> I consider this the most important metrics in
> identifying "bad" SQL. i have seen cases where
> frequently executed queries were performing millions
> of LIO's and hosing up the CPU. A simple index / or
> Adding hint can reduce this number by a very high
> factor resulting in great gains.
>
> DiskReads {also called physical IO's)
> This obviously means that there are a lot of disk
> reads required to satisfy your query. Reasons: maybe
> you are using ineffcient access paths/bad sql or u
> just have insuffient (small) memory to support your
> app. High Diskreads is the reason that makes your
> system IO bound. Resolution is again the same as
> described above. In addition, one of the assumtions
> here is that you have spread your datafiles/logs/cf
> optimally. Also consider using the recycle buffer
> pool
> feature to avoid an innocent FTS from flushing
> everything from your cache. Obviously you cannot
> always prevent any of these and some disk read are
> inevitable.
>
> hth
> Deepak:
>
> --- Erik Williams <ewilliams_at_brownco.com> wrote:
> >
> > I am trying to identify the most harmful
> statements
> > in an application. From
> > the Oracle Performance and Tuning Tips and
> > Techniques book, I found two
> > statements. Both are looking at the statements
> > contained in the v$sqlarea.
> > The first looks at statements with a high number
> of
> > buffer gets and the
> > other looks at the statements with a high number
> of
> > disk reads. Some of the
> > statements appear in both lists, but some in only
> > one. If all of the disk
> > reads are moving blocks into the buffer cache,
> what
> > is the difference
> > between the two measures? Can anyone explain the
> > difference between the two
> > measures?
> >
> > Thanks.
> > Erik
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Erik Williams
> > INET: ewilliams_at_brownco.com
> >
> > Fat City Network Services -- (858) 538-5051
> FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be
> removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
> __________________________________________________
> Do You Yahoo!?
> Make a great connection at Yahoo! Personals.
> http://personals.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Deepak Thapliyal
> INET: deepakthapliyal_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
>
>
>
>
>
> The contents of this e-mail are confidential to the
> ordinary user
> of the e-mail address to which it was addressed and
> may also be
> privileged. If you are not the addressee of this
> e-mail you should
> not copy, forward, disclose or otherwise use it or
> any part of it
> in any form whatsoever. If you have received this
> e-mail in error
> please notify us by telephone or e-mail the sender
> by replying to
> this message, and then delete this e-mail and other
> copies of it
> from your computer system. Thank you.
>
> We reserve the right to monitor all e-mail
> communications through
> our network.
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author:
> INET: BINAY.KUMAR_at_ponl.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deepak Thapliyal
INET: deepakthapliyal_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Oct 24 2001 - 13:26:19 CDT
