Why does the view family xxx_EXTENTS run so slowly on Oracle 8.1 ?

The Oracle (tm) Users' Co-Operative FAQ

Why does the view family xxx_EXTENTS run so slowly on Oracle 8.1 ?

There's been a lot of press about the bitmap in locally managed tablespaces, and that this is the cause of excessive physical reads when using DBA_EXTENTS. This may be true, but its doubtful that this is the full cause of the slowness, the 'proof' for this being that sites which have not converted to locally managed tablespace still have the same grievance.

Its most probably due to the increased diversity of objects within Oracle. If we look the definition for DBA_EXTENTS (in 8.1.7) we see:

select ds.owner, ds.segment_name, ds.partition_name, ds.segment_type,
       ds.tablespace_name,
       e.ext#, f.file#, e.block#, e.length * ds.blocksize, e.length, e.file#
from sys.uet$ e, sys.sys_dba_segs ds, sys.file$ f
where e.segfile# = ds.relative_fno
  and e.segblock# = ds.header_block
  and e.ts# = ds.tablespace_id
  and e.ts# = f.ts#
  and e.file# = f.relfile#
  and bitand(NVL(ds.segment_flags,0), 1) = 0
union all
select /*+ ordered use_nl(e) use_nl(f) */
       ds.owner, ds.segment_name, ds.partition_name, ds.segment_type,
       ds.tablespace_name,
       e.ktfbueextno, f.file#, e.ktfbuebno,
       e.ktfbueblks * ds.blocksize, e.ktfbueblks, e.ktfbuefno
from sys.sys_dba_segs ds, sys.x$ktfbue e, sys.file$ f
where e.ktfbuesegfno = ds.relative_fno
  and e.ktfbuesegbno = ds.header_block
  and e.ktfbuesegtsn = ds.tablespace_id
  and e.ktfbuesegtsn = f.ts#
  and e.ktfbuefno = f.relfile#
  and bitand(NVL(ds.segment_flags, 0), 1) = 1

which does not appear too "traumatic" until we look at the definition for "SYS_DBA_SEGS" which is obviously the main driver in the above query. SYS_DBA_SEGS looks like:

select u.name, o.name, o.subname,
       so.object_type, s.type#,
       ts.ts#, ts.name, ts.blocksize,
       f.file#, s.block#,
       s.blocks * ts.blocksize, s.blocks, s.extents,
       s.iniexts * ts.blocksize,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extsize * ts.blocksize),
       s.minexts, s.maxexts,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extpct),
       decode(s.lists, 0, 1, s.lists), decode(s.groups, 0, 1, s.groups),
       s.file#, s.cachehint, NVL(s.spare1,0), o.dataobj#
from sys.user$ u, sys.obj$ o, sys.ts$ ts, sys.sys_objects so, sys.seg$ s,
     sys.file$ f
where s.file# = so.header_file
  and s.block# = so.header_block
  and s.ts# = so.ts_number
  and s.ts# = ts.ts#
  and o.obj# = so.object_id
  and o.owner# = u.user#
  and s.type# = so.segment_type_id
  and o.type# = so.object_type_id
  and s.ts# = f.ts#
  and s.file# = f.relfile#
union all
select u.name, un.name, NULL,
       'ROLLBACK', s.type#,
       ts.ts#, ts.name, ts.blocksize, f.file#, s.block#,
       s.blocks * ts.blocksize, s.blocks, s.extents,
       s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts,
       s.maxexts, s.extpct,
       decode(s.lists, 0, 1, s.lists), decode(s.groups, 0, 1, s.groups),
       s.file#, s.cachehint, NVL(s.spare1,0), un.us#
from sys.user$ u, sys.ts$ ts, sys.undo$ un, sys.seg$ s, sys.file$ f
where s.file# = un.file#
  and s.block# = un.block#
  and s.ts# = un.ts#
  and s.ts# = ts.ts#
  and s.user# = u.user#
  and s.type# = 1
  and un.status$ != 1
  and un.ts# = f.ts#
  and un.file# = f.relfile#
union all
select u.name, to_char(f.file#) || '.' || to_char(s.block#), NULL,
       decode(s.type#, 2, 'DEFERRED ROLLBACK', 3, 'TEMPORARY',
                      4, 'CACHE', 9, 'SPACE HEADER', 'UNDEFINED'), s.type#,
       ts.ts#, ts.name, ts.blocksize,
       f.file#, s.block#,
       s.blocks * ts.blocksize, s.blocks, s.extents,
       s.iniexts * ts.blocksize,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extsize * ts.blocksize),
       s.minexts, s.maxexts,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extpct),
       decode(s.lists, 0, 1, s.lists), decode(s.groups, 0, 1, s.groups),
       s.file#, s.cachehint, NVL(s.spare1,0), s.hwmincr
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.file$ f
where s.ts# = ts.ts#
  and s.user# = u.user#
  and s.type# not in (1, 5, 6, 8)
  and s.ts# = f.ts#
  and s.file# = f.relfile#

the three parts of which roughly correspond to "normal" segments, rollback segments and temporary (or transient) segments. Of course to compound things further, the first query refers to a list of all segments in the database via "SYS_OBJECTS", the definition of which is:

select decode(bitand(t.property, 8192), 8192, 'NESTED TABLE', 'TABLE'), 2, 5,
       t.obj#, t.file#, t.block#, t.ts#
from sys.tab$ t
where bitand(t.property, 1024) = 0               /* exclude clustered tables */
union all
select 'TABLE PARTITION', 19, 5,
       tp.obj#, tp.file#, tp.block#, tp.ts#
from sys.tabpart$ tp
union all
select 'CLUSTER', 3, 5,
       c.obj#, c.file#, c.block#, c.ts#
from sys.clu$ c
union all
select decode(i.type#, 8, 'LOBINDEX', 'INDEX'), 1, 6,
       i.obj#, i.file#, i.block#, i.ts#
from sys.ind$ i
where i.type# in (1, 2, 3, 4, 6, 7, 8, 9)
union all
select 'INDEX PARTITION', 20, 6,
       ip.obj#, ip.file#, ip.block#, ip.ts#
from sys.indpart$ ip
union all
select 'LOBSEGMENT', 21, 8,
       l.lobj#, l.file#, l.block#, l.ts#
from sys.lob$ l
union all
select 'TABLE SUBPARTITION', 34, 5,
       tsp.obj#, tsp.file#, tsp.block#, tsp.ts#
       from sys.tabsubpart$ tsp
union all
select 'INDEX SUBPARTITION', 35, 6,
       isp.obj#, isp.file#, isp.block#, isp.ts#
from sys.indsubpart$ isp
union all
select decode(lf.fragtype$, 'P', 'LOB PARTITION', 'LOB SUBPARTITION'),
       decode(lf.fragtype$, 'P', 40, 41), 8,
       lf.fragobj#, lf.file#, lf.block#, lf.ts#
from sys.lobfrag$ lf

So physical reads of bitmaps or not, DBA_EXTENTS is a very complicated structure which should not be queried haphazardly. In these times of uniform extent sizes, it should be possible (in most cases) to glean extent information from other sources.

Further reading: N/A