Basic Concept of Performance Tuning in Oracle

Scope of Performance Tuning

There is four main area for Performance Tuning.

1. SQL Tuning          – Responsibility of the Developer
2. Database Tuning     – Responsibility of the Database Administrator
3. System Tuning       – Responsibility of the System Administrator
4. Network Tuning      – Responsibility of the Network / LAN / WAN Administrator.

SQL Tuning

1. Find the problem to a single SQL

You may be lucky and know already the exact SQL causing the problem. If so, move straight on to the second step. Otherwise, click on the link above for help on finding the problem SQL.

2. Analyze the SQL to determine the nature of the problem

Most performance problems are quite common and easy to fix. This section will describe some of these and how to spot them, and then go on to describe a more general analysis method.

3. Fix the problem.

Almost every performance problem has a solution; it’s just that some are more complex than others. In order of increasing complexity and expense, such fixes include:

• Analyze the underlying table to give Oracle’s Cost Based Optimizer the information it needs to resolve the SQL efficiently.
• Add one or more hints to the SQL to encourage or discourage certain execution plans.
• Minor changes to the SQL to encourage or discourage certain execution plans.
• Restructure a poorly designed SQL that cannot be resolved efficiently.
• Alter the underlying infrastructure. eg. Add or change (or even remove!) indexes; introduce clusters, partitions or index-organised tables; denormalize tables; use materialized views. Note that these actions venture outside the scope of this document, and should only be performed with the prior permission of (and preferably assistance from) the DBA and/or System Architect.
• Refer the problem to the database administrator. Possible solutions here would include tuning the Oracle instance, restructuring or moving tablespaces, or archiving old data.
• Refer the problem to the System Adminstrator. Possible solutions may include reconfiguration of existing hardware, or acquisition of new hardware.

Database Tuning

For optimum performance an Oracle database should be regularly tuned. Only tune a database after it has been up and running for a little while.

• Tuning the cache hit ratio
• Tuning the library cache
• Tuning the log buffer
• Tuning buffer cache hit ratio
• Tuning sorts
• Tuning rollback segments
• Identifying missing indexes
• Identifying index fragmentation
• Identifying free list contention
• Identify significant reparsing of SQL
• Reducing database fragmentation
• Rebuilding indexes
• Reduce thrashing or poor system performance (or how to un-tune oracle?!)

System Tuning(Operating System)

Tune your operating system according to your operating system documentation. For Windows platforms, the default settings are usually sufficient. However, the Solaris and Linux platforms usually need to be tuned appropriately. The following sections describe issues related to operating system performance:

• Basic OS Tuning Concepts
• Solaris Tuning Parameters
• Linux Tuning Parameters
• HP-UX Tuning Parameters
• Windows Tuning Parameters
• Other Operating System Tuning Information

Details

Network Tuning

Network tuning is the performance optimization and tuning of SQL*Net based on an arbitrary UNP which could be TCP/IP, SPX/IP or DECnet. SQL*Net performance can be maximized by synchronization with tunable parameters of the UNP, for example, buffer size.SQL*Net transaction performance can be divided into components of connect time and query time, where
Total SQL*Net (Net8) Transaction Time = Connect Time + Query Time 

Connect time can be maximized by calibration of tunable parameters of SQL*Net and the UNP when designing and implementing networks.

SQL*Net Performance
For this discussion, SQL*Net performance and tuning analysis is based on two categories:

• SQL*Net performance
• SQL*Net tuning

Details1 

Details2

DB_WRITER_PROCESSES

Setting the DB_WRITER_PROCESSES parameter (Background process DBWR) to a value greater than one is supported starting with the Oracle 8.0 release.

Multiple DBWR processes are mainly used to simulate asynchronous I/O when the operating system does not support it.  Since Windows NT and Windows 2000 use asynchronous I/O by default, using multiple DBWR processes may not necessarily improve performance.  Increasing this parameter is also likely to have minimal effect on single-CPU systems.  Increasing this parameter could, in fact, reduce performance on systems where the CPU’s are already over burdened.  In cases where the main performance bottleneck is that a single DBWR process cannot keep up with the work load, then increasing the value for DB_WRITER_PROCESSES may improve performance.

When increasing DB_WRITER_PROCESSES it may also be necessary to increase the DB_BLOCK_LRU_LATCHES parameter, as each DBWR process requires an LRU latch.

How to change db_writer_processes parameter

SQL> show parameter db_writer_processes
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
db_writer_processes                  integer     1

SQL> alter system set db_writer_processes=2 scope=spfile sid='*';

System altered.

Now restart the database to take the change effect.

 
SQL> show parameter db_writer_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
db_writer_processes                  integer     2

“The number of DBWR should be two times the number of CPUs (vary 1 or 2 depending on idle state of CPUs during production/test run)”

DB_WRITER_PROCESSES = 2 * number of CPU

Default value of DB_WRITER_PROCESSES is 1 or CPU_COUNT / 8, whichever is greater. If the number of processor groups is less than 36 but greater than the number of DB writer processes, then the number of DB writer processes is adjusted to be a multiple of the number of processor groups. If the number of DB writer processes is greater than or equal to the number of processor groups, then there is no adjustment.

Range of value in Oracle 10g- 1 to 20 but in Oracle 11g – 1 to 36.

Optimize Oracle UNDO Parameters

Data Concurrency and Read Consistency

ROLLBACK or UNDO is the backbone of the READ CONSISTENCY mechanism provided by Oracle. Multi-User Data Concurrency and Read Consistency mechanism make Oracle stand tall in Relational Database Management Systems (RDBMS) world.

Starting in Oracle9i, rollback segments are re-named undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying.

Best of all, automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing “snapshot too old” errors on long running queries.

This is done by setting the UNDO_RETENTION parameter.  The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time.

Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy.  All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO.

UNDO_RETENTION is a parameter in the init.ora initialization parameters file that specifies the time period in seconds for which a system retains undo data for committed transactions. The flashback query can go upto the point of time specified as a value in the UNDO_RETENTION parameter.

The properties of the UNDO_RETENTION parameter are mentioned below:

• Parameter type – Integer
• Default value – 900
• Range of values – 0 to 2³² – 1
• Syntax – ALTER SYSTEM SET UNDO_RETENTION = < seconds >;

However it is worth to tune the following important parameters

1. The size of the UNDO tablespace
2. The UNDO_RETENTION parameter

Calculate UNDO_RETENTION for given UNDO Tabespace

You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION parameter:

Optimal Undo Retention =
           Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)

Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time.

Actual Undo Size

SELECT SUM(a.bytes) "UNDO_SIZE"
FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
WHERE c.contents = 'UNDO'
   AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#;

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat;

DB Block Size

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';

Optimal Undo Retention Calculation

Formula:
Optimal Undo Retention = 
           Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)

Using Inline Views, you can do all calculation in one query

SQL Code:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
    SUBSTR(e.value,1,25)    "UNDO RETENTION [Sec]",
    ROUND((d.undo_size / (to_number(f.value) *
    g.undo_block_per_sec)))"OPTIMAL UNDO RETENTION [Sec]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))undo_block_per_sec
       FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'

Calculate Needed UNDO Size for given Database Activity

If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:

Formula:
Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC

Using Inline Views, you can do all calculation in one query

SQL Code:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
      v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'

Automatic Undo Retention Tuning

Oracle 10g automatically tunes undo retention to reduce the chances of “snapshot too old” errors during long-running queries. The UNDO_RETENTION parameter is used to set a low retention time threshold which the system will attempt to achieve. In the event of any undo space constraints the system will prioritize DML operations over undo retention meaning the low threshold may not be achieved. If the undo retention threshold must be guaranteed, even at the expense of DML operations, the RETENTION GUARANTEE clause can be set against the undo tablespace during or after creation:

Reset the undo low threshold.

ALTER SYSTEM SET UNDO_RETENTION = 2400;

Guarantee the minimum threshold is maintained.

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
 
SELECT tablespace_name, retention FROM dba_tablespaces;
 
TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       GUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY
 
5 rows selected.

Switch back to the default mode.

ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
 
TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       NOGUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY
 
5 rows selected.

The Undo Advisor PL/SQL Interface

Oracle Database provides an Undo Advisor that provides advice on and helps automate the establishment of your undo environment. You activate the Undo Advisor by creating an undo advisor task through the advisor framework. The following example creates an undo advisor task to evaluate the undo tablespace. The name of the advisor is ‘Undo Advisor‘. The analysis is based on Automatic Workload Repository snapshots, which you must specify by setting parameters START_SNAPSHOT and END_SNAPSHOT. In the following example, the START_SNAPSHOT is “1” and END_SNAPSHOT is “2”.

DECLARE
   tid    NUMBER;
   tname  VARCHAR2(30);
   oid    NUMBER;
   BEGIN
   DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
   DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 1);
   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 2);
   DBMS_ADVISOR.execute_task(tname);
end;

Source : http://www.oracle.com/technology/oramag/code/tips2004/032904.html

Automatic Undo Retention Tuning

Oracle 10g automatically tunes undo retention to reduce the chances of “snapshot too old” errors during long-running queries. The UNDO_RETENTION parameter is used to set a low retention time threshold which the system will attempt to achieve. In the event of any undo space constraints the system will prioritize DML operations over undo retention meaning the low threshold may not be achieved. If the undo retention threshold must be guaranteed, even at the expense of DML operations, the RETENTION GUARANTEE clause can be set against the undo tablespace during or after creation:

Reset the undo low threshold.

ALTER SYSTEM SET UNDO_RETENTION = 2400;

Guarantee the minimum threshold is maintained.

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
 
SELECT tablespace_name, retention FROM dba_tablespaces;
 
TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       GUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY
 
5 rows selected.

Switch back to the default mode.

ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
 
TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       NOGUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY
 
5 rows selected.

SQL Optimazition

Cost Based Optimizer (CBO)

This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources. Cost-based optimization uses statistics stored with database objects to help evaluate which set of indexes and joins to follow. As long as you keep the statistics current, the optimizer would usually choose the fastest path possible. There are also some comments called optimizer hints. In Oracle 10g by default Cost Base Optimizer is used.

 
select value from v$parameter where name='optimizer_mode'

By default optimizer_mode set to CHOOSE, which means Cost Base Optimization.
 

Plan Table

The explain plan process stores data in the PLAN_TABLE.

 

SQL> CONN sys/password AS SYSDBA
Connected
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
SQL> GRANT ALL ON sys.plan_table TO public;

AUTOTRACE Parameter to view the SQL Execution Plane

SET TIMING ON
SET AUTOTRACE ON

SELECT e.ename, d.dname
FROM   scott.emp e, scott.dept d
WHERE  e.deptno = d.deptno
  AND  e.ename  = 'KING';

Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
ENAME      DNAME         
---------- --------------
KING       ACCOUNTING    

1 rows selected

Plan hash value: 351108634

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    22 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    22 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | EMP     |     1 |     9 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - filter("E"."ENAME"='KING')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

   Statistics
-----------------------------------------------------------
               0  recursive calls
               0  db block gets
               9  consistent gets
               0  physical reads
               0  redo size
             800  bytes sent via SQL*Net to client
             414  bytes received via SQL*Net from client
               4  SQL*Net roundtrips to/from client
               1  sorts (memory)
               0  sorts (disk)
180ms elapsed

 

Here Cost (%CPU) is (4+4+3+1+0) 12

 

View Execution Plan or EXPLAIN PLAN

EXPLAIN PLAN FOR
  SELECT e.ename, d.dname
  FROM   scott.emp e, scott.dept d
  WHERE  e.deptno = d.deptno
  AND  e.ename  = 'KING';

EXPLAIN PLAN succeeded.

Also get the output from utlxpls.sql or utlxplp.sql

Source : @$ORACLE_HOME/rdbms/admin/utlxpls.sql
Source : @$ORACLE_HOME/rdbms/admin/utlxplp.sql

We use the DBMS_XPLAN.DISPLAY function to display the execution plan

SET LINESIZE 130
SET PAGESIZE 0
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);
 

Rule Based Optimizer (RBO)

This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer recommended by Oracle and will be duplicated in future releases.

 

To change the optimization mode

Alter session set OPTIMIZER_MODE = RULE

SET TIMING ON
SET AUTOTRACE ON

SELECT e.ename, d.dname
FROM   scott.emp e, scott.dept d
WHERE  e.deptno = d.deptno
  AND  e.ename  = 'KING';

Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
ENAME      DNAME          
---------- --------------
KING       ACCOUNTING    

1 rows selected

Plan hash value: 351108634

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  NESTED LOOPS                |         |
|*  2 |   TABLE ACCESS FULL          | EMP     |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |
------------------------------------------------

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

   2 - filter("E"."ENAME"='KING')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

Note
-----
   - rule based optimizer used (consider using cbo)

   Statistics
-----------------------------------------------------------
               0  recursive calls
               0  db block gets
               9  consistent gets
               0  physical reads
               0  redo size
             801  bytes sent via SQL*Net to client
             402  bytes received via SQL*Net from client
               4  SQL*Net roundtrips to/from client
               1  sorts (memory)
               0  sorts (disk)
290ms elapsed