Re: v$sqlarea statistics

From: Deepak Thapliyal <deepakthapliyal_at_yahoo.com>
Date: Tue, 23 Oct 2001 14:00:56 -0700
Message-ID: <F001.003B27AB.20011023141025@fatcity.com>

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).

Received on Tue Oct 23 2001 - 16:00:56 CDT