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.
