暂无图片
暂无图片
5
暂无图片
暂无图片
暂无图片

Oracle 慢SQL监控脚本

原创 布衣&凡尘 2022-09-05
2849

关于秘密不一样的解读:

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

一、数据准备:

-- 解锁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
image.png

– 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的执行时间一致
image.png
– session1:
再次执行一次,未执行结束:insert into EMPLOYEE_TMP select * from EMPLOYEE_TMP;
image.png
– session2:
查询结果:执行次数还是11次,但TIME与总执行时间一直在更新
image.png
直到session1 执行结束后,执行次数才会+1
image.png

四、编写监控脚本:

– 根据上面的实验结果编写慢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到日志中,方便查询。

脚本截图:

image.png

执行结果:

-- 脚本输出:
-- 当前会话有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

                         文章推荐

PostgreSQL URL
《课程笔记:PostgreSQL深入浅出》之 初识PostgreSQL(一) https://www.modb.pro/db/475817
《课程笔记:PostgreSQL深入浅出》之 PostgreSQL源码安装(二) https://www.modb.pro/db/475933
《课程笔记:PostgreSQL深入浅出》之初始化PostgreSQL(三) https://www.modb.pro/db/479524
《课程笔记:PostgreSQL深入浅出》之PSQL管理工具-常用(四) https://www.modb.pro/db/479560
《课程笔记:PostgreSQL深入浅出》之PSQL管理工具-高级命令(四) https://www.modb.pro/db/479559
《课程笔记:PostgreSQL深入浅出》之内存与进程(五) https://www.modb.pro/db/489936
《《课程笔记:PostgreSQL深入浅出》之外存&永久存储(六) https://www.modb.pro/db/502267
Oracle: URL
《Oracle 自动收集统计信息机制》 https://www.modb.pro/db/403670
《Oracle_索引重建—优化索引碎片》 https://www.modb.pro/db/399543
《DBA_TAB_MODIFICATIONS表的刷新策略测试》 https://www.modb.pro/db/414692
《FY_Recover_Data.dbf》 https://www.modb.pro/doc/74682
《Oracle RAC 集群迁移文件操作.pdf》 https://www.modb.pro/doc/72985
《Oracle Date 字段索引使用测试.dbf》 https://www.modb.pro/doc/72521
《Oracle 诊断案例 :因应用死循环导致的CPU过高》 https://www.modb.pro/db/483047
《Oracle 慢SQL监控脚本》 https://www.modb.pro/db/479620
《Oracle 慢SQL监控测试及监控脚本.pdf》 https://www.modb.pro/doc/76068
《Oracle 脚本实现简单的审计功能》 https://www.modb.pro/db/450052
Greenplum: URL
《PL/Java.pdf》 https://www.modb.pro/doc/70867
《GP的资源队列.pdf》 https://www.modb.pro/doc/67644
《Greenplum psql客户端免交互执行SQL.pdf》 https://www.modb.pro/doc/69806
                       欢迎赞赏支持或留言指正
最后修改时间:2022-09-27 21:29:05
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
1人已赞赏
Z
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论