ALTER SESSION SET plsql_optimize_level = 2
Statement processed.
CREATE OR REPLACE PROCEDURE proc1
IS
l_level INTEGER;
PROCEDURE inline_proc1
IS
BEGIN
RAISE PROGRAM_ERROR;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('inline_proc1 handler');
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
RAISE;
END;
BEGIN
SELECT plsql_optimize_level
INTO l_level
FROM user_plsql_object_settings
WHERE name = 'PROC1';
DBMS_OUTPUT.put_line ('Opt level = ' || l_level);
inline_proc1;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('inline handler');
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
END;
Procedure created.
BEGIN
proc1;
END;
Opt level = 2
inline_proc1 handler
ORA-06512: at "SQL_VEMDDIMOAACRURAVYAWMWKNQH.PROC1", line 8
inline handler
ORA-06512: at "SQL_VEMDDIMOAACRURAVYAWMWKNQH.PROC1", line 15 ORA-06512: at "SQL_VEMDDIMOAACRURAVYAWMWKNQH.PROC1", line 8 ORA-06512: at "SQL_VEMDDIMOAACRURAVYAWMWKNQH.PROC1", line 25
Now We Turn On Inlining
ALTER SESSION SET plsql_optimize_level = 3
Statement processed.
Recompile PROC1
ALTER PROCEDURE proc1 COMPILE
Procedure altered.
The Backtrace Changes
BEGIN
proc1;
END;
Opt level = 3
inline_proc1 handler
ORA-06512: at "SQL_VEMDDIMOAACRURAVYAWMWKNQH.PROC1", line 25
inline handler
ORA-06512: at "SQL_VEMDDIMOAACRURAVYAWMWKNQH.PROC1", line 25 ORA-06512: at "SQL_VEMDDIMOAACRURAVYAWMWKNQH.PROC1", line 25
SELECT p.plsql_optimize_level
FROM user_plsql_object_settings p
WHERE name = 'PROC1'
PLSQL_OPTIMIZE_LEVEL |
---|
3 |