一、查看共享池Latch争用
select * From v$latchname where name like 'library cache%';
二、资源争用：
select event,count(*) from v$session_wait group by event;
三、查看造成latch buffer cache chains等待事件的热块
SQL> select distinct a.owner,a.segment_name from dba_extents a
2 join (select dbarfil,dbablk from x$bh where hladdr in (
3 select addr from (
4 select addr from v$latch_children order by sleeps desc
5 )
6 where rownum <20
7 )) b on (a.relative_fno = b.dbarfil)
8 where a.block_id <=b.dbablk
9 and a.block_id+a.blocks > b.dbablk;
OWNER SEGMENT_NAME
------------------------------ --------------------------------------------------------------------------------
四、查询当前数据库最繁忙的Buffer,TCH(touch)表示访问的次数越高，热点块竞争问题的可能性越大。
SQL> SELECT *
2 FROM (SELECT ADDR, TS#, FILE#, DBARFIL, DBABLK, TCH
3 FROM X$BH
4 ORDER BY TCH DESC)
5 WHERE ROWNUM < 11;
ADDR TS# FILE# DBARFIL DBABLK TCH
-------- ---------- ---------- ---------- ---------- ----------
4513CD08 0 1 1 345 255
45139AB0 1 2 2 59358 255
4513CD08 0 1 1 48462 253
4513CD08 0 1 1 11745 248
4513CD08 1 2 2 59422 247
4513CD08 1 2 2 59427 247
4513CD08 2 3 3 256 243
4513CD08 0 1 1 425 230
4513CD08 2 3 3 208 228
4513CD08 0 1 1 417 226
10 rows selected
五、查询当前最繁忙的BUFFER，结合dba_extents查询得到这些热点BUFFER来处哪些对象
SQL> SELECT E.OWNER, E.SEGMENT_NAME, E.SEGMENT_TYPE
2 FROM DBA_EXTENTS E
3 JOIN (SELECT *
4 FROM (SELECT ADDR, TS#, FILE#, DBARFIL, DBABLK, TCH
5 FROM X$BH
6 ORDER BY TCH DESC)
7 WHERE ROWNUM < 11) B ON (E.RELATIVE_FNO = B.DBARFIL)
8 WHERE E.BLOCK_ID <= B.DBABLK
9 AND E.BLOCK_ID + E.BLOCKS > B.DBABLK;
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ -------------------------------------------------------------------------------- ------------------
SYS SCHEDULER$_LOBJ_UK INDEX
SYS JOB$ TABLE
SYS JOB$ TABLE
SYSMAN MGMT_JOB_EXEC_IDX01 INDEX
SYSMAN PK_MGMT_FAILOVER INDEX
SYSMAN MGMT_LAST_SYNC_LOAD_PK INDEX
SYSMAN MGMT_FAILOVER_TABLE TABLE
SYSMAN MGMT_LAST_SYNC_LOAD_DETAILS TABLE
SYS _SYSSMU6_1834113595$ TYPE2 UNDO
SYS _SYSSMU4_2369290268$ TYPE2 UNDO
10 rows selected
六、如果在TOP5发现latch free热点块事件时，可以从v$latch_childern 中查询具体的子latch 信息
SQL> SELECT *
2 FROM (SELECT ADDR,
3 CHILD#,
4 GETS,
5 MISSES,
6 SLEEPS,
7 IMMEDIATE_GETS IGETS,
8 IMMEDIATE_MISSES IMISS,
9 SPIN_GETS SGETS
10 FROM V$LATCH_CHILDREN
11 WHERE NAME = 'cache buffers chains'
12 ORDER BY SLEEPS DESC)
13 WHERE ROWNUM < 11;
ADDR CHILD# GETS MISSES SLEEPS IGETS IMISS SGETS
-------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
3C91F790 4096 2723 0 0 25 0 0
3C91F710 4095 3500 0 0 28 0 0
3C91F690 4094 1497 0 0 26 0 0
3C91F610 4093 1329 0 0 24 0 0
3C91F590 4092 161692 0 0 29 0 0
3C91F510 4091 18042 0 0 26 0 0
3C91F490 4090 2186 0 0 26 0 0
3C91F410 4089 2264 0 0 25 0 0
3C91F390 4088 1043 0 0 22 0 0
3C91F310 4087 2567 0 0 27 0 0
10 rows selected
七、获得当前持有最热点数据块的Latch 和Buffer 信息
SQL> SELECT B.ADDR,
2 A.TS#,
3 A.DBARFIL,
4 A.DBABLK,
5 A.TCH,
6 B.GETS,
7 B.MISSES,
8 B.SLEEPS
9 FROM (SELECT *
10 FROM (SELECT ADDR, TS#, FILE#, DBARFIL, DBABLK, TCH, HLADDR
11 FROM X$BH
12 ORDER BY TCH DESC)
13 WHERE ROWNUM < 11) A
14 JOIN (SELECT ADDR, GETS, MISSES, SLEEPS
15 FROM V$LATCH_CHILDREN
16 WHERE NAME = 'cache buffers chains') B ON (A.HLADDR = B.ADDR);
ADDR TS# DBARFIL DBABLK TCH GETS MISSES SLEEPS
-------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
3D7D23A0 1 2 59422 247 7460 0 0
3D7F5C20 1 2 59427 247 3994 0 0
3C874390 2 3 208 246 178131 0 0
3C8C9C90 2 3 176 243 156397 0 0
3D7675A0 2 3 272 242 163382 0 0
3C89E410 0 1 425 236 41200 0 0
3C89F810 2 3 192 232 158084 0 0
3D7E7320 2 3 224 229 171144 0 0
3C8B3690 0 1 417 229 11499 0 0
3C902C90 0 1 48446 228 2799 0 0
10 rows selected
八、利用前面的SQL可以找到这些热点BUFFER的对象信息
SQL> SELECT DISTINCT E.OWNER, E.SEGMENT_NAME, E.SEGMENT_TYPE
2 FROM DBA_EXTENTS E
3 JOIN (SELECT *
4 FROM (SELECT ADDR, TS#, FILE#, DBARFIL, DBABLK, TCH
5 FROM X$BH
6 ORDER BY TCH DESC)
7 WHERE ROWNUM < 11) B ON (E.RELATIVE_FNO = B.DBARFIL)
8 WHERE E.BLOCK_ID <= B.DBABLK
9 AND E.BLOCK_ID + E.BLOCKS > B.DBABLK;
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ -------------------------------------------------------------------------------- ------------------
SYS I_USER1 INDEX
SYS _SYSSMU4_2369290268$ TYPE2 UNDO
SYS _SYSSMU7_137577888$ TYPE2 UNDO
SYS _SYSSMU3_991555123$ TYPE2 UNDO
SYS I_USER2 INDEX
SYSMAN MGMT_LAST_SYNC_LOAD_PK INDEX
SYS _SYSSMU10_3176102001$ TYPE2 UNDO
SYS I_OBJ1 INDEX
SYSMAN MGMT_LAST_SYNC_LOAD_DETAILS TABLE
SYS _SYSSMU6_1834113595$ TYPE2 UNDO
10 rows selected
九、结合SQL视图可以找到这些操作对象相关的SQL，然后进行优化
SELECT /*+rule*/
HASH_VALUE, SQL_TEXT
FROM V$SQLTEXT
WHERE (HASH_VALUE, ADDRESS) IN
(SELECT A.HASH_VALUE, A.ADDRESS
FROM V$SQLTEXT A
JOIN (SELECT DISTINCT A.OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE
FROM DBA_EXTENTS A
JOIN (SELECT DBARFIL, DBABLK
FROM (SELECT DBARFIL, DBABLK
FROM X$BH
ORDER BY TCH DESC)
WHERE ROWNUM < 11) B ON (A.RELATIVE_FNO = B.DBARFIL)
WHERE A.BLOCK_ID = B.DBABLK
AND A.BLOCK_ID + A.BLOCKS > B.DBABLK) B
ON (A.SQL_TEXT LIKE '%' || B.SEGMENT_NAME || '%' AND
B.SEGMENT_TYPE = 'TABLE'))
ORDER BY HASH_VALUE, ADDRESS, PIECE;
阅读(1455) | 评论(0) | 转发(0) |
