BUFFER CACHE之六：查询一个segment有多少buffer块

V$BH: 当前SGA里所有块的数据对象ID（即，包含每个数据块的信息），X$BH更详细，但是是内部表，Oracle没有文档。

---

Method 1: 计算每个segment的总块数。

SQL> SELECT o.object_name, count(*) "Num of Blks"    

  2    FROM dba_objects o, v$BH bh

  3    WHERE o.data_object_id = bh.objd

  4         AND o.owner ! = 'SYS'

  5    GROUP BY o.object_name

  6    ORDER BY count(*);

OBJECT_NAME                    Num of Blks

------------------------------ -----------

STATS$SNAPSHOT_PK                        1

AQ$_QUEUE_TABLES_PRIMARY                 1

RLM$JOINQKEY                             1

STATS$DATABASE_INSTANCE_PK               1

STATS$SNAPSHOT                           1

AQ$_QUEUE_TABLES                         1

AQ$_QUEUES                               2

7 rows selected.

SQL> select * from hr.students;

        ID NAME      IDCARD

---------- ----- ----------

         1 gwan

         2 wade

         3 jane

SQL> SELECT o.object_name, count(*) "Num of Blks"    

  2    FROM dba_objects o, v$BH bh

  3    WHERE o.data_object_id = bh.objd

  4         AND o.owner ! = 'SYS'

  5    GROUP BY o.object_name

  6    ORDER BY count(*);

OBJECT_NAME                    Num of Blks

------------------------------ -----------

RLM$JOINQKEY                             1

AQ$_QUEUE_TABLES                         1

STATS$SNAPSHOT_PK                        1

AQ$_QUEUE_TABLES_PRIMARY                 1

STATS$DATABASE_INSTANCE_PK               1

STATS$SNAPSHOT                           1

AQ$_QUEUES                               2

STUDENTS                                 6

8 rows selected.

Method 2：查看单个对象的Cache使用情况：

Step 1：查找某 segment的内部对象号（Oracle internal object number）

SELECT DATA_OBJECT_ID , OBJECT_TYPE

FROM DBA_OBJECTS

WHERE OBJECT_NAME = UPPER(‘SEGMENT NAME’);

(两个不同类型的对象可能会同名，所以使用OBJECT_TYPE一栏)

SQL> SELECT DATA_OBJECT_ID, OBJECT_TYPE

  2  FROM DBA_OBJECTS

  3  WHERE OBJECT_NAME = 

  4  UPPER('STUDENTS');

DATA_OBJECT_ID OBJECT_TYPE

-------------- -------------------

         51455 TABLE

Step 2 ：计算某个segment的buffer个数

SELECT COUNT(*) BUFFERS

  FROM V$BH

WHERE BJD = data_object_id_value;   (dba_object_id_value由Step 1获得)

SQL> SELECT COUNT(*) BUFFERS

  2  FROM V$BH

  3  WHERE BJD = 51455;

   BUFFERS

----------

         0                             #这里是因为对象students没有被读到内存中

SQL> SELECT * FROM HR.STUDENTS;

        ID NAME      IDCARD

---------- ----- ----------

         1 gwan

         2 wade

         3 jane

SQL> SELECT COUNT(*) BUFFERS

  2  FROM V$BH

  3  WHERE BJD = 51455;

   BUFFERS

----------

         6

Step 3：计算实例的buffer

SELECT NAME, BLOCK_SIZE, SUM(BUFFERS)

  FROM V$BUFFER_POOL

GROUP BY NAME, BLOCK_SIZE

HAVING SUM(BUFFERS) > 0;

SQL> SELECT NAME, BLOCK_SIZE, CURRENT_SIZE        

  2  FROM V$BUFFER_POOL;

NAME                 BLOCK_SIZE CURRENT_SIZE

-------------------- ---------- ------------

KEEP                       8192           52

RECYCLE                    8192            8

DEFAULT                    8192           32

# 查看各个子缓冲池大小

SQL> SELECT NAME,BLOCK_SIZE,SUM(BUFFERS)

  2    FROM V$BUFFER_POOL

  3  GROUP BY NAME, BLOCK_SIZE

  4  HAVING SUM(BUFFERS) > 0;

NAME                 BLOCK_SIZE SUM(BUFFERS)

-------------------- ---------- ------------

DEFAULT                    8192         3992

KEEP                       8192         6487

RECYCLE                    8192          998

#查看各子缓冲池中buffer块总数

Step 4: 计算单个segment的cache百分比:

Cache used by segment_name = [buffer(Step2)/total buffers(step3)]

Students的百分比 =  6 /(3392 + 6487 + 998)  

Conclusion：不足10%，对象students是小的segment！

The Relationships Among Segments, Extents, and Data Blocks.gif

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24463783/viewspace-675360/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24463783/viewspace-675360/