Pls_integer value requires "less" storage compared to Number. How to verify?

Question

Version :11.2.0.2.0

I'd like to verify the follow assertion I'm reading again in the manual:

The PLS_INTEGER data type has these advantages over the NUMBER data type and NUMBER subtypes:

PLS_INTEGER values require less storage.

---

I'm looking for bytes effectively used.

I know the Oracle built-in data types and PL/SQL data types. I read again the specific sections in the documentation:

Oracle data types

PL/SQL data types

I've reviewed some useful functions: visze, dump(and so on:length,lengthb). I've searched here and in others forum/blogs, and even if it seems an obvious thing learnt in the past, I'm a little stuck.

---

My dummy procedure (visize function will not show any valid values for pls_integer because the reason illustrated by Tony Andrews in his answer below (note 13/07/11)

create or replace procedure TestSize
(
    testPlsInteger in pls_integer
   ,testNumber     in number
) is
    sizePlsInteger pls_integer;
    sizeNumber     pls_integer;
    FORMAT_DIM constant pls_integer := 15;
begin
    select vsize(testPlsInteger), vsize(testNumber)
    into   sizePlsInteger, sizeNumber
    from   dual;

    dbms_output.put_line('number:' || rpad(testPlsInteger, FORMAT_DIM)
                        ||' PLS_INTEGER SIZE:' || rpad(sizePlsInteger, FORMAT_DIM) 
                        || ' NUMBER SIZE:' || sizeNumber);
end;

Test

begin
    TestSize(2147483647, 2147483647);
    TestSize(1, 1);
    TestSize(1000000, 1000000);
    TestSize(12345678, 12345678);
end;

Results

number:2147483647      PLS_INTEGER SIZE:6               NUMBER SIZE:6
number:1               PLS_INTEGER SIZE:2               NUMBER SIZE:2
number:1000000         PLS_INTEGER SIZE:2               NUMBER SIZE:2
number:12345678        PLS_INTEGER SIZE:5               NUMBER SIZE:5

---

-->>>>>>>>>NEW SECTION ADDED 13/07/2011

Following the documentation:

The PLS_INTEGER data type has these advantages over the NUMBER data type and NUMBER subtypes:
PLS_INTEGER values require less storage.

and

The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits.

• Documentation assertion "PLS_INTEGER values require less storage " isn't always true.

It should be true that number data type should require more space than pls_integer. Rewriting the previous procedure using a constant for pls_integer size ( 32 bits like the doc says), we can see below it isn't true.

I suppose it's due to the internal representation of the data type "number"

• But the OP was "how to verify"

Using the "great" suggestion given by Tony Andrews, and loading two different array of different sizes(pls_integer and number), it seems that:

1. Using different numbers that require different/equal storage (output of the previously mentioned procedure "TestSize")
2. Using varray to implement array in pl/sql
3. Testing different dimension for these array 10 000, 100 000
4. Large arrays of 100 000 numbers require more space than the pls_integer ones.

PS Obviously, this method is not totally accurate(like Tony wrote in his post) and collide with the output of my dummy procedure "TestSize", providing that the method to calculate the numbers size in "TestSize" (vsize for number) is correct!

FInally, I rarely use array of more than 100/200 elements in my production code, just to avoid to overload PGA. And for this dimensions the doubt remains!

Others studies I have to do to discover this "oracle", that is beyond my knowledge; but for this reason I wrote this question! :-)

---

Procedure using the new "Testsize" procedure.

Documentation assertion "PLS_INTEGER values require less storage " isn't always true.

create or replace procedure TestSize
(
   testNumber     in number
) is
    sizeNumber     pls_integer;

    SIZE_PLS_INTEGER constant pls_integer:= 4; -- always 32 bits(4 bytes)
    FORMAT_DIM constant pls_integer := 15;
    FORMAT_MINOR constant varchar2(1) := '<';
    FORMAT_EQUAL constant varchar2(1) := '=';

begin
    select vsize(testNumber)
    into    sizeNumber
    from   dual;

    dbms_output.put_line('number:' || rpad(testNumber, FORMAT_DIM)
                        ||' PLS_INTEGER SIZE:'
                        || case when (SIZE_PLS_INTEGER<sizeNumber) then
                                    rpad(SIZE_PLS_INTEGER, FORMAT_DIM,FORMAT_MINOR)
                                when (SIZE_PLS_INTEGER=sizeNumber) then
                                    rpad(SIZE_PLS_INTEGER, FORMAT_DIM,FORMAT_EQUAL)
                                else rpad(SIZE_PLS_INTEGER, FORMAT_DIM)
                           end
                        || ' NUMBER SIZE:'
                        || case when (sizeNumber<SIZE_PLS_INTEGER) then
                                      rpad(sizeNumber,FORMAT_DIM,FORMAT_MINOR)
                                else  rpad( sizeNumber,FORMAT_DIM)
                           end);
end TestSize;

Test

begin
    TestSize(2147483647);
    TestSize(1);
    TestSize(10);
    TestSize(100);
    TestSize(1000);
    TestSize(1000);
    TestSize(100000);
    TestSize(1000000);
    TestSize(10000000);
    TestSize(10000000);
    TestSize(100000000);
    TestSize(1000000000);
    TestSize(1000000000);
    TestSize(90000000);
    TestSize(9923);
    TestSize(99232);
    TestSize(555555);
    TestSize(12345);
    TestSize(1234);
    TestSize(1000001);
    TestSize(20000000000);
    TestSize(12345678);
    TestSize(12345678);
end;

Results

number:2147483647      PLS_INTEGER SIZE:4<<<<<<<<<<<<<< NUMBER SIZE:6              
number:1               PLS_INTEGER SIZE:4               NUMBER SIZE:2<<<<<<<<<<<<<<
number:10              PLS_INTEGER SIZE:4               NUMBER SIZE:2<<<<<<<<<<<<<<
number:100             PLS_INTEGER SIZE:4               NUMBER SIZE:2<<<<<<<<<<<<<<
number:1000            PLS_INTEGER SIZE:4               NUMBER SIZE:2<<<<<<<<<<<<<<
number:1000            PLS_INTEGER SIZE:4               NUMBER SIZE:2<<<<<<<<<<<<<<
number:100000          PLS_INTEGER SIZE:4               NUMBER SIZE:2<<<<<<<<<<<<<<
number:1000000         PLS_INTEGER SIZE:4               NUMBER SIZE:2<<<<<<<<<<<<<<
number:10000000        PLS_INTEGER SIZE:4               NUMBER SIZE:2<<<<<<<<<<<<<<
number:10000000        PLS_INTEGER SIZE:4               NUMBER SIZE:2<<<<<<<<<<<<<<
number:100000000       PLS_INTEGER SIZE:4               NUMBER SIZE:2<<<<<<<<<<<<<<
number:1000000000      PLS_INTEGER SIZE:4               NUMBER SIZE:2<<<<<<<<<<<<<<
number:1000000000      PLS_INTEGER SIZE:4               NUMBER SIZE:2<<<<<<<<<<<<<<
number:90000000        PLS_INTEGER SIZE:4               NUMBER SIZE:2<<<<<<<<<<<<<<
number:9923            PLS_INTEGER SIZE:4               NUMBER SIZE:3<<<<<<<<<<<<<<
number:99232           PLS_INTEGER SIZE:4============== NUMBER SIZE:4              
number:555555          PLS_INTEGER SIZE:4============== NUMBER SIZE:4              
number:12345           PLS_INTEGER SIZE:4============== NUMBER SIZE:4              
number:1234            PLS_INTEGER SIZE:4               NUMBER SIZE:3<<<<<<<<<<<<<<
number:1000001         PLS_INTEGER SIZE:4<<<<<<<<<<<<<< NUMBER SIZE:5              
number:20000000000     PLS_INTEGER SIZE:4               NUMBER SIZE:2<<<<<<<<<<<<<<
number:12345678        PLS_INTEGER SIZE:4<<<<<<<<<<<<<< NUMBER SIZE:5              
number:12345678        PLS_INTEGER SIZE:4<<<<<<<<<<<<<< NUMBER SIZE:5              

---

• The OP was "how to verify"

Using the "great" suggestion given by **Tony Andrews

Dummy procedure "TestSize2"

create or replace procedure testSize2
(
    testNum       in pls_integer
   ,maxDim        in pls_integer
   ,secondsToWait in pls_integer
) is
    type TPlsIntegers is varray(100000) of pls_integer;
    type TNumbers is varray(100000) of number;
    pls      TPlsIntegers := TPlsIntegers();
    numbers  TNumbers := TNumbers();

    MODULE        constant varchar2(20) := 'TestSize2';
    PLS_ACTION    constant varchar2(20) := 'pls_integer';
    NUMBER_ACTION constant varchar2(20) := 'number';
    SEP           constant varchar2(3) := ' - ';

begin
    dbms_application_info.set_action(action_name => PLS_ACTION||SEP||testNum ||SEP||maxDim);

    pls.extend(maxDim);
    for cont in 1 .. maxDim
    loop
        pls(cont) := testNum;
    end loop;
    dbms_lock.sleep(seconds => secondsToWait);

    -- check pga with query
    dbms_application_info.set_action(action_name => NUMBER_ACTION||SEP||testNum ||SEP||maxDim); 
    numbers.extend(maxDim);
    for cont in 1 .. maxDim
    loop
        numbers(cont) := testNum;
    end loop;

    -- check pga with query
    DBMS_LOCK.sleep(secondsToWait);
end;

Test

declare
    MAX_TO_WAIT constant pls_integer := 3;
    MODULE        constant varchar2(30) := 'testSize2';
begin
    debug.disable;
    dbms_application_info.set_module(MODULE, action_name => '');
    testSize2(testNum => 90000000, secondsToWait => MAX_TO_WAIT, maxDim => 100);
    testSize2(testNum => 90000000, secondsToWait => MAX_TO_WAIT, maxDim => 10000); --10  000
    testSize2(testNum => 90000000, secondsToWait => MAX_TO_WAIT, maxDim => 100000); --100 000    

    testSize2(testNum => 12345, secondsToWait => MAX_TO_WAIT, maxDim => 10000); --10  000    
    testSize2(testNum => 12345, secondsToWait => MAX_TO_WAIT, maxDim => 100000); --100 000

    testSize2(testNum => 2147483647, secondsToWait => MAX_TO_WAIT, maxDim => 100);
    testSize2(testNum => 2147483647, secondsToWait => MAX_TO_WAIT, maxDim => 10000); --10  000
    testSize2(testNum => 2147483647, secondsToWait => MAX_TO_WAIT, maxDim => 100000); --100 000*\*\*/

    dbms_application_info.set_action(action_name => 'END');
end;

Query to monitor PGA

select vsst.sid || ',' || vses.serial# username, vsst.value,vses.MODULE,vses.ACTION
from   v$sesstat vsst, v$statname vstt, v$session vses
where  vstt.statistic# = vsst.statistic#
and    vsst.sid = vses.sid
and    vstt.name = 'session pga memory'
and    vses.username = 'HR'
and    vses.MODULE = 'testSize2'

Annoted Results

                                         pls_integer     number     dif
--                                            size        size      size
--  n=90000000          DIM aRRAY= 100      528612      594148<   DIF= 65536
--  n=90000000          DIM aRRAY= 10000    725220      1118436<  DIF= 393216
--  n=90000000          DIM aRRAY= 100000   2101476     5771492>  DIF= 3670016

--  n=12345             DIM aRRAY= 10000    921828      1380580<  DIF= 458752
--  n=12345             DIM aRRAY= 100000   2101476     5771492>  DIF= 3670016

--  n=2147483647        DIM aRRAY= 100      790756      856292 <  DIF= 65536
--  n=2147483647        DIM aRRAY= 10000    921828      1380580<  DIF= 458752
--  n=2147483647        DIM aRRAY= 100000   2101476     5771492>  DIF= 3670016

Answer 1

The VSIZE function will not show any difference because it is an overloaded function that can take a NUMBER, DATE or VARCHAR2 value only (see the spec of SYS.STANDARD). So when you pass it a PLS_INTEGER value that value is implicitly converted to NUMBER first.

The definition of PLS_INTEGER says

The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits.

So it is always 32 bits = 4 bytes. Whereas, as you showed, the VSIZE() of a NUMBER can be more than 4 bytes:

SQL> select vsize(2147483647) v from dual;

v
-
6

One way you may be able to see a difference is if you run some PL/SQL that populates a large array of NUMBERS in memory, and look at the PGA usage for your session before an after. Then repeat for PLS_INTEGER. You could repeat the experiment with small and large values and so on. (Caveat: PGA is allocated in chunks of a certain size, so this will not be totally accurate, but a large enough array may show any significant difference.)

To monitor PGA usage use a query like:

select vsst.sid||','||vses.serial# username,
       vsst.value 
from   v$sesstat vsst, v$statname vstt, v$session vses
where  vstt.statistic# = vsst.statistic#
and vsst.sid = vses.sid 
and    vstt.name = 'session pga memory'
and    vses.username = 'MYUSER';