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.

Guidelines for Tuning the Oracle Shared Pool

Perhaps one of the least-understood aspects of Oracle Shared Global Area tuning is tuning the shared pool. The generally accepted tuning methodology involves throwing memory into the pool until either the problem goes under or the problem is masked. Here we will examine the shared pool and define a method for tuning it that uses measurement, not guesswork, to drive the tuning methodologies. Numerous scripts for examining the shared pool are provided.

What Is the shared pool?

Many people know that the shared pool is a part of the Oracle SGA but little else, so to begin this discussion it’s necessary to answer exactly,What is the shared pool? The shared pool contains several key Oracle performance-related memory areas. If the shared pool is improperly sized, then overall database performance will suffer, sometimes dramatically.

The shared pool is separated into many substructures. The substructures of the shared pool fall into two broad areas: the fixed-size areas, which, for a given database at a given point in time stay relatively constant in size, and the variable-size areas, which grow and shrink according to user and program requirements.

The areas inside the library caches’ substructure are variable in size, while those outside the library caches (with the exception of the request and response queues used with MTS) stay relatively fixed in size. The sizes are determined based on an Oracle internal algorithm that ratios out the fixed areas based on overall shared pool size, a few of the initialization parameters, and empirical determinations from previous versions. In early versions of Oracle (notably 6.2 and earlier), the dictionary caches could be sized individually allowing a finer control of this aspect of the shared pool. With Oracle 7, the internal algorithm for sizing the data dictionary caches took control from the DBA.

The major difference between the shared pools in Oracle8i and Oracle9i is that any excess memory specified by the SGA_MAX_CACHE parameter and not used in the actual cache and buffer definitions will be placed in the miscellaneous area of the shared pool.

The shared pool is used for objects that can be shared among all users, such as table definitions, reusable SQL (although nonreusable SQL is also stored there), PL/SQL packages, procedures, and functions. Cursor information is also stored in the shared pool. At a minimum, the shared pool must be sized to accommodate the needs of the fixed areas, plus a small amount of memory reserved for use in parsing SQL and PL/SQL statements. If this is not done, ORA-04031 and ORA-07445 errors will result.



GUIDELINES FOR TUNING SHARED POOL:

I hope you now understand that the old "just increase the shared pool" answer isn't good enough anymore when it comes to tuning problems. You must take an in depth look at your shared pool and tune what needs to be tuned, not just throw memory at a problem until it submerges. Indeed, I have shown that in some cases increasing the size of the shared pool may harm performance and decreasing the size may be advisable. The shared pool is vital to the proper performance of your Oracle database, you must have it properly tuned or drown in bad performance. Next we will cover what to pin, the shared pool and multi-threaded server, hashing and generalized library and dictionary cache tuning. We have also discussed ways to monitor for what objects should be pinned, discussed multi-threaded server, looked at hashing problems and their resolution as well as examined classic library and data dictionary cache tuning. We have established 8 guidelines for tuning the Oracle shared pool:

Guideline 1: If gross usage of the shared pool in a non-ad-hoc environment exceeds 95% (rises to 95% or greater and stays there) establish a shared pool size large enough to hold the fixed size portions, pin reusable packages and procedures. Gradually increase shared pool by 20% increments until usage drops below 90% on the average.

Guideline 2: If the shared pool shows a mixed ad-hoc and reuse environment, establish a shared pool size large enough to hold the fixed size portions, pin reusable packages and establish a comfort level above this required level of pool fill. Establish a routine flush cycle to filter non-reusable code from the pool.

Guideline 3: If the shared pool shows that no reusable SQL is being used establish a shared pool large enough to hold the fixed size portions plus a few megabytes (usually not more than 40) and allow the shared pool modified least recently used (LRU) algorithm to manage the pool. (also see guideline 8)

Guideline 4: Determine usage patterns of packages, procedures, functions and cursors and pin those that are frequently used.

Guideline 5: In Oracle7when using MTS increase the shared pool size to accommodate MTS messaging and queuing as well as UGA requirements. In Oracle8 use the Large Pool to prevent MTS from effecting the shared pool areas.

Guideline 6: Use bind variables, PL/SQL (procedures or functions) and views to reduce the size of large SQL statements to prevent hashing problems.

Guideline 7: In a system where there is no flushing increase the shared pool size in 20% increments to reduce reloads and invalidations and increase object cache hit ratios.

Guideline 8: In any shared pool, if the overall data dictionary cache miss ratio exceeds 1 percent, increase the size of the shared pool.

Using these guidelines and the scripts and techniques covered in this lesson, your should be well on the way towards a well tuned and well performing shared pool.

Initialization Parameters That Effect The Shared Pool

NAME


DESCRIPTION

shared_pool_size


size in bytes of shared pool (7 and 8)

shared_pool_reserved_size


size in bytes of reserved area of shared pool (7 and 8)

shared_pool_reserved_min_alloc


minimum allocation size in bytes for reserved area of shared pool (7 and 8)

large_pool_size


size in bytes of the large allocation pool (8 only)

parallel_max_servers


Maximum number of parallel query slaves, if set forces calculation and setting of large pool size parameter (8i)

parallel_adaptive_multi_user


If set forces calculation of large pool size is over-ridden if size manually set(8i). Oracle DOES NOT recommend setting parallel_adaptive_multi_user.

parallel_automatic_tuning


If set forces calculation of large pool size is over-ridden if size is manually set (8i)

large_pool_min_alloc


minimum allocation size in bytes for the large allocation pool (8 only, obsolete in 8i)

parallel_min_message_pool


minimum size of shared pool memory to reserve for pq servers (8 only, obsolete in 8i)

backup_io_slaves


Number of backup IO slaves to configure (8 only)

temporary_table_locks


Number of temporary table locks to configure (7 and 8)

dml_locks


Number of DML locks to configure (7 and 8)

sequence_cache_entries


Number of sequence numbers to cache (7 and 8)

row_cache_cursors


Number of row caches to set up (7 and 8)

max_enabled_roles


Number of role caches to set up (7 and 8)

mts_dispatchers


Number of MTS dispatcher processes to start with (7 and 8)

mts_max_dispatchers


Maximum number of dispatcher processes to allow (7 and 8)

mts_servers


Number of MTS servers to start with (7 and 8)

mts_max_servers


Maximum number of MTS servers to allow (7 and 8)

open_cursors


Maximum number of open cursors per session (7 and 8)

Cursor_space_for_time


Hold open cursors until process exits (7 and 8)
note:Initialization Parameters Used In Tuning Shared Pool

Views Dealing With Shared Pool and Buffer Tuning


VIEW NAME


PURPOSE

V$PARAMETER


Contains current settings for all documented initialization parameters

V$SGASTAT


Contains sizing information for all SGA areas

V$SQLAREA


Contains information and statistics on the SQL area of the shared pool

V$DB_OBJECT_CACHE


Contains information on all cached objects in the database shared pool area

V$LIBRARYCACHE


Contains statistics on the library caches

V$ROWCACHE


Contains statistics on the data dictionary caches

DBA_USERS


Contains database user information

V$BUFFER_POOL


Oracle8 view showing pool areas

V$BUFFER_POOL_STATISTICS


Oracle8 buffer pool statistics

V$BH


View that monitors every buffer in buffer pool

scripts related to Shared Pool Information

SHARED POOL QUICK CHECK NOTES:

select 'You may need to increase the SHARED_POOL_RESERVED_SIZE' Description,
'Request Failures = '||REQUEST_FAILURES Logic
from v$shared_pool_reserved
where REQUEST_FAILURES > 0
and 0 != (
select to_number(VALUE)
from v$parameter
where NAME = 'shared_pool_reserved_size')
union
select 'You may be able to decrease the SHARED_POOL_RESERVED_SIZE' Description,
'Request Failures = '||REQUEST_FAILURES Logic
from v$shared_pool_reserved
where REQUEST_FAILURES < 5
and 0 != (
select to_number(VALUE)
from v$parameter
where NAME = 'shared_pool_reserved_size')


Memory Usage

SHARED POOL MEMORY USAGE NOTES:
# Owner - Owner of the object
# Object - Name/namespace of the object
# Sharable Memory - Amount of sharable memory in the shared pool consumed by the object

select OWNER,
NAME||' - '||TYPE object,
SHARABLE_MEM
from v$db_object_cache
where SHARABLE_MEM > 10000
and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
order by SHARABLE_MEM desc


Loads

LOADS INTO SHARED POOL NOTES:
# Owner - Owner of the object
# Object - Name/namespace of the object
# Loads - Number of times the object has been loaded. This count also increases when an object has been invalidated.

select OWNER,
NAME||' - '||TYPE object,
LOADS
from v$db_object_cache
where LOADS > 3
and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
order by LOADS desc


Executions

SHARED POOL EXECUTION NOTES:
# Owner - Owner of the object
# Object - Name/namespace of the object
# Executions - Total number of times this object has been executed

select OWNER,
NAME||' - '||TYPE object,
EXECUTIONS
from v$db_object_cache
where EXECUTIONS > 100
and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
order by EXECUTIONS desc


Details

SHARED POOL DETAIL NOTES:
# Owner - Owner of the object
# Name - Name of the object
# DB Link - Database link name, if any
# Namespace - Namespace of the object
# Type - Type of the object
# Sharable Memory - Amount of sharable memory in the shared pool consumed by the object
# Loads - Number of times the object has been loaded. This count also increases when an object has been invalidated.
# Executions - Total number of times this object has been executed
# Locks - Number of users currently locking this object
# Pins - Number of users currently pinning this object

select OWNER,
NAME,
DB_LINK,
NAMESPACE,
TYPE,
SHARABLE_MEM,
LOADS,
EXECUTIONS,
LOCKS,
PINS
from v$db_object_cache
order by OWNER, NAME


Library Cache Statistics

SHARED POOL V$LIBRARYCACHE STATISTIC NOTES:
# Namespace - Library cache namespace (SQL AREA, TABLE/PROCEDURE, BODY, TRIGGER, INDEX, CLUSTER, OBJECT, PIPE)
# Gets - Number of times the system requests handles to library objects belonging to this namespace
# GetHits - Number of times the handles are already allocated in the cache. If the handle is not already allocated, it is a miss. The handle is then allocated and inserted into the cache.
# GetHit Ratio - Number of GETHITS divided by GETS. Values close to 1 indicate that most of the handles the system has tried to get are cached.
# Pins - Number of times the system issues pin requests for objects in the cache in order to access them.
# PinHits - Number of times that objects the system is pinning and accessing are already allocated and initialized in the cache. Otherwise, it is a miss, and the system has to allocate it in the cache and initialize it with data queried from the database or generate the data.
# PinHit Ratio - Number of PINHITS divided by number of PINS. Values close to 1 indicate that most of the objects the system has tried to pin and access have been cached.
# Reloads - Number of times that library objects have to be reinitialized and reloaded with data because they have been aged out or invalidated.
# Invalidations - Number of times that non-persistent library objects (like shared SQL areas) have been invalidated.

# GetHit Ratio and PinHit Ratio should be > 70

select NAMESPACE,
GETS,
GETHITS,
round(GETHITRATIO*100,2) gethit_ratio,
PINS,
PINHITS,
round(PINHITRATIO*100,2) pinhit_ratio,
RELOADS,
INVALIDATIONS
from v$librarycache


Reserve Pool Settings

SHARED POOL RESERVED SIZE NOTES:
# Parameter - Name of the parameter
# Value - Current value for the parameter

# shared_pool_reserved_size - Controls the amount of SHARED_POOL_SIZE reserved for large allocations. The fixed view V$SHARED_POOL_RESERVED helps you tune these parameters. Begin this tuning only after performing all other shared pool tuning on the system.
# shared_pool_reserved_min_alloc - Controls allocation for the reserved memory. To create a reserved list, SHARED_POOL_RESERVED_SIZE must be greater than SHARED_POOL_RESERVED_MIN_ALLOC. Only allocations larger than SHARED_POOL_RESERVED_POOL_MIN_ALLOC can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool's free lists. The default value of SHARED_POOL_RESERVED_MIN_ALLOC should be adequate for most systems.

select NAME,
VALUE
from v$parameter
where NAME like '%reser%'


Pinned Objects

PINNED OBJECT NOTES:
# Object Name - Name of the object
# Object Type - Type of the object (INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK)
# Kept Status - YES or NO, depending on whether this object has been "kept" (permanently pinned in memory) with the PL/SQL procedure DBMS_SHARED_POOL.KEEP

select NAME,
TYPE,
KEPT
from v$db_object_cache
where KEPT = 'YES'

Oracle SQL query top-n rows

Oracle SQL query top-n rows

Question: I have made a report, and it counts the occurrences of a column, sorts it and displays the top 20 rows. Now I only want to show the top 5 records how do I do this? What is the best way to perform top-n SQL queries?

Answer: If you are using Oracle 9i and beyond you can use the RANK function for top-n queries, with a faster execution plan (WINDOW SORT PUSHED RANK).

select
*
from
(select empno, sal, rank()
over (order by sal desc ) rnk
from emp)
where rnk <= 5;


Oracle also has the row_number function to speed-up top-n queries:

select

*
from
(select empno, sal row_number() over (order by sal desc) rnk from emp)
where rnk <= 5;

Here is the SQL to display the top-n items using ROWNUM. But beware, this technique of using ROWNUM to materialize an in-line view is not efficient, and it's better to materialize the subquery using the SQL-99 WITH clause.

SELECT
ENAME,
SAL
FROM
(SELECT ENAME, SAL
FROM EMP
ORDER BY SAL DESC)
WHERE
ROWNUM < 4;

Beware! This use of ROWNUM< can cause performance problems. Using ROWNUM may change the all_rows optimizer mode for a query to first_rows, causing unexpected sub-optimal execution plans. One solution is to always include an all_rows hint when using ROWNUM to perform a top-n query