Performance Tuning Methodology

Methodology

1. Design the application correctly

2. Tune the application SQL code

3. Tune memory

4. Tune I/O

5. Tune contention and other issues

Reactive performance tuning done in production environments after application is deployed and scales larger in production.

Troubleshooting Performance Issues

A problem related to the database could be many things:

• High CPU consumption

• High IO consumption

• Poorly performing SQL statements

• Database design issues

• Hardware problems

• Application problems

• Software bug

• Etc, many more potential problems

Typical Tools for Troubleshooting Performance

Automatic Database Diagnostics Monitor (ADDM) This tool provides focuses analysis of activities the database is spending most time on to determine the root causes of problems. Automatic Workload Repository (AWR) At regular intervals, the database takes a snapshot of its workload information and stores it for analysis of past activity. Gathers database statistics every hour by default.  Active Session History (ASH) Similar to AWR, this tool stores information specific to sessions, such as past SQL executed and performance metric history. SQL Tuning Advisor This advisor automates the SQL tuning process by comprehensively exploring all the possible ways of tuning SQL statements. This tool also provides action plans to remediate issues. SQL Access Advisor This advisor recommends design and parameter changes to emphasize more throughput and better performance.