Explain PLSQL_OPTIMIZE_LEVEL and subprogram inlining

PLSQL_OPTIMIZE_LEVEL defaults to 2, which reorders code, removes dead code and can rewrite loops (e.g. into BULK internally in some cases). Level 3 additionally inlines calls to small local/private subprograms, removing call overhead in hot loops. Level 1 is minimal optimisation; 0 is essentially none (only for debugging). You can also request inlining per call site with PRAGMA INLINE(proc_name, 'YES'). Recompile the unit after changing the level; verify via USER_PLSQL_OBJECT_SETTINGS.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;

CREATE OR REPLACE PROCEDURE sum_rows AS
  v_total NUMBER := 0;
  FUNCTION weight(p NUMBER) RETURN NUMBER IS BEGIN RETURN p * 1.05; END;
BEGIN
  FOR i IN 1 .. 1000000 LOOP
    PRAGMA INLINE(weight, 'YES');
    v_total := v_total + weight(i);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(v_total);
END;
/