This is article #6 in the series providing tips and techniques to produce orders of magnitude improvements in the performance of Oracle databases. This month we'll see how the use of stored procedures can make a BIG difference to performance. Previous issues can be accessed from here . If you have any questions or comments please send them to us, feedback is always welcome.
Let's quickly recap the techniques you have available to you to improve the performance of your Oracle database:-
The first 5 techniques have already been covered (click here to refresh your memory of these techniques).
First, let's define a stored procedure. A stored procedure is simply a module that is stored in the Oracle database and is written in either PL/SQL (Oracle's proprietary procedural language) or Java. The term procedure is really a misnomer as the "procedure" can be a function (i.e. it returns a value), a procedure (may have input and/or output parameters), or a package (may contain one or more procedures/functions).
This is not a tutorial on PL/SQL (see here for an Oracle PL/SQL tutorial) or a reference on how to use it, merely a tutorial on how best to use it in the context of improving performance. For a complete reference on PL/SQL read Steven Feuerstein's excellent book "Oracle PL/SQL Programming".
We've defined a stored procedure, but what does that mean really? Put simply a stored procedure is tightly coupled with the database and consists of database access commands (SQL), plus control statements and data structures to manipulate the data obtained from the database. How does that help? It helps when a lot of manipulation of the data is required either before it is sent to the database or before it is sent to the client or when there is no client involved such as in a batch program.
The main performance advantages arise from the fact that there is no need to send data back and forth across the network whilst it is being processed and from the already mentioned fact that stored procedures are tightly coupled with the database and are stored in pre-compiled form. This means that the data manipulation commands are processed much more efficiently than would be the case if an external program was used. The other benefit derives from the fact that the stored procedures will be run on the server hosting your Oracle database and that is generally much more powerful than the pcs running the client software.
Stored procedures are ideal when there is a complex piece of business logic that needs to be performed involving a lot of database access. If this logic is required in many different places, then even better.
For example when performing data entry, the front end program (written in VB or Oracle Forms or whatever) can perform simple validation such as checking that numbers are entered in numeric fields, proper dates are entered in date fields etc. But what if there is a need to validate information entered against existing data in the database due to business rules about the acceptance of the data and this validation involves a lot of manipulation of information from the database and complex business knowledge?
Then stored procedures come into their own - the client footprint is small, the network traffic is reduced and the server does all the hard work which is what it was designed to do, and due to the tight coupling of stored procedures with the Oracle database, the load on the server won't be that great.
For a detailed reference on stored procedures and pl/sql you need a proper book such as Oracle PL/SQL Programming as mentioned earlier, but we do need to just touch on some of the features of PL/SQL that you can exploit to boost performance.
As mentioned in the definition a stored procedure can be a procedure, a function or a package (a grouping of related procedures, functions and data structures). The biggest benefit in the use of stored procedures comes from the use of packages, because whenever any element of the package is referenced, the whole package is loaded into memory and (assuming it is not aged out) remains in memory for future use. Also if other elements in the package are needed the overhead of calling them is very small, because they are in the same memory area. This means that you can and should group related program units together so that they can share data structures when needed and call each other with minimal overhead.
Stored procedures (preferably as part of a package) are also a great way to manipulate data extracted from a database before it is returned to the caller and hiding or encapsulating complex logic that needs to be performed in several places.
Stored procedures that are functions can also be embedded directly in SQL statements and used in the same way as the standard SQL functions such as TO_CHAR or TO_DATE. This only works with functions because they have to return a value implicitly. These functions can be stand alone but they are usually embedded within a package comprising related procedures and functions to take advantage of the fact that the whole package is loaded on first reference.
Having extolled the virtues of stored procedures, you should be aware that PL/SQL is not always the best tool, sometimes it is quicker to do things in pure SQL rather than use PL/SQL For example the following, trivial, example shows the pitfalls that you need to avoid.
i := 1;
UPDATE <my_table> SET <my_col> = 1
WHERE id = i;
EXIT WHEN SQL%NOTFOUND;
i := i + 1;
This is a perfectly valid piece of code, but it will have very poor performance because the update statement is called once for every row meeting the condition in the where clause. A much better way to do this would be in pure SQL statement as follows:-
UPDATE <my_table> SET <my_col> = 1;
You may think that it's obvious this would be much faster, and it certainly should be obvious but this is a common mistake when programmers think PL/SQL is the answer to everything.
The other pitfall of using Oracle PL/SQL is that involves a context switch from SQL which may add a considerable overhead. This is most likely to be a problem when embedding PL/SQL function calls in SQL statements. Also the code inside PL/SQL functions called from SQL statements will be ignored by EXPLAIN PLAN. This means that a judgment will have to be made on whether the advantages of using stored procedures will outweigh the disadvantages.
When used correctly, stored procedures can produce a big improvement in performance of Oracle databases. They are ideal for batch programs and complex business validation logic that need to interact with the database. The biggest benefits come from the use of packages which group together related functions, procedures and data structures. Oracle stored procedures are also ideal for simplifying complex SQL statements, but beware that there is an overhead in switching context from SQL to PL/SQL, but even taking this into account, they can still boost performance. You would obviously have to test this though, to make sure the context switching overheads won't outweigh the the performance improvements gained from using PL/SQL
Looking for more Oracle tips and tricks ? Subscribe to our ezine jam-packed full of tips and tricks to help you make more of your Oracle systems and save you hours searching for information. Subscribe today - and your first issue will soon be winging its way to your mailbox.
Smartsoft Computing Ltd
Tel: 0845 0031320
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.
© Copyright Smartsoft Computing Ltd 2001-2009. All rights reserved.