Sokrates on Oracle

Partition Info in V$SESSION_LONGOPS

Posted by Matthias Rogel on 10. May 2013

Oracle’s advanced partitioning has some deficiencies. For example, partition info is missing in V$SESSION_LONGOPS for scan-operations ( full table scans, full index scans ). V$SESSION_LONGOPS.TARGET only shows OWNER.TABLE_NAME in these cases, even when the underlying table/index is partitioned, though the longop doesn’t refer to the whole segment but only to one (sub-)partition of it.
I filed an enhancement request several years ago concerning this matter, but never received any feedback.
However, there is a workaround to that. In many cases, we can find out on which (sub-) partition the longop is working on: V$SESSION_WAIT’s P1- and P2-info can be used for that in case the session is waiting mainly on I/O ( which might be most likely for many systems. )
Here is an extension to V$SESSION_LONGOPS which tries to figure out this additional info.

Update 27/02/2014
Note that the original version has been improved by Jonathan Lewis. I have marked the relevant part with a corresponding comment.
I haven’t observed so far that I wasn’t able to get the partition information from v$session.row_wait_obj# ( as suggested by him ), but from the part marked as “superfluous most likely” ( my original version ). However, I have no proof that this is not possible.

            select 'does not apply'
            from dual
            where slo.TARGET not like '%.%'
            or slo.TARGET is null
            select 'does not apply'
            from dba_tables dt
            where dt.OWNER=substr(, 1, instr(, '.') - 1)
            and dt.TABLE_NAME=substr(, instr(, '.') + 1)
            and dt.PARTITIONED='NO'       
            -- Jonathan Lewis, see
               ob.subobject_name || ' (' || ob.object_type || ')'
            from v$session s, dba_objects ob
              ob.object_id = s.row_wait_obj#
            and s.sid = slo.sid
            and ob.OBJECT_TYPE like '%PARTITION%'            
            -- superfluous most likely
               de.partition_name || ' (' || de.segment_type || ') NOT SUPERFLUOUS IF YOU SEE THAT'
            from v$session_wait sw, dba_extents de
            and slo.opname like '%Scan%'
            and sw.P1TEXT like 'file%'
            and sw.P1 = de.FILE_ID and sw.P2 between de.BLOCK_ID and de.BLOCK_ID + de.BLOCKS - 1
            and de.owner = substr(, 1, instr(, '.') - 1)
            and de.segment_type in
               'INDEX PARTITION', 'INDEX SUBPARTITION'            
            and de.segment_name in
                 -- table
                    substr(, instr(, '.') + 1)
                 from dual
                 union all
                 -- index
                 select di.index_name
                 from dba_indexes di
                 where di.owner=substr(, 1, instr(, '.') - 1)
                 and di.TABLE_NAME = substr(, instr(, '.') + 1)
   as partition_info,     
from v$session_longops slo
where slo.TIME_REMAINING > 0

Note that this might take a bit longer than a simple

select slo.*
from v$session_longops slo
where slo.TIME_REMAINING > 0

, though due to coalesce’s short circuiting it is quite efficient.


2 Responses to “Partition Info in V$SESSION_LONGOPS”

  1. cdonnellytx76 said

    Shouldn’t it be

    sw.P1TEXT like ‘%file%’


    sw.P1TEXT like ‘file%’

    e.g. “db file scattered read”

    Note: I am on 11.2.

    • Hi Cdonnellytx76,

      I never observed “db file scattered read” in v$session_wait.p1text.
      What does

      select count(*), p1text from dba_hist_active_sess_history group by p1text having p1text like ‘%file%';

      gives for you ?


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: