ORACLE 如何查看索引重建進度情況

   在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語句收集、分析過對象的統計信息。

下麵我們來演示一下,首先構造了一個大表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