An example of Oracle ash memory forced Flush log Solution

OracleASH (ActiveSessionHistory) is a fine-grained AWR Report, which is often applied in our performance tuning process. Like all monitoring methods,

Oracle ASH (Active Session History) is a fine-grained AWR Report, which is often applied in our performance tuning process. Like all monitoring methods,

Oracle ASH (Active Session History) is a fine-grained AWR Report, which is often applied in our performance tuning process. Like all monitoring methods, ASH is based on regular performance data sampling and collection, and finally centralized analysis. Compared with AWR, ASH has a more intensive sampling frequency and data is centered on active sessions.

In practice, we may also encounter ASH-related faults. This article briefly introduces a case for future friends to check.

1. Problem Description

During the inspection, an alarm log for overnight running of the 11gR2 database was found to be abnormal.

SQL> select * from v $ version;

BANNER

-----------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

Alarm log information:

Wed Apr 16 02:54:04 2014

Archived Log entry 42964 added for thread 1 sequence 26964 ID 0x408fa96f dest 1:

Archived Log entry 42965 added for thread 1 sequence 26964 ID 0x408fa96f dest 5:

Wed Apr 16 02:54:28 2014

Active Session History (ASH) receivmed an emergency flush. this may mean that ASH is undersized. if emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ ASH_SIZE to a sufficiently large value. currently, ASH size is 67108864 bytes. both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:

Select total_size, awr_flush_emergency_count from v $ ash_info;

The SQL check result is as follows:

Select total_size/1024/1024, awr_flush_emergency_count from v $ ash_info;

TOTAL_SIZE/1024/1024 AWR_FLUSH_EMERGENCY_COUNT

---------------------------------------------

64 1

2. Problem Analysis

From the perspective of alarm logs, it should be the role of Oracle's internal automatic adjustment mechanism. After entering 11 GB, Oracle alert log alarms become more and more effective. Some problems that occur during automatic diagnosis will appear in the log as a reminder. For example, swap conversion and ash change. Today's ash emergency flush is a common one.

The author's management system is a typical OLAP system. There are not many DML operations during the day, most of which are query, retrieval, and report operations. ETL is performed through a series of job SQL statements at night.

The preceding log snippets are generated during the nighttime job process. The log volume generated every two minutes is about 1 GB.

From the analysis point of view, Oracle collects ASH at a high frequency, usually at the minute level. If the database files are stored immediately after collection, the performance loss is unacceptable. One method is to build a dedicated buffer in memory shared storage. Periodically or determine the condition for writing data back to the database from the memory.

As shown in the prompt, when the Oracle load is large, the ASH information exceeds the system limit and a forced emergency clearing action is performed. It is recommended that the _ ash_size parameter be adjusted if this problem occurs repeatedly in Oracle.

Oracle does have the parameter _ ash_size, which can be viewed using SQL as an implicit parameter.

SQL> select

2 x. ksppinm name,

3 y. ksppstvl value,

4 y. ksppstdf isdefault,

5 decode (bitand (y. ksppstvf, 7), 1, 'modified', 4, 'System _ mod', 'false') ismod,

6 decode (bitand (y. ksppstvf, 2), 2, 'true', 'false') isadj

7 from

8 sys. x $ ksppi x,

9 sys. x $ ksppcv y

10 where

11 x. inst_id = userenv ('instance') and

12 y. inst_id = userenv ('instance') and

13 x. indx = y. indx and

14 x. ksppinm = '_ ash_size'

15 order

16 translate (x. ksppinm ,'_','');

NAME VALUE ISDEFAULT ISMOD ISADJ

--------------------------------------------

_ Ash_size 1048618 TRUE FALSE

The Ash size is used to specify the ash buffer (shared pool ). The default value is 1048618 bytes, that is, 1 M. ASH sampling is centered on Active sessions. If the system processes the data too frequently and the number of active user sessions is large, the amount of data sampled each time will exceed the idle state of the system.

The following is the filling of the ash buffer in the memory, which causes the database to forcibly write back data and start the DBWR process read and write operations. DBWR occupies part of system resources when writing data, which is a performance bottleneck.

4. Solve the Problem

Based on the practice officially recommended by Oracle, we need to adjust the ash size parameter to a suitable size range. The current ASH total size is 64 MB. According to the moderately loose principle, the remaining half of the redundancy is added, that is, the size of 96 MB is set.

SQL> alter system set "_ ash_size" = 100663296;

System altered

Determine the adjustment:

SQL> select

2 x. ksppinm name,

3 y. ksppstvl value,

4 y. ksppstdf isdefault,

5 decode (bitand (y. ksppstvf, 7), 1, 'modified', 4, 'System _ mod', 'false') ismod,

6 decode (bitand (y. ksppstvf, 2), 2, 'true', 'false') isadj

7 from

8 sys. x $ ksppi x,

9 sys. x $ ksppcv y

10 where

11 x. inst_id = userenv ('instance') and

12 y. inst_id = userenv ('instance') and

13 x. indx = y. indx and

14 x. ksppinm = '_ ash_size'

15 order

16 translate (x. ksppinm ,'_','');

NAME VALUE ISDEFAULT ISMOD ISADJ

--------------------------------------------

_ Ash_size 100663296 TRUE SYSTEM_MOD FALSE

When the job is executed on the night of the next day, the alarm information does not appear and the fault is resolved.

5. Conclusion