Slow compilation of LPAD and RPAD

user_17343 0
Viewed 59

With Oracle 19c, the following procedure takes several seconds to compile. Reproducible also with db<>fiddle (18c and 21c).
With Oracle 12.1 it compiles immediately.

CREATE OR REPLACE PROCEDURE test_proc AS
  x VARCHAR2(32767);
BEGIN
  x := RPAD('X', 32767);    -- same with LPAD

  -- These compile fast:
  x := RPAD('X', '32767');  -- length parameter as VARCHAR2
  x := RPAD('X', 128);      -- shorter string
END;

Execution of the compiled procedure is always fast. Disabling all PLSQL_WARNINGS does not make a difference.

What could be the reason for this? Some compiler check?

oracle19c plsql oracle
1 Answers

Reproduced on my 19c instance, and seems to be linear degradation based on size of the variable, ie

SQL> set serverout on
SQL> declare
  2    l_ddl varchar2(200) := q'{create or replace procedure test_proc as x varchar2(@@); begin x := rpad('x', @@);  end;}';
  3    l_duration timestamp;
  4  begin
  5    for i in 1 .. 32 loop
  6      l_duration := localtimestamp;
  7      execute immediate replace(l_ddl,'@@',i*1000);
  8      dbms_output.put_line(i||'k:'||substr((localtimestamp-l_duration),18));
  9    end loop;
 10  end;
 11  /
1k:00.006000000
2k:00.011000000
3k:00.022000000
4k:00.032000000
5k:00.050000000
6k:00.069000000
7k:00.092000000
8k:00.119000000
9k:00.150000000
10k:00.184000000
11k:00.221000000
12k:00.264000000
13k:00.308000000
14k:00.356000000
15k:00.408000000
16k:00.465000000
17k:00.522000000
18k:00.587000000
19k:00.652000000
20k:00.724000000
21k:00.796000000
22k:00.873000000
23k:00.953000000
24k:01.039000000
25k:01.125000000
26k:01.218000000
27k:01.314000000
28k:01.410000000
29k:01.516000000
30k:01.621000000
31k:01.730000000
32k:01.84300000000

I don't have an answer for you, but I would suspect it is related to the optimizing compiler, because setting the level to 0 eliminates the wait

SQL> alter session set plsql_optimize_level = 0;

Session altered.

SQL> set serverout on
SQL> declare
  2    l_ddl varchar2(200) := q'{create or replace procedure test_proc as x varchar2(@@); begin x := rpad('x', @@);  end;}';
  3    l_duration timestamp;
  4  begin
  5    for i in 1 .. 32 loop
  6      l_duration := localtimestamp;
  7      execute immediate replace(l_ddl,'@@',i*1000);
  8      dbms_output.put_line(i||':'||substr((localtimestamp-l_duration),18));
  9    end loop;
 10  end;
 11  /
1:00.008000000
2:00.005000000
3:00.005000000
4:00.004000000
5:00.005000000
6:00.004000000
7:00.005000000
8:00.004000000
9:00.004000000
10:00.005000000
11:00.004000000
12:00.005000000
13:00.004000000
14:00.005000000
15:00.004000000
16:00.005000000
17:00.003000000
18:00.005000000
19:00.004000000
20:00.004000000
21:00.005000000
22:00.004000000
23:00.005000000
24:00.004000000
25:00.004000000
26:00.005000000
27:00.003000000
28:00.005000000
29:00.004000000
30:00.005000000
31:00.004000000
32:00.005000000

PL/SQL procedure successfully completed.

Addenda: Identified as a bug in the compiler. Only impacts multi-byte charactersets.

user_73672930
Related