v$buffer_pool_statistics: How to interpret data?

From: Alex Schonlinner <schonlinner_at_yahoo.com>
Date: 1 Aug 2006 05:33:17 -0700
Message-ID: <1154435597.808191.68020@m79g2000cwm.googlegroups.com>

Hi,

I'm observing something which I cannot explain (Oracle 10.2.0.2 on 8-CPU HP/UX): I thought that v$buffer_pool_statistics contains upto date information about how many physical reads have been done since instance startup and how many reads in total have been done.

Now I select from the view and store the values in Excel. Then I do a FTS on a quite large table (about 12 GB in size, buffer cache only 2 GB), thus I would assume that after this query the physical reads and total reads increased by about 12 GB of data.

I assumed that the v$ view does not contain real time data so I waited about 5 minutes after the query to give it the time to reflect the new block reads.

But I don't see the data, and I see only a small increase in the physical reads, although the hard disk was used to 100% (using glance utility), so the query really used the hard disk, it really used a FTS and it really did not fit into the buffer cache.

Here is a table which contains the data from v$buffer_pool_statistics before (KEEP, RECYCLE, DEFAULT) and 5 minutes after the FTS (KEEP*, RECYCLE*, DEFAULT*):

NAME           KEEP      KEEP*
BUF_GOT        3928800   3931367
FREE_BUF_INS   3642027   3644697
DIRTY_BUF_INS  2986      2986
DB_BLCK_CHG    3459789   3459789

DB_BLCK_GETS 170622247 170622247
CONSIST_GETS 881402100 881412561

PHYS_READS     3916261   3918828
PHYS_WRITES    86056     86056

NAME           RECYCLE   RECYCLE*
BUF_GOT        12138557  12138590

FREE_BUF_INS 12043825 12043825
DIRTY_BUF_INS 4927955 4927955

DB_BLCK_CHG    47761840  47761840
DB_BLCK_GETS   47826166  47826166
CONSIST_GETS   10026356  10027706
PHYS_READS     11449803  11449836

PHYS_WRITES 6740056 6740056

NAME           DEFAULT     DEFAULT*
BUF_GOT        53661160    53662236
FREE_BUF_INS   39830003    39830787
DIRTY_BUF_INS  744520      744520
DB_BLCK_CHG    246043831   246051058

DB_BLCK_GETS 235948780 235955328
CONSIST_GETS 2387282941 2388548485

PHYS_READS     47240215    47241120
PHYS_WRITES    6538609     6540360

Where is my mistake? I used to determine the buffer cache hit ratio in a given time interval by querying this view and determine the difference between the number of physical reads and total reads (i.e. db_block_gets+consistent_gets).
But using the information above that seems to be wrong...(I know I should not concentrate too much on buffer cache hit ratio)

Regards,
Alex Received on Tue Aug 01 2006 - 07:33:17 CDT