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!
