buffer cache专题.docx

Buffer Cache1. block、buffer的概念简述段区块的概念，引出块的概念buffer的概念2. Buffer Cache的意义 缓存数据块减少IO物理IO：磁盘读逻辑IO：内存读 构造cr块3. Buffer Cahe的内存组织结构 CBC(Cache Buffer Chain)：根据block地址找block的时候，需要使用到CBC。 LRU ：最近最少使用队列。 MRU：最近热快队列。 LRUW：最近最少被修改队列。 CHECKPOINT QUEUE：检查点队列，按照被修改的时间点来排列。4. DB_WRITER_PROCESSESshow parameter db_writer_process 显示dbwr的后台进程数量(cpu数量/8)5. Buffer Cahe的重要参数配置Buffer Cache的大小配置alter system set db_cache_size=20M scope=memory test表在内存中的占用的块select distinct object_name,dbarfil,dbablk from xsbh a,dba_objects b where a.obj=b.object_id and object_name=TEST; 查询块的状态select class,flag,state,1ru_flag from x$bh where dbarfil=1 and dbablk=61433; 一个对象占用Buffer的具体情况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(*) blocksfrom x$bh,b,dba_objects owhere b.obj=o.data_object_id and state0and o.object_name=TESTgroup by o.object_name,stateorder by blocks asc; 对象使用pool的具体情况（考虑了各种池子的情况）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(*) blocksfrom x$bh,b,dba_objects owhere b.obj=o.data_object_id and state0group by o.object_name,stateorder by blocks asc; 寻找热块select obj object,dbarfil file#,dbablk block# tch touches from x$bh where tch10 order by tch asc; 整个数据库所有文件中block的总合select sum(blocks) from dba_datafiles; 空闲空间的比例，最好控制在10%以内select decode(state,0,free,1, decode(lrba_seq,0,AVAILABLE,BEING USED) 3,BEING USED,state)BLOCK STATUS, count(*)from xbh; 最浪费内存的前10个语句占所有语句的比例，建议控制在5%以内group by decode(state,0,free,1, decode(lrba_seq,0,AVAILABLE,BEING USED) 3,BEING USED,state);select sum(pct_bufgets)Percentfrom (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 vsqlarea)where rank_bufgets11; 找出消耗物理IO资源最大的SQL语句select disk_reads,substr(sql_text,1,4000) from v$sqlarea order by disk_reads desc 找出消耗内存资源最大的SQL语句select buffer_gets,substr(sql_text,1,4000) from v$sqlarea order by buffer_gets desc6. Block状态select distinct state from x$bhstate状态：0, FREE, no valid block image1, XCUR, a current mode block, exclusive to this instance2, SCUR, a current mode block, shared with other instances3, CR, a consistent read (stale) block image4, READ, buffer is reserved for a block being read from disk5, MREC, a block in media recovery mode6, IREC, a block in instance (crash) recovery mode7. dbms_rowidselect dbms_rowid.rowid_block_number(rowid), a from t;根据rowid得到其物理地址create or replace function get_rowid(l_rowid in varchar2)return varchar2isls_my_rowid varchar2(200);rowid_type number;object_number number;relative_fno number;block_number number;row_number number;begindbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno,block_number,row_number);ls_my_rowid:=Object# is :|to_char(object_number)|chr(10)| Relative_fno is :|to_char(relative_fno)|chr(10)| Block number is :|to_char(block_number)|chr(10)| Row number is:|to_char(row_number); return ls_my_rowid:end;select rowid,a.* from test a;select get_rowid(AAAM13AABAAAO/6AAA) rowid from dual;ROW_ID-Object# is :52599Relative_fno is :1Block number is: 61434Row number is: 08. 关于Buffe