PL/SQL Reference VI - Advanced issues

===========================================
Index
===========================================
PLSQL_OPTIMIZE_LEVEL
DBMS_TRACE

===========================================
PLSQL_OPTIMIZE_LEVEL
===========================================
PLSQL_OPTIMIZE_LEVEL has effect when Oracle compiles PL/SQL library units.
The default value is 2.
0 - Back support for Oracle 9i optimization level. 

1- More Optimizations. Does not move source code out of original order.

2- Default. 
     Moves source code out of original order. 
     No automatic inlining is attempted.

3- Optimization techniques beyond level 2. 
    Automatic inlining is attempted.
    The cost is more time during compilation.

===========================================
Subprogram Inlining
===========================================
One of optimizations introduced in Oracle 11 is subprogram inlining - i.e. replacing a cal to a subprogram with a copy of the called program.

Subprogram Inlining Example
Before Inlining 
CREATE OR REPLACE PROCEDURE my_proc IS
  a NUMBER;
  b NUMBER;

  FUNCTION multiply(x IN OUT NUMBER, y NUMBER) IS
  BEGIN
    IF y > 0 THEN
      x := x*x;
    END IF;
  END;

BEGIN
  b:=10;
  a := b;
  FOR i IN 1..10 LOOP
    multiply(a,-17);
    a := a*b;
  END LOOP;
END;
/
After Inlining 
CREATE OR REPLACE PROCEDURE my_proc IS
  a NUMBER;
  b NUMBER;

  FUNCTION multiply(x IN OUT NUMBER, y NUMBER) IS
  BEGIN
    IF y > 0 THEN
      x := x*x;
    END IF;
  END;

BEGIN
  b:=10;
  a := b;
  FOR i IN 1..10 LOOP
    IF -17 > 0 THEN
      a := a*b;
    END IFף
  END LOOP;
END;
/

===========================================
PRAGMA INLINE
===========================================
PRAGMA INLINE value can be NO or YES
NO - no inlining takes place
YES - means inline for level 2, and for level 3

CREATE OR REPLACE PROCEDURE my_proc IS
  a PLS_INTEGER;
  FUNCTION add_number(a PLS_INTEGER, b PLS_INTEGER) 
    RETURN PLS_INTEGER IS
  BEGIN
    RETURN a+b;
  END;
BEGIN
  PRAGMA INLINE (my_proc,'YES');
  a := add_number(3,6) + 9;
END;
/

===========================================
DBMS_TRACE
===========================================
A package to trace PL/SQL code
First of all need to enable subprogram for tracing.
Then set the trace level
Execute the PL/SQL code. As the program executes, the data is collected and stored in database tables.
Turn off tracing.
Examine the collected information.

===========================================
A. Enable subprogram for tracing.
===========================================
Option 1 - alter session, enable debug
ALTER SESSION SET PLSQL_DEBUG=TRUE;
CREATE OR REPLACE FUNCTION...

Option 2 - compile the PL/SQ code with debug flag.
ALTER [PROCEDURE/FUNCTION/PACKAGE BODY] <name> COMPILE DEBUG;

===========================================
B. Set the trace level.
===========================================

set_plsql_trace usage:

EXECUTE DBMS_TRACE.set_plsql_trace(tracelevel1+tracelevel2...)

Possible trace levels are:
trace_all_calls
trace_enabled_calls
trace_all_sql
trace_enabled_sql

trace_all_exceptions
trace_enabled_exceptions
trace_all_lines
trace_enabled_lines
trace_stop
trace_pause
trace_resume

===========================================
C. Run PL/SQL code
===========================================

Execute the PL/SQL code

===========================================
D. Stop the trace
===========================================

EXECUTE DBMS_TRACE.CLEAR_PLSQL_TRACE

===========================================
E. Examine the collected information
===========================================

Query the PLSQL views:

PLSQL_TRACE_RUNS

PLSQL_TRACE_EVENTS

tracetab.sql script creates the dictionary views.