Oracle Performance Tuning I

Oracle Performance Tuning : Tuning Pack 

Oracle has introduced Performance tuning & Diagnostic Pack (10g onwards), let me brief you about Tuning Pack first

Oracle Tuning Pack :

The Oracle Tuning Pack provides database administrators with expert performance management for the Oracle environment, including SQL tuning and storage optimizations. The Oracle Diagnostic Pack is a prerequisite product to the Oracle Tuning Pack. Therefore, to use the Tuning Pack, you must also have a Diagnostic Pack. The Tuning Pack includes the following features:

1.SQL Access Advisor :

The SQL Access Advisor accepts input from all possible sources such as the cursor cache, Automatic Workload Repository (AWR), any user-defined workload (by providing AWR range of snaps)

& It comprehensively analyzes the entire workload and provides recommendations to create new partitions or indexes if required, drop any unused indexes, create new materialized views and

Materialized view logs.

2. SQL Tuning Advisor (STA)

SQL Tuning advisor works with Automatic Tuning Optimizer to analyze historic SQL workload with the help of AWR, it recommends gathering statistics, creating SQL profiles, restructuring SQL statements to improve execution plan & reduce the CPU, Disk IO for problem causing statements

3. SQL Tuning Sets (STS)

STS are created to customize the Advisory output, STS receives SQL statements or AWR snaps or SQL_ID as a input to analyze the performance against workload statistics and execution plans.STS are created & executed to generate the recommendations from Advisory & it is internally managed if advisory accessed through OEM

4. Reorganize objects

Reorganization is used for:

1) Rebuilding indexes and tables that are fragmented,

2) Relocating objects to another tablespace, and

3) Recreating objects with optimal storage attributes.