Oracle数据库中如何查找、定位一些正在执行但是运行时间很长的SQL语句(long running sql)呢？注意，我们这里只查看正在执行long running sql,而不是查询历史执行时间很长的SQL语句（请不要混淆）。大概有下面一些方法供参考。
1：通过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分钟，并可以会存在更长的时间，取决于新的查询所需要的空间。
使用v$sql_monitor视图中所监控的SQL语句时需在满足以下条件：
1） 自动监控任何并行语句
2） 如果一个SQL语句消耗了超过5秒的CPU或I/O时间，则会自动监控
3） 监控任何在语句级启用监控的SQL语句（使用/*+ monitor */提示SQL语句）
注意：要开启sql mointor这个特性/功能的前提条件：
1：初始化参数STATISTICS_LEVEL必须设置为TYPICAL或ALL。
2：CONTROL_MANAGEMENT_PACK_ACCESS必须设置为DIAGNOSTIC+TUNING。
使用下面SQL查询，如果你使用PL/SQL Developer等图形工具，可以去掉设置格式部分。
set termout off;
set markup html on spool on -
HEAD '<title>sql monitor long running</title> -
<style type="text/css"> -
table{-
font-family: "courier new",verdana, arial, sans-serif;-
font-size: 12px;-
color: #333333;-
border-width: 1px;-
border-color: #3A3A3A;-
border-collapse: collapse;-
}-
th {-
border-width: 1px;-
padding: 8px;-
border-style: solid;-
border-color: #FFA6A6;-
background-color: #D56A6A;-
color: #ffffff;-
}-
tr:hover td {-
cursor: pointer;-
background-color: #F7CFCF;-
}-
td {-
border-width: 1px;-
padding: 8px;-
border-style: solid;-
border-color: #FFA6A6;-
background-color: #ffffff;-
}-
</style>'
spool sql_monitor_running_sql.html
select sid
,session_serial#
,username
,program
,to_char(sql_exec_start,'yyyy-mm-dd hh24:mi:ss') sql_exec_start
,sql_id
,sql_text
,is_full_sqltext
,elapsed_time/1000000
from v$sql_monitor
where status in ('QUEUED','EXECUTING');
spool off;
set markup html off;复制
输入对应的SQL_ID,查看对应的报告。
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
spool monitor_long_running.html
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>'fb19u7fh7wd7v',type=> 'HTML') AS report FROM dual;
spool off;复制
2：通过V$SESSION_LONGOPS来查找
V$SESSION_LONGOPS 显示运行时间超过 6 秒（以绝对时间为单位）的各种操作的状态。这个视图不但可以监控运行时间比较长的SQL，也会记录RMAN备份、EXP/EXPDP、收集统计信息、排序等操作，基本数据库所有运行时间超过6秒的SQL都会记录在这个视图中.
如果要使用V$SESSION_LONGOPS的话，也必须满足下面两个条件。
- Set the TIMED_STATISTICS or SQL_TRACE parameters to true
必须将初始化参数 timed_statistics设置为true或者开启sql_trace
- Gather statistics for your objects with the DBMS_STATS package
必须用ANALYZE或者DBMS_STATS包对对象收集过统计信息。复制
下面是具体的SQL语句：
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';复制
注意，使用V$SESSION_LONGOPS来查找long runnging sql,可能会遗漏一些SQL，因为比如某个SQL语句执行时间比较长，但是每个操作都没有超过6秒。那么这个SQL可能不会被捕获到。
方式3：通过V$SESSION和V$SQLAREA来查询。
注意，下面SQL统计的SQL运行时间为AVG_ELAPSED_TIME可能跟V$SQL_MONITOR中的时间有出入，确切来说，V$SQL_MONITOR中的才是SQL运行时长的精准时间。这个是因为统计口径问题，这里统计的是SQL运行的平均时间，而不是这个SQL的真正运行时间，所以下面SQL仅供参考。
SELECT S.USERNAME
,S.SID
,S.SERIAL#
,A.SQL_ID
,S.OSUSER
,S.MACHINE
,S.TERMINAL
,S.PROGRAM
,S.EVENT
,S.SECONDS_IN_WAIT
,S.BLOCKING_SESSION
,S.STATUS
,S.LAST_CALL_ET
,DECODE(A.EXECUTIONS, 0, ROUND(A.ELAPSED_TIME/1000000,2),ROUND(A.ELAPSED_TIME/1000000/NVL(A.EXECUTIONS,1),2)) AS AVG_ELAPSED_TIME
,A.EXECUTIONS
,A.SQL_TEXT
--,A.SQL_FULLTEXT
FROM V$SESSION S ,
V$SQLAREA A
WHERE S.SQL_HASH_VALUE = A.HASH_VALUE
AND S.SQL_ADDRESS = A.ADDRESS
ORDER BY AVG_ELAPSED_TIME DESC;复制
