Re: AWR
- From: Sanjay Mishra <smishra_97@xxxxxxxxx>
- To: John Kanagaraj <john.kanagaraj@xxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 1 Sep 2006 14:30:39 -0700 (PDT)
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.
Other related posts:
