Go Simple Introduction to the Oracle database Ash and AWR

In Oracle databases, there are times when we might encounter the term: Ash and awr, so how do they happen? What is their role? In this article, we will introduce this part of the content.

1.10g ago

The user's connection will generate a session, the current session record is saved in V$session, and a waiting session is copied to the v$session_wait. When the connection is broken, the original connection information is deleted in V$session and v$session_wait. This is the situation before 10g.

2.v$session_wait_history and Ash

If you have a regular session (I mean not having a lot of resources), it's a little less of a performance tuning. However, if the session is heavily consuming resources (such as CPU, memory, I/O, etc.) during the activity, the loss of the session information will not be able to evaluate what the system bottleneck was at that time. To the DBA's Delight, Oracle 10g guarantees that this information is left in v$session_wait.

A new view has emerged in Oracle 10g: V$session_wait_history. This view holds the last 10 wait events for each active session in V$session_wait. But this is not enough for a period of time to monitor the performance of the database, in order to solve this problem, in 10g also added a new view: V$active_session_history. This is Ash (active session history).

Typically, the most recent 5-10 minute details are required in order to diagnose the state of the current database. However, because the activity information of the session is time and space-consuming, Ash adopts the strategy of saving the information of the active session in the waiting state, sampling it from v$session_wait every second, and storing the sampled information in memory.

3.AWR

Note that the sample data for ash is stored in memory. The amount of memory allocated to Ash is limited, and when the allocated space fills up, the old records are overwritten, and all of the ash information disappears after the database restarts. This is not possible for long-term detection of Oracle performance. In oracle10g, the method of permanently preserving ash information is provided, which is the AWR (auto workload repository).

Since all of the information in Ash is very time-and space-consuming, AWR's strategy is to sample v$active_session_history once per hour and save the information to disk for 7 days, 7 days after the old record is overwritten. These sampling information is saved in the view wrh$_active_session_history. This sampling frequency (1 hours) and retention time (7 days) can be adjusted according to the actual situation, which gives DBAs more effective system monitoring tools.

AWR permanently saves the system's performance diagnostic information, which is owned by the SYS user. After a while, you may want to erase this information, and sometimes for performance diagnostics, you may need to define your own sampling frequency to get system snapshot information. Oracle 10g provides a number of procedures in package dbms_workload_repository that allow you to manage snapshots and set baselines (baselines).

4. Summary

In this way, we know the causes and functions of ash and awr. Ash saves the system's latest waiting session record, which can be used to diagnose the current state of the database, and the information in the AWR can be up to 1 hours late, so its sampling information is not used to diagnose the current state of the database, but can be used as a reference for database performance tuning over time.

For the inheritance relationship between these views, Eygle gives a diagram:

Figure 1 Hierarchy of individual views

The view dba_hist_active_sess_history is a joint presentation of Wrh$_active_session_history and several other views, typically accessed through this view for historical data.

We have introduced a brief introduction to the Oracle database Ash and AWR, followed by a detailed description of the AWR's composition and how it works

1.ash occupied memory size

Ash's acquisition information is kept in memory, and after the old information is sampled into the awr, it can be overwritten with the newly acquired information, which is cleared after the Oracle is restarted. The amount of memory allocated to Ash can be queried to:

2.AWR Corrections

For the sake of description and understanding, in the first part, we say that AWR is the information stored in ash.

In fact, the AWR record information is not only ash, but also can collect all aspects of the database running statistics and waiting information to diagnose the analysis.

AWR is sampled by performing a sample of all of its important statistics and payload information at a fixed time interval and storing the sampled information in the AWR.

You can say this: The information in Ash is saved in the view wrh$_active_session_history in Awr. Ash is a true subset of AWR.

3.mmon process and MMNL process

Snapshots are automatically sampled every fixed time by a new background process called Mmon (and its slave processes) and the MMNL background process. Let's take a look at the introduction to these two newly added background processes in the concept guide for 10g:

The Mmon process is responsible for performing various and management-related (manageability-related) background tasks, such as:

A warning is submitted after a measured value (metrics) exceeds the preset limit value (threshold value).

Create a new Mmon affiliation process (Mmon slave process) to take a snapshot (snapshot).

Captures statistics for recently modified SQL objects.

The MMNL process is responsible for performing lightweight, high-frequency, and manageability-related background tasks, such as capturing session history information, measuring value calculations, and so on.

The sample work for AWR is performed by the Mmon process every 1 hours, and ash information is also sampled and written out to the AWR load library. Although Ash buffer is designed to keep 1 hours of information, many times this memory is not enough, when Ash buffer is full, another background process MMNL will actively write ash information.

4.SYSAUX table Space

These sampled data are stored in the Sysaux table space and are named in the format wrm$_* and wrh$_*. The previous type stores metadata information (such as checked databases and captured snapshots), and the latter type holds the actual collected statistics.

When the Sysaux table space is full, AWR automatically overwrites the old information and logs a message in the warning log:

Ora-1688:unable to extend table SYS. Wrh$_active_session_history partition wrh$_active_3533490838_1522 by Tablespace Sysaux

5. Sampling frequency and retention time

The sampling frequency and retention time of the awr can be queried by querying the view Dba_hist_wr_control or (Wrm$_wr_control). The default is to sample every 1 hours and the sampling information is retained for 7 days.

6. Amount of sampled data

Because of the huge amount of data, it is unacceptable to write all of the ash data to disk. Generally, when writing to the disk to filter this data, write the data accounted for 10% of the sampled data, written out by Direct-path insert complete, minimize log generation, thereby minimizing the impact of database performance.

7. Initialize parameter Statistics_level

The behavior of AWR is affected by the parameter statistics_level. This parameter has a value of three:

Basic:awr statistics are computed and derived values are closed. Only a small amount of database statistics is collected.

Typical: Default value. Only part of the statistical collection. They represent the behavior of the typical monitoring Oracle database that is needed.

All: All possible statistics are captured. And there are some information about the operating system. This level of capture should be used only in rare cases, such as when you want more SQL diagnostic information.

The knowledge about the composition of the Oracle database AWR and how it works is introduced here, and I hope this introduction will help you.

This article goes from:

Http://database.51cto.com/art/201108/282827.htm

Http://database.51cto.com/art/201108/282856.htm

Go Simple Introduction to the Oracle database Ash and AWR