Oracle Segment Does Not Equal Extents?

Question

For a given tablespace, why doesn't the sum of bytes in dba_extents equal the sum of bytes in dba_segments? (additional questions after sample script.)

SQL> with
  "SEG" as
    ( select 'segment_bytes' what
             , to_char(sum(bytes), '9,999,999,999,999') bytes
        from dba_segments
       where tablespace_name = 'MYDATA'
    )
  , "EXT" as
    ( select 'extent_bytes' what
             , to_char(sum(bytes), '9,999,999,999,999') bytes
        from dba_extents
       where tablespace_name = 'MYDATA'
    )
  , "FS" as
    ( select tablespace_name
             , sum(bytes) free_bytes
        from dba_free_space
       where tablespace_name = 'MYDATA'
       group by tablespace_name
    ),
  "DF" as
    ( select tablespace_name
             , sum(bytes) alloc_bytes
             , sum(user_bytes) user_bytes
        from dba_data_files
       where tablespace_name = 'MYDATA'
       group by tablespace_name
    )
  select what, bytes from SEG
  union all select 'datafile_bytes-freespace' what
                   , to_char(alloc_bytes - nvl(free_bytes, 0), '9,999,999,999,999') used_file_bytes
              from DF
                  left join FS
                         on DF.tablespace_name = FS.tablespace_name
  union all select 'datafile_userbytes-freespace' what
                   , to_char(user_bytes - nvl(free_bytes, 0), '9,999,999,999,999') used_user_bytes
             from DF
                  left join FS
                         on DF.tablespace_name = FS.tablespace_name
  union all select what, bytes from EXT
;

WHAT                         BYTES            
---------------------------- ------------------
segment_bytes                 2,150,514,819,072
datafile_bytes-freespace      2,150,528,540,672
datafile_userbytes-freespace  2,150,412,845,056
extent_bytes                  2,150,412,845,056

4 rows selected.

I would have expected segment_bytes to equal either extent_bytes or datafile_bytes-freespace, but it falls somewhere in between.

Is segment_bytes more than extent_bytes due to segment "overhead" (keeping track of all of the extents)?

If so, then is it also true that this segment "overhead" is part of the datafile "overhead"?

Oracle 19.1 Enterprise Edition. Thanks in advance.

score 2 · Accepted Answer · answered Jul 03 '21 at 01:54

For example, the difference between dba_segments and dba_extents might be in the objects from recyclebin: please look at the results from my test database:

with 
 seg as (
    select segment_name,sum(bytes) b1 
    from dba_segments 
    group by segment_name
)
,ext as (
    select segment_name,sum(bytes) b2 
    from dba_extents 
    group by segment_name
)
select
   seg.segment_name seg1
  ,ext.segment_name seg2
  ,b1,b2
from seg full outer join ext on seg.segment_name=ext.segment_name
where lnnvl(b1=b2)
order by 1,2;

Results:

SEG1                           SEG2                                   B1         B2
------------------------------ ------------------------------ ---------- ----------
BIN$xi7yNJwFcIrgUwIAFaxDaA==$0                                     65536
BIN$xi7yNJwGcIrgUwIAFaxDaA==$0                                     65536
_SYSSMU10_2262159254$          _SYSSMU10_2262159254$                   0    4325376
_SYSSMU1_3588498444$           _SYSSMU1_3588498444$                    0    3276800
_SYSSMU2_2971032042$           _SYSSMU2_2971032042$                    0    2228224
_SYSSMU3_3657342154$           _SYSSMU3_3657342154$                    0    2228224
_SYSSMU4_811969446$            _SYSSMU4_811969446$                     0    2293760
_SYSSMU5_3018429039$           _SYSSMU5_3018429039$                    0    3276800
_SYSSMU6_442110264$            _SYSSMU6_442110264$                     0    2228224
_SYSSMU7_2728255665$           _SYSSMU7_2728255665$                    0    2097152
_SYSSMU8_801938064$            _SYSSMU8_801938064$                     0    2228224
_SYSSMU9_647420285$            _SYSSMU9_647420285$                     0    3276800

12 rows selected.

As you can see first 2 rows are objects from recyclebin, so you can run the same query and check if your objects are in recyclebin too. They are not visible in dba_extents, because they filtered out by segment_flag:

select text_vc from dba_views where view_name='DBA_EXTENTS';

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
  and bitand(NVL(ds.segment_flags,0), 65536) = 0
union all
select
       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 ds.tablespace_id = f.ts#
  and e.ktfbuefno = f.relfile#
  and bitand(NVL(ds.segment_flags, 0), 1) = 1
  and bitand(NVL(ds.segment_flags,0), 65536) = 0;

So if we comment out those predicates (bitand(NVL(segment_flags,0)....) and check our difference (BIN$... and _SYSSMU... objects), we will find which predicates filter them out:

with
 my_dba_extents(
   OWNER,SEGMENT_NAME,PARTITION_NAME
  ,SEGMENT_TYPE,TABLESPACE_NAME,EXTENT_ID,FILE_ID
  ,BLOCK_ID,BYTES,BLOCKS,RELATIVE_FNO
  ,segment_flags) 
as (
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#
       ,segment_flags
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
--  and bitand(NVL(ds.segment_flags,0), 65536) = 0
union all
select
       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
       ,segment_flags
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 ds.tablespace_id = f.ts#
  and e.ktfbuefno = f.relfile#
--  and bitand(NVL(ds.segment_flags, 0), 1) = 1
--  and bitand(NVL(ds.segment_flags,0), 65536) = 0
)
select 
   segment_name
  ,bitand(NVL(segment_flags, 0), 1) as predicate_1
  ,bitand(NVL(segment_flags,0), 65536) as predicate_2
  ,case when bitand(NVL(segment_flags,0), 1) = 0     then 'y' else 'n' end pred_1_res
  ,case when bitand(NVL(segment_flags,0), 65536) = 0 then 'y' else 'n' end pred_2_res
from my_dba_extents e
where e.segment_name like 'BIN%'
   or e.segment_name like '_SYSSMU%';

SEGMENT_NAME                   PREDICATE_1 PREDICATE_2 PRED_1_RES     PRED_2_RES
------------------------------ ----------- ----------- -------------- --------------
_SYSSMU1_3588498444$                     1           0 n              y
_SYSSMU1_3588498444$                     1           0 n              y
_SYSSMU1_3588498444$                     1           0 n              y
_SYSSMU1_3588498444$                     1           0 n              y
_SYSSMU1_3588498444$                     1           0 n              y
_SYSSMU2_2971032042$                     1           0 n              y
_SYSSMU2_2971032042$                     1           0 n              y
...
_SYSSMU10_2262159254$                    1           0 n              y
_SYSSMU10_2262159254$                    1           0 n              y
_SYSSMU10_2262159254$                    1           0 n              y
BIN$xi7yNJwGcIrgUwIAFaxDaA==$0           1       65536 n              n
BIN$xi7yNJwFcIrgUwIAFaxDaA==$0           1       65536 n              n

Re "datafile_bytes-freespace": Don't forget that each datafile has own header, so nor dba_segments, nor dba_extents should not count it.

PS. Other 10 rows are undo segments, but that is not your case since your query checks just your MYDATA tablespace, not UNDO.

Oracle Segment Does Not Equal Extents?

1 Answers1