ORACLE 如何查看索引重建进度情况

时间:2021-11-02 07:21:34

在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> 

ORACLE 如何查看索引重建进度情况

在会话窗口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> 

ORACLE 如何查看索引重建进度情况

注意,这个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'等待事件,表示重建索引时用到了临时表空间做磁盘排序操作,由于索引非常大,所以产生了这个等待事件。

ORACLE 如何查看索引重建进度情况

ORACLE 如何查看索引重建进度情况

如果跟踪一个非常小的索引重建,你在跟踪文件里面是看不到这个信息的。

 

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> 

ORACLE 如何查看索引重建进度情况

如果你根本不知道会话信息,如果我只知道是在那个表上重建索引,也可以根据表名来查询,如下所示,我们还增加了开始时间等字段

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> 

ORACLE 如何查看索引重建进度情况

当然,如果需要更详细一点的信息,可以使用下面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