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

偏执的太偏执、 2022-07-14 09:11 126阅读 0赞

客户号码办理系统出现会话连接数超高告警，造成数据库性能问题，影响了全网业务办理。告警发生在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.

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL：如何避免因表结构频繁变动造成的困扰

相关 MySQL索引缺失或无效造成的查询效率低下

相关 Java中ArrayIndexOutOfBoundsException: 无效的索引

相关 mysql 数据热点块_深度分析数据库的热点块问题

相关【链表】链表变化时其中的节点变化情况

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

相关 MYSQL索引无效和索引有效的详细介绍

相关 mysql可能造成索引失效的场景

相关 Mysql 索引无效

相关 mysql可能造成索引失效的场景