Optimizing Application Design - Oracle Database 11gR2 Performance Tuning - page 53

Then we flushed the shared pool and the buffer cache, as it is a good practice to start with an
empty working area in a test/development environment—if we don't do so, the first execution
will take longer than the subsequent calls because the data will already be in the buffer cache.
The next set of statements will execute the query and the stored procedure, while at the
same time measuring the time elapsed.
The stored procedure opens the cursor and returns it to
the caller, while the query actually returns all the records.
The Java class executes the same query and stored procedure. In either case, it executes
a loop on the ResultSet to show every record returned; before executing the query and
the stored procedure, flush the shared pool and the buffer cache for the same reason as
explained earlier.
There's more...
Using a stored procedure improves the performance while executing SQL statements on the
database. Another reason to use stored procedures—which are usually grouped in packages—is
that they allow the sharing of reusable code, as illustrated in the previous recipe. If we use only
stored procedures and packages to manipulate the data, there is a single place that stores
all the used statements, which can be easily reused without rewriting. Rewriting similar SQL
statements is not only a waste of time, when coding, but can also be a performance issue
during the execution phase.
See also
F Recipes from Chapter 6 , Optimizing PL/SQL Code
Reducing the number of requests to the
database using sequences
In this recipe, we continue to explore ways to reduce the number of requests made to the
database, illustrating how the use of sequences can help us in achieving this as well as
improved database scalability.
Sequences are used to assign a sequential number—unique until the sequence is recreated
or reinitialized. In many non-Oracle databases, there are tools that allow developers to
automatically assign a sequential number to a field—often the primary key—the so-called
autoinc fields (Microsoft ® SQL Server ® and IBM ® DB2 ® can define a field IDENTITY , MySQL™
has the AUTO_INCREMENT attribute, and so on).