RE: Buffer use under Oracle

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

Consistent Reads: See the Concept Manual discussion of read consistency, that is, rollback segment use.

Every time the table is changed the index is also probably updated. If the indexed columns are changed then two updates to the index have to take place: the existing entry is deleted, and a new entry is created. These changes have to be recorded in rollback, and the RBS blocks are in the buffer.

HTH -- Mark D Powell --

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

:>> At the present time, the heavily inserted/deleted table has 10 rows, but
:>> according to V$BH the index is using 6000 buffers.
 

:>> V$BUFFER_POOL_STATISTICS indicates that there are many searches for a
free
:>> buffer going on.
 

:>> None of the rows in V$BH have a non-zero value in forced_reads or
:>> forced_writes.
 

:>> The vast majority have a V$BH status of XCUR.
 

:>> Why isn't Oracle reusing these buffers for other processing?
 

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


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