监控阻塞 SQL 并分析问题的工作流程 - 案例

查看阻塞 SQL

/* Formatted on 2021/9/15 17:19:32 (QP5 v5.365) */
  SELECT /*+no_unnest*/
            'alter system kill session '''
         || s.sid
         || ','
         || s.SERIAL#
         || ''' immediate;',
         p.SPID,
         q.ROWS_PROCESSED,
         s.USERNAME,
         s.EVENT,
         s.LAST_CALL_ET,
         s.LOCKWAIT,
         s.STATUS,
         q.SQL_FULLTEXT,
         q.SQL_ID,
         s.sid,
         s.BLOCKING_SESSION
    FROM v$session s, v$sql q, v$process p
   WHERE     s.sid > 1
         AND s.PADDR = p.ADDR
         AND q.sql_id(+) = s.sql_id
         AND s.username IS NOT NULL
         AND s.status = 'ACTIVE'
         AND s.event NOT LIKE '%SQL*Net message from client%'
         AND last_call_et > 1
ORDER BY last_call_et DESC, sid

该 SQL 显示的是当前正在执行的 SQL 语句，
如果 BLOCKING_SESSION 有值， 说明有锁的等待，
图中所示为： 1485 等待 1356 的执行；
1356 是 ORDER 同步的接口， 1485 是生产相关操作，
这也反映了现场生产报工业务受到接口影响的实际情况。

找到可疑 SQL 及 SQL_ID

1. 可以根据上述步骤中的 SQL_ID 作为分析的依据。
2. 通过 MES 系统中代码的录制功能，找到具体的 SQL 语句。
3. 执行一遍 SQL。

select * from v$sqlarea where sql_text like '%sap_mes_bom%' ;

找到刚执行的 SQL 的 SQL_ID
可以使用 like 进行查找,
有时可能不太好找, 可以在 SQL 中加入特殊注释,like 中根据注释内容查找.

假设 SQL_ID 为 “fpqaxgjy9yypn”

分析执行计划

通过如下语句获取执行计划

select  * from table (dbms_xplan.display_cursor( 'fpqaxgjy9yypn' , null , 'allstats last' ));

如图，执行计划中重点关注
TABLE ACCESS FULL，
如图，*6，使用的条件是 PROCESSING_GID。 但是没有用到索引， 有问题，需要处理。

采取相应措施

分析执行计划，
具体如何分析，比较复杂，单独介绍。

一般需要采取的措施：
1. 缺索引
2. 统计信息不对
3. 索引失效
4. 分区表, 分区索引问题