| |
 |
|
Oracle Tips by Burleson |
Shared Pool Summary
Let’s review what we have seen so far. We
have examined reports that show both gross and detailed shared-pool
usage and whether or not shared areas are being reused. What can we
do with this data? Ideally, we will use the results to size our
shared pool properly. I’ll set seven general guidelines for
shared-pool sizing:
-
Guideline 1: If gross usage of the
shared pool not in an ad hoc environment exceeds 95 percent
(rises to 95 percent or greater and stays there), establish a
shared-pool size large enough to hold the fixed-size portions,
pin reusable packages and procedures, and then increase shared
pool by 20 percent increments until usage drops below 90 percent
on the average.
-
Guideline 2: If the shared pool shows a
mixed ad hoc and reuse environment, establish a shared-pool size
large enough to hold the fixed-size portions, pin reusable
packages, and establish a comfort level above this required
level of pool fill, then establish a routine flush cycle to
filter nonreusable code from the pool.
-
Guideline 3: If the shared pool shows
that no reusable SQL is being used, establish a shared pool
large enough to hold the fixed-size portions, plus a few
megabytes (usually not more than 40), and allow the
shared-pool-modified least recently used (LRU) algorithm to
manage the pool.
This is an excerpt by Mike Ault’s book “Oracle9i
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download. |
