檢視慢查詢進度

壹頁書發表於2014-06-11
Oracle一個大事務的SQL往往不知道執行到了哪裡,
可以使用如下SQL檢視執行進度。
  1. set linesize 400;
  2. set pagesize 400;
  3. col sql_text format a100;
  4. col opname format a15;
  5. SELECT se.sid,
  6.          opname,
  7.          TRUNC (sofar / totalwork * 100, 2) pct_work,
  8.          elapsed_seconds elapsed,
  9.          ROUND (elapsed_seconds * (totalwork - sofar) / sofar) remain_time,
  10.          sql_text
  11.     FROM v$session_longops sl, v$sqlarea sa, v$session se
  12.    WHERE sl.sql_hash_value = sa.hash_value
  13.          AND sl.sid = se.sid
  14.          AND sofar != totalwork
  15. ORDER BY start_time;
或者
  1. set linesize 400;
  2. set pagesize 400;
  3. set long 4000;
  4. col sql_fulltext format a100;
  5. col opname format a15;
  6. SELECT se.sid,
  7.          opname,
  8.          TRUNC (sofar / totalwork * 100, 2) pct_work,
  9.          elapsed_seconds elapsed,
  10.          ROUND (elapsed_seconds * (totalwork - sofar) / sofar) remain_time,
  11.          sql_fulltext
  12.     FROM v$session_longops sl, v$sqlarea sa, v$session se
  13.    WHERE sl.sql_hash_value = sa.hash_value
  14.          AND sl.sid = se.sid
  15.          AND sofar != totalwork
  16. ORDER BY start_time;


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1180444/，如需轉載，請註明出處，否則將追究法律責任。

相關文章