In this recipe we will present a simple example of a performance tuning session, applying the recipes seen earlier.
The example is based on the SH schema. Be sure Statspack is installed, as presented in an earlier recipe.
The following steps demonstrate a simple example using the SH schema:
We assume the user
PERFSTATwith the passwordPERFSTATand the userSHwith the passwordSH. TheTESTDBdatabase is the default instance.Launch SQL*Plus and connect to the
SHschema:$ sqlplus SH/SHCreate 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; /
Now we create the initial snapshot:
CONNECT PERFSTAT/PERFSTAT EXEC statspack.snap;
Execute the test workload:
CONNECT SH/SH EXEC Chapter1.Workload;
Now we can elaborate the end snapshot:
CONNECT PERFSTAT/PERFSTAT EXEC statspack.snap;
Finally we can launch the report creation:
SQL>@?/RDBMS/ADMIN/SPREPORT.SQLWhen asked, select the last two snapshots created to produce the
Chapter1.lstreport (naming the report accordingly).
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.
To solve this issue, we can:
Change the
CURSOR_SHARINGparameter toSIMILARRecode the
Fooprocedure, 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.
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