Guest Author: Kurt Engeleiter, Principal Product Manager, Oracle
To achieve the highest return from your Oracle Database, the database needs to perform at peak efficiency.
Oracle Enterprise Manager Diagnostics and Tuning Packs come with a wealth of tools to help you understand, tune and maximize your Oracle Database Performance. These tools improve DBA productivity and reduce management costs.
In tuning your database, the first step is to understand how your database is performing. The Automatic Workload Repository (AWR) captures database performance statistics at regular intervals. Oracle Enterprise Manager shows the AWR data in visual format.
After the AWR data is captured, the Automatic Database Diagnostics Monitor (ADDM) immediately analyzes those statistics and makes concrete, actionable recommendations on how to improve the database performance. For example, it might recommend you increase the database SGA memory by two gigabytes, or it might indicate certain high load SQL that requires tuning.
Once you’ve identified areas of improvement, there are many tools to help you increase performance. The SQL Tuning Advisor takes one or more SQL and does an intensive analysis on what might best improve the performance of those SQL. The SQL Tuning Advisor might find that the statistics on database objects are stale, leading to incorrect execution plans. It would then recommend updating those statistics. Or, after analysis, the SQL Tuning Advisor might derive a better execution plan and create a SQL Profile that will allow the optimizer to always choose that better execution plan in the future.
Another tool for SQL tuning is Real-time SQL Monitoring. Real-time SQL Monitoring gives you detailed execution statistics of all long running SQL. It automatically captures detailed data on the SQL, including performance data for every step of a SQL execution plan. Real-time SQL Monitoring makes it easy to identify what steps are consuming the most DB Time, allowing deep analysis and tuning.
All these tools are continually enhanced and updated with each Oracle Enterprise Manager and Oracle Database release.
To learn more about these features and recent enhancements, be sure to watch this webcast:
Extreme Database Optimization: Simple Steps to Maximize Performance