注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

饥民2011

一直在搬砖

 
 
 

日志

 
 
 
 

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

2013-02-02 22:55:43|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
这篇文章是参照甲骨论老相老师的教学视频:
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
 
如图, 表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

可以见到相应地有9个buffer, 但是实际上只有6个 DBABLK编号哦, 因为9个buffer中只有6个xcur状态的buffer啦, 而3个CR 块是不对应dbf文件的.

 可以用下面命令来查看数据文件编号啦
Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011


 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

可以见到, 在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
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

可以见到 JOB$ 这个对象在1号数据文件中2008 和 2008号block 被访问次数最多, 逻辑读发生244次啦~

6. 查找数据库文件所有block的总数
   呢个好简单:

select sum(blocks) from dba_data_files;


Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011
 
 
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

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

如果在生产数据库中,这个数字很大, 例如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

vmstat
Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011
 
top
Oracle - 使用各种SQL来熟知buffer cache使用情况 - 饥民 - 饥民2011

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


 
 
 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
 


 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


 
 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


 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

在生产中, 这个值一般在98以上,才是正常的


 



 仲有好多, 老相老师话以后再详细讲~
  评论这张
 
阅读(409)| 评论(3)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017