Hi,This article will discuss some PL/SQL performance tuning tips as well as make reference to a variety of good coding practices. There are several things you can do to get your PL/SQL code to run optimally. These are my top tuning tips right out of both my SQL Performance Tuning and Advanced PL/SQL courses (this course could easily be named “New Features for Developers”).
Tip 1: Tune Functions
PL/SQL Functions Tips
Any PL/SQL function you code should be tuned for the best execution time, especially if this function will be called from a SELECT clause on a multi-row query.
Single-row queries can benefit from the Deterministic (introduced in Oracle8i) and Result-Cache (introduced in Oracle11) clauses that allow for the prior result set to be returned instead of re-executing the function.
Deterministic saves the prior-execution’s result and if the IN_Variable data is the same as the prior execution, Oracle simply returns this result from the prior execution.
Result Cache is Deterministic on steroids! This feature saves ALL the IN_Variable data and matching results, so after all or most of the possibilities of the input data are achieved, this function hardly executes at all anymore.
Both of these features depend on the same result being produced from the same input data. If there are any outside influences that could possibly produce a different result, don’t use either of these features.
***Tip*** Result Cache is also available for SQL results…using hints.
Review the nature of the function and investigate if database views might be more efficient.
IF the function is being referenced in a WHERE clause, investigate whether a function-based index might be helpful for execution of the SQL.
Use good coding practices with functions. Functions should only have a single RETURN clause in the regular (Begin) section of the code. Purity features can be used to minimize or negate side effects.
Tip 2: Tune the SQL
Tune the SQL
Tune each SQL call. SQL being called from PL/SQL causes some additional overhead called a context switch. The SQL and result come from the SQL engine so PL/SQL has to request each row, or sets of row, from SQL. I have classes on SQL performance tuning, check my website for details.
Use the PL/SQL profiler to help identify long-running SQL. There are two different PL/SQL profilers now, the PL/SQL Profiler, introduced in Oracle8i and the Hierarchical Profiler, introduced in Oracle10g. Toad has a nice interface to the PL/SQL profiler.Both have their advantages and both will show how long each line of code took to execute. Use predefined or database variables when accessing data. This technique promotes cursor sharing and you can also use either deterministic or result cache if applicable.Opening and closing cursors frequently causes additional CPU overhead on the server. Use packages to leave cursors open, a cool programming technique covered in a future article.
Bind variables promote cursor reuse and minimize hard parsing. Use them unless they will have an adverse affect on the execution plan.
Tip 3: Use Variables and Collections
Variable Passing and Collections
Larger data being passed via OUT and IN OUT parameters can have an adverse affect on performance. Use the NOCOPY compiler option to pass pointers instead of the data. An even better practice is to simply have all the routines in the same package and not pass anything!
***Note*** NOCOPY may have become default behavior for all PL/SQL starting in Oracle11. Still doesn’t hurt to list it as a compile option.
Collections have come a long way in Oracle. The BULK COLLECT and FORALL features are very fast and are the solution to row-at-a-time processing. Oracle doesn’t like to hit rows in collections that contain null values. The FIRST/NEXT/LAST methods skip null rows.
Tip 4: Looping Logic Tips
Loop Processing Tips
Keep the code inside the loop to a minimum.
Use good coding techniques and only process the collection or result set once.
Avoid EXIT from within WHILE or FOR loops. This is just poor coding and could lead to unintended behavior.
Use a single EXIT command in simple LOOPS. This is a good coding practice. Makes future maintenance easier.
Don’t declare the FOR loop subscript. If it is declared and it is not a PLS_INTEGER, Oracle will convert it to a PLS_INTEGER before each iteration of the loop. For example: FOR i IN … , don’t declare the ‘i’. This conversion doesn’t take a lot of time but is an unnecessary conversion…
Tip 5: Better Coding Tips
General Coding Tips
Use the existing Oracle technology instead of writing your own version of it. For example, I’ve seen code that does string processing to walk forwards and backwards looking for commas, etc…where I have made clever use of mixing SUBSTR and INSR together to produce the same results…at a fraction of the coding and test time. I would be willing to bet SUBSTR/INSTR is also far more performant.
Use IF-THEN-ELSIF or CASE for multiple checks of the same variable. Since both of these technologies exit the routine when a hit is made, put the checks in the order of likely hits.
Oracle does math on PLS_INTEGER. If you are incrementing a variable and it is NOT a PLS_INTEGER, Oracle will convert it to a PLS_INTEGER for the math part of the request. Again, this conversion isn’t a big deal but is unnecessary with good coding practices.
Tip 6: Use Newer Technologies
Boolean Logic
Use newer coding technologies that now exist in PL/SQL. This might make your code more consistent with coding practices from other languages. When your code needs to be maintained, it is easier if the code being modified is all using the same practices. Boolean logic is common to ‘C’ programming languages, for example.
Tip 7: Packages and Triggers
Packages and Trigger Tips
Packages have a lot of advantages:
- Modular programming/sharing of code
- Grouping of related items
- Passing of parameters
- Sharing of cursors
- Pinning into the library cache
DML triggers can be a useful tool for applications. Care should be taken so that triggers do not touch tables that have triggers that touch tables that have triggers. This will greatly slow down any insert performance.
***Note*** I was invited to a site to help improve row insert performance using their online application. I found these related triggers seven levels deep! Yes, the row being inserted had a trigger that called another trigger…seven times! This was one side where I couldn’t help; this app and the reason they were performing these tasks needed to be redesigned.
***Tip*** I use row-level triggers to manipulate/clean up data, to store who did it/time stamp into each row, to store any PL/SQL exception information into a different table, and for useful but lightweight security tracking.
Consolidate triggers of the same type to insure execution order and to keep any redundancy to a minimum. Oracle11 introduced compound triggers.
Tip8: The DUAL Table
Programming with DUAL
I really don’t see any reason to be using the DUAL table anymore. This table has one column (named DUMMY) and one row (contains an ‘X’) and is owned by SYS.
We used it years ago (Oracle v4) to provide IF/THEN/ELSE logic to the early forms and reports.
I have used it when working with SQL*Plus reports to output a row of dashes, headings, etc.
From a database point of view, it is common practice (not necessarily a good practice) to select sequences into variables from dual. The query will be successful but it is also another hard parse, etc…it is another SQL query. This process takes a lot of time and involves a context switch.
Oracle11 introduced the ability to use sequences as variables.
I can’t think of any PL/SQL reason to use the DUAL table.
Summary
This article is full of both good PL/SQL coding practices and a variety of PL/SQL tips to help your PL/SQL perform better.
I hope you find these tips useful in your day to day use of the Oracle RDBMS.
Start the discussion at forums.toadworld.com