Oracle9iR2 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


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.

    will cause Shared Pool Advisory statistics to be collected along with other

Check Your Current Setting

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

SQL> SHOW PARAMETER statistics_level
———————————— ———– ———–
statistics_level string TYPICAL

To change your setting

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



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;

—————————– ———————– ——————
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


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.


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.

Latest Articles