Oracle numbers in K/M/G/T/P/E
Oracle is very well instrumented, for decades, from a time where measuring the memory in bytes was ok. But today, we spend a lot of time converting bytes in KB, GB, TB to read it easily. I would love to see a Human-Readable format for TO_CHAR, but there’s not. Here is a workaround without having to create a new function.
DBMS_XPLAN does that when displaying execution plans and we can access the functions it uses internally. The metrics can be numbers, and then the Kilo, Mega, Giga applies to powers of 1000. Or they can be a size in bytes, and we prefer the powers of 1024. Or they can be a time in seconds, and then we use a base 60. And then we have 3 sets of functions:
- FORMAT_SIZE for base 2 numbers where we use powers of 1024 🖥
- FORMAT_NUMBER for base 10 numbers with powers of 1000 👐
- FORMAT_TIME for base 60 numbers as this is how we represent time ⏲️
I’ll show an example with the three of them, to get human readable numbers from V$SYSSTAT and V$SYSTEM_EVENT.
Bytes
Let’s say I want to look at the size of data exchanged through SQL*Net, I can query v$sysstat for statistics in bytes:
SQL> select name,dbms_xplan.FORMAT_SIZE(value) FORMAT_SIZE,dbms_xplan.FORMAT_SIZE2(value) FORMAT_SIZE2 from v$sysstat where name like 'bytes%SQL*Net%' and value>0 order by value desc;NAME FORMAT_SIZE FORMAT_SIZE2
bytes received via SQL*Net from client 23T 23t
bytes sent via SQL*Net to client 12T 12t
bytes received via SQL*Net from dblink 165G 166g
bytes sent via SQL*Net to dblink 201M 202m
As you see, we have two functions in DBMS_XPLAN. FORMAT_SIZE2 looks better as it is right-padded. But it is in lower-case, and it is rounded lower(1E6, we will see later). FORMAT_SIZE has a 1E3 precision. SIZE format is used to divide by 1024, typically for bytes.
Numbers
When looking at the numbers that are not in bytes but decimal, we divide by 1000 and again, there are two functions. Note that here all are uppercase and we see a better precision for FORMAT_NUMBER rather than FORMAT_NUMBER2:
SQL> select name,dbms_xplan.FORMAT_NUMBER(value) FORMAT_NUMBER,dbms_xplan.FORMAT_NUMBER2(value) FORMAT_NUMBER2 from v$sysstat where name like '%SQL*Net%roundtrips%' and value>0 order by value desc;NAME FORMAT_NUMBER FORMAT_NUMBER2
SQL*Net roundtrips to/from client 9567M 10G
SQL*Net roundtrips to/from dblink 786K 787K
Those statistics should match the wait events. Here is the same number formatting on the wait count:
SQL> select event,dbms_xplan.FORMAT_NUMBER(total_waits) FORMAT_NUMBER,dbms_xplan.FORMAT_NUMBER2(total_waits) FORMAT_NUMBER2 from v$system_event where event like '%SQL*Net%' order by total_waits desc;EVENT FORMAT_NUMBER FORMAT_NUMBER2
SQL*Net message from client 9552M 10G
SQL*Net message to client 9514M 10G
SQL*Net more data to client 1383M 1G
SQL*Net more data from client 530M 530M
SQL*Net more data from dblink 5377K 5M
SQL*Net break/reset to client 863K 864K
SQL*Net message from dblink 800K 800K
SQL*Net message to dblink 786K 787K
SQL*Net break/reset to dblink 18 18
Seconds
While there in the wait events, we have the time waited in centiseconds. Once divided by 100 to get it in seconds, I can format it in hours, minutes and seconds with FORMAT_TIME:
SQL> select event,dbms_xplan.FORMAT_TIME_S(time_waited/100) FORMAT_TIME_S,time_waited/100 from v$system_event where event like '%SQL*Net%' order by total_waits desc;EVENT FORMAT_TIME_S TIME_WAITED/100
SQL*Net message from client 999:59:59 376461581.58
SQL*Net message to client 01:58:45 7124.77
SQL*Net more data to client 247:05:21 889521.16
SQL*Net more data from client 376:29:01 1355340.99
SQL*Net more data from dblink 04:09:47 14987.19
SQL*Net break/reset to client 00:46:26 2785.71
SQL*Net message from dblink 01:47:27 6447.04
SQL*Net message to dblink 00:00:01 0.7
SQL*Net break/reset to dblink 00:00:00 0
You can see that there’s a limit above which any number is rounded to 999:59:59 and it easy to check that the limit is at 1000 hours:
SQL> select to_number(column_value),dbms_xplan.format_time_s(to_number(column_value)) from xmltable('3599995 to 3600005');TO_NUMBER(COLUMN_VALUE) DBMS_XPLAN.FORMAT_TIME_S(TO_NUMBER(COLUMN_VA
----------------------- --------------------------------------------
3599995 999:59:55
3599996 999:59:56
3599997 999:59:57
3599998 999:59:58
3599999 999:59:59
3600000 999:59:59
3600001 999:59:59
3600002 999:59:59
3600003 999:59:59
3600004 999:59:59
3600005 999:59:59
This is probably to be sure to get a maximum length. Remember that this comes from DBMS_XPLAN and we do not expect an execution time of 40 days…
Then for longer durations, we need to extract the number of days. Here is an example:
SQL> select event,'+'||trunc(time_waited/100/60/60/24)||' '||dbms_xplan.FORMAT_TIME_S(mod(time_waited/100,3600*24)) "+days hh24:mi:ss",dbms_xplan.FORMAT_TIME_S(time_waited/100) FORMAT_TIME_S from v$system_event where event='PING' order by time_waited desc;EVENT +days hh24:mi:ss FORMAT_TIME_S
----- ----------------- -------------
PING +45 08:18:11 999:59:59
Of course, the INTERVAL datatype is most powerful in that case…
Rounding
Here is a little test to show the difference between the two functions:
select to_char(n,'9.99999EEEE') e,n
,dbms_xplan.format_number(n) "NUMBER"
,dbms_xplan.format_number2(n) "NUMBER2"
,dbms_xplan.format_size(n) "SIZE"
,dbms_xplan.format_size2(n) "SIZE2"
from (
select to_number(column_value)-2+1000*1000 n from xmltable('1 to 3')
union all
select to_number(column_value)-2+1024*1024 n from xmltable('1 to 3')
union all
select to_number(column_value)-2+1e9 n from xmltable('1 to 3')
union all
select to_number(column_value)-2+1e12 n from xmltable('1 to 3')
)
;
NUMBER NUMBER2 SIZE SIZE29.99999E+05 999999 999K 1000K 976K 977k
1.E+06 1000000 1000K 1M 976K 977k
1.E+06 1000001 1000K 1M 976K 977k
1.04858E+06 1048575 1048K 1M 1023K 1024k
1.04858E+06 1048576 1048K 1M 1024K 1m
1.04858E+06 1048577 1048K 1M 1024K 1m
1.E+09 999999999 999M 1000M 953M 954m
1.E+09 1000000000 1000M 1G 953M 954m
1.E+09 1000000001 1000M 1G 953M 954m1.E+18 999999999999999999 999P 1000P 888P 888p
1.E+18 1000000000000000000 1000P ##### 888P 888p
1.E+18 1000000000000000001 1000P ##### 888P 888p
So be careful, they have different rounding rules.
The size of your database
As another example, here is an easy query to show the size of the database with human-readable numbers:
select
'Number of Files: '||dbms_xplan.FORMAT_NUMBER(count(*)),
'Database Size: '||dbms_xplan.FORMAT_SIZE(sum(bytes))
from dba_data_files;
