Shared Pool Summary
Oracle Tips by Burleson Consulting
Let’s review what we have seen so far. We have
examined reports that show both gross and detailed shared-pool usage
and whether or not shared areas are being reused. What can we do with
this data? Ideally, we will use the results to size our shared pool
properly. I’ll set seven general guidelines for shared-pool sizing:
Guideline 1: If gross usage of the shared pool not in an
ad hoc environment exceeds 95 percent (rises to 95 percent or greater
and stays there), establish a shared-pool size large enough to hold
the fixed-size portions, pin reusable packages and procedures, and
then increase shared pool by 20 percent increments until usage drops
below 90 percent 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, then 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.
In guidelines 1, 2, and 3, start at around 40
megabytes for a standard-size system. Notice that guideline 2 states
that a routine flush cycle should be instituted. This flies in the
face of what Oracle Support used to push in their shared-pool white
papers; however, they were working from the assumption that proper SQL
is being generated, whereas you want to reuse the SQL present in the
shared pool. Oracle’s latest paper acknowledges that there are times
when a shared-pool flush is beneficial. In a mixed environment, where
there is a mixture of reusable and nonreusable SQL, the nonreusable
SQL will act as a drag against the other SQL (I call this shared-pool
thrashing) unless it is periodically removed by flushing. The
Remote DBA_UTILITIES package contains a PL/SQL package called FLUSH_IT, which
can be used by the DBMS_JOB job queues to periodically flush the
shared pool, but only when it exceeds a specified percent.
Because there is always a debate as to whether
this really does help performance, I set up a test on a production
instance: on day 1, I did no automated flushing; on day 2, I
instituted the automated flushing. Figure 13.2 shows the graphs of
performance indicators, flush cycles, and users.
Figure 13.2 Graphs showing the effects of
In the graphs in Figure 13.2, pay particular
attention to the overall trend of the performance indicator between
day 1 and day 2. On day 1 (the day with an initial flush, as indicated
by the steep plunge on the pool utilization graph, followed by the
buildup to maximum and the flattening of the graph), the performance
indicator shows an upward trend. The performance indicator is a
measure of how long the database takes to do a specific set of tasks
(from the Q Diagnostic tool from Savant Corporation). Therefore, an
increase in the performance indicator shows a net decrease in
performance. On day 2, the overall trend is downward, with the average
value less than the average value from day 1. Overall, the flushing
improved the performance, as shown by the performance indicator, by 10
to 20 percent. Depending on the environment, I have seen improvements
of up to 40 to 50 percent.
One thing that made the analysis difficult was
that, on day 2, several large batch jobs were run, which weren’t run
on day 1. The results still show that flushing has a positive effect
on performance when the database is a mixed-SQL environment with a
large percentage of non-reusable SQL areas.
Guideline 3 also brings up an interesting
point: you may have already overallocated the shared pool. In this
case, guideline 3 may result in your decreasing the size of the shared
pool. In this situation, the shared pool becomes a cesspool filled
with nothing but garbage SQL. After allocating enough memory for
dictionary objects and other fixed areas, and ensuring that the
standard packages and such are pinned, you should maintain only a few
megabytes above and beyond this level of memory for SQL statements.
Since none of the code is being reused, you want to reduce the hash
search overhead as mush as possible. You do this by reducing the size
of the available SQL area memory so as few a number of statements as
possible are kept.
What to Pin
In all of the rules stated so far, I mention
that the memory is usually allocated above and beyond that needed for
fixed-size areas and pinned objects. How do you determine what to pin?
Generally speaking, any package, procedure, function, or cursor that
is frequently used by your application should be pinned into the
shared pool when the database is started.
Packages, cursors, sequences, triggers,
procedures, and functions may be pinned in Oracle databases using the
DBMS_SHARED_POOL package. The DBMS_SHARED_POOL package may have to be
built in earlier releases of Oracle. This is done using the
DBMSPOOL.SQL and PRVTPOOL.PLB scripts, located in (UNIX) $ORACLE_HOME/rdbms/admin
or (NT) x:\ora9i\rdbms\admin, where x: is the home drive for your
How do you determine which packages,
procedures, or functions to pin? Actually, Oracle has made this easy
by providing the V$DB_OBJECT_CACHE view that shows all objects in the
pool, and, more importantly, how they are being utilized. The report
in Source 13.9 provides a list of objects that have been loaded more
than once and have executions greater than 1. Some example output from
this script is shown in Listing 13.8. A rule of thumb is that if an
object is being frequently executed and frequently reloaded, it should
be pinned into the shared pool.
SOURCE 13.9 Stored object statistics report.
FUNCTION: Report Stored Object Statistics
column owner format a11 heading Schema
column name format a30 heading Object|Name
column namespace heading
column kept format a4 heading Kept
column sharable_mem format 999,999 heading
column executions format 999,999 heading Executes
set lines 132 pages 47 feedback off
@title132 'Oracle Objects Report'
break on owner on namespace on type
select OWNER, NAMESPACE, TYPE, NAME, SHARABLE_MEM, LOADS,
EXECUTIONS, LOCKS, KEPT
where type not in (
'NOT LOADED','NON-EXISTENT','VIEW','TABLE','SEQUENCE',’PACKAGE BODY’)
and executions>0 and loads>1 and kept='NO'
order by owner,namespace,type,executions desc;
set lines 80 pages 22 feedback on
Time: 12:54 PM Oracle Should KeepReport
Name Object Should Keep
Space Type Object Name Memory
Executes LOCKS Kept
----------- --------------- -------------- ----------------------
-------- ---------- --
SIEBEL TABLE/PROCEDURE PROCEDURE NGS_EXPORT_PROC
63,310 51 5,471 72 NO
TRIGGER TRIGGER S_OPTY_ESCL_T1
######## 92 NO
7,035 635 94,678 41 NO
6,466 110 78,297 151 NO
6,308 674 40,756 10 NO
2,971 60 38,236 1 NO
6,308 674 36,428 12 NO
6,481 792 3,669 5 NO
6,984 678 3,429 6 NO
6,602 420 2,393 2 NO
7,473 203 1,987 9 NO
5,743 39 1,515 28 NO
6,016 140 443 2 NO
6,162 81 194 2 NO
6,409 38 114 3 NO
6,331 33 102 2 NO
SYS TABLE/PROCEDURE PACKAGE STANDARD
120,844 107 397,486 170 NO
12,161 278 173,486 2 NO
15,097 109 161,973 234 NO
14,615 148 100,434 1 NO
3,596 82 7,210 73 NO
To really show the concept, the example report
in Listing 13.8 was taken from one of the more active instances I have
monitored. What’s interesting to note about this report is that the
majority of objects are triggers: you can pin triggers, cursors,
packages, procedures, or functions. Also note that you have to pin
only the package, not the package and package body.
Another criterion for determining if an object
should be pinned into the shared pool is its size. The
DBMS_SHARED_POOL.SIZES procedure searches the shared pool for any
objects larger than the size in kilobytes of the argument it is
passed. Generally, the larger the size, the more likely that the
object is a package, meaning you will want to keep it in the pool;
smaller objects tend to be individual queries and can be aged out of
the pool. Remember that, generally, the DBMS_SHARED_POOL procedure is
not automatically loaded when an instance is built; the DBMSPOOL.SQL
and PRVTPOOL.PLB scripts must be run from INTERNAL or SYS users for it
to be created. The use of DBMS_SHARED_POOL.SIZES is shown in Listing
LISTING 13.9 Use of DBMS_SHARED_POOL.SIZES.
serveroutput on size 4000;
SQL> execute sys.dbms_shared_pool.sizes(10);
SIZE(K) KEPT NAME
139 SYS.STANDARD (PACKAGE)
56 SYS.DBMS_SHARED_POOL (PACKAGE BODY)
31 SELECT TO_CHAR(SHARABLE_MEM / 1000 ,'999999')
RSIONS,0,' ',RPAD('YES(' || TO_CHAR(KEPT_VERSIONS)
| ')' ,6)) KEEPED,RAWTOHEX(ADDRESS) || ',' ||
_VALUE) NAME,SUBSTR(SQL_TEXT,1,354) EXTRA FROM
WHERE SHARABLE_MEM > :b1 * 1000 UNION SELECT
ARABLE_MEM / 1000 ,'999999') SZ,DECODE(KEPT,'YES','YES
30 SYS.STANDARD (PACKAGE BODY)
27 SYS.DBMS_SHARED_POOL (PACKAGE)
17 SYS.V$SQLAREA (VIEW)
16 SYS.V$DB_OBJECT_CACHE (VIEW)
15 insert into idl_ub2$(obj#,part,version,piece#,length,piece)
PL/SQL procedure successfully completed.
The “set serveroutput on size 4000” command in
Listing 13.9 limits the size of the output buffer to 4,000 bytes. The
“set serveroutput” command is required. Perhaps in the future, if we
all bug Oracle for an enhancement, they will incorporate the use of
UTIL_FILE and generate us a report listing we can review as we desire.
As you can see from this listing, there is one large package in shared
memory. Let’s issue a keep against this package to retain it. Listing
13.10 shows the results from this action.
LISTING 13.10 Example DBMS_SHARED_POOL.KEEP.
PL/SQL procedure successfully completed.
SQL> execute dbms_shared_pool.sizes(130);
SIZE(K) KEPT NAME
------- ------ --------------------------------------------------
139 YES SYS.STANDARD (PACKAGE)
procedure successfully completed.
By issuing keeps against large packages to
hold them in memory, you can mitigate shared-pool fragmentation that
results in the ORA-04031 error. Pinning the packages so they don’t age
out prevents smaller queries, cursors, and procedures from taking
their areas; then, when the packages are reloaded, viola! An ORA-04031
as the package seeks a large enough group of areas in which to install
itself. Under ORACLE8, this was supposed to be eliminated due to the
way the shared-memory area is now used; however, I have had some
reports on as late a version as 18.104.22.168. Oracle9i has promised better
memory management of the shared pool, but the jury is still out, as
there aren’t many large-scale production databases running Oracle9i as
of this writing.
Guideline 4: Always determine usage patterns of
packages, procedures, functions, triggers, and cursors, and pin those
that are frequently used.
Code Depot for Full Scripts
||This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.