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

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

最新文章