The ADDM utility also provides several tuning and diagnostic advisors that you can use to examine several common problem areas in your database and then offer suggestions for improving those areas. The diagnostic and tuning advisors include the following: SQL Tuning Advisor SQL Access Advisor Memory Advisor Mean Time To Recover Advisor Segment Advisor Undo Management Advisor
The links to all these advisors are available by clicking the Advisor Central link at the bottom of the EM Database Control main screen.
The SQL Tuning Advisor As you saw earlier, the ADDM utility allows you to drill down and view the actual SQL of the statements that are contributing to increasing DB Times. Once the SQL has been identified, you can use the SQL Tuning Advisor to attempt to formulate more efficient SQL execution plans for the offending SQL.
There are four options for the SQL Tuning Advisor: Top SQL, SQL Tuning Sets, Snapshots, and Preserved Snapshot Sets.
Types of Analysis for the SQL Tuning Advisor Collection Method Current And Recent SQL Activity
Import Workload From SQL Repository
Import SQL From A Table
The following examples use the Top SQL option of the SQL Tuning Advisor. You can view the Top SQL statements in two ways, Spot SQL and Period SQL; each are represented by a tab in the Top SQL screen.
AWR automatically assigns a system-generated name to each SQL statement that is recorded. These names are a combination of 13 numbers and lowercase letters.
Spot SQL graphically displays all the resource wait, I/O, and CPU statistics for SQL statements that have been active in the most recent five-minute interval. By examining the graphical output, you can readily identify which SQL statements caused spikes in these three areas.
between two points in time. By examining the graphical output on this screen, you can also easily identify which periods of time experienced spikes in the areas of resource waits, I/O, and CPU.
Regardless of whether you isolate your problem SQL statements using ADDM, Spot SQL, or Period SQL, the process of analyzing that SQL using the SQL Advisor is the same. First, you need to decide which statement you wish to analyze and then click the Run SQL Tuning Advisor button on the SQL Details page after selecting that statement from ADDM, Spot SQL, or Period SQL.
Clicking the Run SQL Tuning Advisor button opens the Schedule Advisor screen. You use this screen to formulate the job that will be submitted to the database when the advisor is actually executed. You specify three elements when running the SQL Tuning Advisor: Description, Scope, and Schedule. Each of these elements is described in the following table.
|Top SQL||Allows to identify and tune the most resource-intensive SQL statements|
|SQL Tuning Sets||Allows to group several related SQL statements together for analysis|
|Snapshots||Allows to select a specific snapshot to analyze|
|Preserved Snapshot Sets||Allows to create and analyze a collection of related snapshots|
Lets you specify a schema against which to generate SQL tuning recommendations Select the Current And Recent SQL Activity radio button and click Next to display the Recommendation Options screen.
The Advisor can focus its efforts on three options.
Because Comprehensive tuning analysis can consume a lot of server resources, schedule it when user activity against the database is at its lowest.
Clicking Next after selecting the analysis options displays the scheduling options. Like previous EM Database Control scheduling screens, the time that the analysis will begin, its duration, and its frequency can all be defined on this page.
Finally, clicking Next displays the SQL Access Advisor: Review screen.
This screen displays all the options that you specified before clicking Submit to actually begin the analysis.
When the analysis is complete, you return to the Advisor Central screen and a link to the results of the analysis is displayed at the bottom of the screen.
Click the link in the completed analysis table to display the recommendations that the SQL Access Advisor created for SQL statements that were analyzed.
Clicking Recommendation ID (1) displays the details of the SQL Access Advisor recommendation.
This recommendation indicates that building an index will improve the access path of the SQL statement shown at the bottom of the screen. You can change the default index name of _IDX$$_02930001 suggested by the advisor by typing a new name in its place. Additionally, you can also specify the tablespace where the new index should be stored. Clicking OK returns you to the recommendation summary screen. Click the Schedule Implementation button to schedule the actual creation of the index.
The Memory Advisor Both the SQL Tuning and Access advisors focus on identifying and tuning the SQL that is having the greatest impact on increasing overall DB Time. Alternatively, you can use the Memory Advisor to gather more global tuning recommendations about all aspects of Oracle’s memory structures, including the SGA and user memory structures. You can also access the Memory Advisor from the Advisor Central screen.
The sizes of four components of the SGA—the Shared Pool, Buffer Cache, Large Pool, and Java Pool—are summarized in tabular and in graphical form. Clicking the Advice buttons next to the Shared Pool and Buffer Cache values tells the Memory Advisor to formulate tuning recommendations for that memory structure.
This output shows that as the size of the buffer cache is increased from its current size of 24MB to the recommended size of 48MB, the overall physical reads are reduced by 20 percent (a relative decline from 1.0 to 0.80). This would be beneficial because reads from memory are thousands of times faster than reads from disk.
If you enable Automatic Shared Memory Management the ADDM can automatically adjust the size of the Buffer Cache based on its findings.
The main Memory Advisor screen also has a second tab that shows the results of the advisor’s analysis of the PGA (Program Global Area) memory that is allocated to each user process.
Like the SGA screen, the PGA recommendations screen also has an Advice button; click it to view the Memory Advisor’s recommendations for improving PGA performance.
Using cache hit ratios as the primary basis for performance analysis is not recommended. In several situations, high cache hit ratios can exist even when overall performance is poor.
The Mean Time To Recover (MTTR) Advisor The preceding advisors focused primarily on improving database performance by minimizing user waits for I/O, CPU, and other resources with the goal of minimizing each user’s overall DB Time. The Mean Time To Recover (MTTR) Advisor is not concerned with minimizing DB Time, but instead tries to formulate recommendations that minimize the time it takes to perform instance recovery in the case of instance failure. Instance failure can occur when the host server crashes, when any critical SGA background process fails, or if the instance is shut down using the ABORT option. Instance recovery occurs automatically on the first startup following the instance failure. During instance recovery, Oracle uses the undo segments and online redo logs to roll back any uncommitted transactions that were "in flight" when the instance crashed to ensure that all committed transactions are written to disk. As a DBA, you often try to minimize the time it takes to perform this instance recovery so that the database can be brought up quickly.
In previous Oracle releases, you could use the FAST_START_MTTR_TARGET initialization parameter to specify the maximum allowable instance recovery time (in seconds). This parameter must be set to a non-zero value for the new features described next to work.
The MTTR Advisor analyzes the database during regular processing and makes recommendations about database and instance parameters that can be modified in order to meet your instance recovery goals.
If this MTTR is not acceptable because a Service Level Agreement (SLA) requires a 15- second instance recovery time, then you can specify a new MTTR value of 15 in the Desired Mean Time To Recover box.
The MTTR Advisor screen also has Media Recovery and Flash Recovery sections, which are described in more detail in Chapter 10, "Implementing Database Backups" and Chapter 11, "Implementing Database Recovery."
The Segment Advisor You use the Segment Advisor to identify segments that might benefit from a shrink operation. Segments that can be shrunk are those that the Segment Advisor has found to be needing less space than they are currently allocated. By shrinking or compressing these segments, space is returned to the database for use by other objects, and the total number of I/Os needed to access these objects is reduced, potentially improving the performance of SQL statements that access these objects.
You can analyze potentially compressible segments either at the segment level or at the tablespace level. In addition, you can also specify the degree to which the segments are examined at two levels: Limited and Comprehensive. If you select the Schema Objects and Limited options and then click Continue, the Segment Advisor: Schema Objects screen.
Initially, no segments are listed for analysis, but click the Add button to specify which segments you want the Segment Advisor to examine.
You can specify how much time the Segment Advisor can take when analyzing the specified segments and how long to store the results of the analysis in the repository. Click Next on this and subsequent screens to display the familiar job scheduling and submissions screens. Once the analysis of the selected segments or tablespaces is complete, the results are displayed at the bottom of the Advisor Central screen, along with all other submitted job results. Click the Segment Advisor Job link to display the Segment Advisor Task screen.
The output on this screen shows that the SUPPLEMENTARY_DEMOGRAPHICS table owned by the user SH has been identified as a segment that will benefit from a shrink operation, reducing its allocated space from 4MB to the 1.193MB that the table actually needs to store its data. When shrinking an object identified by the Segment Advisor, there are two shrink options: Compact Segments and Compact Segments And Release Space.
The Compact Segments option compacts the rows in the SUPPLEMENTARY_DEMOGRAPHICS table, but does not release the newly freed space back to the tablespace. This option allows you to put off the more resource-intensive operation of actually releasing the space until later.
The Compact Segments And Release Space option compacts the space in the SUPPLEMENTARY_DEMOGRAPHICS table and also releases the unused space back to the tablespace at the same time. Choosing either of these two compression options displays the familiar job submission screen, which submits the compaction job in the background.
If you have a table that has been compressed using the Compact Segments option and thus does not have its space released, you can later release this space using the SHRINK SPACE option of the ALTER TABLE command, for example: ALTER TABLE supplementary_demographics SHRINK SPACE.
In order for the Segment Advisor to modify segments effectively, you need to enable the ROW MOVEMENT attribute of the affected segments. You can do so using the Options tab in the Edit Table screen or the ALTER TABLE … ENABLE ROW MOVEMENT command.
The Undo Management Advisor The Undo Management Advisor helps you monitor and proactively respond to potential problems in a common trouble area of any transactional database system: undo segments. When a user starts a DML (Data Manipulation Language) transaction, the before-image of the changed data is buffered in the Database Buffer Cache in the SGA. Copies of these buffers are also written to an undo segment, which is stored in the database’s undo tablespace. The before-image data stored in the undo segment is used for three important purposes:
It can be used to restore the original state of the data if the user performing the DML command issues a ROLLBACK command. It provides a read-consistent view of the changed data to other users who access the same data prior to the DML user issuing a COMMIT command. It is used during instance recovery to undo uncommitted transactions that were in progress just prior to an instance failure.
In previous releases of Oracle, undo segments were referred to as rollback segments because they are used to roll back a transaction when a ROLLBACK command is issued. However, this term is now generally used to refer to manually managed undo segments, not the system managed undo segments that Oracle recommends be used in Oracle 10g.
Once a transaction is assigned to an undo segment, the transaction never switches to a different undo segment, even if the original undo segment was not the most appropriate choice. Because of this, undo segment tuning can be one of the most elusive aspects of database administration. Even when undo activity has reached a steady state and no problems are apparent, the right combination of transactions can cause an undo segment error. This can lead to frustrating undo segment–tuning problems that never completely go away. The goals of undo segment tuning usually ensure the following:
That database users always find an undo segment to store their transaction before- images without experiencing a wait That database users always get the read-consistent view that they need to complete their transactions That the database undo segments do not cause unnecessary I/O Every database contains at least one undo segment, which is the SYSTEM undo segment. This undo segment is used only for data dictionary read consistency and transaction control.
The most common undo segment–related error message is ORA-01555: Snapshot Too Old. This error can occasionally occur when some users are running long queries and others are simultaneously modifying the data being queried. This scenario can cause the session running the long-running query to be unable to build a read- consistent view of the database, thus causing the ORA-01555 error message.
The Undo Management Advisor helps prevent undo-related problems in the database by monitoring and analyzing undo activity before making recommendations for improving undo performance.
To open the Undo Management Advisor, click the Undo Management link in the Advisor Central screen in EM Database Control.
To meet this undo retention period, the Undo Management Advisor recommends increasing the size of the undo tablespace to 703MB. For details, click the Undo Advisor button to display the screen.
This output graphically shows how the undo retention time (in minutes along the bottom of the graph) increases to 16 minutes if the size of the undo tablespace increases to 431MB. The graph also shows that the best possible undo retention time that can be obtained with the current undo tablespace size of 200MB is 6 minutes. Implementing these recommendations will minimize database management and performance problems related to undo segments.
ADDM Alerts In addition to monitoring and making recommendations on SQL, memory, mean time to recover, segments, and undo activity, ADDM can also be used to proactively monitor the database for other types of problems related to memory, I/O, and CPU utilization, as well as security and space management. To do so, you use ADDM alerts.
ADDM alerts are also an integral part of the ADDM architecture. They notify you when a management or performance issue occurs and begin taking corrective actions—if you configured such actions. By default, the alert notifications are sent to the EM Database Control main screen.
You can also configure alerts so that they are sent to you via e-mail. To do so, click the Setup link at the top of the EM Database Control screen to display the Setup screen. Click the Notification Methods link on the left to open the Notification Methods screen.
You’ll need to supply three pieces of information: The IP address of your network’s SMTP mail server The name of the user from whom the e-mail address will be sent The e-mail address of the user sending the notification e-mails
smtp.acme.com is the server through which EM Database Control will send the ADDM alert e-mails. The name of the user from whom the e-mail address will be sent is shown as OEM. The e-mail address of the user who will be sending the notification e-mails appears in the From box when you receive an alert notification via e-mail.
After you add the e-mail configuration entries, click Apply to save them.
Click the Test Mail Servers button on the Notification Methods screen to confirm that the configuration you’ve entered is correct.
Once EM Database Control knows how to send notification e-mails when ADDM alert events occur, you need to tell EM Database Control to whom the notification e-mails should be sent. For the user SYS, click the Preferences link at the top of the EM Database Control main screen. To configure e-mail notification information for other users, click the Edit button on the Administrators screen.
You can then enter the e-mail address of the DBA who should receive the notification in the box under the E-mail Address column before clicking Apply to save the change. Click the Test button to send a test e-mail message to the address supplied and confirm the e-mail connectivity between EM Database Control and the DBA’s e-mail address.
You can also set the message format, long or short, at this time. The short format is useful when you are e-mailing the notifications to a text pager or a cell phone.
After you configure the notification methods, alerts are sent to both the EM Database Control main screen and to the e-mail address specified.
An alert is triggered whenever a monitored event occurs or when a specified database threshold, called a metric, is surpassed. Metrics are the statistical performance measurements that are collected and stored in the AWR repository. The ADDM utility then gathers additional database statistics and compares them against the baseline metrics in order to monitor, diagnose, and remedy management problems or poor database performance. There are four default ADDM alerts configured in each database as described in the following table. Oracle 10g has several additional predefined ADDM alerts, which require a small amount of additional configuration before using. These alerts are defined on the Manage Metrics screen of the EM Database Control. To open this screen, click the Manage Metrics link at the bottom of the EM Database Control main screen.
Some of the alerts include the following:
The archive destination is more than 80 percent full. The archive process is hung and returns an error message. The superuser SYS is connecting to the database.
The two alert levels Warning and Critical allow you to achieve greater granularity. For example, you might want two thresholds set up with regard to the archive destination. One might be a warning threshold that triggers an alert when the archive destination is 80 percent full—causing a message to be displayed on the EM Database Control main screen. In addition, you might want to set up a critical threshold so that you receive an e-mail whenever the archive destination device is 90 percent full. In this manner, you can escalate a potential problem from an EM Database Control console message to an e-mail alert as the problem gets worse.
You can also use warning and critical alerts to distinguish between lower severity problems, such as statistics indicating temporary poor performance, and higher severity problems, such as ORA-0600 error messages in the database Alert log. You can achieve this by defining only warning thresholds for lower severity alerts and defining warning and critical alerts for higher severity problems.
Instead of specifying a set value, such as 50 percent and 75 percent for CPU utilization alert levels, you can also raise alerts when CPU utilization exceeds a baseline metric. Baseline metrics are gathered during a period of processing that represents normal database activity. Using these baselines, you can raise alerts when relative performance problems occur. For example, rather than raising an alert when the CPU utilization is 50 percent of the available CPU cycles, you can raise an alert when CPU utilization is 50 percent more than the baseline CPU utilization—which itself could be 85 percent of CPU cycles.
To gather baseline metrics, use the Options link on the Metric Baselines tab in the Manage Metrics screen.
The output shows that no metric baselines have yet been gathered. The AWR can store several baselines, any of which can be used as the basis for the alert system. To gather the first baseline metric, click Create to display the Create Metric Baseline screen.
Using the Create Metric Baseline screen, you can minimally assign a name and date to the baseline statistics that will be gathered. Optionally, you can also assign a time of day, warning, and alert thresholds.
If no value is supplied for Hour Of Day, baseline statistics are gathered for the entire 24- hour period for the date specified.
The Metric Baselines tab in the Manage Metrics screen Click Go to capture the current database metrics and then display them at the bottom of the Create Metric Baseline screen.
Using this screen, you can deselect any metrics that you don’t want to include in the baseline (all metrics are included by default) or modify the baseline values that were used. Once you tailored these metrics to your needs, you can store them by clicking OK at the top of the page, creating the baseline metric called Initial Server Stats.
The first column shows the name of the metric being monitored, the second column displays the warning values, and the third column displays the critical values that were calculated for that threshold. For example, the baseline warning threshold for the metric Current Open Cursors Count is 784, and the critical threshold is 826.
Computation of Thresholds by ADDM: When baseline metrics are gathered, the AWR stores the high value and the low value for each metric. The technique that ADDM uses to compute a baseline threshold depends on the comparison operator that is used for specifying the threshold, and on the associated high and low value.
If the metric comparison operator is a greater than (>), the warning threshold for the metric is computed as Metric High Value * (1 + Warning Percentage/100). For example, if the high value for the metric Current Open Cursors Count is 500, the 85 percent warning threshold is 500 * (1 + 85/100) or 925, raising a warning alert whenever the Current Open Cursors Count exceeds 925. Likewise, the critical threshold is 500 * (1 + 95/100) or 975, raising a critical alert whenever the Current Open Cursors Count exceeds 975.
If the metric comparison operator is a less than (<), the warning threshold for the metric is computed as Metric Low Value * (1 + Warning %/100). For example, if the low value for the metric Large Pool Free % is 300MB, the 85 percent warning threshold is 300 * (1 – 85/100) or 45MB, raising a warning alert whenever the percentage of free space in the Large Pool falls below 45MB. Likewise, the critical threshold is 300 * (1 – 95/100) or 15, raising a critical alert whenever the free space in the Large Pool falls below 15 percent.
Because more than one baseline can be stored in the AWR at one time, EM Database Control gives you a way to choose the baseline that you want to use for alert thresholds. Suppose you have the two baselines.
To use the metrics associated with the Overnight Processing baseline as the basis for ADDM alerts, simply select that option and click the Copy Thresholds From Metric Baseline button. EM Database Control gives you an opportunity to modify selected metric parameters if needed, before you update the threshold values and see the screen. Job Element Description
Schedule Default ADDM Alerts
Alert Description Tablespace Space Usage Alerts you whenever a tablespace’s free space falls below 15 percent and again when it falls below 3 percent. Snapshot Too Old Alerts you whenever the ORA-01555 error message described earlier) occurs. Recovery Area Low On Free Space Alerts you when the Flash Recovery area is low on free space. Resumable Session Suspended Alerts you whenever an operation that can be resumed goes into a suspended state. Description Description of the SQL statement that is being analyzed. Although this is an optional element for submitting a job, it is useful because otherwise Oracle uses a system-generated name for the SQL statement— 85zq7jwf3x3qg.
The depth to which the advisor should examine the statement. Possible values are Limited and Comprehensive. The deeper the analysis, the greater the potential for uncovering additional tuning options. You can specify the maximum time that the advisor should spend performing a comprehensive analysis.
Specifies when to execute the analysis job. The default value is Immediately, but you can also schedule the job to execute at some time in the future. Description Lets you select the SQL to analyze from what is currently in the SGA
Lets you analyze a SQL tuning set that was created using the SQL Tuning Advisor User-Defined Workload;
Lets you perform a tuning analysis on a workload that is not currently running in the database. After you specify the job submission elements, click OK to begin analyzing the specified statement.
Choosing the Comprehensive level of analysis can be time-consuming and resource intensive.
To review the SQL that will be issued to create this index, click the Show SQL button. If desired, you can modify the generated SQL to rename the index or change its storage parameters. Otherwise, clicking OK submits the job to create the new index. Like the SQL Tuning Advisor, the job can be executed immediately or scheduled to run some time in the future.
Like the SQL identifier, each recommendation is also assigned a system-generated ID made up of a combination of 13 numbers and lowercase letters.
The SQL Access Advisor You’ve seen how you can use the SQL Tuning Advisor to identify and create an index to minimize the DB Time for a particular statement. The SQL Access Advisor provides additional support for finding potential schema modifications that you can use to reduce the amount of I/O, CPU, and wait time for a given SQL statement.
The SQL Access Advisor shows four ways in which you can select the SQL statement to be analyzed. The following table compares these four techniques.
Comparison of Four Techniques for Selecting SQL to Analyze