buffer cache理解（5）-优快云博客

本文探讨了如何通过SQL查询和数据库性能优化技术来提升数据访问效率，包括块占用、缓存状态、内存使用情况、热块识别以及关键内存与I/O资源的管理。文章深入分析了SQL查询对内存和物理IO的影响，并提供了实用的查询优化策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查看T2表所占用的块
select object_name,
dbarfil,
dbablk from X$bh a,dba_objects b
where a.obj=b.object_id
and object_name='T2';

OBJECT_NAME             DBARFIL     DBABLK
-------------------- ---------- ----------
T2                            1      60313
T2                            1      60314
T2                            1      60314

在数据文件中占2个块但是在buffer中有3个buffer

buffer里还存在一个cr块

SQL> alter system flush buffer_cache;

System altered.

SQL> select object_name,
2 dbarfil,
3 dbablk from X$bh a,dba_objects b
4 where a.obj=b.object_id
5 and object_name='T2';

OBJECT_NAME             DBARFIL     DBABLK
-------------------- ---------- ----------
T2                            1      60313
T2                            1      60314
T2                            1      60314

SQL> select class,flag,state,lru_flag from x$bh
2 where DBARFIL=1 and DBABLK=60314;

     CLASS       FLAG      STATE   LRU_FLAG
---------- ---------- ---------- ----------
         1          0          0          4
         1          0          0          6

SQL> update t2 set name='xxx' where id=11;

1 row updated.

SQL> select object_name,
2 dbarfil,
3 dbablk from X$bh a,dba_objects b
4 where a.obj=b.object_id
5 and object_name='T2';

OBJECT_NAME             DBARFIL     DBABLK
-------------------- ---------- ----------
T2                            1      60313
T2                            1      60313
T2                            1      60314
T2                            1      60314
T2                            1      60314
T2                            1      60314

6 rows selected.

SQL> select class,flag,state,lru_flag from x$bh
2 where DBARFIL=1 and DBABLK=60314;

     CLASS       FLAG      STATE   LRU_FLAG
---------- ---------- ---------- ----------
         1   33562625          1          0
         1     524288        3          2
         1          0          0          4
         1          0          0          6

SQL>

查看buffercache里面所有的内存情况显示出来
select
o.object_name,
decode(state,0,'free',1,'xcur',2,'scur',3
,'cr',4,'read',5,'mrec',6,'irec',7,'write',
8,'pi') state,
count(*) blocks
from x$bh b,dba_objects o
where b.obj=o.data_object_id
and state <> 0
group by o.object_name,state
order by blocks desc;

T2 xcur 2
T2 cr 2

查找热块
select obj object,
dbarfil file#,
dbablk block#,
tch touches
from x$bh
where tch>10
order by tch asc;

整个数据库所有文件中的block的总和
select sum(blocks)
from dba_data_files;

查看访问的对象

select object_name,
dbarfil,
dbablk from X$bh a,dba_objects b
where a.obj=b.object_id
and dbarfil=1
and dbablk=237;

查看buffer的块使用情况

QL> select decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3,'BEING USED',state) "block status",
count(*) from x$bh
group by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3,'BEING USED',state) 2    3 ;

block status                               COUNT(*)
---------------------------------------- ----------
BEING USED                                       44
FREE                                          15189
AVAILABLE                                      4367

最浪费内存前10个sql语句占用的内存比例
select sum(pct_bufgets) "percent"
from (select rank() over(order by buffer_gets desc) as rank_bufgets,
to_char(100*ratio_to_report(buffer_gets) over (),'999.99') pct_bufgets from
v$sqlarea) where rank_bufgets <11;

消耗物理IO资源最大的sql语句
select disk_reads,substr(sql_text,1,4000) from v$sqlarea
order by disk_reads;

iostat vmstat free top mpstat

mpstat -P 1 1

SQL> select dbms_rowid.rowid_block_number(rowid),id from t2;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)         ID
------------------------------------ ----------
                               60314         11
                               60314         22

SQL> select rowid,id from t2;

ROWID                      ID
------------------ ----------
AAAMijAABAAAOuaAAA         11
AAAMijAABAAAOuaAAB         22