The
v$shared_pool_advice
advisory functionality
has been extended in Oracle9i release 2 to include a new advice called
v$shared_pool_advice, and there is talk to expending the advice
facility to all SGA RAM areas in future releases of Oracle.
In Oracle9i release 2, the
v$shared_pool_advice show the marginal difference in SQL parses
as the shared pool changes in size from 10% of the current value to 200%
of the current value.
The Oracle documentation
contains a complete description for the set-up and use of shared pool
advice, and it is very simple to configure. Once it is installed, you
can run a simple script to query the v$shared_pool_advice view
and see the marginal changes in SQL parses for different shared_pool
sizes.
-- ************************************************
-- Display shared pool advice
-- ************************************************
set lines 100
set pages 999
column c1 heading 'Pool |Size(M)'
column c2 heading 'Size|Factor'
column c3 heading 'Est|LC(M) '
column c4 heading 'Est LC|Mem. Obj.'
column c5 heading 'Est|Time|Saved|(sec)'
column c6 heading 'Est|Parse|Saved|Factor'
column c7 heading 'Est|Object Hits' format 999,999,999
SELECT
shared_pool_size_for_estimate c1,
shared_pool_size_factor c2,
estd_lc_size c3,
estd_lc_memory_objects c4,
estd_lc_time_saved c5,
estd_lc_time_saved_factor c6,
estd_lc_memory_object_hits c7
FROM
v$shared_pool_advice;
Est
Est
Time Parse
Pool Size Est Est LC Saved
Saved Est
Size(M) Factor LC(M) Mem. Obj. (sec) Factor
Object Hits
---------- ---------- ---------- ---------- ---------- ----------
------------
48 .5 48 20839 1459645 1
135,756,032
64 .6667 63 28140 1459645 1
135,756,101
80 .8333 78 35447 1459645 1
135,756,149
96 1 93 43028 1459645 1
135,756,253
112 1.1667 100 46755 1459646 1
135,756,842
128 1.3333 100 46755 1459646 1
135,756,842
144 1.5 100 46755 1459646 1
135,756,842
160 1.6667 100 46755 1459646 1
135,756,842
176 1.8333 100 46755 1459646 1
135,756,842
192 2 100 46755 1459646 1
135,756,842
Here we see the statistics
for the shared pool in a range from 50% of the current size to 200% of
the current size. These statistics can give you a great idea about the
proper size for the shared_pool‑_size. If you are automatic the
SGA region sizes with automated “alter system” commands, creating
this output and writing a program to interpret the results is a great
way to ensure that the shared pool and library cache always have enough
RAM.
Tuning the Shared Pool for Oracle 11g
Oracle 11g provides the v$shared_pool_advice
dynamic performance view that can be used to obtain tuning advice
for the shared pool as shown in the following example.
SQL> desc v$shared_pool_advice
Name Null? Type
----------------------------------------- -------- ----------
SHARED_POOL_SIZE_FOR_ESTIMATE NUMBER
SHARED_POOL_SIZE_FACTOR NUMBER
ESTD_LC_SIZE NUMBER
ESTD_LC_MEMORY_OBJECTS NUMBER
ESTD_LC_TIME_SAVED NUMBER
ESTD_LC_TIME_SAVED_FACTOR NUMBER
ESTD_LC_LOAD_TIME NUMBER
ESTD_LC_LOAD_TIME_FACTOR NUMBER
ESTD_LC_MEMORY_OBJECT_HITS NUMBER
To obtain a truly useful value for sizing the shared pool for
Oracle 11g, set the statistics_level parameter to either TYPICAL
or ALL before querying the v$shared_pool_advice dynamic
performance view.
So if one wants to look at recommended settings for the shared
pool with Oracle 11g, issue the following query:
SQL> SELECT
shared_pool_size_for_estimate "Size of Shared Pool in MB",
2 shared_pool_size_factor "Size Factor",
3 estd_lc_time_saved "Time Saved in sec"
4
FROM v$shared_pool_advice;
Size of Shared Pool in MB Size
Factor Time Saved in sec
------------------------- ----------- -----------------
136 .7727 17000
156 .8864 17009
176 1 17036
196 1.1136 17048
216 1.2273 17051
236 1.3409 17052
256 1.4545 17052
276 1.5682 17052
296 1.6818 17052
316 1.7955 17052
336 1.9091 17052
Size of Shared Pool in MB Size
Factor Time Saved in sec
------------------------- ----------- -----------------
356 2.0227 17052
12 rows selected.
An interesting observation can be noted here in the output from
the v$shared_pool_advice query in the above figure.
The performance benefit varies little between a shared pool
size of 136MB to 156MB. Only a real performance benefit can be
seen when the shared pool size has been increased to 236MB. Also
of note is that increasing the shared pool size beyond 236MB
yields no additional performance benefit for the shared pool
performance with Oracle 11g. In fact, Oracle support notes on
Metalink (Metalink Note #255409.1) advise against increasing the
size of the shared pool to a large value as it may cause latch
contention or fragmentation problems.
Another dynamic performance view, v$rowcache, may also assist
tuning efforts for the shared pool memory cache regions. While hit
ratios are no longer gospel as far as performance tuning results,
they can be helpful when used as part of a comprehensive
performance tuning assessment. In general, hit ratios should be
above 95% for the library cache and data dictionary cache within
the shared pool for Oracle 11g. Also, a performance report
collected via the Oracle 11g ADDM (Automatic Database Diagnostic
Monitor) and/or report from a recent snapshot with the Oracle 11g
ADR (Automatic Diagnostic Repository) will yield outstanding
performance issues with the shared pool cache areas.
