如何查找热点块
来源:互联网 发布:qt 关闭release优化 编辑:程序博客网 时间:2024/04/29 19:47
V$LATCH
字段说明:
gets表示总共有这么多次请求,misses表示请求失败的次数(加锁不成功),而sleeps 表示请求失败休眠的次数,通过sleeps我们可以大体知道数据库中latch的竞争是否严重,这也间接的表征了热点块的问题是否严重
查看与热块有关的latch信息(cache buffer%):
SQL> SELECT latch#, NAME, gets, misses, sleeps 2 FROM v$latch 3 WHERE NAME LIKE 'cache buffer%'; LATCH# NAME GETS MISSES SLEEPS---------- -------------------------------------------------- ---------- ---------- ---------- 123 cache buffer handles 759 0 0 117 cache buffers lru chain 96611 12 12 122 cache buffers chains 2253780 4 4
V$LATCH_CHILDREN
包含子latch的信息,如果子latch的latch#列值相同,则说明他们有相同的父latch。
查看子latch信息(cache buffers chains):
SQL> SELECT addr, LATCH#, CHILD#, gets, misses, sleeps 2 FROM v$latch_children 3 WHERE NAME = 'cache buffers chains' 4 AND rownum < 21; ADDR LATCH# CHILD# GETS MISSES SLEEPS-------- ---------- ---------- ---------- ---------- ----------290C07C4 122 1 2426 0 0290C0940 122 2 589 0 0290C0ABC 122 3 2701 0 0290C0C38 122 4 1844 0 0290C0DB4 122 5 1214 0 0290C0F30 122 6 652 0 0290C10AC 122 7 4897 0 0290C1228 122 8 3474 0 0290C13A4 122 9 977 0 0290C1520 122 10 4210 0 0290C169C 122 11 3392 0 0290C1818 122 12 2913 0 0290C1994 122 13 385 0 0290C1B10 122 14 822 0 0290C1C8C 122 15 2333 0 0290C1E08 122 16 4714 0 0290C1F84 122 17 1001 0 0290C2100 122 18 419 0 0290C227C 122 19 1735 0 0290C23F8 122 20 1105 0 0 20 rows selected
根据v$latch_child.addr关联到对应的x$bh.hladdr(这是buffer header中记录的当前buffer所处的latch地址),通过x$bh可以获得块的文件编号和block编号。
SQL> SELECT dbarfil, dbablk 2 FROM x$bh 3 WHERE hladdr IN (SELECT addr 4 FROM (SELECT addr, LATCH#, CHILD#, gets, misses, sleeps 5 FROM v$latch_children 6 WHERE NAME = 'cache buffers chains' 7 ORDER BY sleeps DESC) 8 WHERE rownum < 11); DBARFIL DBABLK---------- ---------- 1 55375 4 8500 3 756 3 5094 2 1455 1 50338 1 2154 3 30656 1 6492 3 4395 ……104 rows selected SQL> SQL> SELECT dbarfil, dbablk 2 FROM x$bh 3 WHERE hladdr IN 4 (SELECT addr 5 FROM (SELECT addr FROM v$latch_children ORDER BY sleeps DESC) 6 WHERE rownum < 11); DBARFIL DBABLK---------- ---------- 1 55375 4 8500 3 756 3 5094 2 1455 1 50338 1 2154 3 30656 1 6492 3 4395 2 769 ……37 rows selected
知道了文件编号和block编号,可以通过dba_extents获取相关的segment。
SQL> SELECT DISTINCT a.owner, a.segment_name, a.segment_type 2 FROM dba_extents a, 3 (SELECT dbarfil, dbablk 4 FROM x$bh 5 WHERE hladdr IN 6 (SELECT addr 7 FROM (SELECT addr, LATCH#, CHILD#, gets, misses, sleeps 8 FROM v$latch_children 9 WHERE NAME = 'cache buffers chains' 10 ORDER BY sleeps DESC) 11 WHERE rownum < 11)) b 12 WHERE a.RELATIVE_FNO = b.dbarfil 13 AND a.BLOCK_ID <= b.dbablk 14 AND a.block_id + a.blocks > b.dbablk 15 AND a.owner = 'OCP'; OWNER SEGMENT_NAME SEGMENT_TYPE------------------------------ -------------------------------------------------------------------------------- ------------------OCP T TABLESQL> SELECT DISTINCT a.owner, a.segment_name, a.segment_type 2 FROM dba_extents a, 3 (SELECT dbarfil, dbablk 4 FROM x$bh 5 WHERE hladdr IN 6 (SELECT addr 7 FROM (SELECT addr FROM v$latch_children ORDER BY sleeps DESC) 8 WHERE rownum < 11)) b 9 WHERE a.RELATIVE_FNO = b.dbarfil 10 AND a.BLOCK_ID <= b.dbablk 11 AND a.block_id + a.blocks > b.dbablk 12 AND a.owner = 'OCP'; OWNER SEGMENT_NAME SEGMENT_TYPE------------------------------ -------------------------------------------------------------------------------- ------------------OCP T TABLESQL>
在v$sqlarea或者v$sqltext里找到与热点块有关的sql进行优化。
其实也就是下面的语句:
SELECT sql_text
FROM v$sqltext a
WHERE a.sql_text LIKE '%t%'
ORDER BY a.hash_value, a.address, a.piece;
SELECT sql_text FROM v$sqltext a, (SELECT DISTINCT a.owner, a.segment_name, a.segment_type FROM dba_extents a, (SELECT dbarfil, dbablk FROM x$bh WHERE hladdr IN (SELECT addr FROM (SELECT addr, LATCH#, CHILD#, gets, misses, sleeps FROM v$latch_children WHERE NAME = 'cache buffers chains' ORDER BY sleeps DESC) WHERE rownum < 11)) b WHERE a.RELATIVE_FNO = b.dbarfil AND a.BLOCK_ID <= b.dbablk AND a.block_id + a.blocks > b.dbablk AND a.owner = 'OCP') b WHERE a.sql_text LIKE '%' || b.segment_name || '%' AND b.segment_type = 'TABLE' ORDER BY a.hash_value, a.address, a.piece;SELECT sql_text FROM v$sqltext a, (SELECT DISTINCT a.owner, a.segment_name, a.segment_type FROM dba_extents a, (SELECT dbarfil, dbablk FROM (SELECT dbarfil, dbablk FROM x$bh ORDER BY tch DESC) WHERE rownum < 11) b WHERE a.RELATIVE_FNO = b.dbarfil AND a.BLOCK_ID <= b.dbablk AND a.block_id + a.blocks > b.dbablk) b WHERE a.sql_text LIKE '%' || b.segment_name || '%' AND b.segment_type = 'TABLE' ORDER BY a.hash_value, a.address, a.piece;
SELECT dbarfil, dbablk FROM x$bh ORDER BY tch DESC
x$bh.tch (touch count)大的block可能暗示着在当前某个周期内被访问次数比较多。
查看访问次数比较多的块所在对象:
SELECT t.owner, t.object_name, t2.dbarfil, t2.dbablk,t2.tch FROM dba_objects t, x$bh t2 WHERE t.data_object_id = t2.obj AND owner = 'OCP' AND t2.ts# > 0 --ts#表示表空间块号,是什么意思呢? ORDER BY t2.tch DESC
查看访问次数比较多的对象:
SELECT * FROM (SELECT o.owner, o.object_name, SUM(tch) TouchTime FROM x$bh b, dba_objects o WHERE b.obj = o.data_object_id AND o.owner='OCP' AND b.ts#>0 --ts#表示表空间块号,,是什么意思呢? GROUP BY o.owner, o.object_name ORDER BY SUM(tch) DESC) WHERE rownum <= 10
除了优化sql外,当然对于热点的表或者索引来说,如果小的话,我们可以考虑cache在内存中,这样可能降低物理读提高sql运行速度(这并不会减少cache buffer chains的访问次数),对于序列,我们可以对序列多设置一些cache。如果是并行服务器环境中的索引对象,并且这个索引是系列递增类型,我们可以考虑反向索引。
- 如何查找热点块
- 热点块的定义
- 热点块竞争和解决
- 热点块竞争和解决
- 深度分析数据库的热点块问题
- 深度分析数据库的热点块问题
- 深度分析数据库的热点块问题
- 深度分析ORACLE热点块问题
- 深度分析ORACLE热点块问题
- 深度分析ORACLE热点块问题
- 热点块竞争和解决--cache buffers chains 3(转载)
- 找出热点块所属的用户,对象名,类型
- 需要找到造成oracle 热点块的sql
- 热点块竞争和解决--cache buffers chains
- 热点块竞争和解决--cache buffers chains
- 热点块竞争和解决--cache buffers chains
- 热点块竞争和解决--cache buffers chains
- 热点块竞争和解决--cache buffers chains
- 关于typedef的用法总结
- 使用AWT制作图形界面登陆、注册
- 致软件学院的老师们的一封信
- 关于本人第一次写css圆角矩形的心得,请勿喷我...
- silverlight学习笔记--1
- 如何查找热点块
- printf 实现原理
- winpcap 线程未安全关闭造成的问题————bogus savefile header
- android 面试题集
- B-TREE
- 一个图片
- centos6.1安装VSFTPD过程
- 【转载】mini2440 SPI驱动移植
- 倒置数组元素查找--时间复杂度O(lgn)