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 article 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. In this part one of a
two part series I will show how to monitor and tune the shared SQL
areas of the shared pool.
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 1 diagrams the shared
pool structure located inside the various Oracle SGAs.