Oracle Tuning Session Longops

Usage of v$session_longops: =========================== SQL> desc v$session_longops; SID NUMBER Session identifier SERIAL# NUMBER Session serial number OPNAME VARCHAR2(64) Brief description of the operation TARGET VARCHAR2(64) The object on which the operation is carried out TARGET_DESC VARCHAR2(32) Description of the target SOFAR NUMBER The units of work done so far TOTALWORK NUMBER The total units of work UNITS VARCHAR2(32) The units of measurement START_TIME DATE The starting time of operation LAST_UPDATE_TIME DATE Time when statistics last updated TIMESTAMP DATE Timestamp TIME_REMAINING NUMBER Estimate (in seconds) of time remaining for the operation to complete ELAPSED_SECONDS NUMBER The number of elapsed seconds from the start of 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 operation QCSID NUMBER Session identifier of the parallel coordinator This view 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 parameter to true Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package You can add information to this view about application-specific long-running operations by using the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure. Select 'long', to_char (l.sid), to_char (l.serial#), to_char(l.sofar), to_char(l.totalwork), to_char(l.start_time, 'DD-Mon-YYYY HH24:MI:SS' ), to_char ( l.last_update_time , 'DD-Mon-YYYY HH24:MI:SS'), to_char(l.time_remaining), to_char(l.elapsed_seconds), l.opname,l.target,l.target_desc,l.message,s.username,s.osuser,s.lockwait from v$session_longops l, v$session s where l.sid = s.sid and l.serial# = s.serial#; Select 'long', to_char (l.sid), to_char (l.serial#), to_char(l.sofar),

to_char(l.totalwork), to_char(l.start_time, 'DD-Mon-YYYY HH24:MI:SS' ), to_char ( l.last_update_time , 'DD-Mon-YYYY HH24:MI:SS'), s.username,s.osuser,s.lockwait from v$session_longops l, v$session s where l.sid = s.sid and l.serial# = s.serial#; select substr(username,1,15),target,to_char(start_time, 'DD-Mon-YYYY HH24:MI:SS' ), SOFAR,substr(MESSAGE,1,70) from v$session_longops; select USERNAME, to_char(start_time, 'DD-Mon-YYYY HH24:MI:SS' ), substr(message,1,90),to_char(time_remaining) from v$session_longops;