 |
 |
 |
 |
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
|
|
 |
|
|
|
 |
|
 |
Donald K. Burleson Oracle Tips |
Tracking long-running DDL statements
The Oracle data dictionary contains a little-known view called the v$session_longops. The v$session_longops view allows the Oracle professional contract the amount of time that is used by long-running DLL and DML statements.
For example, in the data warehouse environment, building a multi-gigabyte large index can take many hours even with parallel index creation. You can query against the v$session_longops to quickly find out how much of that specific DL statement has been completed. Also note that the v$session_longops can also be used for any long-running operation, including long-running updates.
The script below will display a status message that shows the current amount of time that has been used by long-running DDL operations. Note that you must get the SID from v$session and plug it into the SQL statement below:
select
sid,
message
from
v$session_longops
where
sid = 13
order by
start_time;
Here is a sample of the output, showing the progress of a long running CREATE INDEX statement.
SID MESSAGE
--- -------------------------------------------------------------------
11
Table Scan: CUST.PK_IDX: 732 out of 243260 Blocks done
 |
If you like Oracle tuning, check out my latest book "Oracle
Tuning: The Definitive Reference".
It's 980 pages of hard-core tuning insights, tips and scripts, and you can buy it direct from the publisher for 30%-off. Best of all, you get instant access to the code depot of Oracle tuning scripts. |
|
|
