RE: Buffer use under Oracle
- From: DENNIS WILLIAMS <DWILLIAMS@xxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 May 2004 15:17:33 -0500
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@xxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Binyamin Dissen
Sent: Wednesday, May 12, 2004 2:47 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Buffer use under Oracle
On Wed, 12 May 2004 13:39:17 -0500 "Cary Millsap" <cary.millsap@xxxxxxxxxx>
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
:>On Behalf Of K Gopalakrishnan
:>Sent: Wednesday, May 12, 2004 10:47 AM
:>To: oracle-l@xxxxxxxxxxxxx
:>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@xxxxxxxxxxxxx
:>put 'unsubscribe' in the subject line.
:>--
:>Archives are at //www.freelists.org/archives/oracle-l/
:>FAQ is at //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@xxxxxxxxxxxxx
:>put 'unsubscribe' in the subject line.
--
Binyamin Dissen <bdissen@xxxxxxxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
