Kilobytes, Kibibytes and DBMS_XPLAN undocumented functions

How many bytes in a Kilobyte ? The answer to this question is pretty obvious…and, apparently, wrong.
Yep, apparently we’ve had it wrong all these years for there are, officially, 1000 bytes in a Kilobyte, not 1024.
Never mind that 1000 is not a factor of 2 and that, unless some earth-shattering breakthrough has happened whilst I wasn’t paying attention, binary is still the fundemental basis of computing.
According to the IEEE, there are 1000 bytes in a kilobyte and we should all get used to talking about a collection of 1024 bytes as a Kibibyte

Can you imagine dropping that into a conversation ? People might look at you in a strange way the first time “Kibibyte” passes your lips. If you then move on and start talking about Yobibytes, they may well conclude that you’re just being silly.

Let’s face it, if you’re going to be like that about things then C++ is actually and object orientated language and the proof is not in the pudding – the proof of the pudding is in the eating.

All of which petulant pedantry brings me on to the point of this particular post – some rather helpful formatting functions that are hidden in, of all places, the DBMS_XPLAN pacakge…

Function Signatures

If we happened to be strolling through the Data Dictionary and issued the following query…

select text
from dba_source
where owner = 'SYS'
and type = 'PACKAGE'
and name = 'DBMS_XPLAN'
order by line
/

we might be surprised at what we find….

***snip***
  ----------------------------------------------------------------------------
  -- ---------------------------------------------------------------------- --
  --                                                                        --
  -- The folloing section of this package contains functions and procedures --
  -- which are for INTERNAL use ONLY. PLEASE DO NO DOCUMENT THEM.           --
  --                                                                        --
  -- ---------------------------------------------------------------------- --
  ----------------------------------------------------------------------------
  -- private procedure, used internally

*** snip ***

  FUNCTION format_size(num number)
  RETURN varchar2;

  FUNCTION format_number(num number)
  RETURN varchar2;

  FUNCTION format_size2(num number)
  RETURN varchar2;

  FUNCTION format_number2(num number)
  RETURN varchar2;

  --
  -- formats a number representing time in seconds using the format HH:MM:SS.
  -- This function is internal to this package
  --
  function format_time_s(num number)
  return varchar2;

***snip***

Formatting a time in seconds

Let’s start with DBMS_XPLAN.FORMAT_TIME_S because we pretty much know what it does from the header comments.
To save myself a bit of typing, I’m just going to use the following SQL to see how the function copes with various values :

with actual_time as
(
    select &1 as my_secs
    from dual
)
select my_secs,
    dbms_xplan.format_time_s(my_secs) as formatted_time
from actual_time
/

Plug in a variety of numbers ( representing a time in seconds) and …

SQL> @format_time.sql 60
old   3:     select &1 as my_secs
new   3:     select 60 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
               60.00 00:01:00

SQL> @format_time.sql 3600
old   3:     select &1 as my_secs
new   3:     select 3600 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
             3600.00 01:00:00

SQL> @format_time.sql 86400
old   3:     select &1 as my_secs
new   3:     select 86400 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
            86400.00 24:00:00

SQL> @format_time.sql 129784
old   3:     select &1 as my_secs
new   3:     select 129784 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
           129784.00 36:03:04

SQL> 

I wonder how it treats fractions of a second ….

SQL> @format_time.sql  5.4
old   3:     select &1 as my_secs
new   3:     select 5.4 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
                5.40 00:00:05

SQL> @format_time.sql  5.5
old   3:     select &1 as my_secs
new   3:     select 5.5 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
                5.50 00:00:06

SQL> 

So, the function appears to round to the nearest second. Not great if you’re trying to list the times of the Olympic Finalists of the 100 metres, but OK for longer durations where maybe rounding to the nearest second is appropriate.
One minor quirk to be aware of :

SQL> @format_time.sql 119.5
old   3:     select &1 as my_secs
new   3:     select 119.5 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
              119.50 00:01:60

SQL> 

SQL> @format_time.sql 3599.5
old   3:     select &1 as my_secs
new   3:     select 3599.5 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
             3599.50 00:59:60

SQL> 


If 59.5 seconds is rounded up, the function returns a value containing 60 seconds, rather than displaying the value as a minute.

Formatting Numbers

Next on our list of functions to explore are FORMAT_NUMBER and FORMAT_NUMBER2. At first glance, it may appear that these functions are designed to represent sizes using the IEEE standard definitions…

with myval as
(
    select &1 as the_value
    from dual
)
select the_value, 
    dbms_xplan.format_number(the_value) as format_size, 
    dbms_xplan.format_number2(the_value) as format_size2
from myval
/

Run this with a variety of inputs and we get :

SQL> @format_number.sql 999
old   3:     select &1 as the_value
new   3:     select 999 as the_value

 THE_VALUE FORMAT_NUMBER                  FORMAT_NUMBER2
---------- ------------------------------ ------------------------------
       999 999                             999

SQL> @format_number.sql 1000
old   3:     select &1 as the_value
new   3:     select 1000 as the_value

 THE_VALUE FORMAT_NUMBER                  FORMAT_NUMBER2
---------- ------------------------------ ------------------------------
      1000 1000                              1K

SQL> @format_number.sql 1024
old   3:     select &1 as the_value
new   3:     select 1024 as the_value

 THE_VALUE FORMAT_NUMBER                  FORMAT_NUMBER2
---------- ------------------------------ ------------------------------
      1024 1024                              1K

SQL> @format_number.sql 1000000
old   3:     select &1 as the_value
new   3:     select 1000000 as the_value

 THE_VALUE FORMAT_NUMBER                  FORMAT_NUMBER2
---------- ------------------------------ ------------------------------
   1000000 1000K                             1M

SQL> 

SQL> @format_number.sql 1500
old   3:     select &1 as the_value
new   3:     select 1500 as the_value

 THE_VALUE FORMAT_NUMBER                  FORMAT_NUMBER2
---------- ------------------------------ ------------------------------
      1500 1500                              2K

SQL> 

The FORMAT_NUMBER2 function reports 1000 as 1K.
Furthermore, for numbers above 1000, it appears to round to the nearest 1000.
FORMAT_NUMBER on the other hand, doesn’t start rounding until you hit 1000000.

From this it seems reasonable to infer that these functions are designed to present large decimal numbers in an easily readable format rather than being an attempt to conform to the new-fangled definition of a Kilobyte ( or Megabyte…etc).

Using the following script, I’ve created the BIG_EMPLOYEES table and populated it with 100,000 or so rows…

create table big_employees as
    select * from hr.employees
/

begin
    for i in 1..1000 loop
        insert into big_employees
        select * from hr.employees;
    end loop;
    commit;
end;
/

If we now apply these functions to count the rows in the table, we get the following :

select count(*),
    dbms_xplan.format_number(count(*)) as format_number,
    dbms_xplan.format_number2(count(*)) as format_number2
from big_employees
/

  COUNT(*) FORMAT_NUMBER        FORMAT_NUMBER2
---------- -------------------- --------------------
    107107 107K                  107K

You can see from this, how these functions might be useful when you’re looking at the number of rows in a very large table ( perhaps several million).

Counting the Kilobytes properly

We now come to the other two functions we’ve identified – FORMAT_SIZE and FORMAT_SIZE2.

with myval as
(
    select &1 as the_value
    from dual
)
select the_value, 
    dbms_xplan.format_size(the_value) as format_size, 
    dbms_xplan.format_size2(the_value) as format_size2
from myval
/

Running this the results are :

SQL> @format_size.sql 999
old   3:     select &1 as the_value
new   3:     select 999 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
       999 999                   999

SQL> @format_size.sql 1000
old   3:     select &1 as the_value
new   3:     select 1000 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
      1000 1000                 1000

SQL> @format_size.sql 1024
old   3:     select &1 as the_value
new   3:     select 1024 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
      1024 1024                    1k

SQL> @format_size.sql 1000000
old   3:     select &1 as the_value
new   3:     select 1000000 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
   1000000 976K                  977k

SQL> @format_size.sql 1048576
old   3:     select &1 as the_value
new   3:     select 1048576 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
   1048576 1024K                   1m

SQL> @format_size.sql 2047.4
old   3:     select &1 as the_value
new   3:     select 2047.4 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
    2047.4 2047                    2k

SQL> @format_size.sql 2047.5
old   3:     select &1 as the_value
new   3:     select 2047.5 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
    2047.5 2047                    2k

SQL> 

Things to notice here include the fact that format_size appears to FLOOR the value (1000000 bytes = 976.56 K), wheras FORMAT_SIZE2 rounds it up.
Additionally, once you pass in a value of over 1024, FORMAT_SIZE2 returns values in Kilobytes.

So, if we want to know the size of the BIG_EMPLOYEES table we’ve just created :

select bytes, 
    dbms_xplan.format_size(bytes) as format_size,
    dbms_xplan.format_size2(bytes) as format_size2
from user_segments
where segment_name = 'BIG_EMPLOYEES'
/

     BYTES FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
   9437184 9216K                   9m

If all you need is an approximate value, then FORMAT_SIZE2 could be considered a reasonable alternative to :

select bytes/1024/1024 as MB
from user_segments
where segment_name = 'BIG_EMPLOYEES'
/

As well as it’s primary purpose, DBMS_XPLAN does offer some fairly useful functions if you need a quick approximation of timings, or counts or even sizes.
Fortunately, it adheres to the traditional definition of a Kilobyte as 1024 bytes rather than “Litebytes”.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s