Databases Reference
In-Depth Information
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:
F
Change the
CURSOR_SHARING
parameter to
SIMILAR
F
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
