System Triage Part III - Finding the Top Resource Consumers

Now that we have identified that our database is the cause of our application's poor performance, let's continue our system triage analysis by trying to identify the top resource consumers. During our investigation, we'll also find out what resource the top resources are consuming and the SQL they are executing.

For sake of clarity, we will make the assumption that the database ecosystem (database, operating system and hardware platform) has been performing fairly well historically. In addition, this blog also builds upon my previous two blogs. In those blogs, we created a not so mythical performance problem that began with a business user notifying us that they are experiencing a performance problem. During our analysis, we have ruled out the network and application tiers and narrowed the problem down to a database issue.

Finding the Top Resource Consumers
We begin our investigation by logging into 10G Grid DBConsole and displaying the Database Home Page. The first indicator that we are having a problem is the Host CPU graphic displayed in the middle of the screen. The DBConsole has placed a small red 'X' next to the title 'Host CPU' to notify us that CPU is an issue. If we scroll down to the lower half of the screen, we notice that ADDM has already identified the problem and is reporting its recommendations. I've discussed drilling down into ADDM recommendations in a previous blog. We can certainly use the ADDM output to help us solve our problem but it is important for us to discuss all of the tools that 10Grid DBConsole provides to us.

Let's continue our investigation by using the navigation tab on the top left hand side on the Database Home Page to navigate to the Database Performance Home Page. This panel certainly makes it easy for us to determine what resources are being utilized. We immediately see that we have a high CPU utilization by the large green area displayed at the top of the screen.

We are able to use DBConsole's drill down capabilities to drill down to the specific SQL and Sessions that are consuming that resource. It is important to note the the DBConsole provides drill down capabilities for all major resources displayed on this page. We activate the drill down for CPU utilization by clicking on the green area displayed on the screen. DBConsole responds by displaying the CPU drilldown panel. DBConsole provides a slider that allows us to go back in time and review the utilization of this resource historically. If we click on the SQL statement drill down on the left side of the screen, DBConsole responds by displaying the SQL Details Acitivity Page.

Once again, a large green area shows that this statement is consuming a LOT of CPU resources. You will begin to notice that almost every page DBConsole displays during our ananlysis provides a button to run the SQL Tuning Advisor. For more information on the SQL Tuning Advisor, please refer to my blogs titled The SQL Tuning Advisor and The 10G Automatic Tuning Optimizer. These blogs provide a wealth of information on the SQL Tuning Advisor.

I have personally seen the tool executed numerous times and can attest that the recommendations it makes are worthwhile. Please remember this point - the SQL Tuning Advisor often recommends that you implement a Profile that changes the SQL statement's access path. This tool is currently not a substitute for having a firm understanding of SQL access paths. The advisor will allow you to view the new access path. Review it, determine if you want to activate it and then monitor the new access path for the statement very closely. Don't blindly implement the profile without monitoring the performance of the new access path.

We can use the navigation tab on this page to navigate to the SQL Details Statistics Page. The SQL Details Statistics Page provides us with a wealth of information on this particular SQL statement. Note the large green area in the Activity By Waits pie chart. The page's Activity By Time section also denotes both high elapsed and high CPU times. In addition, the page provides us with other pertinent information including the number of times this statement was executed, buffer gets, disk reads, etc.. Once again we see a navigation button for the SQL Tuning Advisor.

If we click on the Plan navigation tab, DBConsole responds by displaying information on the SQL statement's access path. It's blatantly obvious in this case that we have a cartesian join occurring. We also see that DBConsole is displaying a Schedule SQL Tuning Advisor button.

The Tuning Information Panel provides us with historical information on past tuning changes. When we finally take Oracle's advice and run the SQL Tuning Advisor, it will display the activated profile on this page. If you have implemented several different profiles in the past, DBConsole will provide a listing of them on the Tuning Information Panel. You then have the option of deactivating one profile and reactivating a past profile if you desire. Here's a screenshot of a Tuning Information panel showing multiple executions and profile recommendations generated by the SQL Tuning Advisor. As I stated previously, the intent of this blog is not to instruct you how to use the advisor. I've covered that in a previous blog.

Let's continue our discussion by navigating back to the Database Performance Home Page. If we scroll to the bottom of the page, we can see a column heading titled "Additional Monitoring Links". If we click on the Top Activity link, DBConsole responds by displaying the Top Activity Page. This is the panel that I personally use the most when I am trying to find top resource consumers in DBConsole. The Top Activity Page displays both Top SQL and Top Sessions. If we click on the SQL ID link provided in each line of the Top SQL section of this panel, DBConsole will display the SQL Details Activity page we reviewed earlier in this blog. We are also able to drill down into more specific session information by using the drill down links provided in each line of the Top Session Panel. As I stated, when I use DBConsole to determine what resources are being consumed, I will navigate to the Database Performance Home Page and look at the graph at the top of the page. Although I may drill down into the resource, I most often find myself dropping down to the Top Activity link to display the Top Activity Page. It allows me to quickly identify both the top SQL and sessions.

There is a link displayed in the middle right hand side of the Top Activity Page titled 'Run ASH Report'. Since the Automatic Workload Repository, by default, takes snapshots every 60 minutes, performance information could be up to 60 minutes old. As a result, snapshots do not contain enough information to allow administrators to perform analysis on the active workload currently being performed in the database system. Oracle10g contains a new internal utility, called Active Session History (ASH), to provide administrators with access to current performance information. ASH samples data from the V$SESSION dynamic performance table every second and stores the information in V$ACTIVE_SESSON_HISTORY. The information contains the events for which current sessions are waiting. The information pertains to active sessions only; information from inactive sessions is not recorded. The view contains one row per active session for each one-second sample. Administrators are able to access V$ACTIVE_SESSON_HISTORY as they would any other V$ dynamic performance table. DBConsole also provides this handy report to provide you with information derived from statistical data stored in V$ACTIVE_SESSON_HISTORY. The ASH report provides a wealth of top resource consuming components.

The Top Activity Page also allows me to drill down into individual session information. Each Session ID displayed in the Top Sessions chart on the Top Activity page is a link to a set of session information panels. If we click on the Session ID link, DBConsole will resond by displaying the Session Activity page. The Session Activity Page provides us with information on the resources being used and navigation buttons that allow us to kill the session and enable or disable SQL Tracing. You'll notice that these buttons are displayed on all of the panels dealing with session information.

If we click on the General Tab, DBConsole will display the General Information Page. The General Information page provides us with a wealth of pertinent information on this specific session. Depending on the application's architecture, the Client information could help us identify and contact the specific user experiencing the problem. In addition, the panel provides information on the application and server. The most important piece of information displayed on this page is under the heading titled "Wait". It tells us what resource is currently being waited for, and in this case, the file and object. Great diagnostic information when you are trying to debug a performance problem. The Session Statistics Page is a dump of all the raw statistics for this session. The Open Curors Page displays information on the SQL statement or statements being executed by this specific session. It provides the text of the SQL statement and drill downs that allow you to access the SQL Details drilldown screens discussed previously in this blog. The Blocking Tree Page helps you determine if this session is being locked out by another process or locking out other processes. For more information on locking and blocking, please refer to my blog titled "10G Grid Control Hang Analysis Feature." Lastly, the Wait Event History Page provides drilldown capabilities into the specific waits this session is experiencing.

Let's take a look at another set of panels that provides information on top resource consumption. We will navigate back to the Database Performance Home Page and click on the Top Consumers link to display the Top Consumers Home Page. Although I don't use this page frequently when performing database triage, it does provide some very useful information. My personal favorite is the Top Modules by Service chart on the upper right hand side of the panel. It organizes the resource consumers by the type of service. This information is very beneficial when you have numerous sessions consuming a high level of resources because it allows you to determine if the problem is localized to a specific area of the application.

Currently, it is my SQL*PLUS service that is dominating our database resources. I have seen personally viewed report writer programs, application server programs, TOAD, SQL*PLUS, etc. all displayed on this chart. The information allowed me to quickly identify if the problem sessions were all coming from that one specific component of the application environment. For example, I once viewed TOAD being displayed as the top resource consumer during one of my investigations. I drilled down into the session information, found the accounts that were being used, stopped by the development area and found that a group of developers just had TOAD installed and were testing it by accessing production data. The panel provides drilldowns for Top Services, Top Clients, Top Clients and Top Sessions. The Top Sessions Panel allows me to once again drill down into individual session information. The panel also provides a link that takes you to the SQL being executed by the top sessions.

As we have found, the 10G DBConsole provides many different features that allow us to identify the top resource consumers in the database it is monitoring. With time and experience, you'll be able to quickly determine what sessions, SQL and components are the top resource consumers. In addition, you will also be able to determine what resource the top consumers are utilizing and the SQL they are executing.

Thanks for Reading,

Chris Foot
Oracle Ace