Shared Pool Advisory

If you want to get the most out of Oracle, you will have to learn how to size its internal memory structures. Let's take a look at the Shared Pool, what it is composed of, and a new tool that Oracle has given us tune it.

As an Oracle DBA, you quickly learn one the golden rule in Oracle. If you can use more memory, you are typically better. When it comes to parsing, if you do not allocate enough memory, you could drastically affect the performance of your applications. One the down side, you surely do not want to allocate too much memory or you will be wasting a valuable resource that could be used by other processes or Oracle structures. This article looks at tuning the Shared Pool in Oracle by the new feature: The Shared Pool Advisory.
What Is the Shared Pool Advisory

The shared pool advisory is an Oracle9i feature that keeps track of the library cache's use of shared pool memory. While doing this it keeps statistics to determine the behavior of differently sized shared pools. Typically, the advisory will keep a bucket of statistics for shared pool sizes that range from 50% below your current setting to 200% of your current setting. It is then up to us as database administrators to use these statistics to determine what the size of the shared pool should be through the use of the view V$SHARED_POOL_ADVICE . This view will give us 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 we might experience when parsing if we were to change the shared pool size.
How to Ensure the Advisory is on

STATISTICS_LEVEL

Using the new Oracle initialization parameter called STATISTICS_LEVEL, you can set this advisory to be on. This parameter has three settings BASIC, TYPICAL, and ALL.

* BASIC - which does nothing, basically turned off.
* TYPICAL/ALL - will cause Shared Pool Advisory statistics to be collected along with other statistics.

Check Your Current Setting

Issue the following command to determine what your setting is for STATISTICS_LEVEL.

SQL> SHOW PARAMETER statistics_level
NAME TYPE VALUE
------------------------------------ ----------- -----------
statistics_level string TYPICAL

To change your setting

You may change the current setting of STATISTICS_LEVEL by issuing the following ALTER statement.

SQL> ALTER SYSTEM SET statistics_level=TYPICAL SCOPE=SPFILE;

Use of the V$SHARED_POOL_ADVICE

After you have set the STATISTICS_LEVEL parameter and run some decent workload through your system, you can then issue a very simple query to extract the information from V$SHARED_POOL_ADVICE table and see the advisory suggestions. Listing 1 gives the query that you should issue. The SHARED_POOL_SIZE_FACTOR shows the factor from the current setting. In Listing 1, you can see that my shared pool size was set at 80M since the SHARED_POOL_SIZE_FACTOR is 1 (one). You can also see that by decreasing the shared pool size, the estimated elapsed parse time I could save would actually increase. On the other hand, if I increased the size of the shared pool I could experience some savings in the parse time. Also, note that with a shared pool of sizes over 112M I would not experience any additional parse time savings. By examining the output, it should be clearly seen that I should set my shared pool size to 128M if I have the memory available. In addition, take note that you should also monitor this as time goes on, as the mix of workload into your system may change and you may have to re-adjust. In addition, the advisories are not an exact science and you should monitor these statistics to see if you can gain additional savings or give memory back to other consumers of this precious resource.

Listing 1
Query to extract Shared Pool Advisory Statistics

SQL> l
1 SELECT shared_pool_size_for_estimate,
2 shared_pool_size_factor,
3 estd_lc_time_saved
4 FROM v$shared_pool_advice;

SHARED_POOL_SIZE_FOR_ESTIMATE SHARED_POOL_SIZE_FACTOR ESTD_LC_TIME_SAVED
----------------------------- ----------------------- ------------------
48 .6 4603
64 .8 4594
80 1 4590
96 1.2 4578
112 1.4 4545
128 1.6 4741
144 1.8 4741
160 2 4741


Conclusion

Given the nature of one of Oracle's most precious resources, memory, we should guard it and protect the use of it. The shared pool advisory gives us just one more key tool in the detection of how we are using memory within an Oracle instance. This tool is so easy to use that no one should go long without checking memory usage for the library cache. Who knows you may even get a performance boost in the parsing your instance is doing.