Is there any way to estimate how long a create table/index has left to run ?

The Oracle (tm) Users' Co-Operative FAQ

Is there any way to estimate how long a create table/index has left to run ?

For an understanding of this progress meeting, there are a few possibilities to distinguish:

- simple create table as select, without distinct, order by, nor group by
- create table as select, with a sort phase (as mentioned above).
- create index.

For the latter two, there are three phases during the creation:

- full table scan
- sorting the data (needed also for grouping)
- writing the output.

These phases correspond to 'rows' in v$session_longops, as the index creation progresses.

The creation of an index is discussed below. For table creation it is much the same (or even more simple).

Suppose, an index needs to be created on the column 'name', of table 'customer'. Therefore, a sqlplus session is started. First statement is to find out the session identifier (sid) of that statement, with e.g.

	select s.sid
	,      p.pid
	,      p.spid
	from   v$process p
	,      v$session s
	where  p.addr = s.paddr
	and    s.audsid = userenv('sessionid')
复制

A second sqlplus session is used for progress monitoring. First, define the sid:

	define sid = <<sid value from the select above>>
	set verify off
复制

After that, start the create index in the first session. In the second session, type:

	select sid
	,      message
	from   v$session_longops
	where  sid = &sid
	order by start_time;
复制

In the first phase, this query will return output like:

SID MESSAGE
--- -----------------------------------------------------------------------
 11 Table Scan:  CONVERSIE.RB_RELATIE: 7001 out of 21460 Blocks done
复制

While the table is being read, output will be written to the temporary tablespace (if the sort_area is not sufficient).

When the table scanning phase has been finished, the sort/merge begins. A repeat of the statement shows:

SID MESSAGE
--- -----------------------------------------------------------------
 11 Table Scan:  CONVERSIE.RB_RELATIE: 21460 out of 21460 Blocks done
 11 Sort/Merge:  : 2107 out of 3116 Blocks done
复制

During this phase, there is reading and writing to the temporary tablespace.

In the last phase, the index entries have been sorted, and are being written to the index segment. V$session_longops shows:

SID MESSAGE
--- -----------------------------------------------------------------
 11 Table Scan:  CONVERSIE.RB_RELATIE: 21460 out of 21460 Blocks done
 11 Sort/Merge:  : 3116 out of 3116 Blocks done
 11 Sort Output:  : 800 out of 3302 Blocks done
复制

In this last phase, the temporary tablespace is being read from, and writes occur to the tablespace in which the index is created. Note that the index appears as a temporary segment, once the last phase has been started. The segment is 'promoted' to a real index segment, after the physical writing has been done.

Note that entries for a certain phase occur in v$session_longops after about 10 seconds. If a phase take shorter than that, it will not appear in the view.

Note also that v$session_longops might contain data from previous sessions and/or earlier statements. In that case, one can filter on start_time.

With this knowledge in mind, one can estimate how much work has to be done!

Further reading: See the Oracle database reference guide (version 9.0.1), on OTN:

http://download-eu.oracle.com/otndoc/oracle9i/901_doc/server.901/a90190/ch3161.htm#992382