LATEST TOPICS

Query to monitor progress of Index creation or Index rebuild

The following query can be used to monitor the progress of Index creation or Index rebuild for huge indexes.

-------------------------------------
---- Script: chk_idx_progress.sql
---- Author: Abu Fazal Mohammed Abbas
--------------------------------------
set lines 200
col "Index Operation" for a60 trunc
col "ETA Mins" format 999.99
col "Runtime Mins" format 999.99
select sess.sid as "Session ID", sql.sql_text as "Index Operation",
longops.totalwork, longops.sofar, 
longops.elapsed_seconds/60 as "Runtime Mins",
longops.time_remaining/60 as "ETA Mins"
from v$session sess, v$sql sql, v$session_longops longops
where 
sess.sid=longops.sid
and sess.sql_address = sql.address
and sess.sql_address = longops.sql_address
and sess.status  = 'ACTIVE'
and longops.totalwork > longops.sofar
and sess.sid not in ( SELECT sys_context('USERENV', 'SID') SID  FROM DUAL)
and upper(sql.sql_text) like '%INDEX%'
order by 3, 4
;

The query will give output like something as follows:

Session ID Index Operation                                               TOTALWORK      SOFAR Runtime Mins   ETA Mins
---------- ------------------------------------------------------------ ---------- ---------- ------------ ----------
        76 ALTER INDEX ABBAS.IDX_TEST_IND rebuild parallel 4                  3310       2545   .166666667        .05
        75 ALTER INDEX ABBAS.IDX_TEST_IND rebuild parallel 4                  3330        620   .166666667 .733333333
        60 ALTER INDEX ABBAS.IDX_TEST_IND rebuild parallel 4                  4028        715   .166666667 .766666667
        77 ALTER INDEX ABBAS.IDX_TEST_IND rebuild parallel 4                  2666       2495   .166666667 .016666667
4 Comments
  1. Srinivasant.dba
    • Abu Fazal Abbas
  2. FooManChu
    • Abu Fazal Abbas
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed