Hi,

I will explain Performance Tuning Features and Tools in this article.

 

You should read the following first article of this tutorial series before this.

Introduction to Performance Management and Tuning in Oracle | Oracle Database Performance Tuning Tutorial -1

 

 

 

Performance Tuning Features and Tools in Oracle

 

Several tools are available in Oracle that enable you to gather information about Monitoring of database and performance, diagnose problems and tuning applications.

 

Oracle Performance Monitoring and Tuning Tools 

To do Performance Tuning, we are using lots of tools as follows.

• Automatic Workload Repository (AWR)
• Automatic Database Diagnostic Monitor (ADDM)
• Server-generated alerts
• Advisors
• Enterprise Manager Database Express
• Enterprise Manager Cloud Control
• V$ performance views
• Alert log
• Trace files
• Statspack

 

 

 

 

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
diagnostic tools.

 

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

• Locks
• Wait events
• Sessions
• 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
software.

 

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.

 

Next post link about Performance Tuning Tutorial is as follows.

What is the Oracle Optimizer and How Cost-Based Optimizer Works ? | Oracle Database Performance Tuning Tutorial -3

 

 

 

 

Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.

 

SQL Tuning Tips and Tricks Tutorial in Oracle -3