It would take a book to completely delineate all SQL and PL/SQL (let alone Oracle-related 3GLs) good practices -- indeed several have been written. However, in this section I will try to list a few of the "heavy-hitters."
It is a good practice in PL/SQL to use array processing (for example, bulk collect and forall). Bulk processing minimizes context switching during PL/SQL processing, improving performance sometimes by an order of magnitude or more.
It is also considered a good practice to place all code within stored procedures in pinned packages. By placing code in procedures you can develop modular units. Placing procedures into packages allows grouping of related procedures and functions. When a single piece of a package is utilized, the entire package is brought into memory, which minimizes disk access. This also allows for pinning of an entire application package into memory. Pinning of an application package (another good PL/SQL practice) prevents reloading and parsing of code, thus minimizing performance-robbing code recursion.
Another good practice for PL/SQL (and SQL) is to use proper variable types for variable declaration (don't use a VARCHAR2 when you need a NUMBER or vice-versa). Improper variables (using character to compare to number or the inverse) results in index non-use and on-the-fly conversion. One way to ensure proper variable typing is to use %TYPE and %ROWTYPE to prevent improper variable types from being declared.
Another good coding practice is to always verify loop logic using DBMS_PROFILER or by using a tool like Quest Code Tester from Quest Software. DBMS_PROFILER is an Oracle-provided package that allows you to instrument your code to generate tracing for the number of times each line is executed and the amount of time required. You should verify that loops are executed the minimal number of times.
You also should verify proper IF-THEN-ELSE structures. What I mean by this is that you should place the most utilized option first (for example, the exit test). This good practice applies to CASE structures as well.
One of the more important good practices in PL/SQL (and Java and C, and C++ and all other Oracle-related 3GLs) is to tune all SQL first. The most elegant, best-thought-out and constructed programs in the universe will perform badly if the SQL contained within them is bad. Quest's SQL Optimizer, Performance Analyzer, TOAD and SQL Navigator all help here.
It is a good practice to utilize anonymous PL/SQL to ensure processing environments are similar when testing PL/SQL-SQL codes. If you replace bind-variables with literals to test in a standard SQL environment, you will not get the same execution plan as with use of anonymous PL/SQL and bind variables, thus your tuning will probably not produce good results. This is where in-situ testing using tools like Quest Code Tester really helps guarantee you get the results you need.
Another good programming practice is to make proper use of temporary tables and PL/SQL index by tables. Improper use of "normal" tables for temporary storage is a performance robber; use global temporary tables instead. Improper use of global temporary tables when the memory space is available to utilize PL/SQL tables is a big performance hit. You can also use views to create in-memory tables for intermediate result sets.
It is a good practice (and easy to implement) to utilize NOCOPY on IN OUT and OUT variables. If the NOCOPY keyword is not included in the variable declaration for the header of a PL/SQL procedure that uses IN OUT or OUT variables, all variables are passed by value using copyout routines (pass by value instead of by reference). By using NOCOPY, the values are passed by reference. This can make a significant difference in performance.
Finally, require (at a minimum) explain plans to be generated by the developers for all SQL. All SQL should then be peer-reviewed for performance. In a large project it will be impossible for the DBA staff to review all code for the best performance, therefore it is a necessity for developers to take the lead in SQL code performance tuning. Once the SQL has been tuned for index usage, proper logic and proper technique, the DBAs can apply more advanced tuning for stubborn problem code.
Return to Mike Ault's Oracle "good practices."
About the author
Mike Ault is an Oracle database specialist at Quest Software and a recognized Oracle expert with over 16 years' experience as an Oracle DBA and consultant in a variety of industries and companies. A prolific author, Mike has published over 20 Oracle-related books including Oracle Administration and Management, Oracle DBA OCP ExamCram and Oracle10g Grid and RAC. He is a regular contributor to trade publications including Oracle magazine, and frequently presents at major Oracle conferences such as IOUG.
