Thank you for visiting Spatial DBA - Oracle and ArcSDE.
Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
====================================================================
V$SESSION_LONGOPS 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 parameters to true
· Gather statistics for your objects with the
You can add information to this view about application-specific long-running operations by using the ANALYZE statement or the DBMS_STATS packageDBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure.
Column
|
Datatype
|
Description
|
SID | NUMBER |
Identifier of the session processing the long-running operation. If multiple sessions are cooperating in the long-running operation, then
SID corresponds to the main or master session. |
SERIAL# | NUMBER |
Serial number of the session processing the long-running operation. If multiple sessions are cooperating in the long-running operation, then
SERIAL# corresponds to the main or master session. SERIAL# is used to uniquely identify a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID. |
OPNAME | VARCHAR2(64) |
Brief description of the operation
|
TARGET | VARCHAR2(64) |
Object on which the operation is carried out
|
TARGET_DESC | VARCHAR2(32) |
Description of the target
|
SOFAR | NUMBER |
Units of work done so far
|
TOTALWORK | NUMBER |
Total units of work
|
UNITS | VARCHAR2(32) |
Units of measurement
|
START_TIME | DATE |
Starting time of the operation
|
LAST_UPDATE_TIME | DATE |
Time when statistics were last updated for the operation
|
TIMESTAMP | DATE |
Timestamp specific to the operation
|
TIME_REMAINING | NUMBER |
Estimate (in seconds) of time remaining for the operation to complete
|
ELAPSED_SECONDS | NUMBER |
Number of elapsed seconds from the start of the 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_VALUE column 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 long operation, if any
|
SQL_PLAN_HASH_VALUE | NUMBER |
SQL plan hash value; NULL if
SQL_ID is NULL |
SQL_EXEC_START | DATE |
Time when the execution of the SQL started; NULL if
SQL_ID is NULL |
SQL_EXEC_ID | NUMBER |
SQL execution identifier (see
V$SQL_MONITOR) |
SQL_PLAN_LINE_ID | NUMBER |
SQL plan line ID corresponding to the long operation; NULL if the long operation is not associated with a line of the execution plan
|
SQL_PLAN_OPERATION | VARCHAR2(30) |
Plan operation name; NULL if
SQL_PLAN_LINE_ID is NULL |
SQL_PLAN_OPTIONS | VARCHAR2(30) |
Plan operation options; NULL if
SQL_PLAN_LINE_ID is NULL |
QCSID | NUMBER |
Session identifier of the parallel coordinator
|
Note:
- The v$session_longops statistics are gathered only when statistics_level = TYPICAL or ALL.
- Useful join column: SID, join to V$SESSION.SID.
- Before issuing shutdown immediate, it would be recommended to check the following views, especially when the database needs to be brought down for a very short period of time:
For large queries:
select count(*) from v$session_longops where time_remaining>0;
For large transactions:
select sum(used_ublk) from v$transaction;
A result greater than 0 for the first query and a large value returned for the second one would mean a relatively long time to wait until the shutdown immediate completes.
- Query example of V$SESSION_LONGOPS;
select * from V$SESSION_LONGOPS;
USERNAME
|
SID
|
SERIAL#
|
OPNAME
|
TARGET_
DESC
|
TOTALWORK
|
ELAPSED_
SECONDS
|
MESSAGE
|
SYS
|
1703
|
64794
|
RMAN: archived log backup
|
Set Count
|
20095246
|
1387
|
RMAN: archived log backup: Set Count 192150: 20095246 out of 20095246 Blocks done
|
USERA
|
2633
|
63682
|
Sort Output
|
18380
|
16
|
Sort Output: : 18380 out of 18380 Blocks done
| |
USERB
|
2633
|
63682
|
Hash Join
|
249045
|
657
|
Hash Join: : 249045 out of 249045 Blocks done
| |
PROXY_USER
|
2307
|
62043
|
Table Scan
|
26816
|
9
|
Table Scan: USERA.TABLE1: 26816 out of 26816 Blocks done
| |
USERC
|
2600
|
7129
|
Index Fast Full Scan
|
1027
|
195
|
Index Fast Full Scan: USERC.TABLEC: 1027 out of 1027 Blocks done
| |
USERC
|
1928
|
27598
|
Sort Output
|
4075
|
171
|
Sort Output: : 4075 out of 4075 Blocks done
|
