Remote DBA Plans
Remote DBA Service

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

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

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.

rem FUNCTION: Report Stored Object Statistics
rem
column owner           format a11           heading Schema
column name            format a30           heading Object|Name
column namespace                            heading Name|Space
column type                                 heading Object|Type
column kept            format a4            heading Kept
column sharable_mem    format 999,999       heading Shared|Memory
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
spool rep_out/&db/o_stat
select OWNER, NAMESPACE, TYPE, NAME, SHARABLE_MEM, LOADS,
      EXECUTIONS, LOCKS, KEPT
from   v$db_object_cache
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;
spool off
set lines 80 pages 22 feedback on
clear columns
clear breaks
ttitle off

Date: 12/16/99
Page:   1
Time: 12:54 PM                Oracle Should KeepReport                                 SYSTEM
                                   SBSFAPRD database


            Name            Object         Should Keep             Shared

Schema      Space           Type           Object Name             Memory     LOADS
Executes   LOCKS Kept
----------- --------------- -------------- ---------------------- -------- ---------- --
SIEBEL      TABLE/PROCEDURE PROCEDURE      NGS_EXPORT_PROC          63,310         51    5,471      72 NO

TRIGGER TRIGGER S_OPTY_ESCL_T1 6,733 130
######## 92 NO

                                           S_OPTY_POSTN_ESCL_T2      7,035        635   94,678      41 NO
                                           S_OPTY_PROD_ESCL_T1       6,466        110   78,297     151 NO
                                           S_ACCNT_POSTN_ESCL_T1     6,308        674   40,756      10 NO
                                           S_OPTY_POSTN_ESCL_T3      2,971         60   38,236       1 NO
                                           S_ACCNT_POSTN_ESCL_T2     6,308        674   36,428      12 NO
                                           S_OPTY_CON_ESCL_T3        6,481        792    3,669        5 NO
                                           S_SRV_REQ_ESCL_T1         6,984        678    3,429        6 NO
                                           S_OPTY_PROD_ESCL_T3       6,602        420    2,393        2 NO
                                           S_ADDR_ORG_ESCL_T2        7,473        203    1,987        9 NO
                                           NGS_U_TR                  5,743         39    1,515       28 NO
                                           S_ORG_EXT_ESCL_T3         6,016        140   443 2 NO
                                           S_ORG_EXT_T_ESCL_T3       6,162         81    194    2 NO
                                           S_ADDR_ORG_ESCL_T3        6,409         38    114    3 NO
                                           S_ORG_INDUST_ESCL_T3      6,331         33    102    2 NO

SYS         TABLE/PROCEDURE PACKAGE        STANDARD                120,844        107 397,486     170 NO
                                           DBMS_APPLICATION_INFO    12,161        278 173,486       2 NO
                                           DBMS_STANDARD            15,097        109 161,973     234 NO
                                           DBMS_OUTPUT              14,615        148 100,434       1 NO
                                           UTL_FILE                  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 13.9.

LISTING 13.9 Use of DBMS_SHARED_POOL.SIZES.

SQL> set 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') SZ,DECODE(KEPT_VE
              RSIONS,0,'      ',RPAD('YES(' || TO_CHAR(KEPT_VERSIONS) |
              | ')' ,6)) KEEPED,RAWTOHEX(ADDRESS) || ',' || TO_CHAR(HASH
              _VALUE) NAME,SUBSTR(SQL_TEXT,1,354) EXTRA   FROM V$SQLAREA
              WHERE SHARABLE_MEM > :b1 * 1000   UNION SELECT TO_CHAR(SH
             ARABLE_MEM / 1000 ,'999999') SZ,DECODE(KEPT,'YES','YES
             (004D7F84,2008220828)       (CURSOR)
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) val
            ues(:1,:2,:3,:4,:5,:6)
            (0027BA44,-512326869)       (CURSOR)

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.

SQL> execute dbms_shared_pool.keep('sys.standard');
PL/SQL procedure successfully completed.
SQL> execute dbms_shared_pool.sizes(130);

SIZE(K) KEPT NAME
------- ------ --------------------------------------------------
139 YES SYS.STANDARD (PACKAGE)

PL/SQL 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 8.1.7.2. 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.

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