6.10 Resorting to PLSQL | Oracle High Performance Tuning for 9i and 10g

< Day Day Up >

6.10 Resorting to PL/SQL

PL/SQL is an acronym for Programming Language for SQL. From a purely programming perspective PL/SQL is really SQL with programming logic wrapper controls, amongst numerous other bells and whistles. PL/SQL is a very primitive programming language at best. Beware of writing entire applications using PL/SQL. SQL is designed to retrieve sets of data from larger sets and is not procedural, sequential, or object-like in nature. Programming languages are required to be one of those or a combination thereof. PL/SQL has its place as a relational database access language and not as a programming language. Additionally PL/SQL is interpretive which means it is slow!

Note

Oracle Database 10 Grid PL/SQL objects are now stored in compiled form in binary object variables or BLOB objects. This helps PL/SQL procedure execution performance.

Does PL/SQL allow for faster performance? The short answer is no. The long answer is as follows. PL/SQL allows much greater control over SQL coding than simple SQL does. However, modern Oracle SQL is much more sophisticated than it was years ago and some of the aspects of PL/SQL used in the past allowing better performance in PL/SQL than SQL are effectively redundant, particularly with retrieval of data.

There are a number of reasons for resorting to PL/SQL:

PL/SQL will not provide better performance but will allow a breakdown of complexity. Breaking down complexity can allow easier tuning of SQL statements through better control of cursors.
An obvious benefit of PL/SQL is the ability to build stored procedures and triggers. Triggers should be used sparingly and avoided for implementation of Referential Integrity; constraints are much faster. In the case of stored procedures the obvious benefits are centralized control and potential performance increases because stored procedure code is executed on the server. Execution on a database server reduces network traffic.
There are some situations where it is impossible to code SQL code using SQL alone and thus PL/SQL has to be resorted to. As Oracle Corporation has developed SQL this has become less frequent.
Some exceedingly complex SQL code can benefit from the use of PL/SQL instead of SQL. Some SQL code can become so complex that it is impossible to tune or even code in SQL and hence PL/SQL becomes the faster option. Using the DECODE function is similar to control structures such as IF and CASE statements. PL/SQL allows all the appropriate control structures.
PL/SQL packages can be cached into memory to avoid re-parsing.

PL/SQL can provide better program coding control of cursors plus execution and maintenance from a central, single point. Perhaps the most significant benefit of resorting to PL/SQL is a potential reduction in complexity. Once again, as with views, reducing complexity is not necessarily conducive to performance tuning. In fact simplicity, as with over-Normalization can often hurt performance in a relational database. Thus further discussion of tuning SQL retrieval code using PL/SQL is largely irrelevant.

Tuning PL/SQL is a programming task and has little to do with coding well-performing SQL code. The obvious programming points to note are as follows:

Do not process more in a loop or a cursor than is necessary. Break out of loops when no more processing is required.
Large IF statements should be replaced with CASE statements or appropriate breaks should be used. Traditionally CASE statements are faster than IF statements.
Recursion using embedded procedures creates very elegant code but in PL/SQL can cause performance problems, especially where transactions are not completed. PL/SQL is not executed as compiled code in the sense that compiled C code is. PL/SQL compilation involves syntax parsing and interpretation; no binary copy is created. When PL/SQL code is executed it is interpreted and not executed from a compiled binary form.

Note
Oracle Database 10 Grid PL/SQL objects are now stored in compiled form in binary object variables or BLOB objects. This helps PL/SQL procedure execution performance.
The WHERE CURRENT OF clause can refer to a cursor ROWID, giving direct pointer access to a cursor row in memory. The RETURNING INTO clause can help with reducing the need for subsequent SELECT statements further on in a procedure by returning values into variables.
Explicit cursors can be faster than implicit cursors but are more difficult to code.
PL/SQL has three parameter formats for passing variables in and out of procedures. IN and OUT pass values in and out of procedures, respectively. IN OUT passes a pointer both in and out of a procedure. Use only what is needed.
As with SQL code embedded in applications, PL/SQL procedures of any kind can use bind variables. Using bind variables can have a profound effect on increasing performance by lowering parsing requirements in the library cache.

6.10.1 Tuning DML in PL/SQL

There are two interesting points to note in relation to performance of DML statements in PL/SQL. Other than what has already been covered in this chapter there is little else which can be done to tune DML (INSERT, UPDATE, DELETE, MERGE) or SELECT statements in PL/SQL.

The RETURNING INTO Clause

In the following example the RETURNING clause prevents the coder having to code an extra SELECT statement to find the identifier value required for the second INSERT statement. The same applies similarly to the second INSERT statement returning a second value for the DBMS_OUTPUT.PUT_LINE procedure.

DECLARE       division_id division.division_id%TYPE;       division_name division.name%TYPE;       department_name department.name%TYPE; BEGIN       INSERT INTO division(division_id, name)       VALUES(division_seq.NEXTVAL, 'A New Division')       RETURNING division_id, name INTO division_id,          division_name;           INSERT INTO department(department_id, division_id, name)       VALUES(department_seq.NEXTVAL, division_id, 'A New          Department')       RETURNING name INTO department_name;           DBMS_OUTPUT.PUT_LINE('Added : '||division_name             ||', '||department_name); END; /

Note

Oracle Database 10 Grid The RETURNING clause can be used to return collections.

6.10.2 When to Resort to PL/SQL and Cursors

In general, SQL in Oracle Database is now powerful enough to deal with almost any requirement. Some occasions do call for substituting SQL code nested loop type queries with PL/SQL. PL/SQL provides better programming control and allows much easier management of highly complex SQL code. It is sometimes the case that better performance will be gained using PL/SQL to control nested SQL statements.

PL/SQL can sometimes be faster when compiled, pinned, and executed on the database server, depending on the application. The goal of stored procedures is to minimize on network traffic. With the speed of modern network connections available this is not necessarily an issue anymore.

So what do I mean by resorting to PL/SQL and cursors? After all I did not just state "Resorting to PL/SQL". So what am I talking about? Look at this example.

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT * FROM coa NATURAL JOIN generalledger;

This query plan shows the COA table in the outer loop and the GeneralLedger table in the inner loop.

Query                                 Cost     Rows     Bytes -----------------------------------   ----   ------   ------- 1. SELECT STATEMENT on                1642  1068929  50239663 2.  HASH JOIN on                      1642  1068929  50239663 3.   TABLE ACCESS FULL on COA            2       55      1320 3.    TABLE ACCESS FULL on         GENERALLEDGER                 1128  1068929  24585367

To convert the SQL statement shown previously to PL/SQL I would need PL/SQL code something similar to that shown here.

DECLARE    CURSOR cCOA IS SELECT * FROM coa;    TYPE tGL IS REF CURSOR RETURN generalledger%ROWTYPE;    cGLs tGL;    rGL generalledger%ROWTYPE; BEGIN    FOR rCOA IN cCOA LOOP       OPEN cGLs FOR SELECT * FROM generalledger WHERE          coa# = rCOA.coa#;       LOOP          FETCH cGLs INTO rGL;          EXIT WHEN cGLs%NOTFOUND;          DBMS_OUTPUT.PUT_LINE(             rCOA.coa#||' '||             TO_CHAR(rGL.dte)||' '||             TO_CHAR(rGL.dr)||' '||             TO_CHAR(rGL.cr));       END LOOP;       CLOSE cGLs;    END LOOP; EXCEPTION WHEN OTHERS THEN CLOSE cGLs; END; /

In general, PL/SQL should only replace SQL when coding simply cannot be achieved in SQL, it is too complex and convoluted for SQL, or centralization on the database server is required.

6.10.3 Java or PL/SQL?

Java can be used to construct stored procedures in much the same way that PL/SQL can. When to use Java? That is a question with a very simple answer. Use Java when not accessing the database and writing code which is computationally heavy. What does this mean? When answering a question such as this I always prefer to go back into the roots of a programming language or a database. In other words, what were PL/SQL and Java originally built for? What is their purpose? Let's start by looking at PL/SQL.

PL/SQL is effectively a primitive programming language and is purely an extension of SQL. SQL was originally built purely for the purpose of accessing data in a relational database. Therefore, it follows that PL/SQL is of the same ilk as SQL. PL/SQL was originally devised to create stored procedures in Oracle Database. Stored procedures were devised for relational databases in general, not just Oracle Database, to allow for coding of self-contained blocks of transaction-based SQL code. Those blocks of code are executed on the database server thus minimizing on network traffic. PL/SQL is much richer than stored procedure languages used in other relational databases. PL/SQL can be used to code complexity. This takes us to Java.

Why use Java? Java is an object-oriented programming language built for coding of highly complex front-end and back-end applications. If you know anything about objects at all you will understand that objects in programming are superb at handling complexity. It is in the very nature of objects to handle complexity by breaking everything down into its most simplistic parts. Java can be used to handle highly complex coding sequences and can be used to create Oracle Database stored procedures, much in the same way that PL/SQL can. Java is much more powerful than PL/SQL in doing lots of computations. Java is better than PL/SQL at anything that does not involve accessing the database, especially complex code. Coding requirements when accessing a database are trivial in relation to the complex routines and functions required by applications-level coding.

There is one small but fairly common problem with using Java. Java is object oriented. Oracle Database is relational. Object and relational methodologies do not mix well. Many Java applications, due to their object nature, break things into very small, easily manageable pieces. Object-oriented design is all about easy management of complexity. In relational terms management of complexity by severe break-down and object "black boxing" is an incredible level of Normalization. Too much Normalization leads to too much granularity and usually very slow performance. What commonly happens with Java applications is one or all of a number of things. These are some of the possibilities.

Pre-loading of large data sets.
Separation of parts of SQL statements into separate commands sent to the database. For instance, a SELECT statement could be submitted and then filtering would be performed in the Java application itself. This leads to lots of full table scans and a plethora of other performance problems.
Sometimes object design is imposed onto the database to such an extent as to continually connect to and disconnect from the database for every SQL code execution. This is very heavy on database resource consumption.

So what's the answer to the nagging question of using Java or PL/SQL? The answer is to use both, if your skills set permits it. Java can be used to handle any kind of complex code not accessing the database. PL/SQL should be used to access the database. If you cannot or do not wish to use a mix and prefer Java then be aware that a relational database is not able to manage the complete and total "black box" breakdown into easily definable and understandable individual objects. Do not attempt to impose an object structure on a relational or furthermore even an object-relational database: they are not designed for that level of granularity.

< Day Day Up >