Tuning with ASH Analytics in #em12c

Performance tuning of the Oracle Database has always been a great thing to learn and a valuable skill for any database administer.  In a previous post, I talked about how to use the Active Workload Repository (AWR) and Active Session History (ASH) to performance turn a database.  In this post, I want to take a look at the Active Session History (ASH) Analytics that can be found in Oracle Enterprise Manager 12c.

Active Session History (ASH) Analytics is used to help the database administrator determine the cause of spikes within database activity.  The ASH Analytics page provides stacked area charts to help visualize the ASH data from various dimensions.  Some of these dimensions are:

• Wait Class
• Module
• Actions
• SQL ID
• Instance
• User Session
• Consumer Group

In order to use the ASH Analytics page, you first have to access the database home page.  Once on the database home page, then you can access ASH Analytics from the Performance menu (Performance –> ASH Analytics)(Image 1).

Note: If you are accessing the ASH Analytics page for the first time, it will ask you to install the PL/SQL package for it.

Image 1:

Once you are on the ASH Analytics page, you will notice three distinct sections of graphs and charts (Image 2). 

Image 2:

The top graph (Image 3) provides a high-level perspective of top activity during a selected period of time.  The grey shaded box, by default, shows a 5 minutes window of activity.  If you need to see a larger timeframe drag the box in either direction using the handles provided.

Image 3:

When you select a timeframe to look at the Activity graph below will change to show the activity for that time period (Image 4).   The Activity graph looks a lot like the Top Activity page graph. By highlighting or clicking on the graph or legend (right-hand of graph), you can see what wait category is taking the longest. 

Example: In the case of the graph in image 4, the wait is a configuration (brown) wait due to other (pink) wait.   This is due to log writer (LWGR) issues on my small server.

Image 4:

Notice in image 4, that there are a few option in the upper left corner from the graph.  You see the word “Filter None”.  The graph can be filtered based on dimensions stated earlier. If you change the view of the graph to the Load Map and then click on one of the waits, the filter will dimension from Wait Class to the wait you clicked on in the load map. Image 5 shows that the load map is being filtered by “Wait Event: log buffer space”.

Image 5:

So far, I have selected the timeframe I wanted to see and identified what was taking the most database time during (LGWR and Log Buffer Space) that timeframe.  The last section on the ASH Analytics page is actually two different listing that can be filtered by the dimensions already outlined (Image 6 & Image 7).

In Image 6, you see a list of SQL_IDs that are taking up database time.  You can correlate these SQL_ID activities back to the wait categories in the Activity graph.   At this point, if you wanted to create SQL Tuning Set or Tune the SQL directly; buttons are provided to accomplish these tasks.

Example: I don’t need to tune the SQL because it is a SQL statement I’ve been working with and I already know that the LGWR and Log Buffer Space are the issues.

Image 6:
 

In Image 7, the output was filtered by Wait Class as the dimension.  As you can tell the wait taking the longest is the Configuration wait.  Again this goes right a long with the earlier findings shown on the page. 

Image 7:

At this point, I have identified that the Configuration wait (LGWR) event is causing the problem with the performance on the system.  In order to fix this I would need to look at my redo log sizes and locations to make sure that I’m not trying to perform to many I/O operations over the same I/O interface.

Summary

By adding the ASH Analytics to your DBA Toolkit, you should be able to quickly identify performance problems.  Couple ASH Analytics with ASH Report and AWR Report; you have a solid performance tuning basis to  begin diagnosing problems with the database.

Enjoy!

twitter: @dbasolved

blog: http://dbasolved.com

Please follow and like: