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$ksulop

Currently, 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

Leave a Reply