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:
image

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

Image 2:
image

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:
image

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:
image

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:
image

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:
image 

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:
image

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

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Julian Dontcheff's Database Blog

The good DBA is one who learns from his mistakes, the best DBA is one who learns from other DBA's mistakes

Martins Blog

Trying to explain complex things in simple terms

Oracle Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

PeteWhoDidNotTweet

Stuff that interests me, if not you!

The Oracle Instructor

Explain, Exemplify, Empower

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Oracle Spin - Flimatech Blog

Sharing Our Database Experience

Oracle Scratchpad

Just another Oracle weblog

Follow

Get every new post delivered to your Inbox.

Join 1,556 other followers

%d bloggers like this: