Setting thresholds and baselines in Oracle 10g

Learning objective

After completing this topic, you should be able to set thresholds and baselines for performance metrics.

1. Server-generated alerts

Oracle Database 10 g provides problem alerts via the Enterprise Manager Database Console.

You can configure Enterprise Manager to e-mail you about problems and to display alert data on the console.

You can also set thresholds on key system metrics.

If the database readings approach threshold values, Oracle 10 g notifies you.

The early notification enables you to resolve potential problems for users.

Question

Which of these are characteristics of server-generated alerts?

Options:

0. Automatic e-mail alerts from Enterprise Manager
0. Notification from Oracle Database 10 g when database readings approach threshold values
0. Prediction of problems and how they will affect users

Answer

Server-generated alerts can notify you early about potential problems, such as database readings approaching threshold values.

Option 1 is correct. You must configure Enterprise Manager to e-mail you about problems - it does not do so by default.

Option 2 is correct. You can set thresholds on key system metrics to obtain alerts when those thresholds are reached.

Option 3 is incorrect. Early notification enables you to resolve problems before they trouble users, but alerts can only register and warn of emerging problems, not predict them or how they may affect users.

Thresholds are boundary values with which monitored metric values are compared.

Metrics that can provide thresholds for early problem notification include

• Average File Read Time (centiseconds)
• Dump Area Used (%)
• Response Time (per transaction)
• SQL Response Time (%)
• Tablespace Used (%)
• Wait Time (%)

You can assign these two thresholds to each metric:

• warning thresholds
  
• critical alerts

warning thresholds

You use warning thresholds to provide advance notification when performance trends start to deviate from normal activity.

You are not required to perform any action when you receive performance warnings.

Their purpose is to alert you to monitor the situation more carefully.

critical alerts

You use critical alerts to specify problems that require your immediate attention.

You can configure automated response actions to activate when critical thresholds are reached.

Your aim should be to solve problems before the critical thresholds are reached.

Question

Match the critical and warning thresholds to their definitions and appropriate responses.

Options:

0. Provide advance notification when performance trends start to deviate from normal activity.
0. Specify problems that require your immediate attention.
   
0. Require no action
0. Activate automated response actions

Targets:

0. Critical
0. Warning

Answer

Critical thresholds specify problems that require your immediate attention, and can activate automatic response actions.

Warning thresholds provide advance notification when performance starts to deviate from normal. They require no action other than a closer monitoring of the situation.

2. Setting thresholds

You can define thresholds by either

• setting the amount of space consumed in a tablespace through the Edit Tablespace page
• setting thresholds for general performance metrics through the Edit Thresholds page.

Suppose you want to configure your system to alert you when free space on the SALES tablespace falls below 25 percent.

In Enterprise Manager, you navigate to the Administration tabbed page and then click the Tablespaces link in the Storage section.

In the Tablespaces page, you run a search for the SALES tablespace by entering SALES in the Name field, and clicking Go.

The tablespace is returned as the only result of your search.

It is already selected by default, so you click Edit to modify it.

In the Edit Tablespace: SALES page, you must navigate to the tabbed page that enables you to specify thresholds.

You click the Thresholds tab.

In the Thresholds tabbed page, you want to define the thresholds.

You select Specify Thresholds, by percent used option.

You want to create a warning threshold that alerts you when 70 percent of the space is used up.

To do this, you enter 70 in the Warning (%) field.

Next you want to create a critical threshold for the tablespace.

You type 75 in the Critical (%) field and then click Apply.

An update message confirms that you've modified the SALES tablespace successfully.

Suppose you now want to set thresholds in a more general way.

You want to configure your system to alert you when large pool and java pool free memory fall below 10 percent.

To do this, you navigate to the Related Links section at the bottom of the Administration tabbed page, and click Manage Metrics.

In the Manage Metrics page, you want to navigate to the page where you configure the metrics.

You click Edit Thresholds.

In the Edit Thresholds page, you want to set a warning threshold of 20 percent and a critical threshold of 10 percent on two metrics.

You scroll down to the metrics you want to configure - Java Pool Free (%) and Large Pool Free (%).

For both metrics, you enter 20 in the Warning Threshold field and 10 in the Critical Threshold field.

You scroll back to the top of the page, and click OK to confirm the changes.

A message informs you that your system has been updated.

Question

Suppose you want to set a metric threshold. In Enterprise Manager, you have navigated to the Related Links section at the bottom of the Administration tabbed page.

This question requires you to set a critical threshold to alert you when there is less than 10 percent space left in the Archive Area. Which option enables you to perform this action?

Options:

0. Under the Related Links heading of the Administration page, you click Manage Metrics. In the Manage Metrics page, you click Edit Thresholds. In the Edit Thresholds page, you type 90 in the Critical Threshold field for the Archive Area Used (%) metric, and click OK.
0. Under the Related Links heading of the Administration page, you click Manage Metrics. In the Manage Metrics page, you click Edit Threshold s. In the Edit Thresholds page, you type 10 in the Critical Threshold field, and click OK.

Answer

Under the Related Links heading of the Administration tabbed page, you click Manage Metrics. In the Manage Metrics page, you click Edit Threshold. In the Edit Thresholds page, you type 90 in the Critical Threshold field, and click OK.

3. Setting baselines

You can base thresholds for performance metrics on deviations from real target performance instead of absolute numbers.

For example, you can base thresholds on a given time when your database was performing a normal workload acceptably.

When performance deviates from those ideal metrics by a specified percentage, alerts are generated.

To capture average performance statistics, you use the Create Metric Baseline feature.

This enables you to capture average performance statistics for any hour within a 24-hour period or for the full 24-hour period.

It also calculates warning and critical deviations from average performance.

Oracle 10 g applies threshold values calculated from the baseline to all metrics contained within the baseline.

If required, you can edit the threshold values for individual metrics.

Question

Which of these are characteristics of baselines?

Options:

0. Threshold values calculated from the baseline are applied for all metrics specified in the baseline
   
   
0. You can only base thresholds for performance metrics directly on absolute numbers
0. You can use them to capture average performance statistics
   
   

Answer

You can use baselines to capture average performance statistics. Threshold values calculated from the baseline are applied for all metrics specified in the baseline.

Option 1 is correct. If required, you can edit the threshold values for individual metrics.

Option 2 is incorrect. You can also base thresholds for performance metrics on percentage deviations from target performance.

Option 3 is correct. The Create Metric Baseline feature enables you to select metrics for any hour within a 24-hour period or the full 24-hour period.

Suppose you want to establish a baseline to monitor the wait-time percentage.

In Enterprise Manager, you navigate to the Related Links region at the bottom
of the Administration tabbed page, and click Manage Metrics.

In the Manage Metrics page, you want to navigate to the page where you can specify a baseline.

You click the Metric Baselines tab, and then click Create.

In the Create Metric Baseline page, you specify the details of the baseline.

You enter WaitBaseline as the name.

Then you enter a value for yesterday's date in the Date field - in this case, 12-Aug- 20 04.

You select 10 in the Hour of day drop-down list, and then click the PM option.

You enter 10 as your warning percentage.

Next you want to enter the critical percentage.

You type 25 in the Critical Percentage field, and click Go.

Enterprise Manager displays the list of available metrics.

You click the Select None link to deselect all metrics.

If you want to reselect all metrics, you click Select All, which is also the default.

Once you have deselected all the metrics, you scroll to the bottom of the page.

You select Wait Time (%) and click OK.

You have created a baseline using the wait-time percentage.

After you have created a baseline, you can apply a new baseline by clicking Copy Thresholds From Metric Baseline.

When you click the Copy Thresholds From Metric Baseline button, the Edit Thresholds page appears.

Here you can modify the warning and critical thresholds if needed, and then set the baseline thresholds as the current thresholds.

Question

Suppose you want to set a baseline. In Enterprise Manager, you have already navigated to the Create Metric Baseline page.

This question requires you to define a baseline for all metrics called AllBaseline. You must type 12-Aug-04 as the given date and specify 9am as the given hour. Then you must configure the system so that thresholds are calculated from a warning percentage of 75% and a critical percentage of 80%. Which option enables you to do this?

Options:

0. In the Create Metric Baseline page, you type AllBaseline in the Name field. You type 12-Aug-04 in the Date field and select 9 from the Hour of day drop-down list. You type 25 in the Warning Percentage field and 20 in the Critical Percentage field. Then you click Go and then OK.
0. In the Create Metric Baseline page, you type AllBaseline in the Name field. You type 12-Aug-04 in the Date field and select 9 from the Hour of day drop-down list. You type 75 in the Warning Percentage field and 80 in the Critical Percentage field. Then you click Go and then click OK.

Answer

In the Create Metric Baseline page, you type AllBaseline in the Name field. You type 12-Aug-04 in the Date field and select 9 from the Hour of day drop-down list. You type 75 in the Warning Percentage field and 80 in the Critical Percentage field. Then you click Go and finally click OK.

Summary

You can resolve system problems by configuring server-generated alerts in Enterprise Manager. Thresholds are boundary values with which monitored metric values are compared. Warning thresholds give you notice when performance starts to deviate from normal. Critical alerts indicate problems that require immediate attention.

You can base thresholds for performance metrics on deviations from target performance instead of absolute numbers. Alerts are generated when performance deviates from preset ideal metrics by a specified percentage. Oracle 10 g applies threshold values calculated from the baseline to all metrics specified in the baseline, but you can edit the threshold values for individual metrics if required.