V$SESSION_LONGOPS

Using V$SESSION_LONGOPS


Many operations in the database may take a considerable amount of time. Parallel execution, Recovery Manager, large sorts, loads, and so on fall into this category. These long running operations take advantage of their ability to set values in the dynamic performance view, V$SESSION_LONGOPS to let us know how far along in their work they are, and so can your applications. This view displays the status of various database operations that run for longer than six seconds. That is, functions the database performs that the Oracle developers felt would normally take longer than six seconds have been instrumented to populate the V$SESSION_LONGOPS view. This does not mean anything that takes longer than six seconds will automatically appear in this view. These operations currently include many backup and recovery functions, statistics gathering, and query execution. More operations are added for every Oracle release.

Changes made to this view are immediately visible to other sessions, without the need to commit your transaction.

For any process that updates this view, you will be able to monitor their progress from another session by querying the V$SESSION_LONGOPS view. You too have the ability to populate rows in this view, typically one row, but you may use others if you like.

with the following meanings:
❑ RINDEX – Tells the server which row to modify in the V$SESSION_LONGOPS view. If you set this value to DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT, a new row will be allocated in this view for you, and the index of this row will be returned in RINDEX. Subsequent calls to SET_SESSION_LONGOPS with the same value for RINDEX will update this already existing row.

❑ SLNO – An internal value. You should initially pass a Null number in, and ignore its value otherwise. You should pass the same value in with each call.

❑ OP_NAME – The name of the long running process. It is limited to 64 bytes in size, and should be set to some string that will be easily identified and provides some meaning to you.

❑ TARGET – Typically used to hold the object ID that is the target of the long running operation (for example, the object ID of the table being loaded). You may supply any number you wish here, or leave it Null.

❑ CONTEXT – A user-defined number. This number would have meaning only to you. It is
simply any number you wish to store.

❑ SOFAR – This is defined as any number you want to store, but if you make this number be some percentage or indicator of the amount of work done, the database will attempt to estimate your time to completion for you. For example, if you have 25 things to do, and they all take more or less the same amount of time, you could set SOFAR to the number of things done so far, and then set the next parameter
TOTALWORK. The server will figure out how long it took you to get to where you are, and estimate how long it will take you to complete.

❑ TOTALWORK – This is defined as any number you want to store, but the same caveat for SOFAR applies here. If SOFAR is a percentage of TOTALWORK, representing your progress, the server will compute the time remaining to complete your task.

❑ TARGET_DESC – This is used to describe the contents of the TARGET input from above. If the TARGET actually contained an object ID, this might contain the object name for that object ID.

❑ UNITS – A descriptive term that categorizes what SOFAR and TOTALWORK are measured in. Units might be 'files', 'iterations', or 'calls' for example.
The API to set the values in this view is defined as:

PROCEDURE SET_SESSION_LONGOPS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
RINDEX BINARY_INTEGER IN/OUT
SLNO BINARY_INTEGER IN/OUT
OP_NAME VARCHAR2 IN DEFAULT
TARGET BINARY_INTEGER IN DEFAULT
CONTEXT BINARY_INTEGER IN DEFAULT
SOFAR NUMBER IN DEFAULT
TOTALWORK NUMBER IN DEFAULT
TARGET_DESC VARCHAR2 IN DEFAULT
UNITS VARCHAR2 IN DEFAULT


These are the values you can set. When you look at the V$SESSION_LONGOPS view, you'll see it has many more columns than these however:


SQL> desc v$session_longops

Name Null? Type
-------------------------------- -------- ----------------------
SID NUMBER
SERIAL# NUMBER
OPNAME VARCHAR2(64) **
TARGET VARCHAR2(64) **
TARGET_DESC VARCHAR2(32) **
SOFAR NUMBER **
TOTALWORK NUMBER **
UNITS VARCHAR2(32) **
START_TIME DATE
LAST_UPDATE_TIME DATE
TIME_REMAINING NUMBER
ELAPSED_SECONDS NUMBER
CONTEXT NUMBER **
MESSAGE VARCHAR2(512)
USERNAME VARCHAR2(30)
SQL_ADDRESS RAW(4)
SQL_HASH_VALUE NUMBER
QCSID NUMBER


The columns marked with ** are the ones you have control over, and can set.
The meanings are as follows:

❑ The SID and SERIAL# columns are used to join back to V$SESSION, to pick up the session information.

❑ The START_TIME column marks the time this record was created (typically your first call to DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS).

❑ The LAST_UPDATE_TIME column represents the time of your last call to
SET_SESSION_LONGOPS.

❑ The TIME_REMAINING is an estimate in seconds of the time to completion. It is equal to ROUND(ELAPSED_SECONDS*((TOTALWORK/SOFAR)-1)).

❑ The ELAPSED_SECONDS column is the time in seconds since the start of the long running operation, and the last update time.

❑ The MESSAGE column is a derived column. It concatenates together pieces of the OPNAME,TARGET_DESC, TARGET, SOFAR, TOTALWORK, and UNITS column to make a readable
description of the work in process.

❑ The USERNAME is the name of the user this process is executing under.

❑ The SQL_ADDRESS and SQL_HASH_VALUE may be used to look into V$SQLAREA to see what
SQL statement this process was last executing.

❑ The QCSID is used with parallel query. It would be the session of the parallel coordinator. So, what can you expect from this particular view? A small example will clearly show what it can provide for you.

In one session, if you run a block of code such as:
SQL> declare
l_nohint number default
dbms_application_info.set_session_longops_nohint;
l_rindex number default l_nohint;
l_slno number;
begin
for i in 1 .. 25
loop
dbms_lock.sleep(2);
dbms_application_info.set_session_longops
( rindex => l_rindex,
slno => l_slno,
op_name => 'my long running operation',

13 target => 1234,
14 target_desc => '1234 is my target',
15 context => 0,
16 sofar => i,
17 totalwork => 25,
18 units => 'loops'
19 );
20 end loop;
21 end;
22 /


This is a long running operation that will take 50 seconds to complete (the DBMS_LOCK.SLEEP just sleeps for two seconds). In another session, we can monitor this session via the query below (see the Chapter 23 on Invoker and Definer Rights for the definition of the PRINT_TABLE utility used in this code):

SQL> begin
print_table( 'select b.*
from v$session a, v$session_longops b
where a.sid = b.sid
and a.serial# = b.serial#' );
end;
/

SID : 11
SERIAL# : 635
OPNAME : my long running operation
TARGET : 1234
TARGET_DESC : 1234 is my target
SOFAR : 2
TOTALWORK : 25
UNITS : loops
START_TIME : 28-apr-2001 16:02:46
LAST_UPDATE_TIME : 28-apr-2001 16:02:46
TIME_REMAINING : 0
ELAPSED_SECONDS : 0
CONTEXT : 0
MESSAGE : my long running operation: 1234 is my target
1234: 2 out of 25 loops done
USERNAME : TKYTE
SQL_ADDRESS : 036C3758
SQL_HASH_VALUE : 1723303299
QCSID : 0
-----------------
PL/SQL procedure successfully completed.

SQL> /
SID : 11
SERIAL# : 635
OPNAME : my long running operation
TARGET : 1234
TARGET_DESC : 1234 is my target
SOFAR : 6
TOTALWORK : 25
UNITS : loops
START_TIME : 28-apr-2001 16:02:46
LAST_UPDATE_TIME : 28-apr-2001 16:02:55
TIME_REMAINING : 29
ELAPSED_SECONDS : 9
CONTEXT : 0
MESSAGE : my long running operation: 1234 is my target
1234: 6 out of 25 loops done
USERNAME : TKYTE
SQL_ADDRESS : 036C3758
SQL_HASH_VALUE : 1723303299
QCSID : 0
-----------------
PL/SQL procedure successfully completed.
SQL> /
SID : 11
SERIAL# : 635
OPNAME : my long running operation
TARGET : 1234
TARGET_DESC : 1234 is my target
SOFAR : 10
TOTALWORK : 25
UNITS : loops
START_TIME : 28-apr-2001 16:02:46
LAST_UPDATE_TIME : 28-apr-2001 16:03:04
TIME_REMAINING : 27
ELAPSED_SECONDS : 18
CONTEXT : 0
MESSAGE : my long running operation: 1234 is my target
1234: 10 out of 25 loops done
USERNAME : TKYTE
SQL_ADDRESS : 036C3758
SQL_HASH_VALUE : 1723303299
QCSID : 0
-----------------
PL/SQL procedure successfully completed.


The first question you might ask is, 'why did I join V$SESSION_LONGOPS to V$SESSION if I did not actually select any information from V$SESSION?' This is because the view V$SESSION_LONGOPS will contain values from rows of current, as well as legacy sessions. This view is not 'emptied out' when you log out. The data you left
there remains until some other session comes along, and reuses your slot. Therefore, to see long operations information for current sessions only, you want to join or use a sub-query to get current sessions only.

As you can see from the rather simple example, this information could be quite invaluable to you and your DBA, as far as monitoring long running stored procedures, batch jobs, reports, and so on, goes. A little bit of instrumentation can save a lot of guesswork in production. Rather than trying to 'guess' where a job might be and
how long it might take to complete, you can get an accurate view of where it is, and an educated guess as to the length of time it will take to complete.

Ref : Expert Oracle - Thomas Kyte