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

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