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
      FOR i in 1 .. 50000
      LOOP
       Foo(i);
      END LOOP;
     END Workload;
     PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE) IS
     BEGIN
      DECLARE
       l_stmt VARCHAR2(2000);
      BEGIN
       l_stmt := 'SELECT * FROM sh.sales s WHERE s.cust_id = ' || TO_CHAR(CUSTID);
       EXECUTE IMMEDIATE l_stmt;
      END;
     END Foo;
   END;
   /
   

4. Now we create the initial snapshot:

   CONNECT PERFSTAT/PERFSTAT
   EXEC statspack.snap;
   

5. Execute the test workload:

   CONNECT SH/SH
   EXEC Chapter1.Workload;
   

6. Now we can elaborate the end snapshot:

   CONNECT PERFSTAT/PERFSTAT
   EXEC statspack.snap;
   

7. Finally we can launch the report creation:

   SQL>@?/RDBMS/ADMIN/SPREPORT.SQL
   

8. When asked, select the last two snapshots created to produce the Chapter1.lst report (naming the report accordingly).

How it works...

In this simple example, the stored procedure Foo inside the package Chapter1 is executed 50,000 times to query the SALES table. We have not used bind variables, and the Statspack report reflects this performance issue:

In the highlighted section of the Statspack report, we can see that only 2.92 percent of parses have been "soft", because the cursor_sharing parameter is set to EXACT and we are not using bind variables.

There's more...

To solve this issue, we can:

• Change the CURSOR_SHARING parameter to SIMILAR

• Recode the Foo procedure, introducing bind variables

In the first case, we have to execute the following statement:

ALTER SYSTEM SET CURSOR_SHARING = SIMILAR SCOPE=MEMORY;

Now we can recreate the snapshots:

CONNECT PERFSTAT/PERFSTAT
EXEC statspack.snap;
CONNECT SH/SH
EXEC Chapter1.Workload;
CONNECT PERFSTAT/PERFSTAT
EXEC statspack.snap;

And finally, we launch the report creation:

SQL>@?/RDBMS/ADMIN/SPREPORT.SQL

The newly created report presents a significant change:

Now the Soft Parse is 97.84 percent.

We can recode the procedure as well; let's rollback the change in CURSOR_SHARING:

ALTER SYSTEM SET CURSOR_SHARING=EXACT SCOPE = MEMORY;

And let's alter the Foo procedure:

CREATE OR REPLACE PACKAGE BODY Chapter1 AS
  PROCEDURE Workload IS
  BEGIN
   FOR i in 1 .. 50000
   LOOP
    Foo(i);
   END LOOP;
  END Workload;
 
  PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE) IS
  BEGIN
   DECLARE
    l_stmt VARCHAR2(2000);
   BEGIN
    l_stmt := 'SELECT * FROM sh.sales s WHERE s.cust_id = :p_cust_id';
    EXECUTE IMMEDIATE l_stmt USING CUSTID;
   END;
  END Foo;
END;
/

Let's launch the snapshots and the report:

CONNECT PERFSTAT/PERFSTAT
EXEC statspack.snap;
CONNECT SH/SH
EXEC Chapter1.Workload;
CONNECT PERFSTAT/PERFSTAT
EXEC statspack.snap;
SQL>@?/RDBMS/ADMIN/SPREPORT.SQL

The newly created report presents a result similar to the previous execution:

There is now a Soft Parse of 99.20 percent.

In this simple example, we have seen how to diagnose a simple problem using Statspack; as an exercise, try to use the other tools presented using the same test case.

Tip

To use AWR and ADDM take a manual snapshot before and after running the Workload procedure.

See also

• Using bind variables in Chapter 4, Optimizing SQL Code

• Minimizing latches using bind variables and Tuning resources to minimize latch contention in Chapter 11, Tuning Contention