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: http://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 (http://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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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
|
1 2 3 |
alter session set plsql_optimize_level=1; alter package pkg_test_parameter compile ; alter package pkg_test_parameter compile body; |
then run the test:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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 :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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.
|
1 2 3 4 5 6 7 8 9 10 |
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.