When v$session_longops is not long enough

With large table scans, sometimes the estimated total work is far beyond reality

SQL> select message from v$session_longops where target='SCOTT.EMP';

Table Scan:  SCOTT.EMP: 7377612 out of 629683 Blocks done

The total work is the Oracle estimation :

SQL> select blocks from dba_tables where table_name='EMP';


This may differ quite a lot from the segment size, for instance if the table is not very often analyzed :

SQL> select blocks, sysdate, last_analyzed from dba_tables where table_name='EMP';

---------- ------------------- -------------------
    629683 2013-04-21_09:21:47 2007-10-13_21:40:58

SQL> select blocks from dba_segments where segment_name='EMP';


I have customized my very long ops query to deal with very long waits.

col target for a20
set lin 150 pages 40000 termout off
alter session set nls_currency='%';
col PCT_DONE for 990.00L jus r
col time_remaining for 999999

  100*lo.sofar/seg.blocks PCT_DONE
  dba_segments seg,
  v$session_longops lo
  and lo.totalwork>0 and (lo.time_remaining>0 or lo.time_remaining is null)
  and regexp_substr(lo.target,'[^.]+') = seg.owner
  and regexp_substr(lo.target,'[^.]+$') = seg.segment_name

2 Replies to “When v$session_longops is not long enough”

  1. Laurent,

    thanks for sharing.

    However, Oracle’s “advanced partitioning” isn’t advanced enough to include the partition or subpartition into v$session_longops .
    I have filed an enhancement request for that long time ago, cmp. http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1099233454171#138068800346241191
    with no success so far – still waiting for feedback –

    So, in case you full scan (sub-)partitions of a partitioned table, your query has no chance know which physical segment the longop belongs to and falsely returns one estimation for each partition !

    And, I think, it should be possible to extend your query to the case of IOTs and index fast full scans.


  2. thanks for the iot suggestion. I was just trying to figure out how long my delete of 1 billion row will take. Quite happy it was over in less than one day 🙂

    about “progress”, the “time remaining” is probably better than nothing, and not so much worst than the typical progress bar, à la 99% completed, 10’000 years remaining

    but of course we would welcome better stats !

Leave a Reply