ASH maintains samples of the session-state information in its own buffers within the System Global Area (SGA). The amount of information that ASH maintains could quickly become substantial if not managed properly. Hence, ASH maintains a fixed size circular buffer that is allocated in the SGA at system startup using the formula:
ASH_Buffer_Size = Max (Min(No.of CPUs * 2 Mb, 5% of Shared Pool size, 30 MB), 1 Mb)
In other words, it is at least 1MB and goes up to a maximum of 30MB. In between these extremes, the size is determined by the number of CPUs (No. of CPUs * 2) or by the size of the shared pool (5% of the shared Pool size). Because ASH collects session-state information for active sessions only, it is obvious that the buffer will become full more quickly when there are more active sessions in the database. By default, this persistence, or writing out to disk, is set to occur when the buffers are 66% full. When this occurs, every 10th ASH record for each session is associated with the current snapshot and written out to disk as the rows in the WRH$_ACTIVE_SESSION_HISTORY table. This occurs in the same manner of persisting AWR data to disk. The ASH buffer space is then released for capturing newer session-state information.
It seems that there are a few hidden parameters - those that start with _ (underscore) and should NOT be changed except under Oracle Support's direction—related to ASH. This includes _ash_enable, which by default is set to TRUE and seems to allow ASH to be switched off. Other parameters include _ash_disk_filter_ratio, the ratio of inmemory sample to flush (every 10th by default); _ash_eflush_trigger, the percentage at which an emergency disk flush should occur (66% by default); and _ash_sampling_interval, the time interval between ASH samples (1,000 milliseconds or 1 second by default). Changing these values may adversely affect the behavior of ASH. As the TV commercial says, "Don't try this at home" (or on your production instance)!
The current allocation can be seen from the query:
select * from v$sgastat where name = 'ASH buffers';
<PLUG> All this (and much more) in Chapter 11 "Oracle Database 10g Insider Solutions" http://www.amazon.com/gp/product/0672327910</PLUG>
-- John Kanagaraj <>< DB Soft Inc Phone: 408-970-7002 (W)
Disappointment is always inevitable; Discouragement is invariably optional
** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **
I was in hotsos and attended Graham Wood Presentation, where he mentioned that ASH is bascially 2M Per CPU, < 5% of Shared Pool , 2% of SGA_TARGET and it is fixed for the life of Instance. Now what I am trying to understand is that AWR consist of In memeory Stat ( Comes from SGA) and WorkLoad Repository ( comes from SYSAUX)
In Memory Stats itself comprises of ASH and AWR Stats. ASH comes from SGA and it is allocated as per previous values, then how the space is allocated to AWR Stats. Is there an algorithm for it like ASH
Anjo Kolk <anjo.kolk@xxxxxxxxxxx> wrote:
Strictly speaking AWR is STATSPACK++, so in a way it is only the schema on disk with some PL/SQL packages + some background process doing the loading. The data being stored comes from different places, one of them is the ASH memory. ASH is sized based on a formula that I have seen, but since then forgotten as it is a circular buffer. The memory for this buffer is allocated from the Variable Part of the SGA not the Fixed Part.
On 9/1/06, Sanjay Mishra <smishra_97@xxxxxxxxx> wrote: > > > AWR consists of In-Memory Stats and Workload Repository. Can somebody tell me as how much area in SGA is allocated to In-memory stats or is there any %age of total SGA or like. Is it been allocated from Fixed SGA. > > TIA > > Sanjay > > ________________________________
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min.
Do you Yahoo!?> >
-- Anjo Kolk Owner and Founder OraPerf Projects tel: +31-577-712000 mob: +31-6-55340888
Get on board. You're invited to try the new Yahoo! Mail.