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';

MESSAGE
------------------------------------------------------------
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';

    BLOCKS
----------
    629683

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';

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

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

    BLOCKS
----------
   7749888

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

select
  lo.target,lo.sofar,seg.blocks,
  lo.ELAPSED_SECONDS*seg.blocks/lo.sofar-lo.ELAPSED_SECONDS TIME_REMAINING,
  100*lo.sofar/seg.blocks PCT_DONE
from
  dba_segments seg,
  v$session_longops lo
where
  lo.units='Blocks'
  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 thoughts on “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.

    Regards
    Matthias

  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

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>