Enterprise Manager for 10g DBA

What tool do you use in your day-to-day DBA-related activities? It's a question I asked recently in a user group meeting.

The answers varied depending on the DBA's work experience. Most senior administrators expressed a preference for simple command-line SQL*Plus (my personal favorite), with the rest dividing their allegiances among a handful of third-party products. The same question, however, yielded a different response from entry-level DBAs: among that group, Enterprise Manager (EM) was clearly the tool of choice.

It's not hard to understand these preferences. Oracle Enterprise Manager has been steadily perfected since its introduction several years ago, beginning as the character-mode display SQL*DBA, evolving into a client OS-based tool, and finally taking on a Java flavor. The information presented by EM was sufficiently detailed for most DBA tasks, serving as a solution for users who were either too reluctant or too busy to learn a new syntax and wanted a GUI tool for managing common database chores such as adding users, modifying datafiles, and checking on rollback segments. The diagnostic pack supplied much-needed GUI support for performance tuning.

However, one of the major issues hampering EM's widespread adoption was its inability to keep pace with the development of the database server itself. For example, the Oracle9i Database version of EM doesn't support subpartitioning, a feature first introduced in Oracle8i.

The new version of EM in Oracle Database 10g changes that equation. It has a new architecture, a new interface, and most important, a very powerful and complete toolbox catering to all DBA skillsets—from novices to advanced users. And best of all, it's part of the installation itself without any additional cost. If you are evaluating third-party tools, you can certainly throw EM into the mix to light a fire under the competition. Even if you are an "in-command-line-we-trust" kind of DBA (like me), you will greatly appreciate how EM can help you in several situations.

In this installment I will introduce you to the new EM. Because the tool is so vast in scope, it will be impossible to cover the entire spectrum of features; instead, I will explain a few basics and offer pointers to additional material. Keeping in the spirit of this series, I will provide practical examples that demonstrate the use of the tool to solve real-life problems.

Architecture

Ultra Search HTTP port number = 5620
iSQL*Plus HTTP port number = 5560
Enterprise Manager Agent Port =
Enterprise Manager Console HTTP Port (starz10) = 5500
Enterprise Manager Agent Port (starz10) = 1830
From this file we know that the Agent for the database starz10 listens on the port 1830 and the EM console listens on 5500. We can invoke the EM logon screen by entering the following URL:

http://starz/em/console/logon/logon

This URL brings up a logon screen where you can log on as a DBA user. For our example, we will log in as SYS.

Main Database Home Page

In section named "Host CPU" (2), the CPU details are shown at a glance. Section "Active Sessions" (3) shows the active sessions and what they are doing at the moment (4). We see from the above that 99% of the time spent by the sessions is in waiting. (We will find the cause of these waits later.) The section on "High Availability" (5) shows availability-related information. For example, the value of "Instance Recovery Time," which is the value of MTTR Target for the instance, determines how much time may be required for instance crash recovery.

The section on "Space Usage" (6) is interesting: it shows warnings associated with 23 segments. (Again, more on these warnings later.) The section "Diagnostic Summary" (7) provides a synopsis of database well being. The number of performance findings indicates how many issues were proactively identified by the Automatic Database Diagnostic Monitor (ADDM), the new self-diagnostic engine in 10g. EM also automatically analyzes your environment to determine if any recommended best practices are being violated; the result of this analysis is presented in the "Policy Violation" section. Finally, EM scans the alert log and shows any recent ORA errors. This information is invaluable—automatic scanning of Oracle errors in the alert log saves you the considerable trouble of manually searching for them.

The bottom part of the database home page, shown in Figure 3, we see some of these messages in more detail. The section "Alerts" (1) shows all the relevant alerts that require your attention, each of which can be easily configured. Take the first one (2), for example, which shows that the Archiver process is hanging for some reason. Of course, the next course of action is to determine why. To find out, just click on it. You will be shown more details from the alert.log file containing the error. In this case, the culprit was a filled-up flashback recovery area; we just need to clear it up so the Archiver can start working again.

The next alert (4) shows an audit entry, that the user SYS connected to the database from a certain client machine. Again, by clicking on the hyperlink you can reveal all the details about the connection. The last alert (5) shows that some objects are invalid. Clicking on the hyperlink will get you to the screen where the invalid objects are validated.

As you can see, the database home page serves as a dashboard for everything that needs your attention. Instead of cluttering the screen with detailed information, the interface has been made quite succinct with those details just a click away. You could compile all this information manually, but it would take a lot of time and effort. EM 10g provides an out-of-the-box solution.

General Usage

One common task is to alter a table and its corresponding indexes. From the Database home page, choose the "Administration" tab as shown in Figure 3 and reference the item marked 6. From this page you can administer the database to configure undo segments, create tablespaces and schema objects, set up resource manager, use the new Scheduler (to be covered in a future installment), and more. Choose "Tables" from there, which brings up a screen as shown in Figure 4.

Let's see an example. Choose the table TRANS to modify a column there. Clicking on the hyperlink brings up the "Edit Table" screen as shown in Figure 5.

Another important piece of information is available in the same screen: the growth trend. As you will learn in a future installment on segment management, it is possible to observe object growth over a period of time. This screen offers that same information but in a graphical manner. To see the screen, click on the tab "Segments" (Figure 5 Ref 2). This brings up the segment screen as shown in Figure 6.

You can perform other administrative operations on the table using the tabs for that purpose, such as "Constraints" for managing constraints.

Performance Tuning Using EM

However, experienced DBAs will want more from the tool—especially for troubleshooting problems or proactive performance tuning. Let's use an example. Recall from the previous section that our database is waiting on the "Application" wait class as shown in the database home page (Figure 3 Ref 3) and that we need to diagnose the cause. One of the key things to understand in any tuning process is how various components such as CPU, disk, and host subsystems interact, so it helps if all these variables are viewed together in context. To do that, choose the "Performance" tab from the Database home page. This brings up the screen as shown in Figure 7.

Note the spikes at the end of the timeline—increases in Run Queue Length (5) and Paging Rate (6)—which correlate to another spike in Physical Reads (7). What is the cause?

By comparing the graph "Sessions: Waiting and Working" with the time the spikes were occurring, we can see that most of the sessions were waiting on the "Application" wait class. But we need to find out exactly what it was waiting on during that time period. Click on the area at that time, which brings up the Active Sessions screen as shown in Figure 8.

Latch Contention

First, click on the graph on the area shown for CPU contention (marked with "Click Here" on the figure) to see that particular wait in detail, shown in Figure 11.

The histograms come to our rescue here. From the figure, you know that some 250 times sessions had a wait of 1 millisecond (highlighted inside a circle). Sessions waited some 180 times somewhere between 4 and 8 milliseconds. This screen shows that the waits are typically for small durations, making them insignificant symptoms of latch contention.

From the database home page you can access ADDM, SQL Access Advisor, and other Advisors by clicking on the tab marked "Advisor Central." ADDM runs automatically as metrics are collected and the results are posted immediately on the Advisor Central page, which when clicked shows the recommendations made by ADDM. The SQL Tuning Advisor also examines these metrics and communicates its recommendations on this page. (We'll examine ADDM and SQL Tuning Advisor in much more detail in a future installment.)

Maintenance Made Easy