ORACLE 如何查看索引重建进度情况-阿里云开发者社区

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

高大的卤蛋 · 哈尔滨市巴彦县人民政府· 6 月前 ·

失眠的烤地瓜 · 柳国庆（书法家）_百度百科· 7 月前 ·

憨厚的水煮肉 · 山西阳泉市：坚持“四个融合” 探索资源型城市 ...· 1 年前 ·

骑白马的蚂蚁 · 黄海嵩副省长视察安凯纯电动公交成功运营_新浪 ...· 1 年前 ·

仗义的凉茶 · 《海贼王》百卷纪念 ...· 1 年前 ·

在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语句收集、分析过对象的统计信息。

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

会话窗口1，执行下面SQL语句：

SQL>  SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID');       SID    SERIAL# STATUS---------- ---------- --------       827        746 ACTIVESQL>  ALTER INDEX IDX_TEST_INDX REBUILD;Index altered.SQL> SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID');       SID    SERIAL# STATUS---------- ---------- --------       808       1003 ACTIVESQL> col opname format a32SQL> col target format a32SQL> col perwork format a12SQL> set linesize 1200SQL> 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: 827old   7: from v$session_longops where sofar!=totalwork and sid=&sidnew   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: 827old   7: from v$session_longops where sofar!=totalwork and sid=&sidnew   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: 827old   7: from v$session_longops where sofar!=totalwork and sid=&sidnew   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有时候需要一点时间才能看到结果，因为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.  6        ,sofar  7        ,totalwork  8        ,trunc(sofar/totalwork*100,2)||'%' as perwork  9  from v$session_longops where sofar!=totalwork and target like &target;Enter value for target:  '%TEST_INDX%'old   9: from v$session_longops where sofar!=totalwork and target like &targetnew   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%
     
        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
       
      数据同步软件 Shareplex 异常重建详细步骤（Oracle 数据库）
     
     最近有客户的 shareplex 因为一些稀奇古怪的原因又挂了，由于邮件告警问题，没有及时通知到，并且归档已经被删除，备份也追溯不回丢失的归档日志。
    
    场景描述：2016年1月6日，客户存储RAID5出现问题，更换AIX服务器管理模块，由于设备微码不匹配，导致RAID5中的仲裁盘LUN处于异常状态，进而导致oracle 10.2.0.5的双节点RAC异常宕机。
   
   索引重建是一个争论不休被不断热烈讨论的议题。当然Oracle官方也有自己的观点，我们很多DBA也是遵循这一准则来重建索引，那就是Oracle建议对于索引深度超过4级以及已删除的索引条目至少占有现有索引条目总数的20% 这2种情形下需要重建索引。