Estimate or monitor the time to build an index

index" of a large table. There are three phases of index creation:

1. Table scan: Index Fast Full Scan, Table Scan, Rowid Range Scan
2. Sorting the data: Sort/Merge
3. Writing the output: Sort Output

This script will not show any entry if index creation is less than 10 sec. It will not filter the old entries, so in order to cope with this problem, create each index in different sessions (of course followed by the completion of the previous one). It has been tested on Oracle 9i.

define sid = <   >
select opname, sum(sofar), sum(totalworks) 
from v$session_longops 
where sid = &sid group by opname;

Reader Feedback

Brian Peasland writes: This is a really good tip, but the query in the tip is missing something. The query needs both SID and SERIAL# in the WHERE clause to work correctly. The author only has SID, which can give misleading results in the case of two sessions at two different times that had the same SID. The SID,SERIAL# combination guarantees the results will be for one session only.

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.