Shulou(Shulou.com)06/01 Report--

Measurement, early warning and baseline

When monitoring performance, you need to use specific information other than statistics. In order to determine whether a particular statistic is important, you need to know how much the statistics have changed over a specific period of time. In order to be forward-looking, you need to be notified when certain conditions exist, such as when the system response time reaches the maximum allowable value. In order to diagnose performance problems, you need to know the details of the changes. Measurements, early warning and baselines can provide this information.

A metric is the rate at which cumulative statistics change over a period of time. For example, physical reads per second.

Thresholds can be set for various metrics, and an early warning is generated when the value of the metric exceeds the threshold.

The baseline contains the stored set of measures and statistics. A single set is called a snapshot. The baseline consists of two or more snapshots. Typically, the baseline is captured during a normal or acceptable operation, but any relevant period of time can also be captured. If the performance is not normal, other metric sets can be captured and compared to the baseline. In this way, the data can clearly indicate performance problems. Restrictions on basic statistical information

Statistics are a count of events that occur in the database. Statistics and wait events are raw data. Basic statistics are always just a value for a given time.

If you draw basic statistics over a long period of time, you will see that these values have been increasing over time. This slide shows an example of a possible graph, the physical reads statistics graph extracted from the V$SYSSTAT performance view. As shown in the slide, although the statistical values of the two graphs are the same, the trends are completely different at the end of the observation period. You can see very clearly that the rate of change of the database Iswap O in the above figure is much higher than that in the following figure. Therefore, it doesn't make sense to just look at the statistics.

To better understand database behavior, you need to look not only at the values, but also at these curves or trends. Therefore, it is necessary to calculate the rate of change of statistical values in a certain period of time in order to determine the trend during that period.

Typical incremental tool

The basic statistics are only the original numbers accumulated since the instance was started. A snapshot is a set of statistics captured at a point in time. Meaningful statistical values can be obtained by taking snapshots at different times and obtaining the difference between values. The difference is the increment. There are several tools that can generate increments. Statspack, automatic workload data Archive (AWR), and custom scripts generate incremental reports for two snapshots.

Snapshot sets can be saved through AWR and Statspack for future reference. These saved sets are statistical baselines. Oracle Database 11g solution: metric

The Oracle database server collects basic statistics during normal operation. The basic statistics are simple counts. For example, the result of counting physical reads in the system since startup is basic statistics.

Metrics are secondary statistics derived from basic statistics. Most metrics track the change rate of activity in the Oracle server. For example, the average number of physical reads in the first 60 minutes of the system is a metric. Internal components (clients) use metrics to monitor system health, detect problems, and self-optimize. The manageability Monitor process (MMON) periodically updates the metrics based on the corresponding basic statistics.

The Oracle database server component uses metrics to perform manageability functions. For example, the automatic database diagnostic monitor (ADDM) uses the average physical reads in the last 60 minutes of the system as input. Other components may require different metrics based on the same basic statistics (physical readings). For example, a memory guide might require a physical read count during peak hours. Oracle Database 11g supports the measurement of system, session, file, and wait event statistics. Each metric is uniquely identified by a metric number and is associated with a metric name. The figure lists some fixed views that can be accessed to browse the measurement data. For more information about these views, see the Oracle Database Reference guide. Advantages of measurement

The main advantage of retaining metrics is that the data can be used at any time when the component needs to calculate the change rate for certain activities.

In previous releases, to calculate the change rate for specific basic statistics, the statistics had to be captured before and after the running workload. With metrics, all you need to do is run the workload and select the appropriate metrics.

With the metrics collected by the server, the server component has the basis for self-optimization and health check. Metrics provide the performance information required by automatic memory management and automatic database diagnostic monitors.

View metric history information

Metrics are displayed in some V$ views, where these values are averages over a very small interval. These intervals may vary (from 15 seconds to 10 minutes), depending on the category of measurement. A snapshot of the data in the V$ view is retained in the DBA_HIST table.

The slide lists some metrics and AWR views.

For example:

V$SYSMETRIC_HISTORY shows all the system metrics available in the database. This view shows both metrics of longer duration (60 seconds, 1 hour history) and shorter duration (15 seconds, only one interval).

DBA_HIST_SESSMETRIC_HISTORY shows the history of several important session metrics. It contains a sample (snapshot) of the V$SYSMETRIC_HISTORY view.

For more information about these views and column details, see "Oracle Database Reference". Use EM to view measurement details

Use the All Metrics page to view a list of all performance metrics available to the database. You can access this page from the Database home page by clicking the All Metrics link in the Related Links section.

On the All Metrics page, you can expand all or specific metrics groups to view specific metrics. By selecting a measure, the page for that measure can be displayed. You can view measures for a specific period of time, and you can customize this view. The corresponding graph displays the history of the measure.

To access this page from the Database home page, click All Metrics in the Related Links section. On the All Metrics page, expand Waits by Wait Class (wait by wait class), and then click Database Time Spent Waiting (%) (% of database time spent waiting). On the Database Time Spent Waiting (%) (percentage of database time spent waiting) page, click Concurrency. Statistical information histogram

While measurement allows you to understand the trends of specific statistics, it cannot tell whether a particular bottleneck affects locally or the system as a whole. For example, you may observe a metric with a large rate of change, but this sudden increase may be limited to one or two sessions of the system. In this case, it may not be necessary to study the problem in depth. However, if this sudden growth has a full impact on the entire system, further investigation is needed. This information is available through the histogram performance view.

As shown in the slide, you can observe a sudden increase in the Imax O ratio. This information can be associated with the corresponding Imax O histogram found in V$FILE_HISTOGRAM. This view displays a histogram based on all single-block reads for each file. There is an interval in the histogram, measured in milliseconds, from 1 millisecond to 222 milliseconds (69.9 minutes). The value in each period of time is the number of times the system waits for that amount of time. For example, you can see from the slide that the system waited 5500 times to read blocks from disk, each time between 32 and 64 milliseconds. If the access time is usually less than 10 milliseconds, this is definitely a concern for the system and should be studied further. If you see a large number of times in a short wait period, you don't have to worry too much.

Metrics remind you of potential problems in advance. By using the histogram to examine carefully, it can be clearly determined whether there is a problem.

Histogram view

V$EVENT_HISTOGRAM displays a histogram of the number of waits based on the event.

V$FILE_HISTOGRAM displays a histogram based on all single-block reads for each file.

V$TEMP_HISTOGRAM displays a histogram of all single-block reads based on each temporary file.

A histogram can have the following intervals:

= 2 ^ 22 Ms.

You can also view the histogram statistics through Enterprise Manager. From the "Performance" page, click the legend area item of the "Active Sessions" chart, such as "User Imax O" to enter the "Active Sessions Waiting:User Imax O" page. Click the wait event name on the right side of the Active Sessions (active session) figure again to enter the corresponding Histogram for Wait Event (waiting event histogram) page. The slide shows the "Histogram for Wait Event:db file sequential read (histogram of waiting events: continuous reading of DB files)" diagram.

Note: the histogram is not populated unless the TIMED_STATISTICS initialization parameter is set to TRUE (the default value). TIMED_STATISTICS is automatically set when the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. Server-generated early warning

An early warning is a notification issued when a database is in an undesirable state and requires attention. By default, the Oracle database sends an alert through the Oracle Enterprise Manager Database Control that displays the alert. You can also choose to configure Oracle Enterprise Manager to send an email to the administrator to report the problem condition. The Oracle database server also maintains a history of metrics and alerts in the workload database.

An alert queue is a permanent multi-user queue that can be used by users who want to write custom alert handlers.

By default, thresholds for several major metrics are set, such as the threshold for "Tablespace Used (%) (tablespace used (%))". You can set thresholds for related metrics for your system. If the database deviates from the normal reading and exceeds these thresholds, Oracle Database 11g will proactively send an alert to notify you. Notifying potential problems in advance allows you to respond quickly and usually resolve them before the user notices them.

Some important metrics that can provide problem notification in advance are:

Average file read time (centiseconds)

Response time (per transaction)

SQL response time (%)

Wait time (%)

Database control usage model

The following is the basic usage model for server-generated alerts:

If desired, you can change the threshold setting for the server alert metric. You can do this by using database control or PL/SQL procedures.

Notification rules (such as e-mail addresses or blocking periods) can be established by using database controls.

After the alert is generated, the database control displays the alert on the alert page. The database control sends notifications to administrators who have registered to receive notifications.

After receiving the early warning, the problem can be corrected according to the recommendations given in the early warning.

Note: make sure that the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL. Set threshold

To set or edit the threshold, select Metrics and Policy Settings (Measurement and Policy Settings) in the Related Links area of the Database home page. If you want to change the threshold, enter the desired Warning Threshold (warning threshold) and Critical Threshold (critical threshold). Select the All Metrics view to add a threshold. When the database reaches the specified value, the corresponding warning will appear. Click the Edit button to specify additional response actions, or to add thresholds to individual objects under a set of alerts, such as the percentage of used thresholds on a specific tablespace.

Collection scheduling specifies the frequency of collection. Collection scheduling can be changed, but not for individual thresholds. The collection schedule is set for the measurement group. Click Collection schedule to edit the schedule or view the metrics collected using the schedule.

The Edit button is an icon in the form of a pencil or set of pencils. Click Edit in the form of a single pencil to change the threshold and the concurrency property of the metric. Using the Edit button in the form of multiple pencils, you can add thresholds to individual objects in the measure group, such as adding different thresholds on each tablespace. Create and test early warning

You can also set thresholds for specific objects.

Example: you receive a critical alert when you decide to use more than 75% of the space in the INVENTORY tablespace. (this tablespace does not allow its data files to be automatically extended.) To create and test the alert, perform the following steps:

1. In Oracle Enterprise Manager, navigate to the Tablespace page and set the desired threshold.

two。 In SQL*Plus, use CREATE TABLE... TABLESPACE... AS SELECT... Command to copy the existing table to the relevant tablespace. Continue to add rows to this table to fill the tablespace.

3. After receiving an error indicating that this table cannot be expanded, check the Database Instance home page for the associated alert.

Most alerts contain the name of the relevant guidance, which can be called for detailed advice. For each corresponding warning message, the database control provides a link to invoke the appropriate guidance.

Note: evaluate the "tablespace full" warning every 10 minutes. Measurement view and early warning view

When enabled, MMON periodically calculates metrics and keeps them in memory for an hour. System-level metrics in memory can be viewed through the V$SYSMETRIC view and the V$SYSMETRIC_HISTORY view. A similar view can be used for service level metrics.

Disk metrics collection can be enabled for all these metrics simply by enabling AWR's automatic snapshot mechanism. The disk values of the metrics can be viewed from the DBA_HIST_* view. The purge policy for measuring history is the same as for other snapshot data.

You can access information about server alerts through the following dictionary views:

DBA_OUTSTANDING_ALERTS describes alerts that the Oracle database server considers to be in an unhandled state.

DBA_ALERT_HISTORY represents the history of alerts that are no longer in an unprocessed state within a limited time.

DBA_THRESHOLDS provides the threshold settings defined for this instance.

V$ALERT_TYPES provides information about each server alert type.

Note: for more information about these views, see the "Oracle Database Reference" guide. View user-defined SQL metrics

In addition to the metrics calculated by the server, EM calculates its own set of metrics. The User-Defined Metrics (user-defined metrics) page lists existing user-defined SQL metrics. You can view metric data; view, edit, or delete metric definitions; or create other user-defined SQL metrics based on existing metrics. If no user-defined SQL metrics are created, the list is empty.

You can customize the EM metrics set and create your own metrics that are checked by EM. To create your own metrics, open the User-Defined Metrics page and click Create. The link is in the "Related Links" section of the "Database" home page. Create user-defined SQL metrics

The User-Defined Metrics (user-defined Metrics) page allows you to define SQL metrics. The path to this page is Database Home > User-Defined Metrics (Related Links section) > Create (database home page > user-defined metrics (related links section) > create). Assign a name to the metric and create a SQL statement that returns a single value or two columns of content. In the two columns, the first column is key and the second column is value. You can set warnings and severity thresholds for each key.

For more information about creating user-defined metrics, see Oracle Enterprise Manager Advanced Configuration 10g Release 4.

Note: although user-defined metrics are not displayed on the All Metrics page, the Alerts section of the home page reports anything that exceeds the threshold.

View user-defined host metrics

Similar to user-defined SQL metrics, you can also create and view user-defined host metrics.

You can customize the EM metrics set and create your own metrics that are checked by EM. To create your own metrics, open the User-Defined Metrics page and click Create. The link is in the "Related Links" section of the "Host" home page. To navigate to this page, click the Hostname link on the Database home page, and then click User-Defined Metrics in the relevant links section of the Host page. Create user-defined host metrics

The User-Defined Metrics (user-defined Metrics) page allows you to define host metrics. The path to this page is "Database Home > Hostname > User-Defined Metrics (Related Links section) > Create (database home page > hostname > user-defined metrics (" related links "section) > create)". Assign a name to the metric and create a host command line statement that returns a single value. Warning and severity thresholds can be set in the threshold section.

Note: although user-defined metrics are not displayed on the All Metrics page, the Alerts section of the home page reports anything that exceeds the threshold.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.