What is the Oracle AWR ( Automatic Workload Repository ) Report

Hi,

I will explain What is the Oracle AWR ( Automatic Workload Repository ) report in this article.

AWR3

 

 

Oracle AWR

If you don’t know how to get or generate Oracle AWR report, please read following article.

How to Generate AWR ( Automatic Workload Repository ) Report via SQL*Plus, Enterprise Manager and Toad in Oracle

 

 

Automatic Workload Repository ( AWR ) Report

Oracle Database periodically triggers the MMON (Manageability Monitor Processes) background process and collect the Database statistics and the Snapshot of the Workload information, and saves this data to the several tables in the sys schema within the SYSAUX tablespace.

 

AWR5

 

 

This database statistics data taken as snapshots is collected every 60 minutes as default and is saved in WRM $ _SNAPSHOT, WRM $ _DATABASE_INSTANCE, WRM $ _WR_CONTROL, WRH $ _SQL_PLAN, WRH $ _SEG_STAT, WRM $ SNAP_ERROR and WRM $ _BASELINE vbvb. Tables data are kept by default for 7 days.

 

AWR Views are like DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_WR_CONTROL, DBA_HIST_SQL_PLAN, DBA_HIST_SEG_STAT, DBA_HIST_SNAP_ERROR and DBA_HIST_BASELINE are created from these tables and these views are used for AWR report creation.

 

 

 

 

This is called the AWR (Automatic Workload Repository) report. AWR reports were first introduced to us with Oracle 10g. We use this detailed Database Statistics report, especially at solving problem or during problem identify.

But what does this AWR Report collect from the database to give detailed information about the system? The data collected are as follows.

 

  • Server load profile ( CPU,Memory Utilization etc. )
  • Database Load Profile ( Transaction count, DB Time, Hard Parse, Physical read and etc. )
  • TOP SQL by CPU,Elapsed Time,Buffer Gets,IO and etc.
  • Wait events in database
  • Table,index,trigger etc. usage stats
  • Session stats like V$SESSTAT and V$SYSSTAT
  • Session Historical stats like V$ACTIVE_SESSION_HISTORY.

 

These Performance data and AWR Report sections are like following.

 

WORKLOAD REPOSITORY report for

 

 

 

 

 

 

Top 10 Foreground Events by Total Wait Time

Host CPU

 

 

 

 

SQL Statistics

SQL ordered by Elapsed Time

 

 

SQL ordered by CPU Time

 

 

 

If you don’t know how to analyze Oracle AWR Report, please read following article.

How to Read or Analyze an AWR ( Automatic Workload Repository ) Report in Oracle -3

 

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database

 

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 *