Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 6, 2003

Oracle9iR2 Shared Pool Advisory

By James Koopmann

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.
  • 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.



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

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM