Database Tuning (MOSC)

MOSC Banner

Query returns wrong results

edited Aug 10, 2012 9:05AM in Database Tuning (MOSC) 3 commentsAnswered
How is it possible to have more blocks in the buffer cache (according to v$bh) than the object itself has (according to dba_segments)?

                                              Object                     Blocks in        MB in    % in
Table[.Partition]                             Type             Blocks       Buffer       Buffer  Buffer
--------------------------------------------- ---------- ------------ ------------ ------------ -------
TABLEA                                        TABLE             4,864        7,619          238  156.64 <--
TABLEB.PARTITIONA                             TABLE PART       65,536       80,899        2,528  123.44 <--
TABLEC                                        TABLE             2,304        2,584           81  112.15 <--
TABLED                                        TABLE           104,448      108,499        3,391  103.88 <--
INDEX1.PARTITION1                             INDEX PART       16,640       16,620          519   99.88
TABLEE.PARTITIONB                             TABLE PART      196,064      195,559        6,111   99.74
INDEX2                                        INDEX            67,584       67,366        2,105   99.68
JMSQUEUETABLE                                 TABLE             1,280        1,274           40   99.53
...

set pages 50
set lines 140
column objname           heading "Table[.Partition]" format a45
column objtype           heading "Object|Type"       format a10
column total_blocks      heading "Blocks"            format 999,999,999
column blocks_in_buffer  heading "Blocks in|Buffer"  format 999,999,999

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center