8
oracle 慢SQL监控测试及监控脚本.pdf
10墨值下载
一、数据准备：
-- 解锁scott用户
1 alter user scott account unlock;
2 alter user scott identified by tiger;
-- 创建临时表EMPLOYEE_TMP ：
1 create table EMPLOYEE_TMP as select * from EMPLOYEE;
-- 查看数据量：
1 09:03:03 SQL> select count(*) from EMPLOYEE_TMP;
2 COUNT(*)
3 ----------
4 10000
5 Elapsed: 00:00:03.11
二、慢SQL查询语句：
1 col SQL_FULLTEXT for a50
2 col 平均执行时间 for a10
3 col 总执行时间 for a10
4 col SQL_ID for a15
5 COL OSUSER FOR A10
6 col USERNAME for a10
7 select
8 to_char(sa.last_active_time,'hh24:mi:ss') time,
9 se.osuser,
10 se.username,
11 se.sql_id,
12 sa.sql_fulltext,
13 sa.executions "执行次数",
14 round(sa.ELAPSED_TIME / 1000000, 2) || 's' "总执行时间",
15 round(sa.ELAPSED_TIME / 1000000 / sa.executions, 2) || 's' "平均执行时间"
16 from (select s.osuser, s.username, s.sql_id
17 from v$session s
18 where s.username in ('SCOTT') -- 用户名
19 and s.sql_id is not null
20 group by s.osuser, s.username, s.sql_id) se
21 left join v$sqlarea sa
22 on se.sql_id = sa.sql_id
23 where sa.executions > 0
24 and round(sa.ELAPSED_TIME / 1000000 / sa.executions, 2) > 0 -- 平均执行时间大于0
25 and sa.last_active_time > trunc(sysdate); -- 查询当天的数据
26 TIME OSUSER USERNAME SQL_ID SQL_FULLTEXT
27 -------- ---------- ---------- --------------- -----------------------------------------
28 09:03:27 oracle SCOTT 6rmnz1gbfhd3j select count(*) from EMPLOYEE_TMP
三、开始测试：
-- session 1：执行：11次执行
1 09:11:08 SQL> insert into EMPLOYEE_TMP select * from EMPLOYEE_TMP;
2 10000 rows created.
3 Elapsed: 00:00:00.04
4 09:11:32 SQL> /
5 20000 rows created.
6 Elapsed: 00:00:00.01
7 09:11:44 SQL> /
8 40000 rows created.
9 Elapsed: 00:00:00.03
10 09:11:45 SQL> /
11 80000 rows created.
12 Elapsed: 00:00:00.05
13 09:11:45 SQL> /
14 160000 rows created.
15 Elapsed: 00:00:00.09
16 09:11:46 SQL> /
17 320000 rows created.
18 Elapsed: 00:00:00.19
19 09:11:47 SQL> /
20 640000 rows created.
21 Elapsed: 00:00:00.31
22 09:11:47 SQL> /
23 1280000 rows created.
24 Elapsed: 00:00:00.64
25 09:11:49 SQL> /
26 2560000 rows created.
27 Elapsed: 00:00:01.06
of 7
10墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
文档被以下合辑收录
评论