Oracle Perfornamce Tuning approach.

Oracle Perfornamce Tuning approach.



Its a very vast topic, i am trying to cover very high level of it.


1. In order to tune your Oracle database, First you need to know what to tune. The first step is to define the scope of tuning.

2. After obtaining a full set of operating system, database, and application statistics, examine the data for any evidence of performance problems. This will help you in understanding of problem area and you will be able to set the scope of tuning.


3. What is affected by the slowdown? For example, is the whole instance slow? Is it a particular application, program, specific operation, or a single user?


4. My approach is to first Examine the Host System. (UNIX : here)

Use: iostat (I/o examine), sar ,prstat, vmstat ,netstat ,top , and examine OS error log (e.g /var/adm/messages in (Solaris) )

make sure that you have enough CPU available for oracle processes.

Example of cpu monitor

Basic global program and thread-level summary

tprof -x sleep 10 (For AIX )


sar -u 5 10 Interactively run the sar command and review CPU utlization every 10 seconds for 5 interations

ps - Display status of current processes. This command is useful for determining if runaway processes are excessively utilizing the CPU or memory

with ps -ef
grep oracle , you can get the oracle process id, if the Oracle process taking too much CPU then target Oracle CPU stats.


For Oracle Related Processes:
Oracle CPU Statistics :Oracle CPU statistics are available in several V$ views:


V$SYSSTAT shows Oracle CPU usage for all sessions. The CPU used by this session statistic shows the aggregate CPU used by all sessions. The parse time cpu statistic shows the total CPU time used for parsing.

V$SESSTAT shows Oracle CPU usage for each session. Use this view to determine which particular session is using the most CPU.

V$RSRC_CONSUMER_GROUP shows CPU utilization statistics for each consumer group when the Oracle Database Resource Manager is running



Detecting I/O problems:

Check the Oracle wait event data in V$SYSTEM_EVENT to see whether the top wait events are I/O related.



5) Database tuning , Starting 11g : Automatic Performance Tuning Features are available in the database.

The Oracle automatic performance tuning features include:

Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes.

Automatic Database Diagnostic Monitor (ADDM) analyzes the information collected by the AWR for possible performance problems with the Oracle database.

SQL Tuning Advisor allows a quick and efficient technique for optimizing SQL statements without modifying any statements.

SQLAccess Advisor provides advice on materialized views, indexes, and materialized view logs.

End to End Application tracing identifies excessive workloads on the system by specific user, service, or application component.

Server-generated alerts automatically provide notifications when impending problems are detected.

Other advisors are used to optimize mean time to recovery (MTTR), shrinking of segments, and undo tablespace settings. See Oracle Enterprise Manager Concepts for information on advisors that are available with Oracle Enterprise Manager.


6) Finally if you need to tune the sql.you can read few metalink notes..


NOTE:171647.1 - Tracing Oracle Applications using Event 10046

NOTE:21154.1 - EVENT: 10046 "enable SQL statement tracing (including binds/waits)"

NOTE:214106.1 - Using TKProf to compare actual and predicted row counts

NOTE:225598.1 - How to Obtain Tracing of Optimizer Computations (EVENT 10053)

NOTE:242374.1 - Tracing PX session with a 10046 event or sql_trace

NOTE:258418.1 - Getting 10046 Trace for Export and Import

NOTE:32951.1 - TKProf Interpretation (9i and below)

NOTE:398838.1 - FAQ: Query Tuning Frequently Asked Questions

NOTE:41634.1 - TKProf Basic Overview

NOTE:75713.1 - Important Customer information about using Numeric Events

NOTE:760786.1 - TKProf Interpretation (9i and above)

Recommended Method for Obtaining 10046 trace for Tuning [ID 376442.1]





Best is FAQ: Query Tuning Frequently Asked Questions [ID 398838.1]