Why should you use PLSQL_OPTIMIZE_LEVEL as 3

Hi,

As you know PLSQL_OPTIMIZE_LEVEL parameter is used to optimize the codes while compiling them and it is really important. By default it’s value is 2 but you can increase it to 3 and you should, here is why.

before begin, this is an extension for this post: https://mustafakalayci.me/2020/06/26/performance-gain-of-nocopy-parameters/

Very much thanks to Stephan Borsodi who contacted me over linked in and told me that Java developers are using database objects to interact with database. I actually remember that one of the insurance company (I had trained their developer teams years before) that uses Java, has objects in database. So, Stephan added a object type test script for testing performance gain of NOCOPY parameters. On his tests there were no performance gain for object type plsql parameters. When I do the test, I saw more than 20 times performance gain for nocopy parameters, then we discussed the situation about this and so for I realized that plsql optimize level is really doing a good stuff on this.

So what was the difference, I did my tests after setting the plsql_optimize_level to 1. when I do that nocopy parameters for object types are 20 times faster. I won’t rewrite whole code for my testing purposes, you can find it on my previous post (https://mustafakalayci.me/2020/06/26/performance-gain-of-nocopy-parameters/) but of course he added object type SP_MAIN and SP_PROCESS procedures into my test package here are their codes:

create or replace package pkg_test_parameter as
  ...
  procedure sp_main(p in out str_o_t, p_method varchar2, p_loop_counter pls_integer);
end pkg_test_parameter;
/
create or replace package body PKG_TEST_PARAMETER as
...
  -- OBJECT TABLE ---------------------
  procedure sp_process(p in out str_o_t) as
    x number;
  begin
    x := p.count;
  end sp_process;
  procedure sp_process_nocopy(p in out nocopy str_o_t) as
    x number;
  begin
    x := p.count;
  end sp_process_nocopy;
...
  -- OBJECT TABLE -------------------
  procedure sp_main(p in out str_o_t, p_method varchar2, p_loop_counter pls_integer)
  as
  begin
    for i in 1..p_loop_counter loop
      if p_method = 'NOCOPY' then
        sp_process_nocopy(p => p);
      else
        sp_process(p => p);
      end if;
    end loop;
  end sp_main;
...
End;

create or replace package pkg_test_parameter as

...

procedure sp_main(p in out str_o_t, p_method varchar2, p_loop_counter pls_integer);

end pkg_test_parameter;

create or replace package body PKG_TEST_PARAMETER as

...

-- OBJECT TABLE ---------------------

procedure sp_process(p in out str_o_t) as

x number;

begin

x := p.count;

end sp_process;

procedure sp_process_nocopy(p in out nocopy str_o_t) as

x number;

begin

x := p.count;

end sp_process_nocopy;

...

-- OBJECT TABLE -------------------

procedure sp_main(p in out str_o_t, p_method varchar2, p_loop_counter pls_integer)

begin

for i in 1..p_loop_counter loop

if p_method = 'NOCOPY' then

sp_process_nocopy(p => p);

else

sp_process(p => p);

end if;

end loop;

end sp_main;

...

End;

I must say that, his codding is much tidier than mine 🙂

here is the object type and the array type of that object which Stephan used:

set serverout on size unlimited format wrapped

create or replace type STR_O
as object
(
    ID   number (10,0),
    REC  varchar2(128)
)
/

create or replace type STR_O_T
as table of STR_O
/

set serverout on size unlimited format wrapped

create or replace type STR_O

as object

(

ID number (10,0),

REC varchar2(128)

)

create or replace type STR_O_T

as table of STR_O

compile the test package before starting the test and I use 1 as plsql_optimize_level

alter session set plsql_optimize_level=1;
alter package pkg_test_parameter compile ;
alter package pkg_test_parameter compile body;

alter session set plsql_optimize_level=1;

alter package pkg_test_parameter compile ;

alter package pkg_test_parameter compile body;

then run the test:

declare
  x_otab1 str_o_t;
  x_otab2 str_o_t;
  v_dummy pls_integer  := 0;
  v_loop  number(10,0) := 1000;
  s timestamp with time zone;
begin
  -- generate data for object table
  select str_o( x.ID,
         x.OBJECT_NAME)
  bulk collect into x_otab1
  from
  (with fact (NR) as (
        select LEVEL as NR
        from   DUAL
        connect by LEVEL < 4
    )
    select row_number() over (order by null) as ID,
           OBJECT_NAME
    from   ALL_OBJECTS
    cross join fact
  ) x;

  s := systimestamp;
  pkg_Test_parameter.sp_main(x_otab1, 'COPY', v_loop,0);
  dbms_output.put_line('OBJ.TABLE COPY  : ' ||(systimestamp -s));

  x_otab2 := x_otab1;
  s := systimestamp;
  pkg_Test_parameter.sp_main(x_otab2,'NOCOPY', v_loop,0);
  dbms_output.put_line('OBJ.TABLE NOCOPY: ' ||(systimestamp -s));

end;
/


OBJ.TABLE COPY : +000000000 00:00:31.475000000
OBJ.TABLE NOCOPY: +000000000 00:00:00.077000000

declare

x_otab1 str_o_t;

x_otab2 str_o_t;

v_dummy pls_integer := 0;

v_loop number(10,0) := 1000;

s timestamp with time zone;

begin

-- generate data for object table

select str_o( x.ID,

x.OBJECT_NAME)

bulk collect into x_otab1

from

(with fact (NR) as (

select LEVEL as NR

from DUAL

connect by LEVEL < 4

)

select row_number() over (order by null) as ID,

OBJECT_NAME

from ALL_OBJECTS

cross join fact

) x;

s := systimestamp;

pkg_Test_parameter.sp_main(x_otab1, 'COPY', v_loop,0);

dbms_output.put_line('OBJ.TABLE COPY : ' ||(systimestamp -s));

x_otab2 := x_otab1;

s := systimestamp;

pkg_Test_parameter.sp_main(x_otab2,'NOCOPY', v_loop,0);

dbms_output.put_line('OBJ.TABLE NOCOPY: ' ||(systimestamp -s));

end;

OBJ.TABLE COPY : +000000000 00:00:31.475000000

OBJ.TABLE NOCOPY: +000000000 00:00:00.077000000

so, it is an amazing result, more than 30 times faster in NOCOPY parameters but why Stephan’s results shows no performance difference? I decided to run this code on a different database than my local db and I made a mistake! I forgot to compile package with plsql_optimiz_level as 1. it was 2 which is default value for plsql_optimize_level and there were no performance gain :

alter session set plsql_optimize_level=2;
alter package pkg_test_parameter compile ;
alter package pkg_test_parameter compile body;

declare
  x_otab1 str_o_t;
  x_otab2 str_o_t;
  v_loop  number(10,0) := 1000000;
  s timestamp with time zone;
begin
  -- generate data for object table
  select str_o( x.ID,
         x.OBJECT_NAME)
  bulk collect into x_otab1
  from
  (with fact (NR) as (
        select LEVEL as NR
        from   DUAL
        connect by LEVEL < 4
    )
    select row_number() over (order by null) as ID,
           OBJECT_NAME
    from   ALL_OBJECTS
    cross join fact
  ) x;

  s := systimestamp;
  pkg_Test_parameter.sp_main(x_otab1, 'COPY', v_loop);
  dbms_output.put_line('OBJ.TABLE COPY  : ' ||(systimestamp -s));

  x_otab2 := x_otab1;
  s := systimestamp;
  pkg_Test_parameter.sp_main(x_otab2,'NOCOPY', v_loop);
  dbms_output.put_line('OBJ.TABLE NOCOPY: ' ||(systimestamp -s));

end;
/

OBJ.TABLE COPY : +000000000 00:00:00.228000000
OBJ.TABLE NOCOPY: +000000000 00:00:00.229000000

alter session set plsql_optimize_level=2;

alter package pkg_test_parameter compile ;

alter package pkg_test_parameter compile body;

declare

x_otab1 str_o_t;

x_otab2 str_o_t;

v_loop number(10,0) := 1000000;

s timestamp with time zone;

begin

-- generate data for object table

select str_o( x.ID,

x.OBJECT_NAME)

bulk collect into x_otab1

from

(with fact (NR) as (

select LEVEL as NR

from DUAL

connect by LEVEL < 4

)

select row_number() over (order by null) as ID,

OBJECT_NAME

from ALL_OBJECTS

cross join fact

) x;

s := systimestamp;

pkg_Test_parameter.sp_main(x_otab1, 'COPY', v_loop);

dbms_output.put_line('OBJ.TABLE COPY : ' ||(systimestamp -s));

x_otab2 := x_otab1;

s := systimestamp;

pkg_Test_parameter.sp_main(x_otab2,'NOCOPY', v_loop);

dbms_output.put_line('OBJ.TABLE NOCOPY: ' ||(systimestamp -s));

end;

OBJ.TABLE COPY : +000000000 00:00:00.228000000

OBJ.TABLE NOCOPY: +000000000 00:00:00.229000000

result was almost same and it was so fast that I run the first code for 1.000 times but on this second one I run it for 1.000.000 times and it was extremely faster than previous one. 1 million parameter call completed in 0.22 second. then I got curious about plsql_optimize_level 3 and tested of course:

alter session set plsql_optimize_level=3;
alter package pkg_test_parameter compile ;
alter package pkg_test_parameter compile body;	

declare
  x_otab1 str_o_t;
  x_otab2 str_o_t;
  v_loop  number(10,0) := 1000000;
  s timestamp with time zone;
begin
  -- generate data for object table
  select str_o( x.ID,
         x.OBJECT_NAME)
  bulk collect into x_otab1
  from
  (with fact (NR) as (
        select LEVEL as NR
        from   DUAL
        connect by LEVEL < 4
    )
    select row_number() over (order by null) as ID,
           OBJECT_NAME
    from   ALL_OBJECTS
    cross join fact
  ) x;

  s := systimestamp;
  pkg_Test_parameter.sp_main(x_otab1, 'COPY', v_loop);
  dbms_output.put_line('OBJ.TABLE COPY  : ' ||(systimestamp -s));

  x_otab2 := x_otab1;
  s := systimestamp;
  pkg_Test_parameter.sp_main(x_otab2,'NOCOPY', v_loop);
  dbms_output.put_line('OBJ.TABLE NOCOPY: ' ||(systimestamp -s));

end;
/

OBJ.TABLE COPY  : +000000000 00:00:00.042000000
OBJ.TABLE NOCOPY: +000000000 00:00:00.041000000

alter session set plsql_optimize_level=3;

alter package pkg_test_parameter compile ;

alter package pkg_test_parameter compile body;

declare

x_otab1 str_o_t;

x_otab2 str_o_t;

v_loop number(10,0) := 1000000;

s timestamp with time zone;

begin

-- generate data for object table

select str_o( x.ID,

x.OBJECT_NAME)

bulk collect into x_otab1

from

(with fact (NR) as (

select LEVEL as NR

from DUAL

connect by LEVEL < 4

)

select row_number() over (order by null) as ID,

OBJECT_NAME

from ALL_OBJECTS

cross join fact

) x;

s := systimestamp;

pkg_Test_parameter.sp_main(x_otab1, 'COPY', v_loop);

dbms_output.put_line('OBJ.TABLE COPY : ' ||(systimestamp -s));

x_otab2 := x_otab1;

s := systimestamp;

pkg_Test_parameter.sp_main(x_otab2,'NOCOPY', v_loop);

dbms_output.put_line('OBJ.TABLE NOCOPY: ' ||(systimestamp -s));

end;

OBJ.TABLE COPY : +000000000 00:00:00.042000000

OBJ.TABLE NOCOPY: +000000000 00:00:00.041000000

and the result was even much more better. 5 times faster than plsql_optimize_level 2! of course this is the purpose of plsql_optimize_level but this is really a big performance gain with a very very small change.

Should we stop here? of course not 🙂 Let’s also add PLSQL_CODE_TYPE parameter. it is INTERPRETED by default, let’s make it NATIVE (plsql_optimize_level 3 too) and see the difference one more time.

alter session set plsql_optimize_level=3;
alter session set plsql_code_type=native;
alter package pkg_test_parameter compile ;
alter package pkg_test_parameter compile body ;

--...  RUN SAME SAMPLE CODE

Result:
OBJ.TABLE COPY  : +000000000 00:00:00.024000000
OBJ.TABLE NOCOPY: +000000000 00:00:00.026000000

alter session set plsql_optimize_level=3;

alter session set plsql_code_type=native;

alter package pkg_test_parameter compile ;

alter package pkg_test_parameter compile body ;

--... RUN SAME SAMPLE CODE

Result:

OBJ.TABLE COPY : +000000000 00:00:00.024000000

OBJ.TABLE NOCOPY: +000000000 00:00:00.026000000

Yes, now it is even 2 times more faster than just plsql_optimize_level 2 version. So, if you are a Java developer or a database developer which runs Java application and use database objects (or collection of objects, table collections) then you must consider compiling your plsql objects with plsql_optimize_level 3 and plsql_code_type as NATIVE. Of course all calls will be faster with plsql_optimize_level 3 but non of the data types has this much (10 times in our example) performance gain. for example when I run the same code for VARCHAR2 data types, it worked approximately 2 times faster.

This is the type of performance tuning that I like most. with a very low effort, gain a lot 🙂 I would like to thank to Stephan one more time for improving my code and find out something beautiful about Java applications and db objects.

wish you all healthy days.

Leave a Reply Cancel reply