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 Comments

  • 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

  • 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.