Next ADF Training
Join Demo Session
Toggle Bar
  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
  • Team Blogs
    Team Blogs Find your favorite team blogs here.
  • Archives
    Archives Contains a list of blog posts that were created previously.
  • Login
    Login Login form

Oracle PL/SQL CODE OPTIMIZATION

Posted by on in OA Framework
  • Font size: Larger Smaller
  • Hits: 11700
  • 0 Comments
  • Subscribe to this entry
  • Print
  • Report this post

1. Introduction

 

Programs containing a large number of codes of line and complex structure may consume more time for processing leading to a downfall of performance.

 

In PL/SQL and modplsql code, computing time plays a vital role. The execution time for such applications can be brought down considerably using simple techniques.

 

This document describes certain code optimization techniques and some implementation details.

 

 

2. Code optimization techniques

 

Some of the techniques for code optimization are given below:

 

1.            Maximum usage of in-built functions like INSTR, TO_CHAR, and ROUND can reduce the code length. Inbuilt functions are optimized in nature and hence provide faster results.

2.            Maximum usages of in-built packages, which can further cut down the code length and make the code easier to understand. These packages contain third-party libraries of PL/SQL code. These code libraries might perform specialized calculations or they might offer relatively generic extensions to the base PL/SQL language.

3.            Use cursor FOR loop. Whenever the user needs to read through every record fetched by a cursor, the cursor FOR loop will save lots of typing over the "manual" approach of explicitly opening, fetching from, and closing the cursor.

4.            Records tighten up the code and self-document relationships. So use records at the most. Whenever data is fetched from a cursor, fetch it into a record declared against that cursor with the %ROWTYPE.

5.            Create a function or a procedure to perform the task, happening redundantly in the code. It avoids redundancy and improves readability. If you perform the same task twice or more in a procedure, then call that function twice instead.

6.            Never hardcode the values. Make use of temporary variables to make the code feasible to changes. Assign the value to these temporary variables.

7.            During the declaration of a variable which has anything to do with a database element, use the %TYPE or %ROWTYPE declaration attributes to define the datatype of those structures

8.            Always keep the package specification in a separate file from the package body. If there is a change in package body but not in specification, then only body needs to be recompiled.

9.            Consolidate all exception handling programs into a single package, and predefine all application-specific exceptions in their appropriate packages.

10.        Never write implicit cursors (in other words, never use the SELECT INTO syntax). Instead, always declare explicit cursors. Although, it is easier to implement an Implicit cursor in the code but it costs more on performance level.

11.        Use ELSIF with mutually exclusive clauses in spite of continuous IF statements. During blank value condition check, always take into account the NULL condition.

         E.g.

       Condition Check for variable Name_in that doesn’t have any value. Correct format

       IF (Name_in = '' OR Name_in IS NULL) THEN

12.        Conditions having NOT parameter take more time for execution. So try using maximum of positive condition checks.

       E.g.

       Requires more execution time

       IF (Name_in IS NOT NULL) THEN

       Requires lesser execution time

       IF (Name_in IS NULL) THEN

13.        Never EXIT or RETURN from WHILE and FOR loops. The use of EXIT or RETURN statements inside a WHILE or FOR loop can cause an unstructured termination from the loop. The resulting code is hard to trace and debug.

14.        Never declare the FOR loop index. PL/SQL offers two kinds of FOR loops: numeric and cursor. Both have a general format as follows:

 

       FOR <loop index> IN <loop range>

       LOOP

       <loop body>

       END LOOP;

 

       Where,

       loop index is either an integer or a record;

 

       In either case, it is implicitly declared by the PL/SQL runtime engine and its scope is restricted to the body of the loop. The declaration of loop index variable may lead to confusion with the variables present outside the loop.

15.        Scan collections using FIRST, LAST, and NEXT in loops. A collection in PL/SQL is like a single-dimensional array. If you scan a collection with a FOR loop and the collection is sparse, the FOR loop tries to access an undefined row and raise a NO_DATA_FOUND exception. Instead, use the FIRST and NEXT methods to scan forward through a collection, and use LAST and PRIOR to scan backwards.

16.        Move static expressions outside of loops and SQL statements. In order to tune PL/SQL programs, first take a look at the loops. Any inefficiency inside a loop's body will be magnified by the multiple executions of that code.

17.        Anonymous blocks within IF statements conserve resources. The user can define any set of executable statements as a distinct block, with its own declaration, executable, and exception sections. This block can be directly used within IF statements.

18.        Declare and define variables and data structures at the location where it’s required in the code. The scenario can be certain operations and data structures aren't needed unless a certain condition is satisfied. Then declare and define the variables, data structures, functions and operations inside the conditional statement. The result is that CPU and memory overhead will reduce abruptly.

         E.g.

       In the following block, I declare a local variable First_name and initialize it with a function name_allocation() that usually takes 10 seconds to execute. But the variable is used only on the fulfillment of an IF condition.

 
     DECLARE
          First_name name%TYPE;
          First_name := name_allocation();
           BEGIN 
          IF last_name IS NULL OR last_name = ‘’THEN
           name := first_name;
 
         ELSE
              ………………
              ………………
            END IF;
         END;

       For such a scenario, it’s advisable to declare and initialize the variable within the condition.

         BEGIN
           IF last_name IS NULL OR last_name = ‘’ THEN  
             DECLARE
            First_name name%TYPE;
            First_name := name_allocation();
             BEGIN
           name := first_name;
             END;
           ELSE
             ………………
             ………………
           END IF;
         END;

19.        Strength Reductions help a lot in Performance tuning. Multiplication operation cause CPU overhead and takes larger time for operation. So replace these operations with addition operations, wherever possible.

20.        Replacement of frequently referenced array elements by scalar variables improves the performance.

21.        Use BETWEEN in spite of AND in IF condition.

         E.g.

       Requires more execution time

          SELECT * FROM emp WHERE sal >= 2000 AND sal <= 3000;

       Requires lesser execution time

          SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000;

22.        In order to calculate the total number of rows present in a table or fulfilling a condition, do not "SELECT COUNT(*)" from a table. Use “SELECT COUNT(<PKey col name>)”,this helps to increase the speed of processing.

 

3. Benefits Of Code Optimization

 

The program won't execute unnecessary code, improving performance and reducing memory requirements for the program.

Downsize the system configuration and reap huge savings.

Produce faster response times and better throughput.

 

4. Removing redundant computations:

 

If some of the variables in a huge expression take zero values, a number of computations can be reduced by removing computations on zeroes. The expressions can be modified as follows:

 

1. Replace the variable names (which take zero value) by the constant zero

2. Replace all sub-expression containing a multiplication with zero with the constant zero

3. Remove all additions (and subtractions) to zeros.

 

It should be noted that this technique works only for variables whose values are known to be zero at compile time.

 

Order of magnitude analysis:

 

If the order of magnitudes of each of the variables is known a-priori, this information can be used to further optimize expression. For example, consider the expression x+y, Suppose x is of the order of magnitude 1e10 and y of 1e-10. For all practical purposes the addition to y can be eliminated.

0
Trackback URL for this blog entry.
  • No comments found

Login