Oracle Database 11gR2 Performance Tuning Cookbook

A working example

In this recipe we will present a simple example of a performance tuning session, applying the recipes seen earlier.

Getting ready

The example is based on the SH schema. Be sure Statspack is installed, as presented in an earlier recipe.

How to do it...

The following steps demonstrate a simple example using the SH schema:

1. We assume the user PERFSTAT with the password PERFSTAT and the user SH with the password SH. The TESTDB database is the default instance.
2. Launch SQL*Plus and connect to the SH schema:

   $ sqlplus SH/SH
   

3. Create the package Chapter1:

   CREATE OR REPLACE PACKAGE Chapter1 AS
     PROCEDURE Workload;
     PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE); 
   END;
   /
   
   CREATE OR REPLACE PACKAGE BODY Chapter1 AS
     PROCEDURE Workload IS
     BEGIN