查看Oracle热点块以及热点对象对应的SQL

2019独角兽企业重金招聘Python工程师标准>>>

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

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

转载于:https://my.oschina.net/1272149624/blog/613508