Re: V$active session history
- From: Vishal Gupta <vishal@xxxxxxxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 22 Mar 2015 18:25:10 +0000
Jonathan,
You are right. By default, every 10th sample is written from in-memory ASH
to AWR. We can also control it via _ash_disk_filter_ratio parameter.
I#NameTypeValueISDEFAULTISSESISSYS_MODISMODIFIEDISADJDesc
1_ash_disk_filter_ratioInteger10TRUEFALSEIMMEDIATEFALSEFALSERatio of the
number of in-memory samples to the number of samples actually written to
disk
Regards,
Vishal Gupta
From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
Reply-To: <jonathan@xxxxxxxxxxxxxxxxxx>
Date: Friday, 20 March 2015 09:41
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Subject: RE: V$active session history
>
> No,
> Ryan is correct.
>
> V$active_session_history takes a sample once every second.
> The content of a sample consists of every session that is active at the moment
> the sample was taken.
> The AWR (dba_hist_active_sess_history) reduces this to every 10th sample - i.e
> the result you would get if you sampled once every 10 seconds instead of once
> per second.
>
> You can confirm this, assuming that your ASH data survives for just a little
> more than the basic one hour target, by comparing the sample_time on the
> dba_hist_active_sess_history data of the most recent AWR snapshot with the
> sample_times from v$active_session_history for a similar period.
>
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> @jloracle
>
> From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
> of Vishal Gupta [vishal@xxxxxxxxxxxxxxx]
> Sent: 20 March 2015 09:25
> To: rjanuary@xxxxxxxxx
> Cc: jeremy.schneider@xxxxxxxxxxxxxx; mohamed.houri@xxxxxxxxx; ORACLE-L
> Subject: Re: V$active session history
>
> Ryan,
>
> Session does have to be active for 10 secs to be included in AWR sample. AWR
> takes top 10% of the rows for every second and stored in repository.
>
> Regards,
> Vishal Gupta
>
> On 19 Mar 2015, at 18:11, Ryan January <rjanuary@xxxxxxxxx> wrote:
>
> I agree with Jeremy, but have a slight correction. As I understand it, every
> 10th ASH sample will make it to the dba_hist* views. A sql statement wouldn't
> have to be active a full 10 seconds to be included. That session can sit idle
> for 9.999 seconds and, by being active at just the right time, be included in
> the AWR sample.
>
>> On Mar 19, 2015, at 1:05 PM, Jeremy Schneider
>> <jeremy.schneider@xxxxxxxxxxxxxx> wrote:
>>
>> every SQL that runs for more than 1 second will show up in
>> v$active_session_history (until the memory buffer cycles around).
>> every SQL that runs for more than 10 seconds will be retained in the
>> dba_hist_* view.
>
Other related posts:
