Databases Reference
In-Depth Information
From this query we can see that there were a total of 248 parses, 47 of which were hard
and 201 were soft parses.
If for test purposes we want to flush the content of the library cache, we can execute the
following statement, which flushes the content of the shared pool (so the library cache):
ALTER SYSTEM FLUSH SHARED_POOL;
Please note that flushing the shared pool in a production
environment can have drastic effects on the database. We
are doing this operation in a test environment.
See also
F
The
Using bind variables and parsing
recipe in
Chapter 6
,
Optimizing PL/SQL Code
gives more details on parsing in PL/SQL code
F
Recipes in
Chapter 7
are focused on the Oracle optimizer
F
In
Tuning the Shared Pool
and
Tuning the Library Cache
recipes in
Chapter 9,
we
will explore the structure used in this recipe in more detail.
Reducing the number of requests to the
database using stored procedures
To achieve better performance, we should reduce the number of requests made to the
database, especially if those requests have to be routed to a network. There are many
strategies to reduce these requests. In this recipe, we discuss the use of stored procedures
and packages for achieving this goal.
In this recipe, we execute a simple query in the SH schema. In the first script, we will use
SQL*Plus to test the SQL statement and the corresponding stored procedure execution. In
the Java program, we will use the same query and stored procedure. For each of these tests,
record the execution time.
How to do it...
The following steps will demonstrate how to reduce the number of requests to the database:
1.
Open your preferred text editor and copy the following script, and save it as
StoredProcedure.SQL
:
SET ECHO OFF
SET FEEDBACK OFF
