Detailed introduction to the composition and working principle of AWR in Oracle databases

Last time, we introduced the brief introduction of Oracle Database ASH and AWR. Today we will detail the composition of AWR and its working principles. Next, let's take a look at this part.

1. Memory size occupied by ash

ASH's collection information is stored in the memory. After the old information is sampled into AWR, it can be overwritten by the newly collected information. After oracle is restarted, the information is cleared. The memory size allocated to ASH can be queried:

 
 

  
  
SQL> select pool, name, bytes/1024/1024 From v$sgastat where name like '%ASH %';  
  
  
 
  
  
POOL NAME BYTES/1024/1024  
  
  
 
  
  
------------- ------------- ---------------  
  
  
 
  
  
shared pool ASH buffers 2 
 
 

2. AWR correction

For ease of description and understanding, in the first part, AWR is used to save the information in ASH.

In fact, the information recorded by AWR is not only ASH, but also statistical information and wait information of all aspects of database operation for diagnosis and analysis.

The AWR sampling method is to sample all important statistics and load information at a fixed interval and save the sampling information in AWR.

It can be said that the information in ASH is saved to the view wrh $ _ active_session_history in AWR. ASH is the real subset of AWR.

3. mmon and mmnl Processes

A snapshot is composed of a new background process named MMON and its slave process), and The MMNL background process automatically samples the snapshot at a fixed time. Let's take a look at the introduction of the two newly added background processes in the 10g concept guide:

The MMON process is responsible for executing a variety of background tasks related to manageability-related management. For example:

When a measured value (metrics) exceeds the preset value (threshold value), submit a warning.

Create a new MMON membership process (MMON slave process) to take snapshots ).

Capture statistics of recently modified SQL objects.

The MMNL process is responsible for executing lightweight background tasks related to high frequency and manageability, such as capturing session history information and calculating measured values.

AWR sampling is performed by the MMON process once every hour. The ASH information is also sampled and written to the AWR Load Library. Although the ASH buffer is designed to retain the information for one hour, the memory is often insufficient. When the ASH buffer is full, another background process, MMNL, will take the initiative to write the ASH information.

4. SYSAUX tablespace

These sample data are stored in the SYSAUX tablespace and named in WRM $ _ * And WRH $ _ * formats. The former stores metadata information such as checked databases and collected snapshots. The latter stores the actual collected statistics.

 
 

  
  
SQL> select table_name from dba_tables where table_name like 'WRM$%';  
  
  
 
  
  
TABLE_NAME  
  
  
 
  
  
-----------------------  
  
  
 
  
  
WRM$_WR_CONTROL  
  
  
 
  
  
WRM$_SNAP_ERROR  
  
  
 
  
  
WRM$_SNAPSHOT  
  
  
 
  
  
WRM$_DATABASE_INSTANCE  
  
  
 
  
  
WRM$_BASELINE 
 
 

When the SYSAUX tablespace is full, AWR will automatically overwrite the old information and record a message in the warning log:

ORA-1688: unable to extend table SYS. WRH $ _ ACTIVE_SESSION_HISTORY partition WRH $ _ active_00003490838_1522 by 128 in tablespace SYSAUX

5. sampling frequency and Retention Time

You can query the AWR sampling frequency and retention time by querying the view dba_hist_wr_control or wrm $ _ wr_control. By default, sampling is performed once every hour, and the sampling information is retained for 7 days.

 
 

  
  
SQL> select * from dba_hist_wr_control;  
  
  
 
  
  
DBID SNAP_INTERVAL RETENTION TOPNSQL  
  
  
 
  
  
---- ------------- ----------- ----------  
  
  
 
  
  
1148 +00000 00:1 +00007 00:0 DEFAULT  
  
  
 
  
  
SQL> select DBID, SNAP_INTERVAL, SNAPINT_NUM, RETENTION from wrm$_wr_control;  
  
  
 
  
  
DBID SNAP_INTERVAL SNAPINT_NUM RETENTION  
  
  
 
  
  
---------- ------------------ ----------- --------------------  
  
  
 
  
  
1160732652 +00000 01:00:00.0 3600 +00007 00:00:00.0 
 
 

6. Sample Data Volume

Because of the huge data volume, writing all ASH data to the disk is unacceptable. Generally, this data is filtered when written to the disk, and the written data accounts for 10% of the sampled data. When written, the direct-path insert operation is used to reduce log generation as much as possible, this minimizes the impact on database performance.

7. initialize the statistics_level parameter.

AWR behavior is affected by the STATISTICS_LEVEL parameter. This parameter has three values:

BASIC: the calculation and derivative values of awr statistics are disabled. Only a small amount of database statistics are collected.

TYPICAL: default value. only partial statistics are collected. They represent the TYPICAL monitoring behavior of the oracle database.

ALL: ALL possible statistics are captured. and some information about the operating system. this level of capture should be used in rare cases, such as when you need more SQL diagnostic information.

Here is an introduction to the composition of Oracle Database AWR and its working principles. I hope this introduction will help you.