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