| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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.
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...
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
> -----------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed May 12 2004 - 07:22:05 CDT
 |
 |