Performance Tuning with AWR & ASH in #em12c

Oracle monitoring and performance tuning has grown over the years.  With every release of the Oracle database the monitoring capabilities that have been provided have evolved.  In Oracle11g, monitoring with statpack has given way to Automatic Workload Repository (AWR) and Active Session History (ASH) for near real time monitoring of items within the Oracle database.  Adding AWR and ASH to your performance tool kit can yield huge savings of time to tuning endeavors.

Note: AWR and ASH were also in Oracle10g, but not as robust as it currently is in Oracle11g.

Note: If you are interested in how to run AWR and ASH from the command line check out this post by a good friend of mine: here

Instead of focusing on the command line to run AWR and ASH, lets take a look at how these tools are used through Oracle Enterprise Manager 12c. 

Note: The AWR and ASH tools are covered under the Diagnostic and Tuning Management Packs for the database and OEM.  Before using these tools, make sure you have access to these management packs.

Automatic Workload Repository (AWR)

The Automatic Workload Repository (AWR) is used to automate database statistic gathering by collecting, processing, and maintaining performance statistics for database problem identification and self-tuning.  Part of the AWR is snapshots.  AWR Snapshots are gathered by default every hour and queried from memory.  Snapshots can be used to identify performance problems over time.

In order to use AWR, there are two initialization parameters that are relevant:

• STATISTICS_LEVEL – set to TYPICAL (default) or AL, enables statistics gathering for AWR. setting it to BASIC will disable statistics gathering.
• CONTROL_MANAGEMENT_PACK_ACCESS – set to DIAGNOSTIC+TUNING(default) or DIAGNOSTIC to enable diagnostic monitoring.  Setting to NONE will disable many database features including ADDM (not covered in this post).

Automatic Workload Repository (AWR) Report

With the AWR enabled for the database, an AWR report can be ran from within OEM.  While on a database home page within OEM, AWR report can be accessed from the Performance menu (Performance –> AWR –> AWR Report) (Image 1). 

Image 1:

Before you can run an AWR Report, OEM will ask you to log in to the database.  Like with anything in OEM, you can used Named Credentials or create a new one (Image 2). 

Image 2:

After logging in you will be asked if you want to use “By Baseline” or “By Snapshot”.  Most of the time, “By Snapshot” will be used (Image 3).  This step corresponds to the command line version of AWR Report when it asks what snapshots you would like to use.  Then click the Generate Report button to build the report.

Tip: Use the magnifine glass to lookup the snapshots.  This will open a separate window.

Image 3:

Once the report is generated; the report can be read in the OEM interface or saved to file for later reading or emailing to others.

Reading AWR Report

One thing to notice, the difference between the AWR Report from command line and OEM interface is that at the command line, text based report can be chosen.  When running the AWR Report from OEM, the only option is the report is automatically saved in HTML format.  Although there is a difference in report formats; the report is read the same way no matter what the format is.

The header of the report (Image 4) provides you with general information about the environment where there report was gathered from.  It also provides information about the snapshots used to generate the report and the time differences between the snapshots.

Image 4:

The next section in the report is the Report Summary.  In the Report Summary, there are a few items in interest.  The first of which is the “Instance Efficiency Percentage” section (Image 5).  In this section, you can get a sense of how well the instance is managing it memory operations.  Ideally, the target should be 100%.

Note: The only time I’m concern about this area is if any of the values are below 90%. Often this is an indication that the SGA may be to small.

Image 5:

In the same section, I also look at the “Top 5 Time Foreground Event” section (Image 6).  This section of the report provides you with a list of the current wait events that are taking the most time waiting and what wait class they belong to.  At this time, I can see what I should be tuning the environment to prevent.  In the image provided, I can see that I have a wait dealing with high water mark contention (enq: HW – contention). 

From looking at these waits, it appears that I have an object that is possibly having a problem with extents be allocated.  What I need to do next is see if I can find the object that is causing the issue.

Image 6:

Since I know that I have a high water mark contention issue, this is telling me that I need to look at what objects are being written to in the database.  In the “Segments Statistics” section of the report, I want to look at the “Segments by Physical Writes” sub-section (Image 7).  From looking at this section, I can see that the problem is on a LOB object names SYS_LOB0000253792C00002$$.

Image 7:

From looking at the AWR Report, I can quickly see the object that is causing the problem with the wait.  I can make an attempt to correct the problem and remove the wait by affecting the LOB object in an appropriate way.  In this case, that may be adding extents, looking at moving the LOB to a less accessed tablespace, etc. 

Active Session History (ASH)

Although I have identified the object that is causing the wait, I want to find the session that is causing this wait.  This is where I can use the Active Session History (ASH) Report. Before diving into the ASH Report, lets take a look at what Active Session History (ASH) actually is.

Active Session History provides detailed history of session activity by sampling database activity by sessions every second.  These samplings are taken from memory and then stored in persistent storage (tables).  The sampling that ASH does is done at the session level rather than the instance level.  The statistics that are captured is only for active sessions and is directly related to the work be performed by the sessions rather than the entire database.

Active Session History (ASH) Reports are used to analyzed transient performance problems within the database during specific time. 

Let’s take a look at how the ASH Report is ran and what it provides.

Active Session History (ASH) Report

In Oracle Enterprise Manager 12c (OEM), the ASH Report is accessed through the “Top Activity” menu (Performance –> Top Activity) (Image 8) for that database. 

Image 8:

Once on the “Top Activity” page, below the graph that is displayed there is a button labeled “Run ASH Report” (Image 9). 

Image 9:

Clicking on the “Run ASH Report” button will bring up the page to provide specific information about the ASH Report that you would like to run (Image 10).  Since the ASH samples so much data, per second, you can select a beginning date, ending date and associated times that you would like to look at in the report. 

Note: Most times, when I use generate an ASH report, I try to keep to the same timeframe as the AWR report.  Provides some consistency to findings.

Image 10:

Once I have selected the timeframe I want to use, the report can be generated.  Just like the AWR Report, the ASH Report can be viewed inside of OEM or saved to a file for later reference or sharing with others.

Active Session History (ASH) Report

Let’s read the report now.  As you recall, I found an issue with a high-water mark contention wait.  What I will be looking for in the ASH Report is similar information and possibly who is behind the wait.

Just like the AWR Report, the ASH Report has a header section where I can find all the information related to the database, database instance and timing of the report (Image 11).  This section is general in nature, but it also provides you similar information on SGA usage just like the AWR Report does.

Image 11:

The next think I like to look at in the ASH Report is the “Top User Events” (Image 12).  If I have selected the same timeframe as the AWR Report, I expect to see similar wait events as I found earlier.  As you can see, the high-water contention wait is at the top of the list again.

Image 12:

Now that I have confirmed that the high-water contention wait is the top wait for the sessions during this timeframe, I want to confirm that it is the same object I found in the AWR Report.  Information on the object causing the wait can be found in the “Top Event P1/P2/P3 Values” (Image 13) section.

Image 13:

In order to confirm that it is the same object, I need to search DBA_EXTENTS with the values from P2 (data file) and P3 (block number).  Depending on how fast the system is, I can quickly identify the object being accessed that is causing the wait.

In any tuning that is being done, I want to find the SQL statement that the session is using.  This information can also be found in the ASH Report along with the top sessions and if there are any sessions being blocked.

Summary

Overall, these two reports (AWR and ASH) provide a wide range information for anyone to use in performance diagnosing and tuning.   These reports can be ran from the command line or from OEM depending on your comfort levels with the tools; however, I recommend that any DBA looking to keep a database in good working order and top performance learn how to use and read these reports.  II t will save a lot of time in resolving issues.

Enjoy!

twitter: @dbasolved

blog: http://dbasolved.com

Please follow and like: