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:
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.