頻繁變化的表無效索引造成的熱點塊爭用

客戶號碼辦理系統出現會話連接數超高告警，造成數據庫性能問題，影響了全網業務辦理。告警發生在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 hh34:mi:ss')

   and sample_time <=

       to_date('2016-11-07 21:00:00', 'yyyy-mm-dd hh34: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 hh34:mi:ss')

   and sample_time <=

       to_date('2016-11-07 21:00:00', 'yyyy-mm-dd hh34: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 hh34:mi:ss')

   and sample_time <=

       to_date('2016-11-07 21:00:00', 'yyyy-mm-dd hh34: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.