Maximize Your Oracle Technology Investment with Quality Training and Certification from the Source
-
Expert Instructor Insights
April 28, 2015
Understanding the Shared Pool for Tuning
Written by Ira Singer, Oracle DBA
Understanding
the Shared Pool for Tuning, a recent webinar, I talked about
parsing the library cache of the shared pool, how to cut down on hard parses,
and AWR (Automatic Workload Repository) findings to help tune the library cache.
The main purpose of the shared pool is to share your SQL
statements -- your execution plans, especially for online transaction
processing. There are other areas in the shared pool that can be used, but
there are some important areas of the shared pool that are helpful to understand.
The major components of the shared pool are:
• The
library cache, which stores shared
SQL and PL/SQL code and object metadata in areas distinguished by
namespaces. You're aiming to have most of your code, in an OLTP operation, in
the library cache. But, if it isn't in the library cache, you then have to do a
hard parse.
• The
data dictionary cache, which holds
row images from the data dictionary tables -- also called the row cache.
• The
results cache holds query result
sets and query fragments, so subsequent queries can retrieve the result
directly from the cache. If you have large SQL statements that are going to give
you a smaller set of results, that will go into the results cache.
If
we do the hard parse, we tend to slow down the overall performance. Instead, we’ll
want to tune the library cache.
SQL Statement Processing Phases
SQL
statements are in four phases: a parse, a bind, an execute, and a fetch.
The
aspects that deal particularly with the shared pool are parse and bind:
- Parse
phase is one of the stages in the processing of a SQL statement.
When an application issues a SQL statement, the application makes a parse call
to the Oracle Database. During the parse call, the Oracle Database checks the
statement for syntactic and semantic validity, determines whether the process
issuing the statement has privileges to run it, searches for an sharable match
of the statement in the shared pool, and allocates a private SQL area for the
statement.
- Bind phase checks the statements for the bind variable and assigns/reassigns a value
to the bind variable. When bind variables are used properly, more soft parses
are possible, thereby reducing hard parses and keeping parsed statements in the
library cache for a longer period.
Now
it’s good to look at the Automatic Workload Repository, or the Statspack
indicators that you might be having problems with in your shared pool.
Please watch my webinar, because
in it I show you these AWR reports in detail and how to use them for hints of
problems. Specifically, we look at a load profile, instance efficiencies, top
wait events, and the time model. I also provide several examples for how shared pools
work. I
hope you can join me for the full session.
Check out other webinars
available through Oracle Learning Streams.
"my webinar" link is not working its will be of great help if you can share the correct webinar link.