title: Why PLSQL optimize level is not providing expected result?
body: <p>I am trying to understand  complier optimizations in plsql in depth. Theoretically, default optimisation level <code>PLSQL_OPTIMIZE_LEVEL</code> is set to 2. To get better performance, we can set it to 3. To explain this to myself, I am using an example wherein a procedure is calling another procedure, so that the optimisation feature of level 3 (inline procedure) can be used.</p>
  <p>Here is the first procedure:</p>
  <pre><code>create or replace procedure p1
  is
  n number:=0;
  begin
  for i in 1..500000000
  loop
  n:=n+1;
  end loop;
  end;
  </code></pre>
  <p>And here is the second one:</p>
  <pre><code>create or replace procedure CALL_PROC_ARITH
  IS
  BEGIN
  for i in 1..10
  loop
  P1;
  end loop;
  END;
  </code></pre>
  <p>Here is the <code>plsql_code_type</code> for both the procedures, which is <code>INTERPRETED</code>.</p>
  <p><a href="https://i.stack.imgur.com/CAbfb.png" rel="nofollow noreferrer">plsql_code_type </a></p>
  <p>So initially the optimisation level for both procedures is 2. And when I execute the procedure <code>CALL_PROC_ARITH</code>, it takes about 05:00.866 minutes.<br />
  Later, I modify the optimisation level to 3 at session level. And when I execute the procedure <code>CALL_PROC_ARITH</code>, it takes about 00:05:05.011 minutes which is an increase in 5 seconds.</p>
  <p>Can someone please tell me why I see this deviation from the expected behaviour?<br />
  Will I get to see different results in case I use NATIVE compilation?</p>
  <p>Note: I am running this from an IDE, and not directly from the SQLPlus CLI.</p>
  <p>DB:Oracle 18c XE</p>