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

Oracle Basic Architecture

Oracle Architecture (Basic):
Below is the typical architecture of Oracle 11g. When you fire a SQL from any of the client tools like (SQL Developer/Toad/ or even SQLPLUS) below processes gets triggered at the back end.

Background Processes: It start with the database instance and perform maintenance tasks such as performing instance recovery, cleaning up processes, and writing redo buffers to disk, and so on.

Server Processes: perform work based on a client request.

Terminologies & Functionality:
1. SGA
System global area. A group of shared memory structures that contain data and control information for one Oracle database instance.

2. PGA
Program global area. A memory buffer that contains data and control information for a server process.

3. Shared pool
Portion of the SGA that contains shared memory constructs such as shared SQL areas.

4. Large pool
Optional area in the SGA that provides large memory allocations for backup and restore operations, I/O server processes, and session memory for the shared server and Oracle XA.

5. Database buffer cache
The portion of the system global area (SGA) that holds copies of data blocks. All client processes concurrently connected to the instance share access to the buffer cache.

6. Redo log buffer
Memory structure in the SGA that stores redo entries—a log of changes made to the database. The database writes the redo entries stored in the redo log buffers to an online redo log file, which is used if instance recovery is necessary.

7. Online redo log
The set of two or more online redo log files that record all changes made to Oracle Database data files and control file. When a change is made to the database, Oracle Database generates a redo record in the redo buffer. log writer (LGWR) writes the contents of the redo buffer to the online redo log.
Redo Log has set of files that protect altered database data in memory that has not been written to the data files. The redo log can consist of two parts: the online redo log and the archived redo log.
8. Shared SQL area:
An area in the shared pool that contains the parse tree and execution plan for a SQL statement. Only one shared SQL area exists for a unique statement.

9. Back Ground Processes:
A process that consolidates functions that would otherwise be handled by multiple Oracle programs running for each client process. The background processes asynchronously perform I/O and monitor other Oracle processes.

1. Mandatory Background Processes
These processes run by default in a database instance started with a minimally configured initialization parameter file

i. Process Monitor Process (PMON) :

PMON is responsible for cleaning up the database buffer cache and freeing resources that the client process was using. PMON also registers information about the instance and dispatcher processes with the Oracle Net listener

ii. System Monitor Process (SMON): is in charge of a variety of system-level cleanup duties like Performing instance recovery, Recovering terminated transactions, cleaning up unused temporary segments, Coalescing contiguous free extents.

iii. Database Writer Process (DBWn): It writes the contents of database buffers to data files. Typically database buffer is the memory area that stores copies of data blocks read from data files.

iv. Log Writer Process (LGWR) :

The log writer process (LGWR) manages the redo log buffer. LGWR writes one contiguous portion of the buffer to the online redo log.

v. Checkpoint Process (CKPT) :

The checkpoint process (CKPT) updates the control file and data file headers with checkpoint information and signals DBWn to write blocks to disk

vi. Manageability Monitor Processes (MMON and MMNL):

The manageability monitor process (MMON) performs many tasks related to the Automatic Workload Repository (AWR). For example, MMON writes when a metric violates its threshold value, taking snapshots, and capturing statistics value for recently modified SQL objects.

The manageability monitor lite process (MMNL) writes statistics from the Active Session History (ASH) buffer in the SGA to disk. MMNL writes to disk when the ASH buffer is full.

vii. Recoverer Process (RECO)

In a distributed database, the Recoverer process (RECO) automatically resolves failures in distributed transactions. The RECO process of a node automatically connects to other databases involved in an in-doubt distributed transaction.

2. Optional Background Processes:

i. Archiver Processes (ARCn):
The Archiver processes (ARCn) copy online redo log files to offline storage after a redo log switch occurs. These processes can also collect transaction redo data and transmit it to standby database destinations. ARCn processes exist only when the database is in ARCHIVELOG mode and automatic archiving is enabled.
ii. Job Queue Processes (CJQ0 and Jnnn):
Oracle Database uses job queue processes to run user jobs, often in batch mode.

iii. Flashback Data Archiver Process (FBDA):
It archives historical rows of tracked tables into Flashback Data Archives. When a transaction containing DML on a tracked table commits, this process stores the pre-image of the rows into the Flashback Data Archive.

iv. Space Management Coordinator Process (SMCO) :
The SMCO process coordinates the execution of various space management related tasks, such as proactive space allocation and space reclamation. SMCO dynamically spawns slave processes (Wnnn) to implement the task.

3. Slave Processes
Slave processes are background processes that perform work on behalf of other processes.

i. I/O Slave Processes:
I/O slave processes (Innn) simulate asynchronous I/O for systems and devices that do not support it. In asynchronous I/O, there is no timing requirement for transmission, enabling other processes to start before the transmission has finished.

ii. Parallel Query Slaves:
In parallel execution or parallel processing, multiple processes work together simultaneously to run a single SQL statement. By dividing the work among multiple processes, Oracle Database can run the statement more quickly. For example, four processes handle four different quarters in a year instead of one process handling all four quarters by itself.