RE: Buffer use under Oracle

>:>These blocks are being modified frequently, so new buffers are needed =
to
>:>hold the new versions of the same blocks.

>It is not the data blocks, it is the index blocks.

Right. An index block is cached and subjected to the same rules of = buffer
cache management as data blocks. Every time you update a table with an index, Oracle deletes a row from the appropriate index block(s) and = inserts
a new row(s) into some other index block(s). These delete/insert = operations
on the index generate undo just like changes on a data block.

Now, here's the fun part: When a query comes along later and wants to = access
an index block that has been updated since the query began, the querying session will perform the CR mode block reconstruction on the index (and = of
course the data as well). The CR mode reconstruction of any block will require a clone to be made of the block (Oracle applies undo to the = cloned
copy). This will cause multiple copies of a given block (index or = data--it
doesn't matter) to exist in the buffer cache at the same time. When you consider that a single updated row will often cause manipulation of two = or
more index blocks for every index on a table, it's no wonder why there = are
so many different index blocks in the buffer cache.

<example>For example, if COL is indexed, and you update COL from 'a' to = 'c',
you move its physical location in the index, so you have to delete from = the
index block storing 'a', and you have to insert into the index blocks storing values like 'c'. This accounts for at least two modified index blocks in the buffer cache. It's possible that inserting 'c' will cause = some
branch block manipulation, too, which would account for even more = modified
index blocks in your buffer cache.</example>

The cloned blocks are there so that you can run updates and queries in = the
same instance. <facetious-humor>If you want to reduce the number of = cloned
blocks in your buffer cache, one way to do it is to disallow the use of queries while you're updating. ...And since updates usually /contain/ queries, I guess you'd have to serialize all your updates (that is, = allow
only one user on your system at a time).</facetious-humor>

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

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 Binyamin Dissen
Sent: Wednesday, May 12, 2004 8:21 AM
To: oracle-l_at_freelists.org
Subject: Re: Buffer use under Oracle

On Wed, 12 May 2004 06:25:54 -0600 Tim Gorman <tim_at_sagelogix.com> wrote:

:>These blocks are being modified frequently, so new buffers are needed = to
:>hold the new versions of the same blocks.

It is not the data blocks, it is the index blocks.

Why aren't the old ones freed when a new one is created?

:>You want to be looking at the application code (or V$SQLAREA, = V$ACCESS,
:>V$OPEN_CURSOR) to understand what the application is doing with this = table
:>and this index. Validate that the index is useful by checking :>AVG_DATA_BLKS_PER_KEY and AVG_LEAF_BLKS_PER_KEY in DBA_INDEXES (high values
:>can be problematic, values close to "1" usually good), but only :>understanding how the application uses the index will determine = whether
:>those averaged values are meaningful or not. It could be that the = index
is
:>more of a hindrance than useful...

The indices seem appropriate.

They are using rule based optimization and have never done an analyze, = so
DBA_INDEXES is not populated.

At any rate, the index is {department, timestamp} and the table is a "holding"
table so every row is deleted a short time after being added.

The inserts, except for the "highest" department, will always be in the middle
or top.

How can there be many times more buffers used than data rows?

:>on 5/12/04 6:11 AM, Binyamin Dissen at bdissen_at_dissensoftware.com = wrote:

:>> I am trying to investigate why a large number of buffers are being = used
by a
:>> certain index.
=20
:>> At the present time, the heavily inserted/deleted table has 10 rows, = but
:>> according to V$BH the index is using 6000 buffers. =20
:>> V$BUFFER_POOL_STATISTICS indicates that there are many searches for = a
free
:>> buffer going on.
=20
:>> None of the rows in V$BH have a non-zero value in forced_reads or :>> forced_writes.
=20
:>> The vast majority have a V$BH status of XCUR. =20
:>> Why isn't Oracle reusing these buffers for other processing? =20
:>> How can ten index records use that much buffer space?

:>> What should I be looking at?

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