| |
 |
|
Oracle Tips by Burleson |
Shared Pool Tuning
Perhaps one of the least understood areas of Oracle Shared Global
Area optimization is tuning the shared pool. The generally accepted
tuning methodology involves throwing memory into the pool until the
problem goes under. In this section of the paper we will examine the
shared pool and define a method for tuning the shared pool that uses
measurement, not guesswork to drive the tuning methodologies.
What is the shared pool?
Many people know that the shared pool is a part of the Oracle shared
global area (SGA) but little else, what exactly is the shared pool?
The shared pool contains several key Oracle performance related
memory areas. If the shared pool is improperly sized then overall
database performance will suffer, sometimes dramatically. Figure 7
diagrams the shared pool structure located inside the various Oracle
SGAs.
As you can see from examining the structures pictured in Figure 7,
the shared pool is separated into many substructures. The
substructures of the shared pool fall into two broad areas, the
fixed size areas that for a given database at a given point in time
stay relatively constant in size and the variable size areas that
grow and shrink according to user and program requirements.
In Figure 7 the areas inside the library caches substructure are
variable in size while those outside the library caches (with the
exception of the request and response queues used with MTS) stay
relatively fixed in size. The sizes are determined based on an
Oracle internal algorithm that ratios out the fixed areas based on
overall shared pool size, a few of the intialization parameters and
empirical determinations from previous versions. In early versions
of Oracle (notably 6.2 and lower versions) the dictionary caches
could be sized individually allowing a finer control of this aspect
of the shared pool. With Oracle 7 the internal algorithm for sizing
the data dictionary caches took control from the DBA.
The shared pool is used for objects that can be shared among all
users such as table definitions, reusable SQL (although non-reusable
SQL is also stored there), PL/SQL packages, procedures and
functions. Cursor information is also stored in the shared pool. At
a minimum the shared pool must be sized to accommodate the needs of
the fixed areas plus a small amount of memory reserved for use in
parsing SQL and PL/SQL statements or ORA-07445 errors will result.
This is an excerpt by
Mike Ault’s e-book:
Tuning
Third-party Vendor Oracle Systems
Tuning when you can't touch the code
ISBN:
0-9740716-3-3
http://www.rampant-books.com/ebook_vendor_tune.htm
|
