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
   coalesce(
        (
            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(slo.target, 1, instr(slo.target, '.') - 1)
            and dt.TABLE_NAME=substr(slo.target, instr(slo.target, '.') + 1)
            and dt.PARTITIONED='NO'       
        ),
        (
            -- Jonathan Lewis, see http://jonathanlewis.wordpress.com/2014/01/01/nvl-2/#comment-62048
            select
               ob.subobject_name || ' (' || ob.object_type || ')'
            from v$session s, dba_objects ob
            where
              ob.object_id = s.row_wait_obj#
            and s.sid = slo.sid
            and ob.OBJECT_TYPE like '%PARTITION%'            
        ),
        (
            -- superfluous most likely
            select
               de.partition_name || ' (' || de.segment_type || ') NOT SUPERFLUOUS IF YOU SEE THAT'
            from v$session_wait sw, dba_extents de
            where
              sw.sid=slo.sid
            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(slo.target, 1, instr(slo.target, '.') - 1)
            and de.segment_type in
            (
               'TABLE PARTITION', 'TABLE SUBPARTITION',
               'INDEX PARTITION', 'INDEX SUBPARTITION'            
            )
            and de.segment_name in
            (
                 -- table
                 select
                    substr(slo.target, instr(slo.target, '.') + 1)
                 from dual
                 union all
                 -- index
                 select di.index_name
                 from dba_indexes di
                 where di.owner=substr(slo.target, 1, instr(slo.target, '.') - 1)
                 and di.TABLE_NAME = substr(slo.target, instr(slo.target, '.') + 1)
            )
         ),
        'unknown'
      )
   as partition_info,     
   slo.*
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.

About these ads

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: