合 Oracle查询长时间运行的SQL语句的剩余时间（慢查询）
Tags： OracleDBA脚本慢查询v$session_longopsV$SQL_MONITOR长时间运行的SQL
👉 本文共约1450个字，系统预计阅读时间或需6分钟。
Oracle数据库中如何查找、定位一些正在执行但是运行时间很长的SQL语句(long running sql)呢？注意，我们这里只查看正在执行long running sql,而不是查询历史执行时间很长的SQL语句（请不要混淆）。大概有下面一些方法供参考。
方法1：通过V$SESSION_LONGOPS来查找
在Oracle中，可以使用视图v$session_longops查询运行时间较长的SQL语句，可以查询到预估的剩余执行时间，这包括常规的查询SQL语句，建表，rman备份和还原等操作，都可以查询。
V$SESSION_LONGOPS 显示运行时间超过 6 秒（以绝对时间为单位）的各种操作的状态。这个视图不但可以监控运行时间比较长的SQL，也会记录RMAN备份、EXP/EXPDP、收集统计信息、排序等操作，基本数据库所有运行时间超过6秒的SQL都会记录在这个视图中。
如果要使用V$SESSION_LONGOPS的话，也必须满足下面两个条件。
1 2 3 4 5 | -- Set the TIMED_STATISTICS or SQL_TRACE parameters to true 必须将初始化参数 timed_statistics设置为true（默认为true）或者开启sql_trace -- Gather statistics for your objects with the DBMS_STATS package 必须用ANALYZE或者DBMS_STATS包对对象收集过统计信息。 |
注意，使用V$SESSION_LONGOPS来查找long runnging sql,可能会遗漏一些SQL，因为比如某个SQL语句执行时间比较长，但是每个操作都没有超过6秒。那么这个SQL可能不会被捕获到。
下面是具体的查询SQL语句：
可直接使用的SQL语句
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | -- 可直接使用 SET LINE 9999 PAGESIZE 9999 col username format a10 col OSUSER format a10 col target format a20 col opname format a30 col WAIT_CLASS format a20 col wait_event format a30 col message format a80 col SQL_TEXT format a100 col sofar_TOTALWORK format a20 col session_info format a15 col progress format a8 SELECT a.USERNAME, (SELECT upper(nb.OSUSER) FROM v$session nb WHERE nb.SID = a.sid) OSUSER, (SELECT nb.sid || ',' || nb.SERIAL# || ',' || pr.SPID FROM v$process pr, v$session nb WHERE nb.PADDR = pr.ADDR and nb.sid = a.SID and nb.SERIAL# = a.SERIAL#) session_info, a.opname, to_char(a.START_TIME, 'YYYY-MM-DD HH24:MI:SS') start_time, round(a.SOFAR * 100 / a.TOTALWORK, 2) || '%' AS progress, a.TIME_REMAINING TIME_REMAINING, a.elapsed_seconds elapsed_seconds, message message, (SELECT nb.EVENT FROM V$session_Wait nb WHERE nb.SID = a.SID) wait_event, (SELECT nb.STATUS FROM v$session nb WHERE nb.SID = a.SID) STATUS FROM v$session_longops a WHERE a.time_remaining <> 0 ORDER BY status, a.TIME_REMAINING DESC, a.SQL_ID, a.sid; -- 或者 set lines 1080 pagesize 1000; col inst_id for 99; col con_id for 99; col sid for 99999; col serial# for 99999; col sql_text for a45 trunc col sql_id for a14; col remaining_time for a6 head "REMAIN_TIME" col elapsed_time for a6 head "RUN|DURATION" alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; select s.inst_id ,s.con_id ,s.sid ,s.serial# ,sql.sql_id ,sql.sql_text ,start_time -- ,last_update_time ,round(op.time_remaining/60) || ':' || mod(op.time_remaining,60) as remaining_time ,round(op.elapsed_seconds/60) || ':' || mod(op.elapsed_seconds,60) as elapsed_time ,round((sofar/totalwork) * 100,2) as pct_done from gv$session s, gv$sqlarea sql, gv$session_longops op where s.sid=op.sid and s.sql_id = sql.sql_id and s.sid = op.sid and s.status = 'ACTIVE' and op.totalwork > op.sofar and s.sid not in (select distinct sid from gv$mystat where rownum < 2) order by 4 desc; -- 通过上面SQL语句获取的SQL_ID，找到具体的SQL语句 select sql_fulltext from V$sql where sql_id='&sql_id'; |
需要包
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 | -- 需要包 create or replace FUNCTION fun_get_total_time_lhr(p_total_seconds NUMBER, p_flag VARCHAR2 DEFAULT 'S') RETURN VARCHAR2 DETERMINISTIC IS v_tmp FLOAT(18); returns VARCHAR2(200); V_total_seconds NUMBER := p_total_seconds; BEGIN IF p_total_seconds IS NULL OR p_total_seconds = 0 THEN RETURN 0 || 's'; END IF; IF UPPER(p_flag) = 'D' THEN V_total_seconds := p_total_seconds * 24 * 60 * 60; END IF; v_tmp := trunc(V_total_seconds / (12 * 30 * 24 * 3600)); IF v_tmp > 0 THEN returns := v_tmp || 'y'; END IF; v_tmp := trunc(MOD(V_total_seconds, (12 * 30 * 24 * 3600)) / (3600 * 24 * 30)); IF v_tmp > 0 THEN returns := returns || v_tmp || 'm'; END IF; v_tmp := trunc(MOD(V_total_seconds, (3600 * 24 * 30)) / (3600 * 24)); IF v_tmp > 0 THEN returns := returns || v_tmp || 'd'; END IF; v_tmp := trunc(MOD(V_total_seconds, 3600 * 24) / 3600); IF v_tmp > 0 THEN returns := returns || v_tmp || 'h'; END IF; v_tmp := trunc(MOD(V_total_seconds, 3600) / 60); IF v_tmp > 0 THEN returns := returns || v_tmp || 'mi'; END IF; v_tmp := trunc(MOD(V_total_seconds, 60), 3); IF v_tmp > 0 AND v_tmp < 1 THEN returns := '0' || v_tmp || 's'; ELSIF v_tmp >= 1 THEN returns := returns || v_tmp || 's'; ELSIF v_tmp = 0 AND returns IS NULL THEN returns := '0' || trunc(V_total_seconds, 6) || 's'; END IF; RETURN returns; END fun_get_total_time_lhr; / SET LINE 9999 PAGESIZE 9999 col username format a10 col OSUSER format a10 col target format a20 col opname format a30 col WAIT_CLASS format a20 col wait_event format a30 col message format a80 col SQL_TEXT format a100 col TIME_REMAINING format a20 col sofar_TOTALWORK format a20 col elapsed_seconds format a20 col session_info format a15 col progress format a8 SELECT a.USERNAME, (SELECT upper(nb.OSUSER) FROM v$session nb WHERE nb.SID = a.sid) OSUSER, (SELECT nb.sid || ',' || nb.SERIAL# || ',' || pr.SPID FROM v$process pr, v$session nb WHERE nb.PADDR = pr.ADDR and nb.sid = a.SID and nb.SERIAL# = a.SERIAL#) session_info, a.target, a.opname, to_char(a.START_TIME, 'YYYY-MM-DD HH24:MI:SS') start_time, round(a.SOFAR * 100 / a.TOTALWORK, 2) || '%' AS progress, fun_get_total_time_lhr(a.TIME_REMAINING) TIME_REMAINING, (a.sofar || ':' || a.TOTALWORK) sofar_TOTALWORK, fun_get_total_time_lhr(a.elapsed_seconds) elapsed_seconds, message message, (SELECT nb.EVENT FROM V$session_Wait nb WHERE nb.SID = a.SID) wait_event, (SELECT nb.STATUS FROM v$session nb WHERE nb.SID = a.SID) STATUS FROM v$session_longops a WHERE a.time_remaining <> 0 ORDER BY status, a.TIME_REMAINING DESC, a.SQL_ID, a.sid; |
结果示例
如下结果是我做rman备份，开了4个并行进程，结果：
1 2 3 4 5 6 7 8 9 10 | SQL> / USERNAME OSUSER SESSION_INFO TARGET OPNAME START_TIME PROGRESS TIME_REMAINING SOFAR_TOTALWORK ELAPSED_SECONDS MESSAGE WAIT_EVENT STATUS -------- ---------- --------------- ----------- ------------------------------ ---------------------- -------- -------------- -------------------- ---------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------ SYS ORACLE 741,10325,55494 19 RMAN: full datafile backup 2021-09-21 08:04:17 36.16% 3h9mi19s 6877686:19021946 1h47mi13s RMAN: full datafile backup: Set Count 19: 6877686 out of 19021946 Blocks done RMAN backup & recovery I/O ACTIVE SYS ORACLE 651,26501,55484 18 RMAN: full datafile backup 2021-09-21 08:04:17 37.23% 3h45s 7386038:19838204 1h47mi13s RMAN: full datafile backup: Set Count 18: 7386038 out of 19838204 Blocks done RMAN backup & recovery I/O ACTIVE SYS ORACLE 832,24733,55495 20 RMAN: full datafile backup 2021-09-21 08:04:17 42.12% 2h27mi21s 7986294:18961404 1h47mi13s RMAN: full datafile backup: Set Count 20: 7986294 out of 18961404 Blocks done RMAN backup & recovery I/O ACTIVE SYS ORACLE 1206,50473,5548 17 RMAN: full datafile backup 2021-09-21 08:04:17 50.16% 1h46mi31s 10909748:21748986 1h47mi13s RMAN: full datafile backup: Set Count 17: 10909748 out of 21748986 Blocks done RMAN backup & recovery I/O ACTIVE SYS ORACLE 834,22177,55479 33 RMAN: aggregate input 2021-09-21 08:04:16 41.36% 2h30mi32s 32910294:79572388 1h46mi10s RMAN: aggregate input: backup 33: 32910294 out of 79572388 Blocks done SQL*Net message from client INACTIVE |
如下是还原操作：
1 2 3 4 5 6 7 8 9 | SQL> / USERNAME OSUSER SESSION_INFO OPNAME START_TIME PROGRESS TIME_REMAINING ELAPSED_SECONDS MESSAGE WAIT_EVENT STATUS ---------- ---------- --------------- ------------------------------ ------------------- -------- -------------- --------------- -------------------------------------------------------------------------------- ------------------------------ -------- SYS ORACLE 1749,9,15427 RMAN: full datafile restore 2021-09-21 16:35:56 27.48% 3526 1336 RMAN: full datafile restore: Set Count 18: 5451413 out of 19838204 Blocks done RMAN backup & recovery I/O ACTIVE SYS ORACLE 77,3,15429 RMAN: full datafile restore 2021-09-21 16:35:56 27.54% 3516 1336 RMAN: full datafile restore: Set Count 19: 5237982 out of 19021946 Blocks done RMAN backup & recovery I/O ACTIVE SYS ORACLE 1,5,15428 RMAN: full datafile restore 2021-09-21 16:35:56 31.72% 2876 1336 RMAN: full datafile restore: Set Count 17: 6898467 out of 21748986 Blocks done RMAN backup & recovery I/O ACTIVE SYS ORACLE 154,1,15430 RMAN: full datafile restore 2021-09-21 16:35:56 33.17% 2692 1336 RMAN: full datafile restore: Set Count 20: 6288911 out of 18961404 Blocks done RMAN backup & recovery I/O ACTIVE |
方法2：通过V$SQL_MONITOR来查找
Oracle 11g中引入了新的动态性能视图V$SQL_MONITOR，该视图用以显示Oracle监视的SQL语句信息。SQL Monitor会对那些并行执行或者消耗5秒以上CPU时间或I/O时间的SQL语句自动监控，同时在V$SQL_MONITOR视图中产生一条记录。V$SQL_MONITOER收集的信息每秒刷新一次，接近实时，当SQL执行完毕，信息并不会立即从V$SQL_MONITOER中删除，至少会保留1分钟，V$SQL_PLAN_MONITOR视图中的执行计划信息也是每秒更新一次，当SQL执行完毕，它们同样至少被保留1分钟，并可以会存在更长的时间，取决于新的查询所需要的空间。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
