I will explain Performance Tuning Features and Tools in this article.
You should read the following first article of this tutorial series before this.
Several tools are available in Oracle that enable you to gather information about Monitoring of database and performance, diagnose problems and tuning applications.
To do Performance Tuning, we are using lots of tools as follows.
- Automatic Workload Repository (AWR)
- Automatic Database Diagnostic Monitor (ADDM)
- Server-generated alerts
- Enterprise Manager Database Express
- Enterprise Manager Cloud Control
- V$ performance views
- Alert log
- Trace files
Gathering and monitoring features of Oracle are automatic and are managed by Oracle background processes.
Diagnostic tools gather the following types of data
- Cumulative statistics:
– Wait events with time information
– Time model
- Metrics: Statistic rates
- Sampled statistics: Active Session History, part of the Oracle Diagnostics Pack
– Session Statistics
– SQL Statistics
– User Statistics
– Service Statistics
You can manage these features by using Oracle Enterprise Manager and views.
I recommend you using Oracle Enterprise Manager Cloud Control due to its ease of use, and numerous automated monitoring and
Enterprise Manager Cloud Control,Enterprise Manager Database Express and V$ views will always be used for Real time monitoring during the Performance tuning.
Automatic Workload Repository (AWR) and Statspack tools are used to summarize and present the statistics in a much more usable format.
Automatic Database Diagnostics Monitor (ADDM) is used to provide performance findings and recommendations that have been found by
Automatic Database Diagnostics Monitor (ADDM) for tasks performed in the database for the selected time period
Alert log of Oracle database consist of database events and informational messages (Database startup, shutdown time). You can see all Errors ,warnings and internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occurred via displaying Alertlog.
Dynamic performance views ( V$ dolar views ) are very important for DBA, because they provide access to information about changing states and conditions in the instance.
V$ performance views include the raw information used by AWR and Statspack and provide the following detail information
- Wait events
- SQL execution
- Backup status
- Memory usage and allocation
- System and session parameters
- SQL execution
- Statistics and metrics
Statspack is , used especially in the Standart edition, a set of procedures and scripts supplied with all editions of the Oracle Database
DBAs should use one or more these tools at the same time during Performance tuning and perform the following actions.
• Display the top wait events to determine the highest wait
• Display the time model to diagnose performance issues
• Use dynamic performance views to view statistics and wait events
• Use Enterprise Manager to monitor database and server.
• Identify alert logs and define the important Errors and warnings.
• Create trace files for problem sessions.
I will continue to explain Performance Tuning tutorial in the next articles.
Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.
916 views last month, 4 views today