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

客戶号碼辦理系統出現會話連接配接數超高告警，造成資料庫性能問題，影響了全網業務辦理。告警發生在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 ;

<a href="http://s2.51cto.com/wyfs02/M02/8A/FF/wKiom1hA4KTh2DU0AAAwbRdgTgk817.jpg-wh_500x0-wm_3-wmp_4-s_1195237923.jpg" target="_blank"></a>

根據SQL_id檢視對應時間點所産生的阻塞熱點塊

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

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

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

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

<a href="http://s2.51cto.com/wyfs02/M00/8A/FF/wKiom1hA4OqQQDltAABlGVNSjeA208.jpg-wh_500x0-wm_3-wmp_4-s_2983547862.jpg" target="_blank"></a>

找出主要的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 &gt;=

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

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

<a href="http://s4.51cto.com/wyfs02/M02/8A/FB/wKioL1hA4SqhFVBXAACL0NX7zhE975.jpg" target="_blank"></a>

找出對應的熱點塊為：21463、16199、16215

根據熱點塊找到到底是表還是索引引起的争用

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

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

通過抓取當時20點--21點AWR快照資訊也印證了這一點；

檢視此索引建立的列為'ALTER_TYPE', 'SERIAL_NUMBER'檢視該表的資料量資訊懷疑該表變化特别頻繁：

<a href="http://s3.51cto.com/wyfs02/M02/8A/FF/wKiom1hA4gyBUdFHAABBIzFfJRI050.jpg" target="_blank"></a>

該表在7号22點已經收集過統計資訊。但是實際上的表内資料為14行資料：

<a href="http://s1.51cto.com/wyfs02/M01/8A/FF/wKiom1hA4mOB5szIAADC31u-OjE585.jpg" target="_blank"></a>

看見了嗎，隻有14行資料，但是統計資訊收集後顯示NUW_ROWS為11228。說明這個表變化還是特别頻繁的。

随即決定删除該無用索引，一個表内僅有14條資料。且該表insert、delete特别頻繁。走索引反而适得其反，删除該無效索引UCR_TRADE_03.IDX_SYNC_PHCODE_IDLE_1。

<a href="http://s5.51cto.com/wyfs02/M01/8A/FB/wKioL1hA4uPyTe1-AAFPFf56zTI101.jpg" target="_blank"></a>

附錄：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

      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

         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