[zt] 用v$session_longops監控long RUN操作

一、什麼情況下，操作資訊會出現在V$SESSION_LONGOPS

同時滿足以下幾個條件，操作資訊才會出現在V$SESSION_LONGOPS中。

1、操作是以下幾種操作之一
# Table scan;
# Index Fast Full Scan;
# Hash join;
# Sort/Merge;
# Sort Output;
# Rollback;
# Gather Table's Index Statistics

不同的版本下V$SESSION_LONGOPS記錄的操作可能會不一樣。

2、操作時間大於6秒

3、讀取的block數目大於一定量

1）如果是TABLE FULL SCAN，讀取的block數目至少大於10000
2）如果是Index Fast Full Scan，讀取的block數目至少大於1000
3）其他操作讀取block的數目不明

二、如何監控耗時長的操作
舉例說明。
會話1：發出查詢
suk@SUK> select * from t2;

已選擇826112行。

已用時間: 00: 00: 29.89

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=123 Card=103264 Byte
s=8880704)

1 0 TABLE ACCESS (FULL) OF 'T2' (Cost=123 Card=103264 Bytes=88
80704)


統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
64505 consistent gets
0 physical reads

會話2：在會話1查詢過後一小段時間後（大於6s），查詢V$SESSION_LONGOPS檢視
SQL> SELECT SE.SID,
2 OPNAME,
3 TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK,
4 ELAPSED_SECONDS ELAPSED,
5 ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,
6 SQL_TEXT
7 FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
8 WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
9 AND SL.SID = SE.SID
10 AND SOFAR != TOTALWORK
11 ORDER BY START_TIME
12 ;

(
SELECT SE.SID,OPNAME,TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK,ELAPSED_SECONDS ELAPSED,ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,SQL_TEXT
FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
AND SL.SID = SE.SID
AND SOFAR != TOTALWORK
ORDER BY START_TIME;
)

SID OPNAME PCT_WORK ELAPSED REMAIN_TIME SQL_TEXT
---------- ----------------------------- ------------ ---------- ----------- --------------------------

------------------------
10 Table Scan 67.07% 21 10 select * from t2

會話1的真實執行時間：29.89s
會話2從V$SESSION_LONGOPS的估算時間：21 + 10 = 31s

可以看到，時間執行和估算的有誤差，但是大體是正確的。

在實際中，你可能透過這個查詢估算到某個執行的剩餘時間很短了，但實際上操作過了很久才結束。
這是因為V$SESSION_LONGOPS只記錄部分操作的資訊，但是一個SQL可能會包含很多個操作步驟，V$SESSION_LONGOPS記錄的只是這個SQL要執行眾多操作的一步。