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 


 

 

 


 

 

 

 
 

Faster Performance using Oracle Stored Procedures

Oracle Tips by Burleson Consulting
October 2000 - Updated March 2007

As objects such as stored procedures and triggers become more popular, more application code will move away from external programs and into the database engine. Oracle is committed to providing technology an in Oracle 10g we can store and deploy applications with all of the process logic stored (either Java or PL/SQL), all stored inside the database. However, the Oracle DBA must be conscious of the increasing memory demands of stored procedures, and carefully plan for the days when all of the database access code resides within the database.

For more details on optimizing Oracle stored procedure performance see  my book "Oracle Tuning: The Definitive Reference".

Today, most Oracle databases have only a small amount of code in stored procedures, but this is rapidly changing. There are many compelling benefits to putting all Oracle SQL inside stored procedures. These include:

  • Better performance - Stored procedures are loaded once into the SGA and remain there unless they become paged out. Subsequent executions of the stored procedure are far faster than external code.
     
  • Coupling of data with behavior - Relational tables can be coupled with the behaviors that are associated with them by using naming conventions. For example, if all behaviors associated with the employee table are prefixed with the table name (i.e. employee.hire, employee.give_raise), then the data dictionary can be queried to list all behaviors associated with a table (i.e. select * from dba_objects where owner = ‘EMPLOYEE'), and code can be readily identified and re-used.
     
  • Isolation of code - Since all SQL is moved out of the external programs and into stored procedures, the application programs become nothing more than calls to stored procedures. As such, it becomes very simple to swap-out one database and swap-in another.

One of the foremost reasons why stored procedures and triggers function faster than traditional code is related to the Oracle System Global Area (SGA). After a procedure has been loaded into the SGA, it will remain there until it is paged-out of memory. Items are paged-out based on a least-recently-used algorithm. Once loaded into the RAM memory of the shared pool, the procedure will execute very quickly, and the trick is to prevent pool-thrashing while many procedures compete for a limited amount of shared-pool memory.

When tuning Oracle, there are two init.ora parameters that are more important than all of the others combined. These are the db_cache_size and the shared_pool_size parameters. These two parameters define the size of the in-memory region that Oracle consumes on startup and determine the amount of storage available to cache data blocks, SQL and stored procedures.

Oracle also provides a construct called a "package." Essentially, a package is a collection of functions and stored procedures and can be organized in a variety of ways. For example, functions and stored procedures for employees can be logically grouped together in an employee package:

CREATE PACKAGE employee AS

FUNCTION compute_raise_amount (percentage NUMBER);
	PROCEDURE hire_employee();
	PROCEDURE fire_employee();
	PROCEDURE list_employee_details();

END employee;

Here we have encapsulated all employee "behaviors" into a single package that will be added into Oracle's data dictionary. If we force our programmers to use stored procedures, the SQL moves out of the external programs, and the application programs become nothing more than a series of calls to Oracle stored procedures.

Preparing the SGA for Packages and Stored Procedures

As systems evolve and the majority of process code resides in stored procedures, Oracle?s shared pool becomes very important. The shared pool consists of the following sub-pools:

  1.  Dictionary cache
  2.  Library cache
  3.  Shared SQL areas
  4. Private SQL area (exists during cursor open-cursor close)
    1. persistent area
    2. runtime area

The shared pool utilizes a "least recently used" algorithm to determine which objects get paged-out of the shared pool. As this paging occurs, "fragments," or discontiguous chunks of memory, are created within the shared pool.

Imagine the SHARED_POOL being similar to a tablespace. While you may get ora-1547 when you cannot get sufficient contiguous free space in the tablespace, similarly, you will get ora 4031 when you cannot get contiguous free space in the SHARED_POOL(sga).

This means that a large procedure that initially fit into memory, may not fit into contiguous memory when it is reloaded after paging-out. Consider a problem that occurs when the body of a package has been paged out of the instance?s SGA because of other more recent/frequent activity. Fragmentation then occurs, and the server cannot find enough contiguous memory to re-load the package body, resulting in an ORA-4031 error.

How to Pin Oracle Packages

To prevent paging, packages can be marked as non-swappable, telling the database that after their initial load, they must always remain in memory. This is called "pinning," or "memory fencing." Oracle provides a procedure dbms_shared_pool.keep to pin a package. Packages can be unpinned with dbms_shared_pool.unkeep. NOTE: Only packages can be pinned. Stored procedures should be placed into a package if they are to be pinned.

The choice of whether to "pin" a procedure in memory is a function of the size of the object and the frequency that it is used. Very large procedures that are called frequently might benefit from pinning, but you might never notice any difference because the frequent calls to the procedure have kept it loaded into memory. Therefore, since the object never pages-out, the pinning has no effect.

In an ideal world, the shared_pool parameter of the init.ora should be large enough to accept every package, stored procedure and trigger that may be invoked by the applications. Reality, however, dictates that the shared pool cannot grow indefinitely, and wise choices must be made regarding which objects are fenced.

Oracle recommends that the STANDARD, DBMS_STANDARD, DBMS_UTILITY, DBMS_DESCRIBE and DBMS_OUTPUT packages always be pinned in the shared pool.

connect internal;
@/usr/oracle/rdbms/admin/dbmspool.sql
execute dbms_shared_pool.keep('sys.standard');

A standard procedure can be written to pin all of the recommended Oracle packages into the shared pool. Here is the script:

execute dbms_shared_pool.keep('DBMS_ALERT');             
execute dbms_shared_pool.keep('DBMS_DDL');               
execute dbms_shared_pool.keep('DBMS_DESCRIBE');       
execute dbms_shared_pool.keep('DBMS_LOCK');           
execute dbms_shared_pool.keep('DBMS_OUTPUT');           
execute dbms_shared_pool.keep('DBMS_PIPE');            
execute dbms_shared_pool.keep('DBMS_SESSION');           
execute dbms_shared_pool.keep('DBMS_SHARED_POOL');       
execute dbms_shared_pool.keep('DBMS_STANDARD');         
execute dbms_shared_pool.keep('DBMS_UTILITY');                
execute dbms_shared_pool.keep('STANDARD');

Automatic pinning on database Restart

Unix users may want to add code to the /etc/rc file to ensure that the packages are re-pinned after each database startup. This ensures that all packages are re-pinned with each bounce of the box.

The DBA also needs to remember to run pin.sql whenever they must bounce a database from SQL*DBA.

How to Measure Pinned Oracle Stored Procedures

Here is a handy script to look at pinned packages in the SGA:

MEMORY.SQL

set pagesize 60;
column executions format 999,999,999;
column Mem_used   format 999,999,999;

SELECT SUBSTR(owner,1,10) Owner,
       SUBSTR(type,1,12)  Type,
       SUBSTR(name,1,20)  Name,
       executions,
       sharable_mem       Mem_used,
       SUBSTR(kept||' ',1,4)   "Kept?"
 FROM v$db_object_cache
 WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
 ORDER BY executions desc;

HERE IS THE OUTPUT OF MEMORY.SQL:
SQL> @memory

 

OWNER

TYPE

NAME

EXECUTIONS

MEM_USED

Kept

-----

--------

----------------

----------

--------

---

SYS

PACKAGE

STANDARD

867,600

151,963

YES

SYS

PACKAGE BODY

STANDARD

867,275

0,739

YES

SYS

PACKAGE

DBMS_ALERT

502,126

3,637

NO

SYS

PACKAGE BODY

DBMS_ALERT

433,607

20,389

NO

SYS

PACKAGE

DBMS_LOCK

432,137

3,140

YES

SYS

PACKAGE BODY

DBMS_LOCK

432,137

10,780

YES

SYS

PACKAGE

DBMS_PIPE

397,466

3,412

NO

SYS

PACKAGE BODY

DBMS_PIPE

397,466

5,292

NO

HRIS

PACKAGE

S125_PACKAGE

285,700

3,776

NO

SYS

PACKAGE

DBMS_UTILITY

284,694

3,311

NO

SYS

PACKAGE BODY

DBMS_UTILITY

284,694

6,159

NO

HRIS

PACKAGE

HRS_COMMON_PACKAGE

258,657

3,382

NO

HRIS

PACKAGE BODY

S125_PACKAGE

248,857

30,928

NO

HRIS

PACKAGE BODY

HRS_COMMON_PACKAGE

242,155

8,638

NO

HRIS

PACKAGE

GTS_SNAPSHOT_UTILITY

168,978

11,056

NO

HRIS

PACKAGE BODY

GTS_SNAPSHOT_UTILITY

89,623

3,232

NO

SYS

PACKAGE

DBMS_STANDARD

18,953

14,696

NO

SYS

PACKAGE BODY

DBMS_STANDARD

18,872

3,432

NO

KIS

PROCEDURE

RKA_INSERT

7,067

4,949

NO

HRIS

PACKAGE

HRS_PACKAGE

5,175

3,831

NO

HRIS

PACKAGE BODY

HRS_PACKAGE

5,157

36,455

NO

SYS

PACKAGE

DBMS_DESCRIBE

718

12,800

NO

HRIS

PROCEDURE

CHECK_APP_ALERT

683

3,763

NO

SYS

PACKAGE BODY

DBMS_DESCRIBE

350

9,880

NO

SYS

PACKAGE

DBMS_SESSION

234

3,351

NO

SYS

PACKAGE BODY

DBMS_SESSION

165

4,543

NO

GIANT

PROCEDURE

CREATE_SESSION_RECOR

62

7,147

NO

HRIS

PROCEDURE

INIT_APP_ALERT

6

10,802

NO

There is an easy way to tell the number of times that a non-pinned stored procedure was swapped-out of memory and required a reload. To effectively measure memory, two methods are recommended. The first is to regularly run the estat-bstat utility (usually located in ~/rdbms/admin/utlbstat.sql and utlestat.sql) for measuring SGA consumption over a range of time.

Another handy method is to write a snapdump utility to interrogate the SGA and note any exceptional information relating to the library cache. This would include the following measurements:

  1. Data dictionary hit ratio
  2. Library cache miss ratio
  3. Individual hit ratios for all namespaces

Also, be aware that the relevant parameter, shared_pool_size, is used for other objects besides stored procedures. This means that one parameter fits all, and Oracle offers no method for isolating the amount of storage allocated to any subset of the shared pool.

Here is a sample report for gathering information relating to shared_pool_size. As you can see, the data dictionary hit ratio is more than 95 percent, and the library cache miss ratio is very low. However, we see more than 125,000 reloads in the SQL area namespace and may want to increase the shared_pool_size. Always remember when running this type of report that the statistics are gathered since startup, and the numbers may be skewed. For example, for a system that has been running for six months, the data dictionary hit ratio will be a running average over six months. Of course, this is meaningless if you want to measure today's statistics.

Some DBA's will run utlbstat.sql, wait one hour, and run utlestat.sql. This produces a report that shows the statistics over the elapsed time interval.

=========================
DATA DICT HIT RATIO
=========================
(should be higher than 90 else increase shared_pool_size in init.ora)


Data Dict.      Gets Data Dict.     cache misses DATA DICT CACHE HIT RATIO
------------    ------------------  ------------------
41,750,549      407,609             99

=========================
LIBRARY CACHE MISS RATIO
=========================
(If > 1 then increase the shared_pool_size in init.ora)

Executions Cache misses while executing LIBRARY CACHE MISS RATIO
------------     ------------------     ------------------
22,909,643       171,127                .0075

=========================
Library Cache Section
=========================
hit ratio should be > 70, and pin ratio > 70 ...

 

NAMESPACE

Hit ratio

pin hit ratio

reloads

-------

------

------

---------

SQL AREA

84

94

125,885

TABLE/PROCEDURE

98

99

43,559

BODY

98

84

486

TRIGGER

98

97

1,145

INDEX

0

0

CLUSTER

31

33

OBJECT

100

100

PIPE

99

99

52


Here is the SQL*Plus script that generated the report:

prompt
prompt
prompt         =========================
prompt         DATA DICT HIT RATIO
prompt         =========================
prompt (should be higher than 90 else increase shared_pool_size in init.ora)
prompt

column "Data Dict. Gets"            format 999,999,999
column "Data Dict. cache misses"    format 999,999,999
select sum(gets) "Data Dict. Gets,"
       sum(getmisses) "Data Dict. cache misses,"
       trunc((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT RATIO"
from v$rowcache;

prompt
prompt         =========================
prompt         LIBRARY CACHE MISS RATIO
prompt         =========================
prompt (If > 1 then increase the shared_pool_size in init.ora)
prompt
column "LIBRARY CACHE MISS RATIO" format 99.9999
column "executions"    format 999,999,999
column "Cache misses while executing"    format 999,999,999
select sum(pins) "executions", sum(reloads) "Cache misses while executing",
    (((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"
from v$librarycache;
prompt
prompt         =========================
prompt          Library Cache Section
prompt         =========================
prompt hit ratio should be > 70, and pin ratio > 70 ...
prompt

column "reloads" format 999,999,999
select namespace, trunc(gethitratio * 100) "Hit ratio",
trunc(pinhitratio * 100) "pin hit ratio", reloads "reloads"
from v$librarycache;

As memory becomes cheaper, 500 MB Oracle regions will not be uncommon, but until that time, the DBA must carefully consider the ramifications of pinning a package in the SGA.

 
If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

 

 

��  
 
 
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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational