Почему уровень оптимизации не даёт ожидаемого результата по времени выполнения?

Для понимания PLSQL_OPTIMIZE_LEVEL следует использовать более качественные источники, а также убедиться в том, что вы правильно тестируете.

1. Как работает PLSQL_OPTIMIZE_LEVEL?

Лучший способ узнать о любом параметре - обратиться к официальной документации на БД. Параметр PLSQL_OPTIMIZE_LEVEL часто меняется, поэтому надо убедиться, что ссылка на конкретную версию БД. В интернете можно найти очень много неофициальной, устаревшей информации.

Вот описание допустимых значений из официальной документации для версии 18c:

0

Maintains the evaluation order and hence the pattern of side effects, exceptions, and package initializations of Oracle9i and earlier releases. Also removes the new semantic identity of BINARY_INTEGER and PLS_INTEGER and restores the earlier rules for the evaluation of integer expressions. Although code will run somewhat faster than it did in Oracle 9i, use of level 0 will forfeit most of the performance gains of PL/SQL in Oracle Database 10g.

1

Applies a wide range of optimizations to PL/SQL programs including the elimination of unnecessary computations and exceptions, but generally does not move source code out of its original source order.

2

Applies a wide range of modern optimization techniques beyond those of level 1 including changes which may move source code relatively far from its original location.

3

Applies a wide range of optimization techniques beyond those of level 2, automatically including techniques not specifically requested.

Это описание не позволяет с достоверностью определить, когда произойдет инлайнинг. Звучит так, будто инлайнинг может произойти на уровне 1, и будет вероятно происходить на уровне 2. Тесты ниже показывают большую разницу в производительности от 0 до 1, очень маленькую разницу от 1 до 2, и никакой разницы от 2 до 3.

Но значительная часть поведения не документирована, поэтому сложно сказать, когда и какая оптимизация произойдёт.

2. Был ли перекомпилирован код после изменения уровня оптимизации?

Просто измененить значение уровня оптимизации для сессии недостаточно, также надо перекомпилировать процедуры:

alter session set plsql_optimize_level=3;
alter procedure p1 compile;
alter procedure p2 compile;

Проверить, с каким уровнем PL/SQL оптиизации процедура была скомпилирована:

select name, plsql_optimize_level, plsql_code_type
from user_plsql_object_settings
where name name like 'P_';

3. Действительно ли тестируется инлайнинг?

В процедурах из вопроса много циклов на инкримент и мало на вызов процедуры. Думаю, что должно быть ровно наоброт - чтобы протестировать инлайнинг, должно быть много циклов на вызов процедуры, и немного циклов на инкримент. Трудно заметить разницу во времени выполнения при 10 вызовах процедуры. Использовал такие процедуры для теста:

create or replace procedure p1 is
    n number:=0;
begin
    for i in 1..5 loop
        n:=n+1;
    end loop;
end;
/
create or replace procedure p2 is
begin
    for i in 1..10000000 loop
        p1;
    end loop;
end;
/

4. Достаточно ли надежен метод тестирования?

Tесты должны попытаться компенсировать другие активности, потребляющие процессор. Надо выоплнить несколько тестов в чередующемся порядке, отброросить самые высокие и низкие значения, и сравнивать средние значения. Разница в пять секунд от запуска пятиминутного теста дважды, несущественна.

Использовал нижеприведенные блоки PL/SQL для тестирования времени выполнения. Можно написать PL/SQL скрипт, который запустит блоки в случайном порядке и запишет время выполнения, эту часть сделал вручную. Уровни 3 и 2 работают с одинаковой скоростью, уровень 1 немного медленнее, а уровень 0 значительно медленнее:

--Level 3: 3.331, 3.403, 3.419
alter session set plsql_optimize_level=3;
alter procedure p1 compile;
alter procedure p2 compile;
exec p2;

--Level 2: 3.383, 3.470, 3.444
alter session set plsql_optimize_level=2;

--Level 1: 3.867, 3.859, 3.873
alter session set plsql_optimize_level=1;

--Level 0: 6.286, 6.296, 6.315
alter session set plsql_optimize_level=0;

5. Когда стоит уделить внимание оптимизации PL/SQL?

В большинстве реальных PL/SQL программ инлайнинг процедур не будет иметь смысла. Лучшая практика заключается в том, чтобы уделить как можно больше внимание работе с SQL как наиболее времязатратной. Независимо от того, где находится ваша логика, убедитесь, что используя профайлер идет настройка только тех частей программы, которые занимают значительную часть от времени выполнения. Перед настройкой части PL/SQL программы необходимо представить себе, что следует ожидать от оптимизации, например:

Если оптимизировать функцию X, то программа в целом возможно станет работать на Y% быстрее".

_{Свободный перевод ответа от участника Jon Heller}