Oralce 11gR2 Performance Tuning

Tools for tuning the db:

Oracle Database 11g Enterprise Edition
Oracle Enterprise Manager
Oracle Diagnostics Pack

- AWR, Automatic workload repository

- ADDM, automatic database diagnostic monitor

- ASH, Active session History

Oracle Database Tuning Pack

--SQL Tuning Advisor
This feature enables you to submit one or more SQL statements as input and receive output in the form of specific advice or recommendations for how to tune statements, along with a rationale for each recommendation and its expected benefit. A recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statements, or creation of SQL profiles.
– SQL Access Advisor
This feature enables you to optimize data access paths of SQL queries by recommending the proper set of materialized views and view logs, indexes, and partitions for a given SQL workload.

Oracle Real Application testing

- Database Replay

- SQL Performance analyzer

Performance improvement is an iterative process,removing the first bottleneck may not lead to performance improvement immediately becz another bottleneck leads greater performance impact, so accurately diagnosing the performance proplem is the fist step.

performance proplems results lack of throughput(the amount of work that can be completed in a specified time), unacceptable user or job response time

Gathering Database Statistics Using the Automatic Workload Repository

By default, the database gathers statistics every hour and creates an AWR snapshot, which is a set of data for a specific time that is used for performance comparisons

Initialization parameters are relevant for AWR:

STATISTICS_LEVEL, CONTROL_MANAGEMENT_PACK_ACCESS

statistics_level - TYPICAL or ALL -- for enabling AWR reports

control_management_pack_access - DIAGNOSTIC+TUNING - for enablin ADDM

Setting the DBIO_EXPECTED Parameter
ADDM analysis of I/O performance partially depends on a single argument,DBIO_EXPECTED, that describes the expected performance of the I/O subsystem. The value of DBIO_EXPECTED is the average time it takes to read a single database block, in microseconds. Oracle Database uses the default value of 10 milliseconds..

Set the value one time for all subsequent ADDM executions.For example, if the measured value is 8000 microseconds, then execute the following PL/SQL code as the SYS user:
EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM', 'DBIO_EXPECTED', 8000);

The database statistics collected and processed by AWR include:

■ Time Model Statistics■ Wait Event Statistics■ Session and System Statistics■ Active Session History Statistics■ High-Load SQL Statistics

Common Performance Problems Found in Oracle Databases

CPU Bottlenecks

Undersized Memory Strucutures

I/O Capacity issues

Suboptimal use of Oracle Database by the application

Concurrency issues

Database configuration issues
Short-lived performance problems

Degradation of database performance over time

Inefficient or high-load SQL statements

Object contention

Unexpected performance regression after tuning SQL statements