Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle Concepts - Fixed Size Areas and Pinned Objects

Oracle Tips by Burleson Consulting

What to Pin

In all of the guidelines 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. I suggest adding a ?null? startup function to every in house generated package it essentially looks like Source 17.

FUNCTION start_up RETURN number IS
  Ret NUMBER:=1;
BEGIN
  Ret:=0
  RETURN ret;
END start_up;

Source 17: Example Null Startup Function

The purpose of the null startup function is to provide a touch point to pull the entire package into the shared pool. This allows you to create a startup SQL procedure that pulls all of the application packages into the pool and pins them using the DBMS_SHARED_POOL package. The DBMS_SHARED_POOL package may have to be built in earlier releases of Oracle. The DBMS_SHARED_POOL package is built using the DBMSPOOL.SQL and PRVTPOOL.PLB scripts located in (UNIX) $ORACLE_HOME/rdbms/admin or (NT) x:\orant\rdbms\admin  (where x: is the home drive for your install).

How do you determine what packages, procedures of 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 script in figure 15.32 provides a list of objects that have been loaded more than once and have executions greater than one. Some example output from this script is shown in Source 18. A rule of thumb is that if an object is being frequently executed and frequently reloaded it should be pinned into the shared pool.

rem
rem FUNCTION: Report On Objects Which Should Be Kept
rem
COLUMN owner FORMAT A11 HEADING Schema
COLUMN name FORMAT A22 HEADING 'Should Keep|Object Name'
COLUMN HEADING Name|Space
COLUMN type FORMAT A20 HEADING Object|Type
COLUMN kept FORMAT A4 HEADING Kept
COLUMN sharable_mem FORMAT 999,999 HEADING Shared|Memory
COLUMN executions FORMAT 9,999,999 HEADING Executes
SET LINES 132 PAGES 47 FEEDBACK OFF
@title132 'Oracle Should Keep Report'
BREAK ON owner ON namespace ON type
SPOOL rep_out/&db/should_keeps
SELECT 
      owner,
      namespace,
      type,
      name,
      sharable_mem,
      loads, 
      executions,  
      locks,   
      pins,
      kept
FROM
      v$db_object_cache
WHERE
      type NOT IN ('NOT LOADED','NON-EXISTENT','VIEW','TABLE',
                    'SEQUENCE','INVALID TYPE')
      AND loads>1 AND executions>loads AND executions>100 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

Source 18: Script to Show Objects Which Should Be Kept

The output from the script in Source 18 is shown in Listing 21. Notice the objects with high executions.

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

            Name            Object         Should Keep                     
Shared
Schema      Space           Type           Object Name                     
Memory      LOADS Executes      LOCKS       PINS Kept
----------- --------------- -------------- ------------------------------
SIEBEL      TABLE/PROCEDURE PROCEDURE      NGS_EXPORT_PROC                 
63,310         51    5,471         72          0 NO    
            TRIGGER         TRIGGER        S_OPTY_ESCL_T1                   
6,733        130 ########         92          0 NO    
                                           S_OPTY_POSTN_ESCL_T2             
7,035        635   94,678         41          0 NO    
                                           S_OPTY_PROD_ESCL_T1               
6,466        110   78,297        151          0 NO    
                                           S_ACCNT_POSTN_ESCL_T1           
6,308        674   40,756         10          0 NO    
                                           S_OPTY_POSTN_ESCL_T3             
2,971         60   38,236          1          0 NO    
                                           S_ACCNT_POSTN_ESCL_T2            
6,308        674   36,428         12          0 NO    
                                           S_ACCNT_POSTN_ESCL_T3            
6,372        220   31,664          4          0 NO    
                                           S_OPTY_PROD_ESCL_T2             
6,618        167   24,587         14          0 NO    
                                           S_OPTY_CON_ESCL_T2               
6,273        664   17,926         14          0 NO    
                                           S_OPTY_ESCL_T2                   
6,981        156   16,783          6          0 NO    
                                                         S_OPTY_POSTN_ESCL_T1             
6,707        705   16,306         25          0 NO    
                                           S_CONTACT_ESCL_T2                
6,064        183   11,077         10          0 NO    
                                           S_ORG_EXT_T_ESCL_T2              
6,882        467    7,022          8          0 NO    
                                           S_ORG_EXT_ESCL_T2                
7,368        448    7,021          8          0 NO    
                                           S_OPTY_CON_ESCL_T3               
6,481        792    3,669          5          0 NO    
                                           S_SRV_REQ_ESCL_T1                
6,984        678    3,429          6          0 NO     
                                           S_OPTY_PROD_ESCL_T3              
6,602        420    2,393          2          0 NO    
                                           S_ADDR_ORG_ESCL_T2               
7,473        203    1,987          9          0 NO    
                                           NGS_U_TR                         
5,743         39    1,515         28          0 NO    
                                           S_ORG_EXT_ESCL_T3                
6,016        140      443          2          0 NO    
                                           S_ORG_EXT_T_ESCL_T3              
6,162         81      194          2          0 NO    
                                           S_ADDR_ORG_ESCL_T3               
6,409         38      114          3          0 NO    
                                           S_ORG_INDUST_ESCL_T3             
6,331         33      102          2          0 NO    
SYS         BODY            PACKAGE BODY   STANDARD                        
28,700         11  397,486        170          0 NO    
                                           DBMS_APPLICATION_INFO            
4,209         40  164,523          2          0 NO    
                                                         DBMS_OUTPUT                      
9,399         30  134,819          1          0 NO    
                                           UTL_FILE                         
9,380         53    7,210         73          0 NO    
            TABLE/PROCEDURE PACKAGE        STANDARD                        
120,844        107  397,486        170          0 NO
                                           DBMS_APPLICATION_INFO           
12,161        278  173,486          2          0 NO    
                                           DBMS_STANDARD                    
15,097        109  161,973        234          0 NO
                                           DBMS_OUTPUT                     
14,615        148  100,434          1          0 NO    
                                           UTL_FILE                         
3,596         82    7,210         73          0 NO    

Listing 21. Example Output From the Script In Source 18.

Note that you only have to pin the package, not the package and package body.

Another criteria 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 likelihood that the object is a package and 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 the DBMS_SHARED_POOL procedure is not generally 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 22.

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.

Listing 22. Example of the use of the DBMS_SHARED_POOL.SIZES procedure

The ?set serveroutput on size 4000? command in Listing 22 limits the size of the output buffer to 4000 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 just generate us a report listing 21 we can review as we desire. As you can see from Listing 22, there is one large package in shared memory. Let?s issue a keep against this package to retain it. Listing 23 shows the results from this action.

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.

Listing 23: Example of the use of DBMS_SHARED_POOL.KEEP packaged procedure.

By issuing keeps against large packages to hold them in memory you can mitigate shared pool fragmentation that results in the ORA-04031 error. By pinning the packages so they don?t age out this prevents smaller queries, cursors and procedures from taking their areas and 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 is 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.0.5.

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

 


This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It?s only $19.95 when you buy it directly from the publisher here.

 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.