8
文檔 /
oracle 慢SQL監控測試及監控腳本.pdf
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進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
下載排行榜
文檔被以下合輯收錄
評論