频繁变化的表无效索引造成的热点块争用
客户号码办理系统出现会话连接数超高告警，造成数据库性能问题，影响了全网业务办理。告警发生在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.
原文 http://yangjunfeng.blog.51cto.com/539796/1878793正文到此结束
热门推荐
相关文章
Loading...
![[HBLOG]公众号](http://www.liuhaihua.cn/img/qrcode_gzh.jpg)
