動態檢視V$SESSION_LONGOPS學習

For v$session_longops, it exists in the  lock  part of oracle directory.

本檢視顯示執行超過6秒的操作的狀態。包括備份，恢復，統計資訊收集，查詢等等.
主要列說明：
l        SID：Session標識
l        SERIAL#：Session串號
l        OPNAME：操作簡要說明
l        TARGET：操作執行所在的物件
l        TARGET_DESC：目標物件說明
l        SOFAR：至今為止完成的工作量
l        TOTALWORK：總工作量
l        UNITS：工作量單位
l        START_TIME：操作開始時間
l        LAST_UPDATE_TIME：統計項最後更新時間
l        TIME_REMAINING：預計完成操作的剩餘時間(秒)
l        ELAPSED_SECONDS：從操作開始總花費時間(秒)
l        MESSAGE：統計項的完整描述
l        USERNAME：執行操作的使用者ID
l        SQL_HASH_VALUE：用於連線查詢的列
一、什麼情況下，操作資訊會出現在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的數目不明

二、如何監控耗時長的操作
會話一：發出查詢

----建立一個臨時表
SQL>set timing on;
SQL>create table ttt as select level lv,rownum rn from dual connect by level<10000000;

Table created.

Elapsed: 00:00:19.95

SQL>set autot traceonly

----執行超過6秒的查詢

SQL>select * from gary.ttt;

9999999 rows selected.

Elapsed: 00:02:10.77

Execution Plan
----------------------------------------------------------
Plan hash value: 774701505

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    11M|   296M|  5640  (10)| 00:01:08 |
|   1 |  TABLE ACCESS FULL| TTT  |    11M|   296M|  5640  (10)| 00:01:08 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
     689537  consistent gets
      13693  physical reads
          0  redo size
  214444903  bytes sent via SQL*Net to client
    7333722  bytes received via SQL*Net from client
     666668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    9999999  rows processed

會話二：在會話1執行一段時間（大於6s）後，再查詢V$SESSION_LONGOPS檢視

SQL>set linesize 300

SQL>col opname for a20

SQL>col pct_work for a20
SQL>col sql_text for a30

SQL>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
---------- -------------------- -------------------- ---------- ----------- ------------------------------
       156 Table Scan           42.34%                      57           78 select * from gary.ttt

SID OPNAME               PCT_WORK      ELAPSED REMAIN_TIME SQL_TEXT
---------- -------------------- -------------------- ---------- ----------- ------------------------------
       156 Table Scan           58.31%                       78          56 select * from gary.ttt

 SID OPNAME               PCT_WORK      ELAPSED REMAIN_TIME SQL_TEXT
---------- -------------------- -------------------- ---------- ----------- ------------------------------
       156 Table Scan           89.77%                      118          13 select * from gary.ttt

  SID OPNAME               PCT_WORK     ELAPSED REMAIN_TIME SQL_TEXT
---------- -------------------- -------------------- ---------- ----------- ------------------------------
       156 Table Scan           98.92%                      130           1 select * from gary.ttt

會話1的真實執行時間：02:10.77=130.77s
會話2從V$SESSION_LONGOPS的最終估算時間：130 + 1 = 131s

可以看到，開始執行時間和估算的有誤差，但最終時間基本上是正確的。

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

另外，此檢視也可以用來監視比如RMAN備份的時間進度：
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "% COMPLETED" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'  AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

有關此檢視的詳細連結：