mysql 数据热点块,查看Oracle热点块以及热点对象对应的SQL

！！没有相关优化知识的筒子们请慎用以下脚本！！

一、查看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 

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 

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