【DB笔试面试671】在Oracle中，如何监控数据库中的非常耗费性能SQL语句？

小麦苗DBA宝典

发布于 2019-10-30 11:22:29

1.7K0

发布于 2019-10-30 11:22:29

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

题目部分

在Oracle中，如何监控数据库中的非常耗费性能SQL语句？

答案部分

在前边的触发器章节中介绍了如何利用系统触发器监控用户的登陆登出问题，并且可以记录所有的数据库DDL语句，这对数据库的安全审计是非常有帮助的。利用触发器还可以限制用户在某一段固定时间才能登陆数据库。接下来介绍一下如何利用SQL的实时监控特性来监控数据库中的非常耗费性能SQL语句。

由于V$SQL_MONITOR和V$SQL_PLAN_MONITOR收集的信息每秒刷新一次，接近实时。当SQL执行完毕，信息并不会立即从V$SQL_MONITOR中删除，至少会保留1分钟。所以，根据这两个视图的数据来源及保留策略，可以写一个轻量级的JOB来针对性的监控这两个视图。从而可以实现监控：执行时间超过N小时、笛卡尔积SQL监控、分区表全分区扫描、SQL执行次数、解析次数过大、占用CPU过大等类型的SQL监控。至于消耗小于5秒的CPU或I/O时间的SQL语句一般都是非常高效的，所以不用监控。

考虑到定时任务对Oracle数据库性能的影响，所以，可以通过Oracle的轻量级JOB来实现的。Oracle的JOB分为一般性的JOB和轻量级的JOB（Lightweight Jobs）。使用轻量级的JOB可以提高JOB的性能。因为轻量级JOB适合于在短时间内执行非常频繁的JOB。

整个监控脚本如下所示：

该脚本中有视图也有表。若想直接查询数据库耗费性能的SQL语句，可以直接使用视图VW_SQL_PP_LHR进行查询。若想查询历史记录，则可以通过表XB_SQL_MONITOR_PP_LHR来查询。另外，对于监控中使用的参数表为XB_SQL_PARAMETERS_LHR。JOB每次都会从该表中读取到配置参数的值，该表的查询结果如下图所示：

下面简单测试一下上边的监控脚本的效果。首先构造一个笛卡尔积连接的SQL，并开启并行。再构造一个锁等待的SQL。如下所示的3条SQL语句：

① SELECT /*+ monitor parallel(20)*/ COUNT(*) 
   FROM DBA_OBJECTS A,DBA_OBJECTS B,DBA_OBJECTS C,DBA_OBJECTS D;
② UPDATE  /*+ MONITOR */ XB_SQL_MONITOR_LHR T SET T.SQL_TEXT='XXXXXXXXXXXX';
③ UPDATE  /*+ MONITOR */ XB_SQL_MONITOR_LHR T SET T.SQL_TEXT='XXXXXXXXXXXX';

让以上3条SQL在不同的会话运行，等待大约5分钟后然后查看监控效果。

首先查询视图VW_SQL_PP_LHR：

从结果可以很明显的看出，会话（28，583）在等待锁，而会话（133,437）阻塞了会话（28，583）。对于会话（29，1207）可以看出，由于开了20个并行，所以导致系统CPU不足，所有的会话均在等待CPU资源，而且该会话的SQL语句产生了笛卡尔积、并行数过多、COST花费过大等问题。

对于该JOB的性能，由于作者从多个方面做了优化，所以基本不影响数据库的运行。下面是该JOB的运行日志：

SELECT JRD.LOG_ID,
       JRD.JOB_NAME,
       N.JOB_CLASS,
      TO_CHAR(JRD.ACTUAL_START_DATE, 'YYYY-MM-DD HH24:MI:SS') ACTUAL_START_DATE,
       TO_CHAR(JRD.LOG_DATE, 'YYYY-MM-DD HH24:MI:SS') LOG_DATE,
       JRD.STATUS,
       JRD.ERROR#,
       JRD.RUN_DURATION 运行时长,
       JRD.ADDITIONAL_INFO
  FROM DBA_SCHEDULER_JOB_LOG N, DBA_SCHEDULER_JOB_RUN_DETAILS JRD
 WHERE N.LOG_ID = JRD.LOG_ID 
   AND N.JOB_NAME LIKE 'JOB_SQL_%' 
 ORDER BY JRD.LOG_ID DESC;

JOB运行日志如下图所示：