热点块竞争和解决--cache buffers chains 3(转载)

      select object_name
      from dba_objects
      where data_object_id in
            (select obj
             from x$bh
             where hladdr in
                  (select addr
                   from (select addr
                         from v$latch_children
                         order by sleeps desc)
                   where rownum < 11
                   )
              )
      ;

      OBJECT_NAME
      ------------------------------------
      I_CCOL2
      RESOURCE_PLAN$
      DUAL
      FGA_LOG$
      AV_TRANSACTION
      COMPANY_DRAFT
      MEMBER
      SAMPLE
      SAMPLE_GROUP
      VERTICAL_COMPONENT
      MEMBER_PK
      SAMPLE_GROUP_PK
      IM_BLACKLIST_PK
      IM_CONTACT
      IM_GROUP
      CMNTY_USER_MESSAGE
      CMNTY_VISITOR_INFO_PK
      IM_OFFLINEMSG_TID_IND
      OFFER
      OFFER_PK
      OFFER_EMAIL_IND
      OFFER_DRAFT
      CMNTY_USER_MESSAGE_TD_BSM_IND
      CMNTY_MESSAGE_NUM_PK
      BIZ_EXPRESS_MEMBER_ID_IND
      ……………………
      到这里我们基本能找到热点块对对应的对象。但实际上还有另外一个途径来获取这些信息，那就是和x$bh.tch
      相关的一种方法。对于8i开始oracle提供了接触点(touch
      count)来作为block是冷热的标志，在一定条件满足的情况下block被进程访问一次touch count
      增加一，到某个标准之后被移动到LRU热端(关于touch count
      在这里不做详细介绍，那又将是一大篇文章)。那在短时间内从某种意义上讲，touch count
      大的block可能暗示着在当前某个周期内被访问次数比较多。
      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;
      OWNER                           SEGMENT_NAME                   
      SEGMENT_TYPE
      ------------------------------ ------------------------------
      ------------------
      ALIBABA                         CMNTY_USER_MESSAGE              TABLE
      ALIBABA                         MEMBER_PK                       INDEX
      ALIBABA                         OFFER_DRAFT_GMDFY_IND           INDEX
      同上面一样还有这个方法
      select object_name
      from dba_objects
      where data_object_id in
             (select obj
              from (select obj
                    from x$bh order by tch desc)
              where rownum < 11) ;

      OBJECT_NAME
      ---------------------------------------------------
      DUAL
      MEMBER_PK
      SAMPLE_GROUP_PK
      CMNTY_USER_MESSAGE_TD_BSM_IND
      OFFER_DRAFT_MID_GMDFY_IND
      OFFER_MID_GPOST_IND
      OFFER_DRAFT_PK
      MEMBER_GLLOGIN_IND
      OFFER_MID_STAT_GEXPIRE_IND
      SAMPLE_MID_STAT_IND
      10 rows selected.

      到这里，我们寻找热点块和热点对象的工作算是完成了，但我们还并没有解决问题。
      热点问题的解决
         热点块和热点对象我们都找到了，但是我们该怎么来解决这个问题呢？一般来说，热点块会导致cache buffers
      chains竞争等待，但并不是说cache buffer
      chains一定是因为热点块而起，在特别情况下有可能是因为latch数量的问题导致的，也就是一个latch管理的buffers数量太多而导致竞争激烈。但是latch数量我们一般是不会轻易去设置的，这是oracle的隐藏参数。
        
      实际上最有效的办法，是从优化sql入手，不良的sql往往带来大量的不必要的访问，这是造成热点块的根源。比如本该通过全表扫描的查询却走了索引的range
      scan，这样将带来大量的对块的重复访问。从而形成热点问题。再或者比如不当地走了nested
      loops的表连接，也可能对非驱动表造成大量的重复访问。那么在这个时候，我们的目标就是找出这些sql来并尝试优化。在statspack报告中，根据报告中sql列表，我们如果是通过dba_extents确定的热点对象而不是通过dba_objects确定的，则可以通过查找出的热点segment转换为对应的表，对于非分区的索引，index_name就是segment_name,通过dba_indexes很容易的找到对应的table_name,对于分区表和分区索引也能通过和dba_tab_partition和dba_ind_partitions找到segment和table的对应关系。通过这些table到statspack报告中去找相关的sql。
      select sql_text
      from stats$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
      SELECT SYSDATE FROM DUAL
      select seq_News_Forum.NextVal from DUAL
      SELECT SEQ_SMS_USER.nextval FROM DUAL
      select seq_Biz_Member.NextVal from DUAL
      select seq_Pymt_Managing.NextVal from DUAL
      E= '+08:00' NLS_DUAL_CURRENCY = '$' NLS_TIME_FORMAT = 'HH.MI.SSX
      SELECT SEQ_COMPANY_DRAFT.nextval FROM DUAL
      SELECT 1 FROM DUAL
      select seq_offer_draft.NextVal from DUAL
      select seq_Biz_Express_Category.NextVal from DUAL
      20 rows selected.
        
       
      除了优化sql外，当然对于热点的表或者索引来说，如果小的话，我们可以考虑cache在内存中，这样可能降低物理读提高sql运行速度(这并不会减少cache
      buffer
      chains的访问次数)，对于序列，我们可以对序列多设置一些cache。如果是并行服务器环境中的索引对象，并且这个索引是系列递增类型，我们可以考虑反向索引(关于反向索引这里就不过多地做介绍了)。

 

 

 

 

from:http://hi.baidu.com/xingyundaocao/blog/item/15c9173fa57b72eb55e72320.html