监控sql执行时剩余时间
栏目：互联网时间：2014-11-04 08:53:21
--监控sql履行时剩余时间
你知道正在运行的sql大概需要多长时间么？
你知道正在运行的sql大概完成了百分之几么？
你知道正在运行的sql大概还要多长时间完成么？
V$SESSION_LONGOPS帮你忙。
V$SESSION_LONGOPS在<span style="font-family: Arial, Helvetica, sans-serif;">官方文档中的描写：</span>
V$SESSION_LONGOPS displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every http://www.wfuyu.com/oracle/ release.
To monitor query execution progress, you must be using the cost-based optimizer and you must:
Set the TIMED_STATISTICS or SQL_TRACE parameters to true
Gather statistics for your objects with the DBMS_STATS package
--意义：
统计各个操作时间大于6s的语句
--条件条件：
基于本钱的优化器cbo
TIMED_STATISTICS或SQL_TRACE为true
对象有统计信息
测试：
select * from v$version;
--http://www.wfuyu.com/oracle/ Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
select * from v$parameter where name like '%timed_statistics%'
VALUE
TRUE
select last_analyzed from user_tables where table_name='LHJ_1G'
LAST_ANALYZED
2013⑴2⑴0 PM 01:19:36
--session1：
drop table t;
create table t as
select * from lhj_1g;
--session2：
select a.username,
a.target,
a.sid,
a.SERIAL#,
a.opname,
round(a.sofar * 100 / a.totalwork, 0) || '%' as progress, --进度条
time_remaining second, --剩余时间：秒
trunc(a.time_remaining / 60, 2) minute,--剩余时间：分钟
b.sql_text,
b.LAST_ACTIVE_TIME
from v$session_longops a, v$sqlarea b
where a.time_remaining <> 0
and a.sql_address = b.address
and a.sql_hash_value = b.hash_value
and a.username = 'GGS'
过1会再重复履行上面语句：
sql语句履行完成或取消后，以上查询为空
------分隔线----------------------------
------分隔线----------------------------