Oracle - 使用各种SQL来熟知buffer cache使用情况

水深无声 2022-03-19 13:15 211阅读 0赞

这篇文章是参照甲骨论老相老师的教学视频:  
http://v.youku.com/v\_show/id\_XMzkyMjE3NTA0.html  
所做的学习笔记  
  
1. 查看某个对象所占用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')  as state,   
       count(1) blocks   
from x$bh b, dba\_objects o   
where b.obj = o.data\_object\_id   
   and o.object\_name = ' CL\_DEPT'   
   and o.owner = ' HR'   
group by o.object\_name, state   
order by blocks  desc;  
  
如图:

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011]

如图, 表hr.cl\_dept占用6个 xcur状态的buffer, 3个cr状态的buffer啦.  
  
2. 查看某个对象所占用buffer所对应的dbf文件中的block  
  
使用如下语句:  
select object\_name, DBARFIL, DBABLK   
from x$bh a, dba\_objects b  
where a.obj = b.data\_object\_id   
and b.object\_name = ' CL\_DEPT'  
and b.owner= ' HR';  
  
其中视图x$bh中 DBARFIL 就是知dbf文件编号啦, 而DBABLK就是block的编号  
如下图:

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 1]  
  
可以见到相应地有9个buffer, 但是实际上只有6个 DBABLK编号哦, 因为9个buffer中只有6个xcur状态的buffer啦, 而3个CR 块是不对应dbf文件的.

可以用下面命令来查看数据文件编号啦

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 2]

3. 查看具体某个BLOCK对应buffer状态

既然上面已经获得了数据文件编号和block的编号, 就可以在x$bh中查看某个BLOCK的状态了.  
语句:  
select DBARFIL, DBABLK, STATE  from x$bh  
where DBARFIL = 4  
and   DBABLK = 175;

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 3]  
  
可以见到, 在4号dbf文件中, 175编号的block在buffer cache里有4个相关的buffer, 其中只会有1个是状态是xcur的, 其余3个的state是3, 就是3个CR块啦.  
  
  
  4. 查看buffer cache所有对象的buffer占用状态  
   其实就是第一条sql语句作一些修改就得啦, 不过既然老相老师有讲, 我也写出来吧.  
select o.object\_name,   
        decode(state, 0, 'free', 1, 'xcur' , 2, 'scur', 3, 'cr',   
                     4, 'read', 5, 'mrec' , 6, 'irec', 7, 'write',   
                     8, 'pi')  as state,   
       count(1) blocks   
from x$bh b, dba\_objects o   
where b.obj = o.data\_object\_id   
group by o.object\_name, state   
order by o.object\_name, state

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 4]  
group by o.object\_name, state

注意, 如果对blocks这个列排序, 就知道baffer\_cache哪个对象占用数据最多啦~  
  
  
5. 查找热块  
     首先说明一下什么是热块,  之前已经介绍过在buffer cache里所有干净的buffer都会被挂在LRU链, 按照访问次数(逻辑读次数)排序,次数多的在热端,  次数少的在冷端. 所以实在上就是查找访问次数最多的几个buffer  
  
     而在x$bh中包含了所有buffers的信息, 其中TCH列意思就是touch, 1个buffer每被逻辑读一次,那么TCH的值就加1,  所以只需按照这个值排序就ok了.  
  
     命令如下:  
select  
        o.object\_name,    
        b.dbarfil file\#,  
        b.dbablk  block\#,  
        b.tch     touches  
from    x$bh b, dba\_objects o  
where   b.obj = o.data\_object\_id  
and     b.tch >0  
order by b.tch;

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 5]  
  
可以见到 JOB$ 这个对象在1号数据文件中2008 和 2008号block 被访问次数最多, 逻辑读发生244次啦~  
  
6. 查找数据库文件所有block的总数  
   呢个好简单:

select sum(blocks) from dba_data_files;

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 6]

7. 查看buffer cache可以使用比率  
     其实这个比率就是 (free buffers的数量+ 干净buffer的数量 )/ buffer的总数  
     free buffer在 x$bh 中的state 是0, 我们可以根据这个判断.  
     那么state不是0的可分成干净的和脏的,   我们可以用lrba\_seq这个字段来判断  
  
     lrba\_seq意思 是脏buffer第一次变脏时所对应的redo entries在redo log file里的位置.  
  
   可以根据lrba\_seq来判断,如果lrba\_seq=0, 则说明这个buffer是干净的, 否则是脏的  
     
下面这个语句分别列出buffer cache里 空闲,干净,脏的buffer的数量  
  
select decode(state, 0, 'free',  
                    decode(lrba\_seq, 0, 'AVAILABLE',  
                                        'BEING USED')) as Block\_status,  
       count(1) as Count  
from x$bh  
group by decode(state, 0, 'free',  
                       decode(lrba\_seq, 0, 'AVAILABLE',  
                                         'BEING USED'));  
  
如下图: 可以见到干净buffer 有15个(生产中最好控制在10%以内), SGA太大没好处啦  
         脏的有23个, 证明物理IO读取很少啦.

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 7]

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 4]

8. 查看前10条最耗缓存(逻辑IO最多)的sql语句所占的缓存比率.  
     在生产中这个比例最好控制在5%以内.  
    使用如下语句:  
     
    
 select sum(pct\_bufgets) as Percent  
from   (select rank() over (order by buffer\_gets desc) as rank\_bufgets,  
               to\_char(100 \* ratio\_to\_report(buffer\_gets) over (), '999.99') as pct\_bufgets  
        from v$sqlarea)  
where rank\_bufgets < 11;  
  
  
简单解析下上面语句啦~  
其中rank 是oracle的1个排名的函数,  返回1个排名的值, over()是rank的1个附加条件,  
 rank() over (order by buffer\_gets desc)  的意思就是返回1个值,  这个值是按照 buffer\_gets倒序排列的排名整数.  
  
详细用法可以参考这里:  
http://docs.oracle.com/cd/E11882\_01/server.112/e26088/functions141.htm\#SQLRF00690  
  
而ratio\_to\_report 是 oracle的另1个函数, 作用是返回1个字段的值占这张表这个字段的所有值的比率. 也可以通过over()附加条件..  
 100 \* ratio\_to\_report(buffer\_gets) over ()  的意思就是返回某一行buffer\_gets值在表里所占的比率啦~  
 详细用法可以参考这里:  
http://docs.oracle.com/cd/B19306\_01/server.102/b14200/functions124.htm  
  
而 v$sqlarea 中的字段 buffer\_gets就是逻辑读次数的意思啦  
  
执行如下图: 这个比率太大了, 因为是学习用数据库嘛

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 8]  
  
如果在生产数据库中,这个数字很大, 例如20%以上, 说明有用户执行了若干个占用缓存很大的sql了, 一般来讲执行这种规模的sql语句会托慢数据库的啦.  
  
9. 查看前10条物理IO最多sql语句  
   select disk\_reads, substr(sql\_text,1,4000) from v$sqlarea  
   where rownum <= 10 order by disk\_reads desc;  
  
对应buffer\_gets,    v$sqlarea 字段disk\_reads就算物理读次数啦.  
所以  
   select  buffer\_gets, substr(sql\_text,1,4000) from v$sqlarea  
   where rownum <= 10 order by  buffer\_gets desc;  
就是查看逻辑读次数最多的10条sql.  
  
  
其实在操作系统级别也有很多工具来查看物理IO的状态啦.  
  
例如:  
iostat

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 9]  
  
vmstat

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 10]

top

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 11]  
  
mpstat  
  这个只查看cpu状态的,    mpstat -P n 可以用来查看第n个cpu的状态,  如果系统有多cpu的话

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 12]

10. 查看buffer\_cache每个对象具体占用情况  
          
这条sql语句就复9杂了, 我在视频上也抄不下来, 幸好甲骨论论坛能找到下载, 老相好人啊.  
  
SELECT /\*+ ORDERED USE\_HASH(o u) MERGE \*/  
  DECODE(obj\#,  
         NULL,  
         to\_char(bh.obj),  
        u.name || '.' || o.name) name,  
  COUNT(\*)  total,  
  SUM( DECODE(( DECODE(lru\_flag, 8, 1, 0) +  DECODE( SIGN(tch - 2), 1, 1, 0)),  /\*sign用来判断数值的正负\*/  
            2, 1,  
            1,1,  
            0))  hot,  
 SUM( DECODE( DECODE( SIGN(lru\_flag - 8), 1, 0, 0, 0, 1) +DECODE(tch, 2, 1, 1, 1, 0, 1, 0),  
            2,1,  
            1,0,  
            0))  cold,  
  SUM( DECODE( BITAND(flag,  POWER(2, 19)), 0, 0, 1))  fts,  /\*BITAND函数请见下面注1,POWER函数见注2\*/  
  SUM(tch)  total\_tch,  
  ROUND( AVG(tch), 2)  avg\_tch,  /\*round 是四舍5入函数啦\*/  
  MAX(tch)  max\_tch,  
  MIN(tch)  min\_tch  
   FROM x$bh bh, sys.obj$ o, sys.user$ u  
  WHERE  
    bh.obj <> 4294967295  
    AND bh.state in (1, 2, 3)  
    AND bh.obj = o.dataobj\#(+)  
    AND bh.inst\_id = USERENV('INSTANCE')  
  AND o.owner\# = u.user\#(+)  
\--   AND o.owner\# > 5  
    AND u.name NOT like 'AURORA$%'  
  GROUP BY DECODE(obj\#,  
                 NULL,  
                  to\_char(bh.obj),  
                 u.name || '.' || o.name)  
 ORDER BY  total  desc  
 /  
  
  
注1:  
BITAND函数  返回两个数值按位 AND运算的结果, 返回的值是十进制..  
 BITAND(    )    将    nExpression1    的每一位同    nExpression2    的相应位进行比较。如果    nExpression1    和    nExpression2    的位都是    1，相应的结果位就是    1；否则相应的结果位是    0。   
例如 BITAND(6,3) 的结果是2.    因为6的2进制是110, 3是011, 所以and后是 010 就是2啦..  
详细请参考:  
http://docs.oracle.com/cd/E11882\_01/server.112/e26088/functions021.htm\#SQLRF00612  
  
注2:  
POWER函数就是指数函数啦, 例如Power(3,2) 就是等于3的2次方=9   如果底数小于0 幂必须是整数  
可以参考:  
http://docs.oracle.com/cd/E11882\_01/server.112/e26088/functions129.htm\#SQLRF00689  
  
执行效果如下图:

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 13]

11. 汇总buffer\_cache每个对象 的各状态buffer个数  
  
这条语句就简单多啦~  
  
SELECT t.name  AS tablespace\_name,  
       o.object\_name,  
        SUM(DECODE(bh.status, 'free', 1, 0))  AS free,  
        SUM(DECODE(bh.status, 'xcur', 1, 0))  AS xcur,  
        SUM(DECODE(bh.status, 'scur', 1, 0))  AS scur,  
        SUM(DECODE(bh.status, 'cr', 1, 0))  AS cr,  
        SUM(DECODE(bh.status, 'read', 1, 0))  AS read,  
        SUM(DECODE(bh.status, 'mrec', 1, 0))  AS mrec,  
        SUM(DECODE(bh.status, 'irec', 1, 0))  AS irec  
FROM   v$bh bh  
        JOIN dba\_objects o  ON o.data\_object\_id = bh.objd  
        JOIN v$tablespace t  ON t.ts\# = bh.ts\#  
GROUP BY t.name, o.object\_name  
order by xcur desc

效果如下图:

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 14]

12. 查看buffer\_cache每个对象占用多少个buffer, 有多少block被缓存到buffer cache中.  
      呢个就犀利了, 首先要创建1个试图buffer map  
create or replace view buffer\_map  as  
select  
   o.owner          owner,  
   o.object\_name    object\_name,  
   o.subobject\_name subobject\_name,  
   o.object\_type    object\_type,  
    count(distinct file\# || block\#)         num\_blocks  
from  
   dba\_objects  o,  
   v$bh         bh  
where  
   o.data\_object\_id  = bh.objd  
\-- and  o.owner not in ('SYS','SYSTEM')  
and  
   bh.status != 'free'  
group by  
   o.owner,  
   o.object\_name,  
   o.subobject\_name,  
   o.object\_type  
order by  
    count(distinct file\# || block\#)  desc  
;  
  
跟住就可以用下面语句查询了~

column c0 heading "Owner"                                    format a12
    column c1 heading "Object|Name"                              format a30
    column c2 heading "Object|Type"                              format a8
    column c3 heading "Number of|Blocks in|Buffer|Cache"         format 99,999,999
    column c4 heading "Percentage|of object|blocks in|Buffer"    format 999
    column c5 heading "Buffer|Pool"                              format a7
    column c6 heading "Block|Size"                               format 99,999
     
    select
       buffer_map.owner                                          c0,
       object_name                                       c1,
       case when object_type = 'TABLE PARTITION' then 'TAB PART'
            when object_type = 'INDEX PARTITION' then 'IDX PART'
            else object_type end c2,
       sum(num_blocks)                                     c3,
       (sum(num_blocks)/greatest(sum(blocks), .001))*100 c4,
       buffer_pool                                       c5,
       sum(bytes)/sum(blocks)                            c6
    from
       buffer_map,
       dba_segments s
    where
       s.segment_name = buffer_map.object_name
    and
       s.owner = buffer_map.owner
    and
       s.segment_type = buffer_map.object_type
    and
       nvl(s.partition_name,'-') = nvl(buffer_map.subobject_name,'-')
    group by
       buffer_map.owner,
       object_name,
       object_type,
       buffer_pool
    having
       sum(num_blocks) > 10
    order by
       sum(num_blocks) desc
    ;

下面是效果图:

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 15]  
  
  
 13. 查看buffer\_cache当前命中率.  
     缓存命中率的定义之前都提过啦, 就是缓存中逻辑读的次数/ 逻辑读+ 物理读的总次数.  
  
语句如下:  
select (1 -( sum( decode(name, 'physical reads', value,0))/  
         ( sum( decode(name, 'db block gets', value,0))  +  sum( decode(name,'consistent gets', value,0))))  
         )\* 100  as Hit\_Radio  
           from v$sysstat;  
  
  
解析一下:  
官方在线文档基本的定义为如下：  
DB block gets：the number of accesses to the current image of a block  
Consistent gets：the number of accesses to a read-consistent image of a block  
Physical reads：the number of blocks read from disk  
  
这 其中主要涉及到了Oracle读取数据的consistent mode和current mode这两个模式，对于db block gets是在current mode下读取的block数目（单位应该是“块次”，同一个block读取了两个算做2），而consistent gets是在consistent mode下读取的block数目（单位同上）。  
current mode下读取数据是为了保证读取到的数据是当前时间点上最新的数据，这样做的目的一般都是为了DML语句的需求，比如需要更新，自然需要知道最新的数据 才行；consistent mode呢主要是为了保证Oracle数据一致读的特性，一般都是在select情况下发生，读到的数据可能是一个实际存在的block，也有可能需要根 据scn信息以及transaction相关信息以及回滚段中数据来构造。  
  
而physical reads是与logical reads相对的一个概念，两者的区别是读取的数据是从buffer中读取到还是从disk上的db file中取到。通过v$sysstat也可以看到，里面还有db block gets from cache以及consistent gets from cache两项，且这两项的数值与db block gets和consistent gets并不相同且小于后两者。所以不管是db block gets还是consistent gets，都可能出现了physical reads和logical reads两种情况（由buffer中的是否已经存在需要的数据），也即是说，db block gets与consistent gets两者已经构成了一次数据库操作中读取的所有block的总次数了。因此，logical reads自然也就可以通过如下公式算的：logical reads = (db block gets + consistent gets) - physical reads。  
  
注,上面3段转自:http://fengyaping1210.itpub.net/post/39890/486348  
  
执行一下:

![Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011][Oracle - _SQL_buffer cache_ - _ - _2011 16]  
  
在生产中, 这个值一般在98以上,才是正常的

仲有好多, 老相老师话以后再详细讲~

[Oracle - _SQL_buffer cache_ - _ - _2011]: http://img5.ph.126.net/fnFkr7lWKIm-IS_6WlFuFg==/6597559049331399485.jpg
[Oracle - _SQL_buffer cache_ - _ - _2011 1]: http://img2.ph.126.net/JOpjj3FAplqxlN3kZl3yyg==/6597841623819748072.jpg
[Oracle - _SQL_buffer cache_ - _ - _2011 2]: http://img4.ph.126.net/Tp57rCtGrJ78MboGwRVlrw==/6597528263005823994.jpg
[Oracle - _SQL_buffer cache_ - _ - _2011 3]: http://img5.ph.126.net/BiBlH2QLnE2jOHAzM5EKDA==/6598103307586939965.jpg
[Oracle - _SQL_buffer cache_ - _ - _2011 4]: http://img1.ph.126.net/cK7xOWcDEJz6ht6xg_SWhw==/6597649209284877271.jpg
[Oracle - _SQL_buffer cache_ - _ - _2011 5]: http://img6.ph.126.net/_uAFDM3rmlqQ7SqSnnlf3Q==/6598238547517153856.jpg
[Oracle - _SQL_buffer cache_ - _ - _2011 6]: http://img1.ph.126.net/mzyLXakiKEHhjpPuCj1T_g==/6597447998656741981.jpg
[Oracle - _SQL_buffer cache_ - _ - _2011 7]: http://img9.ph.126.net/gjObubGOxDoJAUon1vy24Q==/6597272076796048949.jpg
[Oracle - _SQL_buffer cache_ - _ - _2011 8]: http://img7.ph.126.net/Lss0TZnvJba27YFtDpZ9ww==/6597524964470945317.jpg
[Oracle - _SQL_buffer cache_ - _ - _2011 9]: http://img2.ph.126.net/XyQ53yuJ70WCYIiWrSqqFA==/6597816335052313292.jpg
[Oracle - _SQL_buffer cache_ - _ - _2011 10]: http://img8.ph.126.net/fQreTMKDxO7J0JTMBhFpsQ==/6597201708053692098.jpg
[Oracle - _SQL_buffer cache_ - _ - _2011 11]: http://img6.ph.126.net/43JO-7sBzNmEMAdrcGzi0Q==/6597234693402531956.jpg
[Oracle - _SQL_buffer cache_ - _ - _2011 12]: http://img6.ph.126.net/jtXyfJAO7g3vJxzzJgp2xQ==/6597742667773243516.jpg
[Oracle - _SQL_buffer cache_ - _ - _2011 13]: http://img2.ph.126.net/_ci1JpZU4lF0HRBxoXRazg==/6597736070703477282.jpg
[Oracle - _SQL_buffer cache_ - _ - _2011 14]: http://img4.ph.126.net/g2unav0QN0G_4mOCsAR4MA==/6597150031007194864.jpg
[Oracle - _SQL_buffer cache_ - _ - _2011 15]: http://img3.ph.126.net/e9F9zpuJd93RhzSCzbc3Sg==/6598172576819494393.jpg
[Oracle - _SQL_buffer cache_ - _ - _2011 16]: http://img3.ph.126.net/mPr0L3TckBB5ejRodUsdnA==/6597276474842593363.jpg