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

 

 

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

 

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.

Mehmet Salih Deveci

I am Founder of 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 mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

3 thoughts on “What is the Oracle AWR ( Automatic Workload Repository ) Report

  • June 14, 2019 at 4:34 pm
    Permalink

    Hi ,
    Could you pls explain different wait events . When wait events occur and how to fix these events ?

    Thanks

    Reply

Leave a Reply

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