Geek Answers Handbook

The value of Pls_integer requires less memory than the number. How to check?

Version: 11.2.0.2.0

I would like to confirm the following statement, which I read again in the manual:

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

PLS_INTEGER values require less storage.

I am looking for efficient bytes.

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

Oracle Data Types

PL / SQL Data Types

I examined some useful functions: visze , dump (and so on: length, length b). I searched here in other forums / blogs, and even if this seems obvious in the past, I'm a little stuck.

( visize pls_integer, , ( 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;


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

- → → → → > 13/07/2011

:

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


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

, , pls_integer. pls_integer (32 , doc), , , .

, ""

  • OP " "

, , (pls_integer number), , :

  • , / ( "TestSize" )
  • varray pl/sql
  • 10 000, 100 000
  • 100 000 , , pls_integer.

PS , ( ) "TestSize" ,, , "TestSize" (vsize ) !

FInally, 100/200 , , PGA. !

, , "", ; !: -)

, "Testsize".

" PLS_INTEGER " .

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;


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
  • OP " "

", **

- "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;

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'


                                         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
+5
1

VSIZE , , NUMBER, DATE VARCHAR2 (. SYS.STANDARD). , PLS_INTEGER, NUMBER.

PLS_INTEGER

PLS_INTEGER -2,147,483,648 2,147,483,647, 32 .

, 32 = 4 . , , VSIZE() NUMBER 4 :

SQL> select vsize(2147483647) v from dual;

v
-
6

, , - PL/SQL, NUMBERS , PGA . PLS_INTEGER. . (: PGA , , .)

PGA , :

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';
+8
source

More articles:


All Articles