全部博文（41）
分类： Oracle
2008-04-12 10:52:58
Finally, a tool that serves as one-stop-shop for Oracle administration and management—whether by novices or experts
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 EM 10g is installed by default when you install the 10g software. Conceptually, it differs from previous versions in that instead of being a client-installed tool, it's actually an HTTP server (called DB Console) sitting on the database server itself. (See Figure 1.) You can use any browser to see the EM interface.
 |
The port number for DB Console is found in $ORACLE_HOME/install/portlist.ini. Here is an example of a file; ports in your case may be different.
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) = 1830From 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: 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 After logon, the main database home page comes up. The top portion of the home page enables a quick glance at important details. (See Figure 2.)
 |
Some of the most important points in the above figure have been circled and annotated with numbered references in this article. First, note the section labeled "General" (1); this section shows some most rudimentary details about the database, such as the fact that the database has been up since March 20 as well as the instance name. The Oracle Home is shown as a hyperlink, which, when clicked, shows all the products and all other Oracle databases sharing that home. The hyperlink for Listeners shows all the databases and instances registered with the listener, whose name is shown immediately below. Finally, it shows the host name (starz).
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.
 |
Another alert (3) is about a wait: the database is waiting 69% of the time for a wait related to the wait class "Application." Remember how the top part of the home page indicates that a session is waiting? This alert shows us what it is waiting on. Clicking on the hyperlink will immediately show you the actual waits.
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 Let's see how some of the more common tasks are accomplished through the new EM. 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.
 |
Note the flashlight symbol highlighted inside a red circle; this is the button for bringing up a list of values. In the screen shown in the figure, you can click on the LOV symbol to bring up a list of users in the database and select one from the list. Clicking on the button "Go" brings up a list of tables for that user. You can also specify a wildcard with the "%" sign—for example, by using %TRANS% to bring up all the tables with the word TRANS in the name.
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.
 |
If you want to modify the column ACTUAL_RATE from NUMBER(10) to NUMBER(11), you can modify the number (Ref 1) and click "Apply." To see the actual SQL statement used to accomplish this task, can click the button "Show SQL."
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" ( Ref 2). This brings up the segment screen as shown in Figure 6.
 |
Note the item marked inside the red circles. The screen shows how much space is allocated to the segment (2), how much is actually used (1), and how much is wasted (3). On the bottom part of the screen (4), you can see a graph of the space used and allocated for the object. In this example, the pattern of the table usage has been steady—hence the straight line.
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 As you've learned up to this point, although EM's look-and-feel has changed, it offers at least as much functionality as the previous Java version. However, unlike the latter, EM now also supports newer Oracle Database functionality. For example, EM can now handle subpartitions. 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 ( 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 how all the metrics have been aligned on the same timeline, which makes viewing their interdependencies easier. Note the spike (3), which corresponds to the Scheduler task. It shows that some seven sessions were waiting for Scheduler-related waits at that time. So, what was the impact? Note the CPU metrics located in the same place (the green area)—they indicate the maximum CPU ever used, as shown in the graph by the broken line (4). Before and after that point, we don't see the CPU spikes occurring, which provides one clue. Note the spike in CPU run queue length (1), which is a direct consequence of the Scheduler, which might have generated an excessive memory requirement, having caused the increased paging activity (2). As you can see, all the symptoms fall in line to enable a better understanding of the database load "profile."
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.
 |
The screen shows that the sessions were waiting for the wait event enq: TX � row lock contention. So what was the SQL statement that caused it? Simple: The SQL ID of the statement 8rkquk6u9fmd0 is shown on the screen itself (inside the red circle). Click on the SQL ID to bring up the SQL screen as shown in Figure 9.
 |
 |
 |
 |
 |
