1、问题 现象
alert.log告警
Active Session History (ASH) performed 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 134217728 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;
2、官方文档
（1）QUESTION
1、Active Session History (ASH) performed 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 134217728 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;
（2）CAUSE
Typically some activity on system causes more active sessions, therefore filling the ASH buffers faster than usual causing this message to be displayed. It is not a problem per se, just indicates the buffers might need to be increased to support peak activity on the database.
（3）SOLUTION
The current ASH size is displayed in the message in the alert log, or can be found using the following SQL statement.
select total_size from v$ash_info;
Then increase the value for _ash_size by some value, like 50% more than what is currently allocated. For example if total_size = 16MB, then an increase of 50% more would be (16MB + (16MB * 50%)) = 24MB.
sqlplus / as sysdba alter system set "_ash_size"=25165824;
You can verify the change using the following select:
select total_size from v$ash_info;
3、问题分析：
从告警日志情况看，应该是Oracle内部自动调节机制的作用。进入11g之后，Oracle alert log的告警提示作用愈加明显。对于一些自动诊断过程中出现的问题，都会作为提醒出现在日志中。比如swap转换，ash变化等。今天的ash emergency flush就是比较常见的一个。
管理系统是一个典型的OLAP系统，
从分析角度，Oracle在收集ASH过程中，频度是很高的，通常为分钟级别。如果收集之后就立即存储入数据库文件，在性能上损耗是不容易被接受的。一种方法是构建在内存共享存储中的专门buffer。定期或者确定激发条件将数据从内存中写回到数据库中。
从提示信息中看，Oracle在负载比较大的情况下，会出现ASH信息超过系统限制，进行了一次强制的紧急清空动作。Oracle建议，如果反复出现这样的情况，就建议调整_ash_size参数大小。
Oracle内部的确是存在参数_ash_size，作为隐含参数可以使用SQL进行查看。
select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and
x.ksppinm ='_ash_size'
order by
translate(x.ksppinm, ' _', ' ');
NAME VALUE ISDEFAULT ISMOD ISADJ
---------- ---------- --------- ---------- -----
_ash_size 1048618 TRUE SYSTEM_MOD FALSE
Ash size大小用于指定ash buffer（shared pool）。默认给定的是1048618 bytes，也就是1M。ASH工作采样是以Active Session为中心的。如果系统处理操作过于频繁，活跃用户会话数量很多，这样每次采样的数据量就会超过系统空闲状态。
随之而来的就是内存中ash buffer的填满，进而引发数据库强制回写数据，启动DBWR进程读写动作。DBWR在写入的时候，会占用一部分系统资源，从整体看是性能瓶颈点。
4、解决：
根据Oracle官方推荐的经验做法，我们要调整ash size参数到一个适合大小范围。当前ASH total size是128MB，按照适度宽松的原则，另外加入一半的冗余量，也就是设置192M大小。
大小：_ash_size=134217728 +（134217728 *50%）=201326592
SQL>alter system set "_ash_size"=201326592;
5、查看调整情况：
select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and
x.ksppinm ='_ash_size'
order by
translate(x.ksppinm, ' _', ' ');
NAME VALUE ISDEFAULT ISMOD ISADJ ---------- ---------- --------- ---------- ----- _ash_size 201326592 TRUE SYSTEM_MOD FALSE
6、结论
这个问题本身不大，而且根据Oracle的提示也比较容易解决问题。
应该说，这种提示信息不一定一出现就进行调整，Oracle一般建议ash强制刷新在多次出现的时候才尝试解决。
