PL/SQL Performance tuning and optimization

When to Tune PL/SQL Code

The information in this chapter is especially valuable if you are responsible for:

• Programs that do a lot of mathematical calculations. You will want to investigate the datatypes PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE.
  
• Functions that are called from PL/SQL queries, where the functions might be executed millions of times. You will want to look at all performance features to make the function as efficient as possible, and perhaps a function-based index to precompute the results for each row and save on query time.
  
• Programs that spend a lot of time processing INSERT, UPDATE, or DELETE statements, or looping through query results. You will want to investigate the FORALL statement for issuing DML, and the BULK COLLECT INTO and RETURNING BULK COLLECT INTO clauses for queries.
  
• Older code that does not take advantage of recent PL/SQL language features. (With the many performance improvements in Oracle Database 10g, any code from earlier releases is a candidate for tuning.)
  
• Any program that spends a lot of time doing PL/SQL processing, as opposed to issuing DDL statements like CREATE TABLE that are just passed directly to SQL. You will want to investigate native compilation. Because many built-in database features use PL/SQL, you can apply this tuning feature to an entire database to improve performance in many areas, not just your own code.
  
• Before starting any tuning effort, benchmark the current system and measure how long particular subprograms take. PL/SQL in Oracle Database 10g includes many automatic optimizations, so you might see performance improvements without doing any tuning.

Oracle PL/SQL Tuning tips


There are several proven techniques for improving the speed of PL/SQL execution and they are presented below in order of importance:

Use bulk collect: When reading-in lots of related rows, bulk collect can run 10x faster than a conventional loop. This tuning is possible because Oracle reduces context switches into a single operation.

Use forall: When loading a table from an array, the forall operator is 10x faster than a conventional SQL insert statement. Again, this is because of the reduction of context switching.

Use SQL analytics: Many advanced data operations can be done without using PL/SQL and are readily available as a SQL built-in function.

Use implicit cursors: Implicit cursors are faster in PL/SQL than explicitly defining cursors

Explain Plan

EXPLAIN PLAN parses a query and records the "plan" that Oracle devises to execute it. By examining this plan, you can find out if Oracle is picking the right indexes and joining your tables in the most efficient manner. There are a few different ways to utilize Explain Plan. We will focus on using it through SQL*Plus since most Oracle programmers have access to SQL*Plus.

Contents 1 Creating a Plan Table 2 Explain Plan Syntax 3 Formatting the output 4 Some Examples 5 Using SQL*Plus Autotrace 6 Also see

Creating a Plan Table

The first thing you will need to do is make sure you have a table called PLAN_TABLE available in your schema. The following script will create it for you if you don't have it already:

@?/rdbms/admin/utlxplan.sql

Explain Plan Syntax

EXPLAIN PLAN FOR your-sql-statement;

or

EXPLAIN PLAN SET STATEMENT_ID = statement_id FOR your-sql-statement;

Formatting the output

After running EXPLAIN PLAN, Oracle populates the PLAN_TABLE table with data that needs to be formatted to presented to the user in a more readable format. Several scripts exist for this, however, one of the easiest methods available is to cast dbms_xplan.display to a table and select from it (see examples below).

Some Examples

SQL> EXPLAIN PLAN FOR select * from dept where deptno = 40;
Explained.
SQL> set linesize 132
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 2852011669
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=40)

14 rows selected.

Using SQL*Plus Autotrace

SQL*Plus also offers an AUTOTRACE facility that will display the query plan and execution statistics as each query executes. Example:

SQL> SET AUTOTRACE ON
SQL> select * from dept where deptno = 40;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

Execution Plan
----------------------------------------------------------
Plan hash value: 2852011669


---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    20 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=40)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        443  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed