Oracle Database 10 G insider
Oracle Database 10 G: The best top 20 feature provided for DBA (13)
Author Arup Nanda Source: OTN
10th week Enterprise Manager 10 g
Finally, discuss a one-stop tool for managing and use Oracle - no matter the initiator or expert
What tools do you use in daily DBA related activities? I have recently put forward this issue in a user group meeting.
The answer is different from the work experience of DBA. Most senior administrators prefer simple command line SQL * Plus (my personal preference), and the rest of the people prefer to use some third-party products. However, the same problem has different reactions in entry-level DBA: In this group, Enterprise Manager (EM) is obviously their choice.
These preferences are not difficult to understand. Oracle Enterprise Manager has been continuously perfected since the introduction a few years ago. It starts at the character mode displayed SQL * DBA. Subsequently, it is developed into an operating system-based client tool, and finally has a Java style. The information provided by the EM is very detailed, enough to complete most DBA tasks, can be used as an unwilling or innocent understanding of new grammar and want to use the GUI tool to manage common database tasks (such as adding users, modifying data files, and checking back segments) solution. Diagnostic packages provide a very needed GUI support for performance adjustment.
However, a major problem that hinders EM is widely used is that it cannot keep up with the development of the database server itself. For example, the Oracle9 I database version of EM does not support sub-partition (this feature is introduced first in Oracle8 I).
The new version of the EM in the Oracle Database 10g changed this. It has a new architecture and a new interface, and most importantly, it has a very powerful and perfect toolbox to provide all DBA skills set from beginners to advanced users. The best is that it is part of the installation itself without additional costs. If you are assessing third-party tools, you can of course add EM to the evaluation line, making the competition more intense. Even if you are the DBA of the "belief order" (like me), you will also appreciate the help you provide for you in some cases.
In this article, I will introduce you to the new EM. Since the tool is widely scope, it is impossible to discuss all the features here; I will introduce several basic features and provide clues to other materials. I will follow the spirit of this series to provide practical examples, demonstrate how to use this tool to solve practical problems.
Architecture
By default, when you install 10 g, it is installed when EM 10g is installed, and it is different from the previous version, it is not a tool for the client; in fact it is located on the database server itself. HTTP server (called DB console). (See Figure 1.) You can use any browser to view the EM interface.
Figure 1: EM architecture
The port number of the DB console can be found in $ 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 understand that the database STARZ10 agent listens 1830, while the EM consists can listen 5500. We can call the EM login screen by entering the URL:
Http: // starz / em / console / logon / logon
This URL calls up the login screen and you can log in with a DBA user. In our example, we will log in with SYS.
Main database main page
The primary database main page appears after logging in. The upper part of the homepage provides quick browsing of important details. (See Figure 2.)
Figure 2: Main database main page (upper)
Some of the most important parts have been circulated in the above figure, and they are labeled with references to the numbers herein. First, please note the part of "General" (1); this section shows some of the most basic details about the database, such as the database that has been launched from March 20, and instance names. Oracle Home is displayed as a hyperlink that all products and all other Oracle databases shared by sharing the main directory are displayed when clicking the link. Listener's hyperlink display is registered to the listener (its name is displayed in all databases and instances below it). Finally, the hostname (Starz) is displayed.
In the section named "Host CPU" (2), the details of the CPU are displaced. "Active Sessions" (3) section shows the session of the activity and its current state (4). From above we see, 99% of the time is taken up by a session in the state. (We will find out the cause of these waiting.) The "High Availability" (5) section shows information related to usability. For example, the value of "Instance Recovery Time" (Value of the Mttr Target of the instance) determines that the instance crash will restore the time that may take.
"Space usage" (6) is very interesting: it displays a warning related to 23 segments. (Similarly, these warnings will be described in detail later.) "Diagnostic Summary" (7) provides a summary information of a well-running database. The number of performance issues found represents the Auto Database Diagnostic Monitor (AddM) - how much problems with new self-diagnostic engines in 10G - active recognition. EM also automatically analyzes your environment to determine if it violates the best practices recommended; the results of this analysis are displayed in the "Policy Viological" section. Finally, the EM scan the alarm log and displays any latest ORA errors. This information is very valuable - automatically scan Oracle errors in the alert log allows you to avoid a lot of troubles from manually searching these errors.
At the lower part of the database main page, as shown in Figure 3, we can view some of the messages in more detail. "Alerts" (1) section shows all relevant alerts that you need to pay, each alert can be easily configured. Take the first alert (2) as an example, it shows the Archiver process hangs for some reason. Of course, the next step is to determine the reason. To find out the reason, just click it. You will get more details from the alert.log file containing the error. In this case, the fault point is a flashback recovery area that has already been filled; we only need to empty it, and Archiver can restart work.
Figure 3: Main database main page (lower)
Another alert (3) is about waiting: the database is waiting in 69% of the time waiting for the waiting class "Application". Remember how the home page shows a session in a waiting state? This alert shows us what it is waiting for. Clicking hyperlink will now display the actual wait for you. The next alert (4) shows an audit item, that is, the user SYS is connected to the database from a particular client machine. Similarly, by clicking the hyperlink, you can display all the details about the connection. The last alert (5) shows that some objects are invalid. Click Hyperlink, you will go to the object being verified invalid.
As you can see, the database home page is asked to display all the items that need you pay attention to all the dashboards. This interface does not accumulate detailed information on the screen, and its interface is quite simple, just click to get these details. You can collect all this information manually, but this may cost a lot of time and effort. EM 10 G provides a solution with take advantage of.
General application
Let's take a look at how to use the new EM to complete some more common tasks.
A common task is to change the table and its corresponding index. In the database main page, the "Administration" tab is selected as shown in Figure 3, and the item tagged as 6 is referenced. In this page, you can manage the database to configure a retire segment, create a tablespace and mode object, set the resource manager, use the new scheduler (described in later articles) and more. Select "Tables" here, which will call the screen as shown in Figure 4.
Figure 4: Table Management
Note that highlights highlights in the red circle; this is a button for calling a numeric list. In the screen shown in the figure, you can click the LOV flag to call the list of users in the database and select a user from the list. Click the button "Go" to appear a list of the user's table. You can also use the "%" symbol to specify wildcards - for example, by using% TRANS%, you can find all the tables with words TRANS in the name.
Let's take an example. Select Table TRANS to change one of them. Click the hyperlink to call 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 Number (11), you can change the number (reference 1), and then click Apply. To see the actual SQL statement that completes this task, click the button "Show SQL".
Another important information can also be obtained on the same screen: growth trend. You will have learned in a subsequent article management in subsequent sections that observer that target growth in a period of time is possible. This picture provides the same information, but is expressed in a graphical manner. To see this screen, click the tab "segments" (Figure 5 reference 2). This operation is called the segment screen, as shown in Figure 6.
Figure 6: Segment screen
Note the items marked in the red circle. How many space allocation to segments (2) are displayed, how much (1) actually use (3). At the lower part of this screen, you can see a graphic of space used by the object and space allocated to the object. In this example, the mode of use is stable - it is a straight line.
You can perform additional management operations on the table, using tabs for this destination, such as "consTRAINTS" for managing constraints.
Performance adjustment using EM
So far, you have learned that although the appearance of the EM has changed, it provides a function as much as the previous Java version. However, different from the latter is that the EM now supports updated Oracle database functions. For example, EM can now process sub-partitions.
However, experienced DBA hopes that this tool can do more work - especially in terms of fault diagnosis or active performance. Let us give an example. Recalling our database in the "Application" waiting class in the "Application" waiting class, such as the database home page (Figure 3), and we need to diagnose the reasons. One of the key things that need to be understood during any adjustment process is how many components (such as CPU, disk, and host subsystems) are interacting, which helps to observe all of these variables in the context environment. To do this, select the "Performance" tab in the database home page. This operation calls up the screen shown in Fig. 7. Figure 7: "Performance" tab
Note that all measures have been aligned on the same time axis, which easily observes their interdependence. Pay attention to spike (3), which corresponds to the scheduler task. It shows that there are about seven sessions at this moment, waiting for a wait event related to the scheduler. So what is the influencing factor? Note that the CPU metric in the same position (green area) - they show the maximum CPU usage that has been used, expressed in the dashed line (4) in the graph. Before and after this point, we didn't see the CPU spikes, which provided a clue. Note that the cpu run queue length (1), which is the direct consequence of the scheduler, and the scheduler may have too much memory demand, resulting in an increase in paging activity (2). As you can see, all phenomena is set together to facilitate the understanding of the database load "Overview".
Note the spike ending at the time axis - increasing the running queue length (5) and the pages rate (6) - they are associated with another spike of physics (7). what is the reason?
Through comparison graphics "sessions: waiting and working" with spikes, we can see that most sessions are waiting for the "Application" waiting class. But we need to find out what it is waiting for it during this period? Click the area of this time, call up the active session screen, as shown in Figure 8.
Figure 8: Active session waiting
This screen shows the wait event waiting for the session is ENQ: TX? Row Lock Contention. So what is the SQL statement of this issue? Very simple: The screen itself shows the SQL ID of the statement 8RKQUK6U9FMD0 (in the red circle). Click the SQL ID to call the SQL screen as shown in Figure 9.
Figure 9: SQL details
On this screen, you can see the SQL statement about it and related details, including execution plans. It shows that this SQL leads to row lock, so the application design may be the root of the problem.
Bolt
Assume that click the "Performance" tab appears like the screen shown in Fig. 10.
Figure 10: "Performance" tab, example 2
In the figure, please pay attention to the highlighted measure in the red rectangle. You can see a lot of Wait at around 12:20 AM, which causes a huge run queue in the CPU. We need to diagnose this waiting.
First, click on the graph showing the CPU contention area ("Click Here" on the graph is marked) to view this particular waiting, as shown in Figure 11.
Figure 11: Activity session waiting
Note box (1) with shadows in the "Active Sessions Working: CPU Used" graphic. You can use the mouse to drag it to place the focus. This operation results in calculations only in the period incorporated by the following. Here we see, a specific SQL with ID 8GGW94H7MVXD7 is very difficult (2). We also see that a user session with username ARUP and SID 265 is the most important run session (3). Click this session to view its details. This action calls up the "Session Details" screen. Click Tab "Wait Events" to call the details of the wait event experienced by the session, which is similar to that shown in Figure 12. Figure 12: Details waiting for the event
In this picture, please note the longest wait for the longest waiting in the red circle of 118%, it is waiting for the library cache. When you click the hyperlink of "Latch: library cache", you will see the screen shown in Figure 13.
Figure 13: Waiting histogram
This screen provides some unique information available before the 10 G database. When diagnosing this bolt contention problem, how to know the 118% seconds of waiting is made by multiple sessions, or just a big waiting consisting of a session, so that the data is deviated?
Here, histograms can help us. From the figure, you know about 250 sessions have 1 millisecond (highlighted in the circle). A session is waiting for about 180 times a place between 4 and 8 milliseconds. This screen shows that these waiting time is usually very short, so they are not the main symptoms of suppression.
On the database main page, you can access AddM, SQL Access Advisor, and other advisers by clicking tabs with the ADVISOR Central. The AddM is automatically running when collecting metrics, and the results are released in the Advisor Central page; when clicking this page, the recommendations given by AddM will be displayed. SQL TUNING ADVISOR also checks these metrics and publishes its suggestions on this page. (We will study addM and SQL Tuning Advisor more detail in future articles.)
Simplify maintenance
The tab of the database homepage is the "Maintenance" tab is a common maintenance activity - such as backup and recovery, data export or import (data pump), database cloning, and more activities - start console. On this screen, you can edit the basic principles of the best practice based on the policy verification alert.
in conclusion
As mentioned earlier, this article involved is only a huge iceberg. In this article, my purpose is not to provide a comprehensive overview; but hoped to provide a quick browsing of some specific activities across multiple skills sets.
Oracle 10 G EM provides sufficient resources for DBA newers to quickly understand the subtleties of Oracle database management. A good outline of the task and technology using EM is Oracle "Two-Day Express DBA" Reference Manual. I strongly recommend that you read it, especially when you have just started learning.
