Enterprise Manager Ten G

Enterprise
Insider of Oracle Database G

Oracle Database G: Best 20-bit features for DBAs (13)

Author Arup Nanda Source: OTN

Week 13th
Enterprise Manager Ten G

Finally, discuss a one-stop tool for managing and using Oracle-whether for beginners or experts

What tools do you use in your daily DBA-related activities? I recently raised this question in a user group meeting.

The answer varies according to the DBA's working experience. Most senior administrators prefer simple command-line sql*plus (my personal preference), while others prefer to use some third-party products. However, the same problem has a different response at the entry-level DBA: in this group, Enterprise Manager (EM) is clearly their choice.

These preferences are not difficult to understand. Oracle Enterprise Manager has been evolving since its launch a few years ago, when it started as a sql*dba of character-mode display, followed by an operating system-based client tool and finally Java-style. The information provided by EM is very detailed enough to complete most DBA tasks and can be a solution for users who are unwilling or too busy to learn the new syntax and want to use GUI tools to manage common database tasks such as adding users, modifying data files, and checking fallback segments. The diagnostics package provides much-needed GUI support for performance tuning.

However, one of the major obstacles to the widespread use of EM is its inability to keep up with the database server itself. For example, the ORACLE9 I database version of EM does not support sub partitions (this feature was first introduced in Oracle8 i).

The new version of EM in Oracle database G has changed this. It has a new architecture and a new interface, and most importantly, it has a very powerful and sophisticated toolbox that provides all the DBA skill sets needed from beginners to advanced users. The best thing is that it is part of the installation itself, at no extra cost. If you are evaluating third-party tools, you can certainly add EM to the evaluation ranks, making the competition even more intense. Even if you're the kind of "command-line" DBA (like me), you'll appreciate the help that EM can offer you in some cases.

In this article, I'll introduce you to the new EM. Since the tool covers a wide range, it is not possible to discuss all the characteristics here; I will present a few basic features and provide clues to other materials. I will follow the spirit of this series to provide a practical example that demonstrates how to use the tool to solve practical problems.

Architecture

By default, when you install the G software, when you install EM g, conceptually it differs from previous versions in that it is not a client-installed tool; it is actually an HTTP server (called the DB Console) located on the database server itself. (see Figure 1.) You can view the EM interface using any browser.



Figure 1:em Architecture

The port number of the DB console can be found in the $ORACLE _home/install/portlist.ini. The following is an example of a file; For you, the port may not be the same.

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 learned that the agent for the database starz10 listens on port 1830 and the EM console listens to 5500. We can invoke the EM login screen by entering the following URL:

Http://starz/em/console/logon/logon

This URL brings up the login screen from which you can log on as a DBA user. In our example, we will log in as SYS.

Master Database Home

The main database home page appears after you log on. The upper part of the homepage provides quick browsing of important details. (see Figure 2.) ）



Figure 2: Master Database home page (upper)

Some of the most important parts have been circled in the diagram above and are annotated with the reference numbers in this article. First, note the section labeled "General" (1), which shows some of the most basic details about the database, such as the database started from March 20, and the instance name. Oracle home is displayed as a hyperlink that, when clicked, displays all products and all other Oracle databases that share the home directory. A Listener hyperlink displays all databases and instances registered to the listener whose name appears immediately below it. Finally, the host name (Starz) is displayed.

In the section named "Host CPU" (2), the details of the CPU are displayed prominently. The active Sessions (3) section shows the active session and its current status (4). From the above we see that 99% of the time is occupied by a session in the waiting state. (We'll find out why these wait later.) The "High Availability" (5) section shows information related to availability. For example, the value of "Instance Recovery Time" (the value of the instance's MTTR Target) determines how long the instance crash recovery may take.

The "Space Usage" (6) section is interesting: it displays warnings related to 23 paragraphs. (again, these warnings will be covered in detail later.) The "Diagnostic Summary" (7) section provides a summary of the good running of the database. The number of performance problems found indicates the Automated Database Diagnostics Monitor (ADDM)-The new self-diagnosis engine in 10g-proactively identifies the number of problems. EM also automatically analyzes your environment to determine if the recommended best practices are violated, and the results of this analysis are shown in the "Policy violation" section. Finally, EM scans the alert log and displays any recent ORA errors. This information is valuable-automatically scanning Oracle errors in the alert log allows you to avoid the hassle of manually searching for these errors.

In the lower part of the database home page, as shown in Figure 3, we can look at some of the messages in more detail. The "Alerts" (1) section shows all the relevant alerts that require your attention, and each alert can be easily configured. Take the first alert (2) as an example, which shows that the archiver process hangs for some reason. The next step, of course, is to determine its cause. To find out why, just click it. You will get more detailed information from the Alert.log file that contains the error. In this case, the point of failure is an already filled flashback recovery area; we just need to empty it and archiver it again.



Figure 3: Master Database Home page (bottom)

Another alert (3) is about waiting: The database waits in a 69% of the time for a wait that is related to the waiting class "application". Remember how the upper part of the home page shows a waiting state for a session? This alert shows us what it is waiting for. Clicking the hyperlink will immediately show you the actual wait.

The next alert (4) displays an audit project in which user SYS connects to the database from a specific client machine. Also, by clicking the hyperlink, you can display all the details about the connection. The last alert (5) shows that some of the objects are invalid. When you click the hyperlink, you will go to the screen where the object is not validated.

As you can see, the database home page is like a dashboard that shows all the things you need to be aware of. The interface does not stack the details on the screen, and the interface is fairly concise, with just a click to get the details. You can collect all of this information manually, but it can take a lot of time and effort. EM G provides a solution to use with.

General application

Let's look at how to use the new EM to do some of the more common tasks.

A common task is to change the table and its corresponding index. On the Database home page, select the "Administration" tab as shown in Figure 3, and refer to the item labeled 6. On this page, you can manage the database to configure fallback segments, create tablespaces and schema objects, set up resource managers, use the new scheduler (which will be covered in future articles), and more. Select "Tables" here, which will pull up the picture shown in Figure 4.



Figure 4: Table Management

Notice the flashlight logo highlighted in the red circle; This is the button that brings up the list of values. In the picture shown, you can click the LOV flag, bring up the list of users in the database, and select a user from the list. Click the button "Go" and a list of the user's table appears. You can also use the% symbol to specify a wildcard character-for example, by using%trans%, you can find all the tables in the name with the word TRANS.

Let's take a look at an example. Select Table TRANS to change one of the columns. Click the hyperlink to pull up the Edit Table screen as shown in Figure 5.



Figure 5: Table Management

If you want to change the column actual_rate from number (10) to numbers (11), you can make changes (reference 1), and then click Apply. To see the actual SQL statement that completes the task, you can click the button "Show SQL."

Another important piece of information can be obtained on the same screen: the growth trend. You will learn in a later article about paragraph management that it is possible to observe the growth of objects over time. The screen provides the same information, but it is graphically represented. To view this screen, click the tab "segments" (Figure 5 refers to 2). This operation brings up a segment of the screen, as shown in Figure 6.



Figure 6: Segment Screen

Notice the items marked in the red circle. The screen shows how much space is allocated to the segment (2), how much (1) is actually used, and how much (3) is wasted. In the lower part of the screen (4), you can see a graphic of the space used by the object and the space allocated to the object. In this example, the usage pattern for a table has stabilized-so it's a straight line.

You can perform other administrative actions on a table by using the tabs that are used for that purpose, such as "Constraints", which is used to manage constraints.

Use EM for performance tuning

As you've learned so far, although the appearance of EM has changed, it provides as much functionality as at least the previous Java version. However, unlike the latter, EM now supports updated Oracle database functionality. For example, EM can now handle child partitions.

However, experienced DBAs want this tool to do more work-especially in troubleshooting or active performance tuning. Let's give an example. Recall that our database is waiting on the "application" waiting class, as shown in the Database home page (Figure 3 refers to 3), and we need to diagnose why. One of the key things to understand in any tuning process is how many components (such as CPUs, disks, and host subsystems) interact with each other, thus helping to see all of these variables in a contextual context. To do this, you can select the Performance tab in the database home page. This operation brings up the picture as shown in Figure 7.



Figure 7: "Performance" tab

Note that all metrics are aligned on the same timeline, which makes it easier to observe their interdependencies. Note the Spike (3), which corresponds to the scheduler task. It indicates that approximately seven sessions are waiting for the scheduler-related wait event at that time. So what are the factors that affect it? Note CPU metrics in the same location (green area)-they show the maximum CPU usage ever used, represented by a dashed line (4) in the graph. Before and after this point, we did not see the CPU spikes appearing, which provided a clue. Note the spike (1) in the length of the CPU run queue, which is a direct consequence of the scheduler, which may generate excessive memory requirements, resulting in increased paging activity (2). As you can see, all the phenomena are concentrated, promoting an understanding of the database load "profile".

Note that spikes at the end of the timeline-increase the run queue Length (5) and paging rate (6)-they are related to another spike in physical reading (7). What's the reason?

By comparing the time between the graphic "sessions:waiting and Working" and the spike, we can see that most of the sessions are waiting on the "application" waiting class. But we need to find out exactly what it is waiting for during that period. Click the area of the time to bring up the active session screen, as shown in Figure 8.



Figure 8: Active session waiting

This screen shows that the waiting event that the session is waiting for is enq:tx row lock contention. So what is the SQL statement that causes this problem? Very simple: The screen itself shows the SQL ID of the statement 8rkquk6u9fmd0 (in the red circle). Click the SQL ID and pull up the SQL screen shown in Figure 9.



Figure 9:sql Detailed information

On this screen, you can see the SQL statements about it and the relevant details, including the execution plan. It shows that this SQL causes row lock contention, so application design may be the source of the problem.

Tying lock contention

Suppose you click the Performance tab to appear like the picture shown in Figure 10.



Figure: "Performance" tab, example 2

In the diagram, note the highlighted metrics in the red rectangle. You can see that there are a lot of CPU-related waits around 12:20am, which results in a huge running queue on the CPU. We need to diagnose this wait.

First, click the graphic that shows the CPU contention area (labeled "Click here" on the diagram) to see the specific wait in detail, as shown in Figure 11.



Figure 11: Active Session Waiting

Note the shaded box (1) in the Active Sessions working:cpu Used graphic. You can use the mouse to drag it to place the focus. This action causes the following pie charts (2 and 3) to be evaluated only within the time period that the box contains. Here we see that a specific SQL with ID 8ggw94h7mvxd7 is running very hard (2). We also see that a user session with the username ARUP and SID 265 is the primary running session (3). Click the session to view its details. This operation calls the "session Details" screen. Click the tab "Wait events" to bring up the details of the waiting event that the session is experiencing, similar to the picture shown in Figure 12.



Figure 12: Details of the wait event

In this screen, note that the longest wait for the 118-minute seconds highlighted in the red circle is waiting for the library cache. When you click the latch:library Cache hyperlink, you will see a picture similar to that shown in Figure 13.



Figure 13: Wait for histogram

This screen provides some unique information not previously provided by the G database. When diagnosing this lock contention problem, how do you know if this 118-minute wait is made up of several small waits in a few sessions, or is it just a large wait in a session that makes the data deviate?

Here, histograms can help us. From the diagram, you know that approximately 250 sessions have 1 milliseconds of waiting (highlighted in the circle). The session waits somewhere between 4 and 8 milliseconds for about 180 times. This screen shows that these wait times are usually very short, so they are not the main symptom of lock contention.

On the Database home page, you can access ADDM, SQL Access Advisor, and other advisor programs by clicking the tab labeled "Advisor". ADDM runs automatically when the metrics are collected, and the results are immediately published in the Advisor Central page, and when you click the page, the suggestions given by ADDM are displayed. SQL Tuning Advisor also examines these metrics and publishes their recommendations on this page. (We will study ADDM and SQL tuning Advisor in more detail in future articles.) ）

Simplified maintenance

The tab labeled "Maintenance" on the Database home page is the startup console for common maintenance activities-such as backup and recovery, data export or import (data pump), database cloning, and more activities. On this screen, you can also edit the rationale for the best practices on which policy validation alerts are based.

Conclusion

As previously mentioned, this article is about the tip of a huge iceberg. In this article, my goal is not to provide a comprehensive overview, but rather to provide a quick glance at some specific activities across multiple skill sets.

Oracle G EM provides the novice DBA with sufficient resources to quickly understand the subtleties of Oracle database management. A good outline of the tasks and techniques for using EM is the Oracle two-day quick DBA reference manual. I strongly recommend that you read it, especially when you are just starting to learn.