WHEN DO WE USE GV$SESSION_LONPOPS?
DBA’s confuse to use longops view for long running operations due to misunderstanding. This view cannot be used to monitor all long running operations but only for large resource intensive operations.
There are two main criteria for the records
- If the table scan exceeds 10,000 formatted blocks
- operation is greater than 6 seconds
Scenarios during which oracle consider things as long running sessions
- Rman Backup and Restore
- Parallel Query(Large chunks)
- Recovery ( Crash and Media )
- Large Full Table scans not all Full table scans
- Sorting
- Stats job
x$ksulop is the internal table which sample and populate the long running information in GV$SESSION_LONGOPS
SQL> select VIEW_DEFINITION from v$fixed_view_definition where VIEW_NAME='GV$SESSION_LONGOPS';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select inst_id, ksulosno, ksulosrn, ksulopna, ksulotna, ksulotde, ksulosf
r, ksulotot, ksulouni, to_date(ksulostm,'MM/DD/RR HH24:MI:SS','NLS_CALEND
AR=Gregorian'), to_date(ksulolut,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Greg
orian'), to_date(ksuloinft, 'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian
'), decode(ksulopna, 'Advisor', ksuloif2, decode(sign(ksulo
tot-ksulosfr),-1,to_number(NULL), decode(ksulosfr, 0, to_num
ber(NULL), round(ksuloetm*((ksulotot-ksulosfr)/ksulosfr))))
), ksuloetm, ksuloctx, ksulomsg, ksulounm, ksulosql, ksulosqh, ksul
osqi, ksulosqph, ksulosqesta, decode(ksulosqeid, 0, to_number(NULL), ksul
osqeid), decode(ksulosqplid, 0, to_number(NULL), ksulosqplid), ksu
losqplop, ksulosqplnm, ksuloqid, con_id from x$ksulopCurrently, there are 0 long running sessions in the database
kIsH@STAX<>select count(*) from v$session_longops;
COUNT(*)
----------
0
Note step 2 of the execution plan which shows large table full table scan. How can one consider a table size as large? It depends on the available resource and cost estimated by optimizer along with the conditions like 10000 block scans and elapsed second > 6.
Example: If a table size is 100TB and there is 1000 CPU with 1000 cores, then it is obvious that resource power is also aggressive as table size. In this case, long running session may be calculated differently.
kIsH@STAX<>set autot traceonly explain
kIsH@STAX<>select * from dbobj;
Execution Plan
----------------------------------------------------------
Plan hash value: 1315669886
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6464K| 813M| 34311 (1)| 00:00:02 |
| 1 | TABLE ACCESS FULL| DBOBJ | 6464K| 813M| 34311 (1)| 00:00:02 |
---------------------------------------------------------------------------
After 6 seconds, the output is printed in v$session_longops
kIsH@STAX<>set lines 200 pages 1000
col opname for a20
col target for a20
col message for a20
col sql_id for a20
SELECT sid,
serial#,
opname,
target,
sofar,
100 * (sofar / totalwork) TotalWork,
message,
sql_id,
elapsed_seconds,
time_remaining
from v$session_longops
order by sid;kIsH@STAX<>kIsH@STAX<>kIsH@STAX<>kIsH@STAX<>kIsH@STAX<> 2 3 4 5 6 7 8 9 10 11 12
no rows selected
kIsH@STAX<>/
no rows selected
kIsH@STAX<>/
no rows selected
kIsH@STAX<>/
no rows selected
kIsH@STAX<>/
no rows selected
kIsH@STAX<>/
no rows selected
kIsH@STAX<>/
no rows selected
kIsH@STAX<>/
SID SERIAL# OPNAME TARGET SOFAR TOTALWORK MESSAGE SQL_ID ELAPSED_SECONDS TIME_REMAINING
---------- ---------- -------------------- -------------------- ---------- ---------- -------------------- -------------------- --------------- --------------
34 40942 Table Scan SYS.DBOBJ 32 .0253614 Table Scan: SYS.DBO afw2anq220ubg 7 <== ela > 6 27594
BJ: 32 out of 126176 <====== formatted blocks > 10000
Blocks done
Summary:
- Operations like index scans may not be displayed in v$session_longops
- Small full table scans where there are less than 10000 block touches may also be not included