Oracle AWR - From performance testing perspective

To analyze the AWR reports we need to pull reports:

1)      For both good and bad times i.e. we need reports when database was behaving bad and also when it was good.

2)      Pull AWR reports only for the timelines for which it is required. For example when we are analyzing a performance bottleneck just pull the report for only that time when that bottleneck appeared.

 Few terms which we need to be aware of:

AWR – Automatic workload repository.

At regular intervals, the Oracle Database makes a snapshot of all of its vital statistics and workload information and stores them in the AWR.

Oracle RAC – Oracle real applications clusters.

To understand this we first need to understand Oracle database structure.

Suppose we have only 2 layers one is application layer consisting of all the application which uses database services and second layer is Oracle data base layer as shown below.

Here different applications are directly interacting with the database layer for database operations.

 

                                                               

 

Now Oracle database layers consists of two items

1)      Files (This is the actual database)

2)      Processes (also called instances)

 

 

 

 

 

 

 

  

 

Now in a Non RAC environment one software instance is talking to the oracle database. As shown above.

But is a RAC environment two or more software instance talk to single database. As shown below.

 

 

 

 

 

  

3)      Hard parse and soft parse

Hard parse – Whenever a new SQL statement is passed to Oracle data base which is not there in shared pool, it perform number of steps to execute it. The steps are Load the SQL code into RAM -> Parse the statement for syntax -> Semantic parse -> Transform the query into simpler one -> Optimize the query -> Create a executable file -> retrieve rows.

Soft parse – In this case statement received by oracle is already present is shared pool. So there is no need to load the same into RAM.

Now to the point.

Sections to look in AWR report:

1)      LOAD Profile

 

 

 

 

 

 

2)      Top 5 Timed Foreground Events

 

 

 

 

 

 

3)      SQL Statistics: These statistics are easy to understand. Nomenclature of each section is pretty much self explanatory.

 

 

 

 

 

 

 

 

 

 

 

We can click on any SQL id and get details of the particular query which was executed.

4)      Operating system statistics details

 

 

 

5)      Time model statistics

These provide the details about where the processing time was spent.