How Can DBMS_XPLAN.FORMAT_SIZE Be Useful For You?

At one of the presentations I attended this year at Oracle Open World, the speaker Franck Pachot, had an interesting way of showing the size of a table segment in his demo. It really caught my eye, and made me curious, so I decided to look into it.

The way he was retrieving the size of a table, was with the function dbms_xplan.format_size. It seems strange, as dbms_xplan is used for displaying execution plans, and not for displaying bytes or numbers (or so I thought).

It turns out that dbms_xplan has a few undocumented functions, that are used to convert numbers into Human-Readable format, while dbms_xplan displays execution plans.
What is a Human-Readable format? Human-Readable format is a representation of the information or data, that can be easily and naturally read by humans. Instead of reading how many bytes the table size is, you could actually get the size right in MB or GB. It is much easier to read 1GB, then 1073741824 bytes.

The three undocumented functions that I am talking about are: dbms_xplan.format_size,dbms_xplan.format_number,dbms_xplan.format_time_s.
The one that I am more interested in is, dbms_xplan.format_size.Let’s look at examples, to see where and how these functions could be useful for you!

DBMS_XPLAN.FORMAT_SIZE is used to display values that represent bytes or sizes, therefore these numbers are to be divided by 1024, same as you would convert bytes to GB. To easier remember the purpose of the function, think about table size, datafile size…thus the name format_size.
Here is an example below:

select segment_name, dbms_xplan.format_size(bytes) as SEG_Size
from dba_segments
where owner='IP'
and segment_name like '%APP%'
and segment_type='TABLE'
order by bytes;

SEGMENT_NAME                   SEG_SIZE
------------------------------ ----------
APPLICATION_TABLE              65536
APP_VALIDATION_T               704K
USER_APP_TABLE                 76M
HR_APP_TABLE                   10G

Another use for this function is to display the datafile sizes:

select file_name, dbms_xplan.format_size(bytes) as file_size
from dba_data_files
where tablespace_name like '%UNDO%' order by bytes;

FILE_NAME                                          FILE_SIZE
-------------------------------------------------- ----------
C:\oracle\HRTST\datafile\undotbs1.dbf              100M
C:\oracle\HRTST\datafile\undotbs2.dbf              3G

You can also quickly display the database size:

select dbms_xplan.format_size(sum(bytes)) as db_size
from dba_data_files;

DB_SIZE
--------------
25G

DBMS_XPLAN.FORMAT_NUMBER is used for numbers that have to be divided by 1000, such as converting decimal numbers.

select 1000000 original_number,
dbms_xplan.format_number(1000000) formated_number
from dual;

ORIGINAL_NUMBER FORMATED_NUMBER
--------------- ------------------
1000000         1000K

DBMS_XPLAN.FORMAT_TIME_S is used to format time that is represented in seconds, and display it in the format HH:MI:SS.

Have a look at an example below:

select dbms_xplan.format_time_s(3600) as display_time
from dual;

DISPLAY_TIME
------------
01:00:00

select dbms_xplan.format_time_s(4500) as display_time from dual;

DISPLAY_TIME
------------
01:15:00

Beware that these functions do round the numbers, so the displayed values might not always be 100% accurate.

Did you know about these functions? Do you find them useful? Leave a comment below, and let me know!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive

The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!