Oracle 慢SQL监控脚本

关于秘密不一样的解读：

秘密之所以是秘密是因为它不可告人，于我把它写下来了…

一、数据准备：

-- 解锁scott用户
alter user scott account unlock;
alter user scott identified by tiger; 

-- 创建临时表EMPLOYEE_TMP ：
 create table EMPLOYEE_TMP as select * from EMPLOYEE;

-- 查看数据量：
09:03:03 SQL> select count(*) from EMPLOYEE_TMP;
  COUNT(*)
----------
     10000
Elapsed: 00:00:03.11

二、查看当前会话执行的慢SQ语句：

col SQL_FULLTEXT for a50
col 平均执行时间 for a10
col 总执行时间 for a10
col SQL_ID for a15
COL OSUSER FOR A10
col USERNAME for a10
select 
       to_char(sa.last_active_time,'hh24:mi:ss') time,
       se.osuser,
       se.username,
       se.sql_id,
       sa.sql_fulltext,
       sa.executions "执行次数",
       round(sa.ELAPSED_TIME / 1000000, 2) || 's' "总执行时间",
       round(sa.ELAPSED_TIME / 1000000 / sa.executions, 2) || 's' "平均执行时间"
  from (select s.osuser, s.username, s.sql_id
          from v$session s
         where s.username in ('SCOTT') -- 用户名
           and s.sql_id is not null
         group by s.osuser, s.username, s.sql_id) se
  left join v$sqlarea sa
    on se.sql_id = sa.sql_id
 where sa.executions > 0
   and round(sa.ELAPSED_TIME / 1000000 / sa.executions, 2) > 0 -- 平均执行时间大于0
   and sa.last_active_time > trunc(sysdate); -- 查询当天的数据
TIME     OSUSER     USERNAME   SQL_ID          SQL_FULLTEXT                                         执行次数 总执行时间 平均执行时
-------- ---------- ---------- --------------- -------------------------------------------------- ---------- ---------- ----------
09:03:27 oracle     SCOTT      6rmnz1gbfhd3j   select count(*) from EMPLOYEE_TMP                           1 3.1s       3.1s

三、开始测试：

-- session 1：执行：11次执行
09:11:08 SQL> insert into EMPLOYEE_TMP select * from EMPLOYEE_TMP;
10000 rows created.
Elapsed: 00:00:00.04
09:11:32 SQL> /
20000 rows created.
Elapsed: 00:00:00.01
09:11:44 SQL> /
40000 rows created.
Elapsed: 00:00:00.03
09:11:45 SQL> /
80000 rows created.
Elapsed: 00:00:00.05
09:11:45 SQL> /
160000 rows created.
Elapsed: 00:00:00.09
09:11:46 SQL> /
320000 rows created.
Elapsed: 00:00:00.19
09:11:47 SQL> /
640000 rows created.
Elapsed: 00:00:00.31
09:11:47 SQL> /
1280000 rows created.
Elapsed: 00:00:00.64
09:11:49 SQL> /
2560000 rows created.
Elapsed: 00:00:01.06
09:11:50 SQL> /
5120000 rows created.
Elapsed: 00:00:06.57
09:11:58 SQL> /
10240000 rows created.
Elapsed: 00:00:12.63

– session2:

通过慢SQL语句查询到：sql_id:687546kf2qazt 执行11次，总时间：21.62s,平均：1.96s

– session1:
执行一次查询：执行时间：00:00:00.45

09:12:20 SQL> select count(*) from EMPLOYEE_TMP;
  COUNT(*)
----------
  20480000
Elapsed: 00:00:00.45
09:16:18 SQL> 

– session2：
SQL_ID：6rmnz1gbfhd3j 执行一次，总时间0.45s，与session1的执行时间一致

– session1:
再次执行一次，未执行结束：insert into EMPLOYEE_TMP select * from EMPLOYEE_TMP;

– session2:
查询结果：执行次数还是11次，但TIME与总执行时间一直在更新

直到session1 执行结束后，执行次数才会+1

四、编写监控脚本：

– 根据上面的实验结果编写慢SQL监控脚本思路：
1、以平均时间为条件，大于1s（自己决定）的进行报警。
（因为是平均值，针对于执行次数过多的会延时报警，存在一定的误差）。
2、SQL 未执行结束，平均时间也一直在更新。
3、在v$session视图取的SQL_ID,监控会话正在执行的SQL。
4、username in (‘SCOTT’) 指定用户名，避免出现系统SQL。
5、监控脚本输出格式：慢SQL:条数|阀值
示例：SlowSQLNums:3|1s
解释：超过1s的慢SQL有3条
6、打印详细的慢SQL到日志中，方便查询。

脚本截图：

执行结果：

-- 脚本输出：
-- 当前会话有1条慢SQL
[oracle@db~]$ sh Check_SlowSQL.sh 
Error|SlowSQLNums:1|0s
-- 当前会话没有慢SQL
[oracle@db~]$ sh Check_SlowSQL.sh  
ok
-- 临时输出文件
[oracle@db~]$ cat SlowSQL.tmp
TIME:09:20:30|OSUSER:oracle|USERNAME:SCOTT|SQL_ID:687546kf2qazt|AVERAGE_ELAPSED_TIME:3.45|SQLText:insert into EMPLOYEE_TMP select * from EMPLOYEE_TMP
TIME:11:18:27|OSUSER:oracle|USERNAME:SCOTT|SQL_ID:6rmnz1gbfhd3j|AVERAGE_ELAPSED_TIME:1.81|SQLText:select count(*) from EMPLOYEE_TMP
-- 历史慢SQL输出文件
[oracle@db~]$ cat SlowSQL.his
TIME:09:20:30|OSUSER:oracle|USERNAME:SCOTT|SQL_ID:687546kf2qazt|AVERAGE_ELAPSED_TIME:3.45|SQLText:insert into EMPLOYEE_TMP select * from EMPLOYEE_TMP
TIME:09:20:30|OSUSER:oracle|USERNAME:SCOTT|SQL_ID:687546kf2qazt|AVERAGE_ELAPSED_TIME:3.45|SQLText:insert into EMPLOYEE_TMP select * from EMPLOYEE_TMP
TIME:09:20:30|OSUSER:oracle|USERNAME:SCOTT|SQL_ID:687546kf2qazt|AVERAGE_ELAPSED_TIME:3.45|SQLText:insert into EMPLOYEE_TMP select * from EMPLOYEE_TMP
TIME:11:18:27|OSUSER:oracle|USERNAME:SCOTT|SQL_ID:6rmnz1gbfhd3j|AVERAGE_ELAPSED_TIME:1.81|SQLText:select count(*) from EMPLOYEE_TMP
TIME:09:20:30|OSUSER:oracle|USERNAME:SCOTT|SQL_ID:687546kf2qazt|AVERAGE_ELAPSED_TIME:3.45|SQLText:insert into EMPLOYEE_TMP select * from EMPLOYEE_TMP
TIME:11:18:27|OSUSER:oracle|USERNAME:SCOTT|SQL_ID:6rmnz1gbfhd3j|AVERAGE_ELAPSED_TIME:1.81|SQLText:select count(*) from EMPLOYEE_TMP

                         文章推荐