Using the shared pool advisor in 9.2+. The v$shared_pool_advice view.

July 30, 2012 admin Old but still valuable, Oracle

To make use of new feature of Oracle 9.2.x i.e. Shared Pool Advisory.

This advisory advices on about library cache memory and predicts the effect
of altering shared pool size on the total amount parsing activities in the system.
+ When it comes for parsing, if the appropiate memory is not allocated,
one could land-up having poor
performance of the database and application.
+ On the other side if one allocates memory more than required to shared pool,
he will be wasting the invaluable resources of the server.

What is Shared Pool Advisory ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ The shared pool advisory is an Oracle 9.2 feature which keeps a track of library cache’s
usage of shared pool.
+ It keeps the statistics for different sizes of shared pool.(50% of current setting to 200%).
+ DBA can undesrtand the Shared pool size and assoaciated performance using a
new view V$SHARED_POOL_ADVICE.
+ This view (v$shared_pool_advice) will give information on such items as an
estimate on how much memory is being used by the library cache, the sizes
of objects in the library cache, the estimated parse time and the time
savings one might experience when parsing if one was to change the shared pool size.

How to Enable Shared Pool Advisory ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ Init.ora parameter STATISTICS_LEVEL needs to be set to either ALL or TYPICAL (not BASIC).
+ This parameter is dynamaically modifiable.

SQL> alter system set statistics_level=typical;

How to collect useful information from V$SHARED_POOL_ADVICE ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

After setting the above stated parameter and running a decent workload on the system,
we can query this view using a simple SQL given as below :-

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
————————- ———– —————–
24 .5 525
32 .6667 525
40 .8333 525
48 1 525
56 1.1667 525
64 1.3333 525
72 1.5 525
80 1.6667 525
88 1.8333 526
96 2 526

10 rows selected.

+ The above output shows the current setting of the shared pool is
48M (for which Size factor is 1).
+ It also shows decreasing the size of the shared pool to the 50% of its
current value will also be equally efficient as the current value.
+ Also doubling the size of the shared pool will save extra 1 sec in parsing.
+ Using this view a DBA has the correct picture to design Shared pool.

Rename, Recreate or Move REDO Logs Disable AWR snapshots.