| |
 |
|
Oracle Tips by Burleson |
Monitoring and Tuning the
Shared Pool
Let me begin this subsection by stating that
the default values for the shared pool size initialization
parameters are almost always too small by at least a factor of 4.
Unless your database is limited to the basic scott/tiger type
schema, and your overall physical data size is less than a couple of
hundred megabytes, even the “large” parameters are far too small.
Which parameters control the size of the shared pool? Essentially,
only one: SHARED_POOL_SIZE. The other shared-pool parameters control
how the variable-space areas in the shared pool are parsed out, but
not overall shared pool size. In Oracle8, a new area, the large
pool, controlled by the LARGE_POOL_SIZE parameter, is also present.
The large pool shares latch structures with the shared pool, so
proper sizing of the large pool is critical to help maintain shared
pool health.
Generally speaking, I suggest you start at a
shared-pool size of 40 megabytes and move up from there. The large
pool size will depend on the number of concurrent users, number of
multithreaded server servers and dispatchers and the sort
requirements for the application.
What should you monitor to determine if the
shared pool is too small? For this, you need to wade into the data
dictionary tables, specifically the V$SGASTAT and V$SQLAREA views.
Source 13.2 is a report that shows how much of the shared pool is in
use at any given time the script is run.
See Code Depot
www.oracle-script.com |
