Performance Tuning Features and Tools in Oracle | Oracle Database Performance Tuning Tutorial -2

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

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *