Methodology
Design the application correctly
Tune the application SQL code
Tune memory
Tune I/O
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.
Comments