|
 |
|
Oracle Concepts -
Guidelines for Tuning the Oracle Shared Pool
Oracle Tips by Burleson Consulting |
In Summary
I hope you now understand that the old "just
increase the shared pool" answer isn't good enough anymore when it
comes to tuning problems. You must take an in depth look at your
shared pool and tune what needs to be tuned, not just throw memory at
a problem until it submerges. Indeed, I have shown that in some cases
increasing the size of the shared pool may harm performance and
decreasing the size may be advisable. The shared pool is vital
to the proper performance of your Oracle database, you must have it
properly tuned or drown in bad performance. Next we will cover what to
pin, the shared pool and multi-threaded server, hashing and
generalized library and dictionary cache tuning. We have also
discussed ways to monitor for what objects should be pinned, discussed
multi-threaded server, looked at hashing problems and their resolution
as well as examined classic library and data dictionary cache tuning.
We have established 8 guidelines for tuning the Oracle shared pool:
Guideline 1: If gross usage of the shared pool
in a non-ad-hoc environment exceeds 95% (rises to 95% or greater and
stays there) establish a shared pool size large enough to hold the
fixed size portions, pin reusable packages and procedures. Gradually
increase shared pool by 20% increments until usage drops below 90% 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.
Establish a routine flush cycle to filter non-reusable 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. (also see guideline 8)
Guideline 4: Determine usage patterns of
packages, procedures, functions and cursors and pin those that are
frequently used.
Guideline 5: In Oracle7when using MTS increase
the shared pool size to accommodate MTS messaging and queuing as well
as UGA requirements. In Oracle8 use the Large Pool to prevent MTS from
effecting the shared pool areas.
Guideline 6: Use bind variables, PL/SQL
(procedures or functions) and views to reduce the size of large SQL
statements to prevent hashing problems.
Guideline 7: In a system where there is no
flushing increase the shared pool size in 20% increments to reduce
reloads and invalidations and increase object cache hit ratios.
Guideline 8: In any shared pool, if the
overall data dictionary cache miss ratio exceeds 1 percent, increase
the size of the shared pool.
Using these guidelines and the scripts and
techniques covered in this lesson, your should be well on the way
towards a well tuned and well performing shared pool.
Table 18: Initialization Parameters That
Effect The Shared Pool
|
NAME |
DESCRIPTION |
|
shared_pool_size |
size in bytes of shared pool (7 and 8) |
|
shared_pool_reserved_size |
size in bytes of reserved area of shared
pool (7 and 8) |
|
shared_pool_reserved_min_alloc |
minimum allocation size in bytes for
reserved area of shared pool (7 and 8) |
|
large_pool_size |
size in bytes of the large allocation pool
(8 only) |
|
parallel_max_servers |
Maximum number of parallel query slaves,
if set forces calculation and setting of large pool size parameter
(8i) |
|
parallel_adaptive_multi_user |
If set forces calculation of large pool
size is over-ridden if size manually set(8i). Oracle DOES
NOT recommend setting parallel_adaptive_multi_user. |
|
parallel_automatic_tuning |
If set forces calculation of large pool
size is over-ridden if size is manually set (8i) |
|
large_pool_min_alloc |
minimum allocation size in bytes for the
large allocation pool (8 only, obsolete in 8i) |
|
parallel_min_message_pool |
minimum size of shared pool memory to
reserve for pq servers (8 only, obsolete in 8i) |
|
backup_io_slaves |
Number of backup IO slaves to configure (8
only) |
|
temporary_table_locks |
Number of temporary table locks to
configure (7 and 8) |
|
dml_locks |
Number of DML locks to configure (7 and 8) |
|
sequence_cache_entries |
Number of sequence numbers to cache (7 and
8) |
|
row_cache_cursors |
Number of row caches to set up (7 and 8) |
|
max_enabled_roles |
Number of role caches to set up (7 and 8) |
|
mts_dispatchers |
Number of MTS dispatcher processes to
start with (7 and 8) |
|
mts_max_dispatchers |
Maximum number of dispatcher processes to
allow (7 and 8) |
|
mts_servers |
Number of MTS servers to start with (7 and
8) |
|
mts_max_servers |
Maximum number of MTS servers to allow (7
and 8) |
|
open_cursors |
Maximum number of open cursors per session
(7 and 8) |
|
Cursor_space_for_time |
Hold open cursors until process exits (7
and 8) |
Table 19: Initialization Parameters Used In
Tuning Shared Pool
|
VIEW NAME |
PURPOSE |
|
V$PARAMETER |
Contains current settings for all
documented initialization parameters |
|
V$SGASTAT |
Contains sizing information for all SGA
areas |
|
V$SQLAREA |
Contains information and statistics on the
SQL area of the shared pool |
|
V$DB_OBJECT_CACHE |
Contains information on all cached objects
in the database shared pool area |
|
V$LIBRARYCACHE |
Contains statistics on the library caches |
|
V$ROWCACHE |
Contains statistics on the data dictionary
caches |
|
DBA_USERS |
Contains database user information |
|
V$BUFFER_POOL |
Oracle8 view showing pool areas |
|
V$BUFFER_POOL_STATISTICS |
Oracle8 buffer pool statistics |
|
V$BH |
View that monitors every buffer in buffer
pool |
Table 20: Views Dealing With Shared Pool and
Buffer Tuning
|
Software |
Manufacturer |
Purpose |
|
Oracle Administrator |
RevealNet, Inc. |
Administration Knowledge base |
|
Q Diagnostic |
Savant, Corp. |
Provide Oracle DB diagnostics |
Table 21: Software Mentioned in Lessons
This is an excerpt from
the eBook "Oracle
DBA made Simple".
For more details on Oracle
database administration, see the "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam. It’s
only $19.95 when you buy it directly from the publisher
here.
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
