在ORACLE數據庫中，如果一個比較大的索引在重建過程中耗費時間比較長，那麼怎麼查看索引重建耗費的時間，以及完成了多少(比例)了呢，我們可以通過V$SESSION_LONGOPS視圖來查看索引重建的時間和進度。
官方文檔關於V$SESSION_LONGOPS的介紹如下
V$SESSION_LONGOPS
This view 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 parameter to true
Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package
這個視圖顯示運行時間超過6秒的各類數據庫操作的狀態，這些操作包括備份、恢復功能，統計信息收集，以及查詢操作等。
要監控查詢執行進展情況，你必須是CBO優化器模式，並且滿足下面條件：
Column
DateType
Description
Description（中文）
SID
NUMBER
Session identifier
Session標識
SERIAL#
NUMBER
Session serial number
Session串號
OPNAME
VARCHAR2(64)
Brief description of the operation
操作簡要說明
TARGET
VARCHAR2(64)
The object on which the operation is carried out
操作的對象
TARGET_DESC
VARCHAR2(32)
Description of the target
目標對象說明
SOFAR
NUMBER
The units of work done so far
迄今為止完成的工作量
TOTALWORK
NUMBER
The total units of work
總工作量
UNITS
VARCHAR2(32)
The units of measurement
工作量單位
START_TIME
DATE
The starting time of operation
操作開始時間
LAST_UPDATE_TIME
DATE
Time when statistics last updated
統計項最後更新時間
TIMESTAMP
DATE
Timestamp
TIME_REMAINING
NUMBER
Estimate (in seconds) of time remaining for the operation to complete
預計完成操作的剩余時間(秒)
ELAPSED_SECONDS
NUMBER
The number of elapsed seconds from the start of operations
從操作開始總花費時間(秒)
CONTEXT
NUMBER
Context
上下文關系
MESSAGE
VARCHAR2(512)
Statistics summary message
統計項的完整描述
USERNAME
VARCHAR2(30)
User ID of the user performing the operation
操作用戶
SQL_ADDRESS
RAW(4 | 8)
Used with the value of the SQL_HASH_VALUEcolumn to identify the SQL statement associated with the operation
SQL_HASH_VALUE
NUMBER
Used with the value of the SQL_ADDRESS column to identify the SQL statement associated with the operation
SQL_ID
VARCHAR2(13)
SQL identifier of the SQL statement associated with the operation
QCSID
NUMBER
Session identifier of the parallel coordinator
下面我們來演示一下,首先構造了一個大表TEST_INDX，表TEST_INDX上建有一個索引IDX_TEST_INDX。我們開啟兩個會話窗口：
會話窗口1，執行下面SQL語句：
SQL> SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID');SID SERIAL# STATUS
---------- ---------- --------
827 746 ACTIVE
SQL> ALTER INDEX IDX_TEST_INDX REBUILD;
Index altered.
SQL>
在會話窗口2，執行下面SQL
SQL> SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID');SID SERIAL# STATUS
---------- ---------- --------
808 1003 ACTIVE
SQL> col opname format a32
SQL> col target format a32
SQL> col perwork format a12
SQL> set linesize 1200
SQL> select sid
2 ,opname
3 ,target
4 ,sofar
5 ,totalwork
6 ,trunc(sofar/totalwork*100,2)||'%' as perwork
7 from v$session_longops where sofar!=totalwork and sid=&sid;
Enter value for sid: 827
old 7: from v$session_longops where sofar!=totalwork and sid=&sid
new 7: from v$session_longops where sofar!=totalwork and sid=827
SID OPNAME TARGET SOFAR TOTALWORK PERWORK
---------- --------------------- ------------------ ---------- ---------- --------
827 Index Fast Full Scan TEST.TEST_INDX 27914 157907 17.67%
SQL> /
Enter value for sid: 827
old 7: from v$session_longops where sofar!=totalwork and sid=&sid
new 7: from v$session_longops where sofar!=totalwork and sid=827
SID OPNAME TARGET SOFAR TOTALWORK PERWORK
---------- -------------------- ------------------ ---------- ---------- -------
827 Index Fast Full Scan TEST.TEST_INDX 105075 157907 66.54%
SQL> /
Enter value for sid: 827
old 7: from v$session_longops where sofar!=totalwork and sid=&sid
new 7: from v$session_longops where sofar!=totalwork and sid=827
SID OPNAME TARGET SOFAR TOTALWORK PERWORK
---------- --------------- ------------------- ---------- ---------- --------
827 Sort Output 41728 175125 23.82%
SQL>
注意，這個SQL有時候需要一點時間才能看到結果，因為v$session_longpos中記錄的是執行時間超過6秒的操作，另外，你有時候會看到在Index Fast Full Scan之後，出現Sort Output操作。這個是索引重建時的排序操作，對這個Sort OutPut有點不清楚，在https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3114287916999 這個鏈接裡面，看到Tom大師的這麼一個回復:
It is not showing you the end to end time of the index create, it is showing you the end to end time of the STEPS within the index create.
For example, I had a sort_area_size of 1m. I created a table t with 1,000,000 rows based on all_objects. On this table, I put an index on object_id. This took many TABLE SCAN followed by SORT/MERGE followed by SORT/OUTPUT steps. Each was timed independently.
Next, I dropped that index and set my sort_area_size to 300m (large enough to avoid a sort to disk). This time, the ONLY thing in v$session_longops was a TABLE SCAN, no sort/merge, no sort/output. Since we didn't swap to disk, these steps were avoided.
So, I'll guess -- your single key index was done in memory, your concatenated key was not.
也就是說，如果sort_area_size足夠大，就不會出現Sort Merge或Sort Output操作，因為在sort_area_size不夠大的時候，就會使用臨時表空間的臨時段來排序。由於沒有查到較權威的官方資料，猜測是在索引重建過程中，由於sort_area_size不夠大，所以要使用磁盤排序，即使用了臨時表空間來排序，所以出現了Sort Output操作，它表示內存排序輸出到磁盤進行排序（當然僅僅是個人猜測，如有不正確的地方，敬請指正），另外在metalink上也看到這樣一段介紹：
First, there are the temporary segments that are used to store partial sort
data when the SORT_AREA_SIZE is too small to process the complete sort set
These segments are built in the user's default TEMPORARY tablespace.
Second, as the index is being rebuilt, it uses a segment which is defined as
a temporary segment until the rebuild is complete. Once this segment is fully
populated, the old index can be dropped and this temporary segment is redefined
as a permanent segment with the index name.
下面我們對索引重建做一個10046跟蹤
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> ALTER INDEX TEST.IDX_TEST_INDX REBUILD;
Index altered.
SQL> alter session set events '10046 trace name context off';
Session altered.
此時在trc文件裡面，你會看到大量的'direct path read temp'等待事件，表示重建索引時用到了臨時表空間做磁盤排序操作，由於索引非常大，所以產生了這個等待事件。
如果跟蹤一個非常小的索引重建，你在跟蹤文件裡面是看不到這個信息的。
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> alter index scott.pk_emp rebuild;
Index altered.
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL>
如果你根本不知道會話信息，如果我只知道是在那個表上重建索引，也可以根據表名來查詢，如下所示，我們還增加了開始時間等字段
SQL> col opname format a32
SQL> col target format a32
SQL> col start_time format a24
SQL> col elapsed_seconds format 99
SQL> col perwork format a12
SQL> select sid
2 ,opname
3 ,target
4 ,start_time
5 ,elapsed_seconds
6 ,sofar
7 ,totalwork
8 ,trunc(sofar/totalwork*100,2)||'%' as perwork
9 from v$session_longops where sofar!=totalwork and target like ⌖
Enter value for target: '%TEST_INDX%'
old 9: from v$session_longops where sofar!=totalwork and target like &target
new 9: from v$session_longops where sofar!=totalwork and target like '%TEST_INDX%'
SID OPNAME TARGET START_TIME ELAPSED_SECONDS SOFAR TOTALWORK PERWORK
---------- -------------------- ------------------ --------------------- --------------- ---------- ---------- -----
827 Index Fast Full Scan TEST.TEST_INDX 2016-07-13 23:47:57 30 99732 157907 63.15%
SQL>
當然，如果需要更詳細一點的信息，可以使用下面SQL語句來查看。
SELECT b.start_time,
b.sid,
b.opname,
b.target,
b.sofar,
b.totalwork,
b.units,
b.elapsed_seconds,
b.message,
b.sql_hash_value,
a.sql_text
FROM v$sqlarea a,
v$session_longops b
WHERE a.hash_value=b.sql_hash_value
and b.sofar!=totalwork
and b.target like &target
ORDER BY b.start_time DESC;
參考資料：
http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2092.htm#REFRN30227
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=92427097847005&id=94178.1&_afrWindowMode=0&_adf.ctrl-state=wdhanhwgh_4
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3114287916999
