John THis is an excellent update as It is more clear now. Can you also update as this portion of SGA comes from fixed Area of SGA. Also AWR stats are also stored in SGA and how the space is allocated for it. I am going to buy this Insider books tomorrow as it look like had greate review by everybody on Amazon TIA Sanjay John Kanagaraj <john.kanagaraj@xxxxxxxxx> wrote: Sanjay, 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'; All this (and much more) in Chapter 11 "Oracle Database 10g Insider Solutions" http://www.amazon.com/gp/product/0672327910 -- 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 ** On 9/1/06, Sanjay Mishra wrote: > > > Anjo > > 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 > > TIA > Sanjay > > > Anjo Kolk wrote: > > > Sanjay, > > 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. > > Anjo. > > > On 9/1/06, Sanjay Mishra 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. > > > > > > > > > > > -- > Anjo Kolk > Owner and Founder OraPerf Projects > tel: +31-577-712000 > mob: +31-6-55340888 > > > > ________________________________ Do you Yahoo!? > Get on board. You're invited to try the new Yahoo! Mail. > > --------------------------------- Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min.