Brief Introduction to Oracle Database ASH and AWR

InOracle DatabaseIn, sometimes we may encounter such a term:ASHAndAWRHow are they produced? What are their functions? This article introduces this part.

Before 1.10g

A user's connection will generate a session, and the current session record will be saved in v $ session; a session in the waiting state will be copied and put in v $ session_wait. After the connection is disconnected, the original connection information will be deleted in v $ session and v $ session_wait. This is the status before 10 Gb.

2. v $ session_wait_history and ASH

For a common session, I mean it does not consume a lot of resources. However, if a session occupies a large amount of resources such as CPU, memory, and I/O during activity, the loss of the session information will not be able to evaluate the system bottleneck at that time. The DBA was pleased that the information in v $ session_wait was left in Oracle 10 Gb.

A New View is displayed in Oracle 10g: v $ session_wait_history. This view stores the last 10 waiting events of each active session in v $ session_wait. However, it is far from enough to monitor the database performance in a period of time. To solve this problem, a new view is added in 10 GB: v $ active_session_history. This is ASHactive session history ).

In typical cases, the last five to ten minutes of detailed information is required to diagnose the current database status. However, because it takes a lot of time and space to record the session activity information, ASH adopts the policy of saving the information of the Active session in the waiting state, sampling once a second from v $ session_wait, and save the sampling information in the memory.

3. AWR

Note that the sample data of ASH is stored in the memory. The memory space allocated to ASH is limited. When the allocated space is full, the old records will be overwritten. After the database is restarted, all the ASH information will disappear. In this way, it is impossible to check the performance of oracle for a long time. In Oracle10g, You can retain the ASH information permanently. This is AWRauto workload repository ).

Since saving all the information in ASH is time-and space-consuming, AWR adopts the policy of sampling v $ active_session_history every hour and saving the information to the disk, the record is retained for 7 days. The old record will be overwritten after 7 days. The sample information is stored in view wrh $ _ active_session_history. However, the sampling frequency is 1 hour) and the retention time is 7 days), which can be adjusted according to the actual situation. This provides DBA with a more effective system monitoring tool.

AWR permanently stores system performance diagnostic information, which is owned by SYS users. After a period of time, you may want to clear this information. Sometimes, for performance diagnosis, you may need to define the sampling frequency to obtain System Snapshot information. Oracle 10 Gb provides many processes in the package dbms_workload_repository. Through these processes, you can manage snapshots and set baselines ).

4. Summary

In this way, we know the causes and functions of ASH and AWR. ASH stores the latest waiting session records of the system and can be used to diagnose the current state of the database. The information in AWR may have a delay of up to one hour, therefore, the sampling information cannot be used to diagnose the current status of the database, but can be used as a reference for database performance adjustment in a period of time.

For the inheritance relationships between these views, eygle provides a relational diagram:

Figure 1 view hierarchy

The view dba_hist_active_sess_history is the joint presentation of wrh $ _ active_session_history and several other views. It is usually used to access historical data.

Here is an introduction to Oracle Database ASH and AWR. I hope this introduction will be helpful to you.