Database Performance Monitoring Using 10G OEM Grid Control
We’ll then take a look at 10G Grid Control’s performance advisors that are grouped together in Advisor Central. Once we have a firm understanding of Enterprise Manager’s performance and tuning capabilities, we’ll put our newfound knowledge to use when we use EM to solve some real-world performance problems.
Database Home Page
Take a couple of minutes and look at all of the information that our sample
database
home page provides. By reviewing the information on this page, we can quickly
determine (starting at top left column):
General Information Column
- How long the database has been available
- Database Version
- The Oracle home directory
- Listener information
- Host information
Host CPU Column
- Current CPU utilization and drill downs to more specific host performance information
Active Sessions Column
- Time required to perform instance recovery (MTTR)
- Information on the last backup and related availability information
Space Usage Column
- Database sizing information with drilldown capabilities
Diagnostic Information
Column
- Alerts, warning and policy violations, which will be discussed in an upcoming blog
Alert Section
At the bottom of the database's
home page is a section that display's information on alerts that are currently
active. Alerts are generated when a metric's threshold is exceeded. Metrics
are units of measurement that are set at predefined values to assess the health
of the target being monitored. These predefined values are called metric thresholds.
Thresholds are one or more values against which current statistical values are
compared. To learn how to set up 10G Grid Control's early warning, alert notification
system, please refer to my blog titled "Configuring
10G's Automatic Alert Notification System."
You'll notice that there is a series of tabs at the top left side of the panel that allow users to navigate to performance, administration and maintenance components. Clicking on the performance tab takes us to the database's performance home page.
Database Performance
Home Page
One of the great benefits that 10G Grid Control provides is the ability to display historical
performance information. The database
performance home page displays a drop down that allows users to view current
as well as historical performance statistics for the last 24 hours, 7 days and
31 days.
This feature not only helps administrator answer questions like "my program ran long yesterday, can you help me fix it before it runs today?" but also helps to determine exactly when a particular performance problem began to occur. Knowing when performance first "went South" is a critical piece of information that allows you to determine what changes were made since the problem first occurred. This is often all that is needed to identify and solve the performance problem.
The Run Queue Length and Instance Throughput Charts (top and bottom charts on the screen) allow administrators to evaluate general host and database performance. It's really quite simple, the more work you have waiting in the queue or the lower your instance throughput is, the more disgruntled your end users are.
The Additional Monitoring Links area contains links that provide information on top resource consuming sessions, overall instance workload (I/O, number of sessions, etc.), database lock, blocking session and general session information. We'll take a look at these additional components in upcoming blogs.
You can use the Sessions: Waiting and Working Chart in the middle of the screen to determine exactly what resource your disgruntled users are waiting for. Take a look at our sample Sessions: Waiting and Working Chart. The chart allows administrators to quickly determine where the most waiting time is spent. In our example, we are waiting for I/O. Since this is a multi-terabyte data warehouse, no surprises here.
I/O DrillDown
Clicking on one of the colored areas of the Sessions: Waiting and Working Chart
allows administrators to drill down to the specific resource being waited on.
If we click on the purple I/O color on our chart, we'll navigate to the I/O drill down page.
The Active Session Waiting: User I/O panel displays in-depth I/O information. As with all OEM performance panels, a drop down is provided to view current as well as historical resource utilization statistics for the last 24 hours, 7 days and 31 days. Administrators are able to focus on a particular time period by dragging the gray slider over the desired time slot.
No matter what resources you drill down into, they all have the same look and feel. I chose I/O since that is the top resource being waited on by our sessions. All of the resource panels display the particular resource's historical utilization and two pie charts on the bottom of the screen that allow administrators to drill down into the top sessions and SQL statements utilizing that resource.
We view more specific information on our top I/O resource consuming SQL by clicking on the Top Waiting SQL: User I/O pie chart or on the link next to chart itself. The SQL Details Page displays the SQL statement, the access path the statement is using and historical execution performance information. As I stated previously, knowing when performance degraded is often a vital piece of information. In our example, we have a TOAD session that is doing a full table scan on a multi-million row table (there's something new). The SQL Details Page also provides a link to the SQL Tuning Advisor. We'll discuss the SQL Tuning Advisor in-depth in a future blog. This is one powerful tool and we'll need to know how to use it wisely.
Since we are evaluating I/O statistics in our example, it is important for us to know what objects we are waiting for. We can change the view from top session and SQL information to top files and top object statistics by clicking on the Top Files and Top Object links just above the pie charts. In my last screen print, I have clicked on the Top Files Link to display I/O utilization statistics on a per file basis.
I realize that we only reviewed one resource out of many. But as I said, they all have the same look and feel. Each drill down panel provides you with historical information, a slider to focus on a particular time period and further drilldowns into SQL, session and object information related to the resource in question.
Next Up
Now that we have a better understanding of some of the database information
that 10G Grid Control provides , I'll provide you with a few helpful hints on using this
information to evaluate database performance. We'll then discuss 10G's Advisor
Central toolsets. Wait until you get a load of them!
Thanks for reading!
