Oracle PL/SQL Performance Tips

1)          Avoid Usage of  “Like”

The LIKE-clause can ignore indexes, causing queries to run forever while doing full table scans.

Generally, search arguments in the WHERE clause such as "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'" prevents Oracle from using an index to perform the search (however, not always).

If you use LIKE in your WHERE clause, try to specify one or more leading characters if at all possible. For example, use LIKE 'm%' and not LIKE '%m'. If you specify a leading character, Oracle has a better chance of being able to use an index to perform the query - this will increase performance and reduce the load on the database server.

2)          Use “Exists” and “Not Exists”  instead of  “ in” and “not in “

When coding a SQL statement with tables in master-detail relationships, it's common to have to decide whether to write the query using the WHERE EXISTS (. . .) clause or the WHERE value IN (. . .) clause. You may resist using WHERE EXISTS because it has the awkward syntax of returning a value, which you always ignore.

However, there's a difference when using rule-based optimization. You can determine the performance of a rule-based query by understanding which table is the driving table and how many rows each part returns.

When you write a query using the IN clause, you're telling the rule-based optimizer that you want the innerquery to drive the outer query (think: IN = inside to outside). For example, to query the 14-row EMP table for the direct reports to the employee KING, you could write the following:

SELECT ename

  FROM emp e

WHERE mgr IN (SELECT empno

                              FROM emp

                            WHERE ename = 'KING'

                           );

You can write the same query using EXISTS bymoving the outer query column to a subquery condition, likethis:

  SELECT ename

   FROM emp e

 WHERE EXISTS ( SELECT ‘Y’

                               FROM emp

                             WHERE e.mgr = empno

                                  AND ename = 'KING'

                             );

When you write EXISTS in a where clause, you’re telling the optimizer that you want the outer query to be run first, using each value to fetch a value from the inner query(think: EXISTS = outside to inside).

To determine which clause offers better performance in rule-based optimization, consider how many rows the inner query will return in comparison to the outer query. In many cases, EXISTS is better because it requires you to specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the subquery are very small. You usually want to run the query that returns the smaller set of results first.

3)          Use “bulk collect“ if data fetched is huge

 When filling a PLSQL collection you can either do this one row at a time (the slow way) using a cursor FOR LOOP or you can fill it one hit by using BULK COLLECT. By BULCOLLECT SQL statement will be executed only once by the Oracle database instead of multiple times.

The BULK COLLECT clause can be used with the SELECT INTO and FETCH statements and the RETURNING clause of DELETE, INSERT and UPDATE statements.

Example:
DECLARE

    TYPE employee_tab_typ IS TABLE OF  employee%ROWTYPE;

    TYPE emp_id_tab_typ      IS TABLE OF employee.emp_id%TYPE;

   

    CURSOR all_employees

    IS

       SELECT *

        FROM employee;

    TYPE all_emp_tab_typ IS TABLE OF all_employee%ROWTYPE;

    employee_tab employee_tab_typ;

    emp_id_tab      emp_id_tab_typ;

    all_emps            all_emp_tab_typ;

BEGIN

    SELECT *

     FROM employee

     BULK COLLECT INTO employee_tab;

    OPEN all_employees;

    FETCH all_employees INTO all_emps;

UPDATE employees

         SET sal=sal*1.1

      WHERE role='DEVELOPER'

    RETURNING emp_id INTO emp_id_tab;

   

    CLOSE all_employees;

END;

DML – use Dynamic SQL

Tuning Dynamic SQL with EXECUTE IMMEDIATE Statement and Cursor Variables

Some programs (a general-purpose report writer for example) must build and process a variety of SQL statements, where the exact text of the statement is unknown until run time. Such statements probably change from execution to execution. They are called dynamic SQL statements.

Formerly, to execute dynamic SQL statements, you had to use the supplied package DBMS_SQL. Now, within PL/SQL, you can execute any kind of dynamic SQL statement using an interface called native dynamic SQL. The main PL/SQL features involved are the EXECUTE IMMEDIATE statement and cursor variables (also known as REF CURSORs).

Native dynamic SQL code is more compact and much faster than calling the DBMS_SQL package.

4)          No Copy

Tuning PL/SQL Subprogram Calls with NOCOPY Hint

By default, OUT and IN OUT parameters are passed by value. The values of any IN OUT parameters are copied before the subprogram is executed. During subprogram execution, temporary variables hold the output parameter values. If the subprogram exits normally, these values are copied to the actual parameters. If the subprogram exits with an unhandled exception, the original parameters are unchanged.

When the parameters represent large data structures such as collections, records, and instances of object types, this copying slows down execution and uses up memory. In particular, this overhead applies to each call to an object method: temporary copies are made of all the attributes, so that any changes made by the method are only applied if the method exits normally.

To avoid this overhead, you can specify the NOCOPY hint, which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference. If the subprogram exits normally, the action is the same as normal. If the subprogram exits early with an exception, the values of OUT and IN OUT parameters (or object attributes) might still change. To use this technique, ensure that the subprogram handles all exceptions.

5)          Usage of  hints

Use hints to force the optimizer to use a specific chosen execution plan.

Hints are suggestions that you give to the optimizer for optimizing a SQL statement

Use hints to specify

•                                 The optimization approach for a SQL statement

•                                 The goal of the cost-based approach for a SQL statement

•                                 The access path for a table accessed by the statement

•                                 The join order for a join statement

•                                 A join operation in a join statement

6)          Create indexes  where ever required

An index is an optional structure, associated with a table that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O.

•             It avoids full table scan

•             Index is an easier way to access the data.

•             Create indexes on fields that are queried on most frequently.

•             Indexing speeds up select.

If the database has lot of DML operations do not have more than 2 (max 3) for a table

•                     While updating an indexed column, we delete the original value of the index and create a new one as the column which is being indexed is being changed. This will have two I/O as one for the data block and another for the index block.

•                     Solution to this is to drop the index before updating and recreate it after updating is complete.

•                     To rebuild the index,

      -  Alter index index_name rebuild

Create Indexes on fields which have a high Selectivity factor (around 0.7)

Selectivity factor  =  No. of distinct values for a field / Total rows in the table

7)          Tkprof, explain  plan, cost of sqls

Use the EXPLAIN PLAN statement to determine the execution plan Oracle Database follows to execute a specified SQL statement. This statement inserts a row describing each step of the execution plan into a specified table PLAN_TABLE. You can also issue the EXPLAIN PLAN statement as part of the SQL trace facility.

TKPROF is a program that you invoke at the operating system command prompt in order to reformat the trace file into a format that is much easier to comprehend. Each SQL statement is displayed in the report, along with counts of how many times it was parsed, executed, and fetched. CPU time, elapsed time, logical reads, physical reads, and rows processed are also reported, along with information about recursion level and misses in the library cache. TKPROF can also optionally include the execution plan for each SQL statement in the report, along with counts of how many rows were processed at each step of the execution plan.

EXPLAIN PLAN and TKPROF allow you to proactively tune an application while it is in development. It is relatively easy to enable SQL trace, run an application in a test environment, run TKPROF on the trace file, and review the output to determine if application or schema changes are called for. EXPLAIN PLAN is handy for evaluating individual SQL statements.

8)          Follow order to map joins

Suppose if there are three tables in a query first complete all the joins between two tables and then add join conditions with the third table.

9)           When custom logic is repetitive, use stored procedure/function.

10)      Avoiding Memory Overhead in PL/SQL Code:

Declare VARCHAR2 Variables of 4000 or More Characters

You might need to allocate large VARCHAR2 variables when you are not sure how big an expression result will be. You can conserve memory by declaring VARCHAR2 variables with large sizes, such as 32000, rather than estimating just a little on the high side, such as by specifying 256 or 1000. PL/SQL has an optimization that makes it easy to avoid overflow problems and still conserve memory. Specify a size of more than 4000 characters for the VARCHAR2 variable; PL/SQL waits until you assign the variable, then only allocates as much storage as needed.

11)      Make loops as efficient as possible

 Because PL/SQL applications are often built around loops, it is important to optimize both the loop itself and the code inside the loop:

•             To issue a series of DML statements, replace loop constructs with FORALL statements.

•             To loop through a result set and store the values, use the BULK COLLECT clause on the query to bring the query results into memory in one operation

12)      Use lookups instead of hardcoding the values in the code.

Programming Standards for PLSQL objects

PL/SQL Objects

Objects such as Libraries, Procedures, Functions and Packages will be owned by APPS

Indentation – Suggestion of 2 characters for all new programs.  Follow through with the written style on old programs.  Usage of TABS are discouraged because many editors can be set to interpret TAB widths differently and thus indentation and vertical alignment can vary depending on the editor used to the view code. The main point being, make the logic readable with efficient use of horizontal space.

IF Statements - IF, THEN, ELSE and ENDIF must align vertically. Furthermore, nested IF statements should be indented and it’s corresponding THEN, ELSE and ENDIF segments must align vertically.

Looping - LOOP and END LOOP statements must align vertically. All statements contained within the loop must be indented. The first line of the loop must be a comment statement describing the purpose of the loop.

Branching - GO-TO statements are not allowed, with the exception of error handling. Instead, the programmer should use externally called sub-procedures with passing arguments, as required.

Line Continuation - Program statements require more than one line for various reasons. Generally a line of code must not exceed 80 characters, including preceding special characters and spaces. The rules’ governing the structure of multi-line statements follows.

Procedure calls argument list - one argument per line with the second and subsequent lines indented the same number of spaces

DDL, DML statement - one column per line with the second and subsequent lines indented the same number of spaces

Other multi-line statements - second and subsequent lines indented the same number of spaces

·         All reserved words, key Oracle words, table aliases to be capitalized and all others (column names, column aliases etc.) should be in lower case.

Examples: -

1. Statements: SELECT, UPDATE, INSERT, ALTER, CREATE, COMMIT, ROLLBACK

2. Clauses: INTO, FROM, WHERE, GROUP BY, ORDER BY, SET

3. Functions: TO_CHAR, TO_DATE, DECODE, NULL, NVL, RTRIM

4. Operators: AND, OR, BETWEEN, EXISTS, IN, NOT

5. Table Aliases: RCT

·         Comma (,) separating parameters, columns or tables should appear at the beginning of lines and not at the end of the lines.

·         Source code indentation is essential.

·         Use first character from each word to build table aliases in your queries.

Example: ra_customer_addresses_all RCAA, mtl_system_items MSI

·         Expressions/Nested function calls should be properly indented to come inside the calling function calls.

·         In the case of DECODE/REPLACE, each combination of ‘search’ and ‘replace’ string should appear on a new line.

Example: -

TO_DATE('10102002','DDMMYYYY')

DECODE(APS.class

      ,'INV', 'Invoice'

      ,'CM','Credit Memo')

DECODE( SIGN(SYSDATE-APS.due_date)

      ,-1, DECODE(APS.amount_due_remaining

                  ,0,'Past Due -Paid'

                  ,'Past Due-Open')

     , DECODE(APS.amount_due_remaining

                  ,0,'Paid'

                  ,'Open'))

·         Extensive comments need to be incorporated into the code.

·         Avoid the use of GO TO.

·         Instead of incorporating the programming logic in Triggers, Reports or Alerts, it is better to write the logic in a package procedure and call it from the Triggers, Reports or Alerts. The advantage is the package is compiled, parsed and stored in the database and therefore it is required primarily to fetch and execute.

·         Define packages and procedures. Anonymous PL/SQL procedure definitions are not acceptable except for data fixes.

·         Do not use “SELECT *” in your queries. Always write the required column names after the SELECT statement, like SELECT tab1.column1, tab1.column2, tab1.column3. This technique results in fewer disks IO and less network traffic and hence better performance.

·         Do not include explicit commits within the package.  In case the package requires processing of records that can create rollback segment problem, please put commits at logical transaction points with SAVEPOINT and ROLLBACK functions. This would be required in Databases versioned 8i and backwards. For databases versioned 9i and onwards, the UNDO Management feature, if implemented, does not require such provisions.