RE: Buffer use under Oracle

Binyamin

   If you are striving to learn how to tune Oracle, make the right start and purchase the book that embodies the best thinking - Optimizing Oracle Performance by Cary Millsap.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Binyamin Dissen Sent: Wednesday, May 12, 2004 2:47 PM
To: oracle-l_at_freelists.org
Subject: Re: Buffer use under Oracle

On Wed, 12 May 2004 13:39:17 -0500 "Cary Millsap" <cary.millsap_at_hotsos.com> wrote:

:>I didn't get the sense from the original note that there was a 'free
buffer
:>waits' problem, so I'm assuming that there's not really demand for new
:>buffers. I thought the original post was an expression of curiosity about
:>how there could be so many index blocks in the buffer cache at the same
:>time. (Perhaps I didn't read carefully enough, and it's of course
difficult
:>to tell at this point what the original post said because the original
post
:>isn't present in this thread anymore.)

Let me qualify by stating that I have DB2 experience, not Oracle experience. I
have worked many years as a computer trouble shooter.

A client, despite knowing my lack of experience with Oracle, has asked me to look at a performance problem in one of their systems (counting on my trouble
shooting experience).

I have been spending time reading the performance manuals.

Among the thing that I have found is that, on occasion, one must steer Oracle
to choose the best index. But I digress.

I am noticing that v$buffer_pool_statistics buffer examined numbers are jumping pretty quickly, while the index is using a huge amount of the buffers.
There are other indices which would be more useful in buffers.

Stopping the processes that play with that table allows other data to be placed and retained in the buffers. It also seems to improve response. It would seem fruitful to figure out how to cause that index to use no more than
X buffers.

At any rate, I have tried to associate the buffers with processes to try to get a line on things but have had no luck. Do not even know if it is possible.

I tried:

SELECT vl.sid, vs.osuser, vs.username, vs.machine, vs.process FROM DBA_OBJECTS o, V$BH bh, V$LOCK vl, V$SESSION vs WHERE o.object_id = bh.objd
AND o.object_name = '&indexname'
AND bh.lock_element_addr = vl.addr
AND vl.sid = vs.sid
order by vl.sid

No rows returned.

I am not sure how to relate V$LOCK to V$BH

I also tried:

SELECT vl.sid, vs.osuser, vs.username, vs.machine, vs.process, o.object_name FROM V$LOCK vl, V$SESSION vs, DBA_OBJECTS o WHERE vl.sid = vs.sid
AND (o.object_id = vl.id1 or o.object_id = vl.id2) order by vl.sid

which ran for hours before I cancelled it.

:>I'm curious though, if a cloned buffer is accessed frequently by running
:>queries, are you saying that the probability is high that it will get aged
:>out prematurely, and therefore force re-reconstruction the next time that
:>particular incarnation of the buffer is required?
:>
:>
:>Cary Millsap
:>Hotsos Enterprises, Ltd.
:>http://www.hotsos.com
:>* Nullius in verba *
:>
:>Upcoming events:
:>- Performance Diagnosis 101: 5/18 Edison NJ, 6/22 Pittsburgh, 7/20 Boston
:>- SQL Optimization 101: 5/24 San Diego, 6/14 Chicago, 6/28 Denver
:>- Hotsos Symposium 2005: March 6-10 Dallas
:>- Visit www.hotsos.com for schedule details...
:>
:>
:>-----Original Message-----
:>From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
:>On Behalf Of K Gopalakrishnan
:>Sent: Wednesday, May 12, 2004 10:47 AM
:>To: oracle-l_at_freelists.org
:>Subject: Re: Buffer use under Oracle
:>
:>Cary:
:>
:>I don't think this is right unless I am reading between lines ;).
:>Cloned buffers are always kept in the cold end in the new alogorithm
:>and they are ready to be flushed when ever there is a demand for
:>new buffers. The CR buffers are kept in the FROZEN end (note the
:>word freeze in the parameter, it is not cold. Freeze)
:>
:>Of course the behavior can be controlled by _db_aging_freeze_cr
:>parameter.
:>
:>KG
:>
:>
:>> >Why aren't the old ones freed when a new one is created?
:>>
:>> ...because of Oracle's LRU buffer cache management algorithm.
:>> Currently-executing queries may "like" having a slightly older CR copy =
:>> of a
:>> newly modified block in the buffer cache. Such a block will tend to =
:>> remain
:>> cached as long as some session keeps using it. Those blocks are in your
:>> buffer cache because your application needs them.
:>>
:>
:>----------------------------------------------------------------
:>Please see the official ORACLE-L FAQ: http://www.orafaq.com
:>----------------------------------------------------------------
:>To unsubscribe send email to: oracle-l-request_at_freelists.org
:>put 'unsubscribe' in the subject line.
:>--
:>Archives are at http://www.freelists.org/archives/oracle-l/
:>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
:>-----------------------------------------------------------------
:>
:>----------------------------------------------------------------
:>Please see the official ORACLE-L FAQ: http://www.orafaq.com
:>----------------------------------------------------------------
:>To unsubscribe send email to: oracle-l-request_at_freelists.org
:>put 'unsubscribe' in the subject line.

--
Binyamin Dissen <bdissen_at_dissensoftware.com>
http://www.dissensoftware.com

Director, Dissen Software, Bar & Grill - Israel
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------