Oracle 19c及后续版本中LPAD与RPAD函数编译缓慢问题排查

This slow compilation behavior is directly tied to changes in the PL/SQL compiler's constant expression evaluation logic introduced in Oracle 12.2 and carried forward to later releases (18c, 19c, 21c)—a behavior that wasn't present in Oracle 12.1.

Here's a detailed breakdown of what's happening:

• Compile-time constant precomputation: In newer Oracle versions, the PL/SQL compiler attempts to precompute the result of constant expressions during compilation instead of deferring them to runtime. When you write RPAD('X', 32767), the compiler generates the full 32767-character string as a literal in the compiled procedure's metadata. Generating and storing this large string requires measurable memory and processing time, which is why you see the compilation slowdown.

• Why alternative syntaxes compile faster:

  • When using RPAD('X', '32767'), the length argument is a string that requires an implicit TO_NUMBER conversion at runtime. The compiler can't precompute the RPAD result here because the conversion isn't a pure constant expression during compilation, so it skips the precomputation step entirely.
  • With RPAD('X', 128), the resulting string is short—generating and storing a 128-character literal is negligible in terms of processing time, so compilation stays fast.

• Why execution remains fast: Once compiled, the precomputed large string is already stored in the procedure's bytecode. At runtime, the code just uses this pre-built value instead of recalculating it, which is why execution performance isn't impacted.

• PLSQL_WARNINGS don't resolve the issue: This behavior isn't related to compiler warnings—it's part of the optimizer's constant folding feature, which isn't controlled by warning settings.

Workarounds to speed up compilation #

If you need to keep the 32767-length string but avoid slow compilation, you can force the expression to be evaluated at runtime instead of compile time. For example:

CREATE OR REPLACE PROCEDURE test_proc AS 
x VARCHAR2(32767);
len PLS_INTEGER := 32767; -- Runtime-assigned variable prevents compile-time folding
BEGIN 
x := RPAD('X', len); 
END;

Or use an expression the compiler can't fully evaluate during compilation:

x := RPAD('X', TO_NUMBER('32767'));

内容的提问来源于stack exchange，提问作者Peter Lang