在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优化器模式，并且满足下面条件：
- TIMED_STATISTICS或SQL_TRACE参数为true。
- 使用DBMS_STATS包或ANLYZE语句收集、分析过对象的统计信息。
|
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://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3114287916999