Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DB physical size and logical sizes vary greatly

User_4ZSEXApr 11 2017 — edited Apr 12 2017

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

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 10 2017
Added on Apr 11 2017
10 comments
2,516 views