監控sql執行時剩餘時間，監控sql剩餘時間

監控sql執行時剩餘時間，監控sql剩餘時間

--監控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 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 trueGather statistics for your objects with the DBMS_STATS package--意義：統計各個操作時間大於6s的語句--前提條件：基於成本的最佳化器cboTIMED_STATISTICS或SQL_TRACE為true對象有統計資訊測試：select * from v$version;--Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64biselect * from v$parameter where name like '%timed_statistics%'VALUETRUEselect last_analyzed from user_tables where table_name='LHJ_1G'LAST_ANALYZED2013-12-10 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' 

過一會再重複執行上面語句：

sql語句執行完成或者取消後，以上查詢為空白

怎監控一句SQL執行的效率？用什工具？怎監控？

用sqlServer內建的sql server profiler

可以監視sql執行的cpu佔用率，執行時間長度等
 
oracle 中怎監控sql執行的進度？

pl/sql tool session 找到相應的session 一般應用程式是 JDBC Thin Client 然後點下面的sqltext就可以看到執行到那句話了，可以不停重新整理看sql變化