Database Reference
In-Depth Information
The following real-world example demonstrates just how bad things can get if you use the shared pool poorly.
I was asked to work on a system where the standard operating procedure was to shut down the database every night,
to wipe out the SGA and restart it clean. The reason for doing this was that the system was having issues during the
day whereby it was totally CPU-bound and, if the database were left to run for more than a day, performance really
started to decline. They were using a 1GB shared pool inside of a 1.1GB SGA. This is true: 0.1GB dedicated to block
buffer cache and other elements and 1GB dedicated to caching unique queries that would never be executed again.
The reason for the cold start was that if they left the system running for more than a day, they would run out of free
memory in the shared pool. At that point, the overhead of aging structures out (especially from a structure so large) was
such that it overwhelmed the system and performance was massively degraded (not that performance was that great
anyway, since they were managing a 1GB shared pool). Furthermore, the people working on this system constantly
wanted to add more and more CPUs to the machine, as hard-parsing SQL is so CPU-intensive. By correcting the
application and allowing it to use bind variables, not only did the physical machine requirements drop (they then had
many times more CPU power than they needed), but also the allocation of memory to the various pools was reversed.
Instead of a 1GB shared pool, they had less than 100MB allocated—and they never used it all over many weeks of
continuous uptime.
One last comment about the shared pool and the parameter
SHARED_POOL_SIZE
. In Oracle9
i
and before, there is
no direct relationship between the outcome of the query:
ops$tkyte@ORA9IR2> select sum(bytes) from v$sgastat where pool = 'shared pool';
SUM(BYTES)
----------
100663296
and the
SHARED_POOL_SIZE
parameter
ops$tkyte@ORA9IR2> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 83886080
other than the fact that the
SUM(BYTES) FROM V$SGASTAT
will always be larger than the
SHARED_POOL_SIZE
. The shared
pool holds many other structures that are outside the scope of the corresponding parameter. The
SHARED_POOL_SIZE
is typically the largest contributor to the shared pool as reported by the
SUM(BYTES)
, but it is not the only contributor.
For example, the parameter
CONTROL_FILES
contributes 264 bytes per file to the “miscellaneous” section of the shared
pool. It is unfortunate that the “shared pool” in
V$SGASTAT
and the parameter
SHARED_POOL_SIZE
are named as they
are, since the parameter contributes to the size of the shared pool, but it is not the only contributor.
In Oracle 10
g
and above, however, you should see a one-to-one correspondence between the two, assuming you
are using manual shared memory management (i.e., you have set the
SHARED_POOL_SIZE
parameter yourself ):
ops$tkyte@ORA10G> select sum(bytes)/1024/1024 mbytes from v$sgastat where pool = 'shared pool';
MBYTES
----------
128
ops$tkyte@ORA10G> show parameter shared_pool_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 128M
