在ORACLE資料庫中，如果一個比較大的索引在重建過程中耗費時間比較長，那麼怎麼檢視索引重建耗費的時間，以及完成了多少(比例)了呢，我們可以通過V$SESSION_LONGOPS視圖來檢視索引重建的時間和進度。
官方文檔關于V$SESSION_LONGOPS的介紹如下
<b>V$SESSION_LONGOPS</b>
<a></a>
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優化器模式，并且滿足下面條件：
TIMED_STATISTICS或SQL_TRACE參數為true。 使用DBMS_STATS包或ANLYZE語句收集、分析過對象的統計資訊。
Column
DateType
Description
Description（中文）
SID
NUMBER
Session identifier
Session辨別
SERIAL#
Session serial number
Session串号
OPNAME
VARCHAR2(64)
Brief description of the operation
操作簡要說明
TARGET
The object on which the operation is carried out
操作的對象
TARGET_DESC
VARCHAR2(32)
Description of the target
目标對象說明
SOFAR
The units of work done so far
迄今為止完成的工作量
TOTALWORK
The total units of work
總工作量
UNITS
The units of measurement
工作量機關
START_TIME
DATE
The starting time of operation
操作開始時間
LAST_UPDATE_TIME
Time when statistics last updated
統計項最後更新時間
TIMESTAMP
Timestamp
TIME_REMAINING
Estimate (in seconds) of time remaining for the operation to complete
預計完成操作的剩餘時間(秒)
ELAPSED_SECONDS
The number of elapsed seconds from the start of operations
從操作開始總花費時間(秒)
CONTEXT
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
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
Session identifier of the parallel coordinator
下面我們來示範一下,首先構造了一個大表TEST_INDX，表TEST_INDX上建有一個索引IDX_TEST_INDX。我們開啟兩個會話視窗：
會話視窗1，執行下面SQL語句：
<a href="http://images2015.cnblogs.com/blog/73542/201607/73542-20160715130441623-806119284.png"></a>
在會話視窗2，執行下面SQL
<a href="http://images2015.cnblogs.com/blog/73542/201607/73542-20160715130442717-1814256320.png"></a>
注意，這個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跟蹤
此時在trc檔案裡面，你會看到大量的'direct path read temp'等待事件，表示重建索引時用到了臨時表空間做磁盤排序操作，由于索引非常大，是以産生了這個等待事件。
<a href="http://images2015.cnblogs.com/blog/73542/201607/73542-20160715130443701-1169690634.png"></a>
<a href="http://images2015.cnblogs.com/blog/73542/201607/73542-20160715130445264-1338811408.png"></a>
如果跟蹤一個非常小的索引重建，你在跟蹤檔案裡面是看不到這個資訊的。
<a href="http://images2015.cnblogs.com/blog/73542/201607/73542-20160715130446139-2005436319.png"></a>
如果你根本不知道會話資訊，如果我隻知道是在那個表上重建索引，也可以根據表名來查詢，如下所示，我們還增加了開始時間等字段
<a href="http://images2015.cnblogs.com/blog/73542/201607/73542-20160715130447529-420754081.png"></a>
當然，如果需要更詳細一點的資訊，可以使用下面SQL語句來檢視。
參考資料：
<a href="http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2092.htm#REFRN30227">http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2092.htm#REFRN30227</a>
<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=92427097847005&id=94178.1&_afrWindowMode=0&_adf.ctrl-state=wdhanhwgh_4">https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=92427097847005&id=94178.1&_afrWindowMode=0&_adf.ctrl-state=wdhanhwgh_4</a>
<a href="https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3114287916999">https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3114287916999</a>
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)