Re: AWR
- From: "John Kanagaraj" <john.kanagaraj@xxxxxxxxx>
- To: smishra_97@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 1 Sep 2006 13:55:15 -0700
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';
<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 **
On 9/1/06, Sanjay Mishra <smishra_97@xxxxxxxxx> 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 <anjo.kolk@xxxxxxxxxxx> 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 <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.
>
>
--
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
