Oracle Database 11gR2 Performance Tuning Cookbook

Inlining PL/SQL code

In this recipe, we will see the benefits of inlining the PL/SQL code in our functions and procedures.

How to do it...

The following steps will demonstrate how to make PL/SQL functions inline:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create a SIMPLE_FUNCTION function, which returns the area of a triangle given the length of he base and the height:

   CREATE OR REPLACE FUNCTION SIMPLE_FUNCTION (N IN NUMBER,
    K IN NUMBER) RETURN NUMBER
   IS
   BEGIN
     RETURN (N * K / 2);
   END;
   /
   

3. Create a STRESS procedure, which calculates the area for a number of triangles using the SIMPLE_FUNCTION function created in step 2:

   CREATE OR REPLACE PROCEDURE STRESS(ANUM NUMBER)
   IS
     AVAL NUMBER;
     T1 NUMBER;
   BEGIN
     T1 := DBMS_UTILITY.get_time;
    FOR J IN 1..ANUM ...