Tricks for using Oracle stored procedures

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 and know how to manage them for optimal database performance.

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 DBAs must be conscious of the increasing memory demands of stored procedures, however, and carefully plan for the days when all of the database access code resides within the database.

Today, most Oracle Server 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, including:

- Better performance. Stored procedures load once into the shared pool and remain there unless they become paged out. Subsequent executions of the 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 them.. 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.

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

Read more on optimizing stored procedures here:

http://www.dba-oracle.com/art_proc.htm

For complete details, see my book "Oracle Tuning: The Definitive Reference":

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call me at 800-766-1884 for details, and check-out our on-site Oracle training catalog:

http://www.dba-oracle.com/bc-catalog.pdf