Oracle Stored Procedures Tips Oracle Tips by Burleson Consulting

 

Oracle Stored Procedures Tips

By Don Burleson

Oracle stored procedures and triggers are faster than traditional code, which means they are becoming increasingly popular. As application code moves away from external programs and into the database engine, DBAs need to understand the related memory requirements for Oracle stored procedures and know how to manage Oracle stored procedures for optimal database performance.

Oracle stored procedures and triggers are becoming more popular, and more application code will move away from external programs and into the database engine. However, Oracle DBAs must be conscious of the increasing memory demands of Oracle stored procedures and carefully plan for the days when all of the database access code (PL/SQL) resides within the database.

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

• Better performance. Oracle stored procedures load once into the shared pool and remain there unless they become paged out. Subsequent executions of the Oracle stored procedure are far faster than executions of external code.
   
• Coupling of data with behavior. DBAs can use naming conventions to couple relational tables with the behaviors associated with a table by using Oracle stored procedures as "methods". If all behaviors associated with the employee table are prefixed with the table name--employee.hire, employee.give_raise, for example--the data dictionary can be queries to list all behaviors associated with a table (select * from dba_objects where owner = 'EMPLOYEE'), and it's easy to identify and reuse code via stored procedures.
   
• Isolation of code. Since all SQL is moved out of the external programs and into the Oracle stored procedures, the application programs become nothing more than calls to Oracle stored procedures. As such, it becomes very simple to swap out one database and swap in another one.

Thus a benefit of using stored procedures is being able to move all SQL into the data dictionary allowing you to tune SQL (re-writing the SQL, adding hints), without directly touching the application layer.

One of the foremost reasons Oracle stored procedures and triggers function faster than traditional code is related to caching in the Oracle SGA. After an Oracle stored procedure has been loaded into the shared pool of the SGA, it remains there until it is paged out of memory to make room for other Oracle stored procedures. Items, such as stored procedures, are paged out based on a least recently used (LRU) algorithm.

Once loaded into the RAM memory of the shared pool, stored procedures will execute very quickly.  The trick to the proper use of stored procedures is to prevent pool thrashing as many procedures compete for a limited amount of shared-pool memory.

The db_cache_size and shared_pool_size parameters define most of 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 Oracle stored procedures.

Also see these notes on Oracle stored procedures

A Oracle stored procedure is the derived form of the anonymous block structure which can be identified by its own unique name. It is stored in the database as a database object and it has header and body sections.

The header section consists of the name of the procedure, the parameters for passing IN, OUT and INOUT parameters to a procedure. The body section consists of the declaration section, execution section and the exception section just like the anonymous blocks.

These are called as standalone Oracle stored procedures when they are not defined within the context of any package.   The below prototype defines the syntax for creating a procedure.

 

 

 

The below prototype defines the syntax for compiling or decompiling a procedure.

 

 

The below prototype defines the syntax for dropping a procedure.

 

OR Replace

The "OR Replace" clause recreates the procedure if it is already present in the database. This clause can be used for modifying the definition of the procedure without dropping, recreating and re-granting the necessary object privileges already granted earlier. The function based indexes depending on this procedure will be disabled.

Invoker Rights Clause

The invoker rights clause decides whether the procedure is to be executed with the privileges and in the schema of the owner of the procedure or with the privileges and in the schema of the user who executes it.

Authid Current_user

If Current_user syntax is specified in the Authid clause, the procedure executes with the privileges and in the schema of the user who runs the procedure. This procedure will be then created with invoker-rights.

 

The external names in the queries, DML operations and dynamic queries resolve in the name of the current user.

Authid definer

If definer syntax is specified in the Authid clause, the procedure executes with the privileges and in the schema of the user who owns the procedure. This procedure will be then created with definer-rights.

 

The external names in the queries, DML operations and dynamic queries resolve in the name of the schema where the procedure resides.

 

A Simple Standalone Procedure

The below procedure when executed creates a standalone procedure with the name proc_cube with one input parameter ip_n_var1 of number data type and one output parameter op_n_var2 of number datatype.

 

The following procedure when called, returns the cube value of the input parameter.

 

 

The below message is returned to the user once the procedure creation script is executed.

 

Line no.	Description
1,2	A procedure named proc_cube's header is placed with REPLACE keyword. Replace keyword allows the procedure to be recreated if it is already present in the database.
3,4	The procedure's parameters are declared. The input parameter is ip_n_var1 with number data type and the output parameter is op_n_var2 with number data type.
5	Start of the declare section of the procedure.
6	Start of the execution section of the procedure.
7	The output variable op_n_var2 is assigned with the cube of the input variable ip_n_var1 by multiplying the input variable thrice with itself.
8,9	End of the procedure.

Calling a Procedure

A procedure can be called either via SQL*Plus or by an anonymous block.

SQL*Plus Method

The below illustration shows the process of executing a procedure from SQL*Plus.

 

 

Result:

125

Line no.	Description
1	This environment variable opens up an output buffer of size limit of 200000.
2	A variable l_n_var1 of number data type is declared.
3	Execute command is used for executing the procedure proc_cube with the input parameter as 5 and the output parameter as a bind variable.
4	Execute command is used for printing the output bind variable l_n_var1's value using the dbms_output.put_line procedure.
5	End of the PL/SQL unit.

Anonymous Block Method

The below illustration shows the process of calling a procedure from an anonymous block.

 

Result:

27

Line no.	Description
1	Start of the declare section of the block.
2	A variable l_n_var1 of number data type is declared.
3	Start of the execution section of the block.
4	The procedure proc_cube is called with input parameter as 3 and the variable l_n_var1 is used as the output variable.
5	The local variable l_n_var1's value is printed using the DBMS_OUTPUT.PUT_LINE procedure.
6,7	End of the execution section of the block.

Declaring a stored Procedure in an Anonymous Block

The below approach explains how a procedure can be declared in an anonymous block without creating them permanently in the database. The declaration of the procedure should be always at the end of the declare section of an anonymous block after all the necessary declarations of the anonymous block are declared.

 

The below snippet declares a procedure with two input variables and one output variable all in number data types. When the two parameters of the procedure are passed with two numbers, the output variable returns the product of those.

 

These procedures cannot be viewed in the data dictionary tables as these are not permanently saved in the database.

 

 

Result:

27

Line no.	Description
1	Start of the declare section of the block.
2	A variable l_n_var1 of number data type is declared.
3	Start of the declaration of the procedure proc_prod in the block.
4,5,6	The input variables ip_n_var1, ip_n_var2 and the output variable op_n_var3 are declared with number data type.
7	Start of the declare section of the procedure.
8	Start of the execution section of the procedure.
9	The output variable is assigned with the product of the two input variables.
10	End of the execution section of the procedure.
11	Start of the execution section of the block.
12	The procedure proc_prod is called with the input variables 3,5 and the local variable l_n_var1 is used for fetching the output variable's result.
13	The local variable l_n_var1's value is printed using the DBMS_OUTPUT.PUT_LINE procedure.
14,15	End of the execution section of the block.

 Oracle PL/SQL packages

Oracle provides a construct called a package. Essentially, a package is a collection of functions and Oracle stored procedures. DBAs can organize packages in a variety of ways. For example, you can group functions and Oracle stored procedures for employees logically together in an employee package:

This code encapsulates all employee behaviors into a single package of stored procedures, which will be added to Oracle Server's data dictionary. If DBAs force their programmers to use Oracle 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. This makes the application programs completely portable, while at the same time offering the benefit of using the Oracle dictionary as a ventral repository for all SQL.

• Preparing the SGA for Oracle Stored Procedures

• Dictionary cache.

• Library cache.

• Shared SQL areas

• Private SQL area (exists during cursor open/cursor close). Within the private SQL area are the persistent area and the runtime area.

How To Pin Oracle Packages

To prevent paging, you can mark packages containing Oracle stored procedures 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 the procedure dbms_shared_pool.keep for pinning a package. You can unpin packages by using dbms_shared_pool.unkeep. In addition to pinning packages at database startup time, you can call the dbmspool.keep procedure at runtime to pin a package of stand-alone Oracle stored procedures.

The choice of whether to pin a procedure in memory is a function of the size of the object and the frequency in which it is used.

Very large Oracle stored procedures that are called frequently might benefit from pinning, but you might never notice any difference in that case, because the frequent calls to the stored procedure will have kept it loaded into memory anyway.

In an ideal world, the init.ora shared_pool_size parameter would be large enough to accept every package, Oracle stored procedure and trigger your applications might invoke. Reality, however, dictates that the shared pool cannot grow indefinitely, and you need to make wise choices regarding which objects you pin. You can query the sharable_mem column of the v$db_object_cache table to see how much memory each package consumes in the library cache.

Automatic Re-pinning of Oracle Stored Procedures

Monitoring Pinned Stored Procedures

There is a easy way to tell the number of times a nonpinned Oracle stored procedure was swapped out of memory and needed to be reloaded. One method of effectively measuring memory is to regularly run the estat/bstat utility (usually located in ~/rdbms/admin/utlbstat.sql and utlestat.sql) for measuring SGA memory consumption over a range of time (the range of time is the interval between running the bstat utility and running the estat utility).

Also, be aware that Oracle Server uses the relevant parameter, shared_pool_size, for other objects besides Oracle stored procedures. This means that one parameter fits all, and Oracle Server offers no method for isolating the amount of storage allocated to any subset of the shared pool, including the library cache where packages and Oracle stored procedures reside.

As memory becomes cheaper, it will eventually become desirable to have all of an application's SQL and code loaded into the Oracle library cache, where the code will be quickly available for execution by any external application regardless of platform or host language. The most compelling reasons for putting all SQL within packages are portability and code management. If all applications become "SQL-less," with calls to Oracle stored procedures, then DBAs will be able to port entire applications to other platforms without touching a line of the application code.

Don Burleson is a recognized expert in PL/SQL tuning and PLSQL optimization.  A special thanks to Gita Gupta, of Oracle Corporation, for help with this article.
 

For more Oracle stored procedure scripts, see "Oracle Tuning: The Definitive Reference".