频繁变化的表无效索引造成的热点块争用

客户号码办理系统出现会话连接数超高告警，造成数据库性能问题，影响了全网业务办理。告警发生在11月7日20点--21点时间段，查询当时等待事件最高的buffer busy waits。

查询该等待事件对应的sql；

select sql_id, count(*)

  from v$active_session_history

 where sample_time >=

       to_date('2016-11-07 20:00:00', 'yyyy-mm-dd hh24:mi:ss')

   and sample_time <=

       to_date('2016-11-07 21:00:00', 'yyyy-mm-dd hh24:mi:ss')

   and event = 'buffer busy waits'

 group by sql_id order by 2 desc ;

根据SQL_id查看对应时间点所产生的阻塞热点块

select a.BLOCKING_SESSION,count(*) from gv$active_session_history a where sql_id='5qhcs0sc47t5t' and  sample_time >=

       to_date('2016-11-07 20:00:00', 'yyyy-mm-dd hh24:mi:ss')

   and sample_time <=

       to_date('2016-11-07 21:00:00', 'yyyy-mm-dd hh24:mi:ss')

   and event = 'buffer busy waits' group by a.BLOCKING_SESSION;

找出主要的BLOKING_SESSION为2830,3994,4252,4107.

根据找到的BLOKING_SESSION找到当时争用的热点块

   select sql_id,p1,a.p1text,p2,p2text,p3,p3text,count(*) from v$active_session_history a where  sample_time >=

       to_date('2016-11-07 20:00:00', 'yyyy-mm-dd hh24:mi:ss')

   and sample_time <=

       to_date('2016-11-07 21:00:00', 'yyyy-mm-dd hh24:mi:ss')

       and a.SESSION_ID in (2830,3994,4252,4107)

 group by sql_id,p1,a.p1text,p2,p2text,p3,p3text;

找出对应的热点块为：21463、16199、16215

根据热点块找到到底是表还是索引引起的争用

select * from DBA_EXTENTS where FILE_ID = &AFN and &BL between BLOCK_ID and BLOCK_ID + BLOCKS - 1;

&AFN$BL代入上面查到的值AFN=169，BL为21463、16199、16215是UCR_TRADE_03.IDX_SYNC_PHCODE_IDLE_1索引

通过抓取当时20点--21点AWR快照信息也印证了这一点；

查看此索引创建的列为'ALTER_TYPE', 'SERIAL_NUMBER'查看该表的数据量信息怀疑该表变化特别频繁：

该表在7号22点已经收集过统计信息。但是实际上的表内数据为14行数据：

看见了吗，只有14行数据，但是统计信息收集后显示NUW_ROWS为11228。说明这个表变化还是特别频繁的。

随即决定删除该无用索引，一个表内仅有14条数据。且该表insert、delete特别频繁。走索引反而适得其反，删除该无效索引UCR_TRADE_03.IDX_SYNC_PHCODE_IDLE_1。

附录：ADDM建议信息：

SQL statements consuming significant database time were found.

   RECOMMENDATION 1: SQL Tuning, 88% benefit (726535 seconds)

      ACTION: Investigate the SQL statement with SQL_ID "5qhcs0sc47t5t" for

         possible performance improvements.

         RELEVANT OBJECT: SQL statement with SQL_ID 5qhcs0sc47t5t and

         PLAN_HASH 2432174272

         UPDATE  TF_R_PHCODE_IDLE R              SET     R.UPDATE_TIME =

         SYSDATE,                     R.SALE_SYSTEM_TAG = '2'

         WHERE   R.SERIAL_NUMBER = :1             AND     R.PROVINCE_CODE = :2

      RATIONALE: SQL statement with SQL_ID "5qhcs0sc47t5t" was executed 3887

         times and had an average elapsed time of 186 seconds.

      RATIONALE: Waiting for event "buffer busy waits" in wait class

         "Concurrency" accounted for 92% of the database time spent in

         processing the SQL statement with SQL_ID "5qhcs0sc47t5t".

      RATIONALE: Waiting for event "enq: TX - row lock contention" in wait

         class "Application" accounted for 5% of the database time spent in

         processing the SQL statement with SQL_ID "5qhcs0sc47t5t".

      RATIONALE: Waiting for event "enq: TX - contention" in wait class

         "Other" accounted for 1% of the database time spent in processing the

         SQL statement with SQL_ID "5qhcs0sc47t5t".

   RECOMMENDATION 2: SQL Tuning, 87% benefit (721075 seconds)

      ACTION: Investigate the SQL statement with SQL_ID "b08xxahpxcak4" for

         possible performance improvements.

         RELEVANT OBJECT: SQL statement with SQL_ID b08xxahpxcak4

         INSERT INTO SYNC_PHCODE_IDLE (CHNL_NO ,ROW_ID ,ALTER_TIME ,ALTER_TYPE

         , SERIAL_NUMBER ,CODE_REVERSE ,NET_TYPE_CODE ,BRAND_CODE ,IMSI ,

         SIM_CARD_NO ,CODE_STATE ,TRADE_CATE ,CODE_GRADE ,LIMIT_ID , NICE_RULE

         ,GROUP_ID ,PROVINCE_CODE ,EPARCHY_CODE ,CITY_CODE , DEPART_ID

         ,CHANNEL_ID ,STAFF_ID ,STOCK_ID ,STOCK_LEVEL , POOL_ID ,ECS_TAG

         ,BATCH_DEF_TAG ,BATCH_ID ,STAFF_IN ,TIME_IN , STAFF_UPSHELF

         ,TIME_UPSHELF ,STAFF_DOWNSHELF ,TIME_DOWNSHELF , OCCUPY_TIME

         ,REUSE_COUNT ,OPER_BATCH_ID ,OPER_DEPART_ID , OPER_STAFF_ID

         ,OPER_TIME ,ASSIGN_BATCH_ID ,ASSIGN_TAG , CONFIRM_TAG

         ,ASSIGN_STAFF_ID ,ASSIGN_TIME ,OPEN_DEPART_ID , OPEN_STAFF_ID

         ,BACK_STAFF_ID ,BACK_TIME ,UPDATE_STAFF , UPDATE_TIME ,RSVALUE1

         ,RSVALUE2 ,RSVALUE3 ,RSVALUE4 , RSVALUE5 ,RSVALUE6

         ,WIRELESS_CARD_TYPE ,RELEASE_TIME ,SYS_CODE , PROC_KEY ,PROC_KEY_MODE

         ,USE_TYPE) VALUES (SUBSTR(:B1 ,-2) ,:B2 ,TO_CHAR(SYSTIMESTAMP

         ,'YYYYMMDDHH24MISSFF') ,'UPD' , :B1 ,:B3 ,:B4 ,:B5 ,:B6 , :B7 ,:B8

         ,:B9 ,:B10 ,:B11 , :B12 ,:B13 ,:B14 ,:B15 ,:B16 , :B17 ,:B18 ,:B19

         ,:B20 ,:B21 , :B22 ,:B23 ,:B24 ,:B25 ,:B26 ,:B27 , :B28 ,:B29 ,:B30

         ,:B31 , :B32 ,:B33 ,:B34 ,:B35 , :B36 ,:B37 ,:B38 ,:B39 , :B40 ,:B41

         ,:B42 ,:B43 , :B44 ,:B45 ,:B46 ,:B47 , :B48 ,:B49 ,:B50 ,:B51 ,:B52 ,

         :B53 ,:B54 ,:B55 ,:B56 ,:B57 , :B58 ,:B59 ,:B60 )

      RATIONALE: SQL statement with SQL_ID "b08xxahpxcak4" was executed 9774

         times and had an average elapsed time of 73 seconds.

本文转自yangjunfeng 51CTO博客，原文链接:http://blog.51cto.com/yangjunfeng/1878793