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

I will continue to explain How to Analyze or Interpret an AWR report in Oracle in this article.

 

 

Read or Analyze an AWR ( Automatic Workload Repository ) Report

 

Read previous Articles before this.

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

 

 

 

Foreground Wait Class

You can detect which wait class are foreground in the database. Sessions are in CPU or they are performing I/O or They are waiting for Network, Commit, Concurrency wait event.

%1-3 values are not big problem for following wait events. But If Network, Commit,Concurrency and etc. wait class are more than %3-5, probably you will feel as slowness in the Database.

 

Foreground Wait Events

You can find out which wait events and how much they affect your database with this section. For example if db file sequential read is TOP, then Probably a user tries to perform a Physical I/O while doing sequential reads on the Buffer cache.

This wait event usually result from single block readings when the data on the table is accessed using index rather than full table scan.

and If Direct path read event occurs if the Oracle Instance access data asynchronously from the Datafiles and places this data in the PGA rather than the Buffer Cache.

 

Background Wait Events

You can detect which wait events are background in the database. Background Sessions are in CPU or they are performing I/O or They are waiting for Network, Commit, Concurrency wait event.

Background sessions or processes are generally executed from Oracle and they are internall processes or sessions.

 

 

 

 

Wait Event Histogram

You can see wait event details as histogram in this section like following. For example; enq: TX – row lock contention occurred so much in the database. It means any two sessions are blocking each other.

 

 

 

If library cache lock wait event occurred so much like above, then we need to solve this problem. You can read following article to solve library cache lock wait event.

 

https://ittutorial.org/library-cache-lock-cursor-and-pin-s-wait-on-x-oracle-concurrency-wait-event/

 

I will continue to explain How to Analyze an AWR Report in Oracle in the next article.

 

You can access the second post of AWR report with the following link.

How to Read or Analyze an AWR Report in Oracle -4 | TOP SQL

 

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database