Oracle dynamic performance view v $ session_longops

Document directory

• V $ session_longops

V $ session_longops

This view shows the status of operations that have been running for more than 6 seconds. This includes backup, recovery, statistics collection, and query.

 

To monitor the query progress, you must use the cost-based optimization method and:

L set timed_statistics or SQL _trace to true.

L collect object statistics through analyze or dbms_stats data packets.

 

You can use dbms_application_info.set_session_longops to add application-specific long run operation information to this view. For more information about dbms_application_info.set_session_longops, see Oracle supplied PL/SQL packages and types reference.

 

V $ session_longopsColumn description

 

L Sid: Session ID

L serial #: Session serial number

L opname: Brief Description

L target: the object where the operation is running

L target_desc: Target Object Description

L SOFAR: The workload completed so far

L totalwork: total workload

L units: unit of work

L start_time: operation start time

L last_update_time: The Last Update Time of the statistical item.

L time_remaining: the remaining time (in seconds) for the operation to be completed)

L elapsed_seconds: Total time consumed from the operation (in seconds)

L context: frontend and backend relationships

L message: complete description of the statistical item

L Username: User ID for the operation

L SQL _address: Column Used for connection Query

L SQL _hash_value: Column Used for connection Query

L qcsid:

 

Example:

Find a large table and make sure it takes more than 6 seconds to query it. Oh, it's my strength to make it fast ~~

SQL> set timing on

SQL> Create Table TTT as select level LV, rownum rn from dual connect by level <10000000; -- create a temporary table

Table created

Executed in 19.5 seconds

SQL> commit;

Commit complete

Executed in 0 seconds

SQL> select * from (select * From TTT order by Lv DESC) Where rownum <2; -- execute a time-consuming Query

 

LV Rn

--------------------

9999999 9999999

Executed in 9.766 seconds -- Haha, success exceeds 6 seconds

SQL> select Sid, opname, SOFAR, totalwork, units, SQL _hash_value from V $ session_longops; ---- check if there are records in V $ session_longops.

 

Sid opname SOFAR totalwork units SQL _hash_value

--------------------------------------------------------------------------------------------------------------------------------------------

10 Table scan 47276 47276 blocks 2583310173

Executed in 0.047 seconds

 

SQL> select a. SQL _text from V $ sqlarea A, V $ session_longops B where a. hash_value = B. SQL _hash_value; -- Contact hash_value to query the query statement just executed.

 

SQL _text

--------------------------------------------------------------------------------

Select * from (select * From TTT order by Lv DESC) Where rownum <2

Executed in 0.063 seconds