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:
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' ), ( select de.partition_name || ' (' || de.segment_type || ')' 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.