DB version: 11.2.0.4
Platform : Oracle Linux 6.4
I have a VLDB where the DB physical size is much higher than the logical size. ie. sum(dba_data_files.size) is much larger than sum(dba_extents.bytes).
sum(dba_data_files.size) is 25 Terabytes
sum(dba_extents.bytes) is 8.6 Terabytes
I would like to know why such difference.
This DB has lot of partitioned tables with lot of empty partitions. And I don't want to drop these empty partitions as these partitions are created by product software. Could this be the reason why the physical size and logical sizes vary greatly ? Any other potential causes for this ?
Also, I would like to know if it is normal for the below query on dba_extents to take 90 minutes in Very Large DBs.
SQL> select sum(bytes/1024/1024/1024/1024) PhysicalFileSizeTB from dba_data_files;
PHYSICALFILESIZETB
------------------
28.7894449
SQL> set time on timing on
18:38:25 SQL>
18:38:26 SQL>
-- The below query on dba_extents took nearly 90 minutes to execute !!!
18:38:26 SQL> select /*+ parallel(30) */ sum(bytes/1024/1024/1024/1024) LogicalDBsizeTB from dba_extents;
LOGICALDBSIZETB
---------------
8.60878474
Elapsed: 01:28:50.66
--- Query on DBA_SEGMENTS took only 1 minute 20 seconds though
21:30:41 SQL> select /*+ parallel(10) */ sum(bytes/1024/1024/1024/1024) LogicalDBsizeTB from dba_segments;
LOGICALDBSIZETB
---------------
8.6283083
Elapsed: 00:01:20.22
--- Two business schemas in this DB have lots of partitioned tables.
--- I used num_rows column to determine the partitions which are empty. Optimizer stats in these tables are not very accurate though.
--- Once every week , I drop unwanted (non-empty) partitions to free up space
select TABLE_OWNER, count(*) from DBA_tab_partitions where TABLE_owner in ('WMSCOM', 'PKTSOM')
GROUP BY TABLE_OWNER ORDER BY COUNT(*) DESC;
TABLE_OWNER COUNT(*)
------------------------------ ----------
WMSCOM 215292
PKTSOM 116398
select TABLE_OWNER, count(*) from DBA_tab_partitions where TABLE_owner in ('WMSCOM', 'PKTSOM')
and num_rows = 0
GROUP BY TABLE_OWNER ORDER BY COUNT(*) DESC;
TABLE_OWNER COUNT(*)
------------------------------ ----------
WMSCOM 124470
PKTSOM 83847
--- SubPartitions
select TABLE_OWNER, count(*) from DBA_TAB_SUBPARTITIONS where TABLE_owner in ('WMSCOM', 'PKTSOM')
GROUP BY TABLE_OWNER ORDER BY COUNT(*) DESC;
TABLE_OWNER COUNT(*)
------------------------------ ----------
WMSCOM 11072
PKTSOM 6080
select TABLE_OWNER, count(*) from DBA_TAB_SUBPARTITIONS where TABLE_owner in ('WMSCOM', 'PKTSOM')
and num_rows = 0
GROUP BY TABLE_OWNER ORDER BY COUNT(*) DESC;
no rows selected
Elapsed: 00:00:00.01