oracle块段区间,Oracle的逻辑结构(表空间、段、区间、块)——Oracle数据块（二）...

Normal

0

7.8 磅

0

2

false

false

false

EN-US

ZH-CN

X-NONE

以下脚本来自于互联网，具体出处已经找不到了，如有知道还请告知！

关于热点块的查询

====查询当前数据库最繁忙的Buffer，TCH(Touch)表示访问次数越高，热点快竞争问题就存在=====

SELECT *

FROM

(SELECTaddr, ts#, file#, dbarfil,

dbablk, tch

FROM x$bh

ORDER BY tch DESC)

WHERE

ROWNUM < 11;

====查询当前数据库最繁忙的Buffer，结合dba_extents查询得到这些热点Buffer来自哪些对象=====

SELECT e.owner, e.segment_name,

e.segment_type

FROM dba_extents e,

(SELECT *

FROM (SELECTaddr, ts#, file#, dbarfil, dbablk, tch

FROM x$bh

ORDER BY tch DESC)

WHERE ROWNUM < 11) b

WHERE e.relative_fno = b.dbarfil

AND e.block_id <= b.dbablk

AND e.block_id + e.blocks > b.dbablk;

=============如果在Top 5中发现latch free热点块事件时，可以从V$latch_children中查询具体的子Latch信息============

SELECT *

FROM

(SELECTaddr, child#, gets, misses,

sleeps, immediate_gets igets,

immediate_misses imiss,

spin_gets sgets

FROM v$latch_children

WHERE NAME = 'cache buffers chains'

ORDER BY sleeps DESC)

WHERE

ROWNUM < 11;

================获取当前持有最热点数据块的Latch和buffer信息==========

SELECT b.addr, a.ts#, a.dbarfil, a.dbablk,

a.tch, b.gets, b.misses, b.sleeps

FROM

(SELECT *

FROM (SELECTaddr, ts#, file#,

dbarfil, dbablk, tch, hladdr

FROM x$bh

ORDER BY tch DESC)

WHERE ROWNUM < 11) a,

(SELECT addr, gets, misses, sleeps

FROM v$latch_children

WHERE NAME = 'cache buffers chains') b

WHERE

a.hladdr = b.addr;

===============利用前面的SQL可以找到这些热点Buffer的对象信息===========

SELECT distinct e.owner, e.segment_name,

e.segment_type

FROM dba_extents e,

(SELECT *

FROM (SELECTaddr, ts#, file#, dbarfil, dbablk, tch

FROM x$bh

ORDER BY tch DESC)

WHERE ROWNUM < 11) b

WHERE e.relative_fno = b.dbarfil

AND e.block_id <= b.dbablk

AND e.block_id + e.blocks > b.dbablk;

================结合SQL视图可以找到操作这些对象的相关SQL，然后通过优化SQL减少数据的访问，

或者优化某些容易引起争用的操作(如connect by等操作)来减少热点块竞争=================

break on hash_value skip 1

SELECT /*+ rule */ hash_value,sql_text

FROM v$sqltext

WHERE (hash_value, address) IN (

SELECT a.hash_value, a.address

FROM v$sqltext a,

(SELECT DISTINCT a.owner,

a.segment_name, a.segment_type

FROM dba_extents

a,

(SELECT

dbarfil, dbablk

FROM (SELECTdbarfil, 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 hash_value, address, piece;

也可以参看 热点块竞争和解决(cache buffers chains)

http://blog.oracle.com.cn/html/32/203732-4268.html