Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

Why should you use PLSQL_OPTIMIZE_LEVEL as 3

Mustafa, 2021-10-08

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:

Oracle PL/SQL
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:

Oracle PL/SQL
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

Oracle PL/SQL
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:

Oracle PL/SQL
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 :

Oracle PL/SQL
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:

Oracle PL/SQL
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.

Oracle PL/SQL
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.

12c 18c 19c 21c collection of objectdatabase objectsjava applicationjava developernocopyperformanceplsql_optimize_leveltable collection

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage

Recent Comments

  • Mustafa on How to call HTTPS Url Without SSL Wallet in 19c
  • Накрутка авито on How to call HTTPS Url Without SSL Wallet in 19c
  • Mustafa on Cloud Base Database Service
  • Raja on Cloud Base Database Service
  • Mustafa on Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23ai
  • Administration
  • Cloud
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed
RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset=UTF-8`
©2025 Keep Learning Keep Living | WordPress Theme by SuperbThemes