!!没有相关优化知识的筒子们请慎用以下脚本!!
一、查看Oracle热点块以及热点对象脚本
select distinct a.owner, a.segment_name
from dba_extents a,
(select dbarfil, dbablk
from x$bh
where hladdr in
(select addr
from (select addr from v$latch_children 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;
--结果如下:
OWNER SEGMENT_NAME SEGMENT_TYPE
--------------------- ---------------------- ------------------
OA BIZ_SEARCHER TABLE
OA CMNTY_USER_MESSAGE TABLE
OA CMNTY_VISITOR_INFO_PK INDEX
OA COMPANY_AMID_IND INDEX
OA COMPANY_DRAFT TABLE
OA FEEDBACK_POST TABLE
OA IM_BLACKLIST_PK INDEX
OA IM_GROUP TABLE
OA IM_GROUP_LID_IND INDEX
OA MEMBER TABLE
OA MEMBER_PK INDEX
OA MLOG$_SAMPLE TABLE
二、查看产生热点对应的SQL
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;
--结果如下:
SQL_TEXT
----------------------------------------------------------------
SELECT SEQ_SMS_TRANSACTION.nextval FROM DUAL
SELECT SEQ_BIZ_EXPRESS.nextval FROM DUAL
SELECT bizgroup.seq_grp_post.NextVal FROM DUAL
SELECT SEQ_SAMPLE.nextval FROM DUAL
SELECT bizgroup.seq_grp_user.NextVal FROM DUAL
SELECT SEQ_BIZ_SEARCHER.nextval FROM DUAL
SELECT SEQ_OFFER_DRAFT.nextval FROM DUAL
select seq_Company_Draft.NextVal from DUAL
SELECT SEQ_SAMPLE_GROUP.nextval FROM DUAL
SELECT SEQ_CMNTY_USER_MESSAGE.nextval FROM DUAL