RE: how can I proove Oracle doesn't measure every SQL-execution

From: Milen Kulev <makulev_at_gmx.net>
Date: Sun, 26 Apr 2009 21:39:37 +0200
Message-ID: <011e01c9c6a6$bc7f2610$357d7230$_at_net>

Martin,
sorry for cathing up lately with this thread . 1) You can increase the sample rate of ASH _ash_sampling_interval =100 (milliseconds, the minimum). There is no measurable increased CPU consumption , latch contention etc. (I have tried to measure it, but I am getting < 0.2 CPU overhead and this value could be a measurement noise).
2) ASH makes persistent only 1 of 10 records of the ASH buffer (and NOT "1 of 10 records *per session* of the ASH buffer"). The only way to write all the information from ASH buffer to the WRH$_ACTIVE_SESSION_HISTORY is to set
_ash_disk_filter_ratio=1 (instead of default 10). Be prepared to see muuuch more wait evecnt of wait class "Systime I/O" ;( 3) ASH can give you the count of wait events of each sql, but not the times (TIME_WAITED and WAIT_TIME (for CPU consumprion) ) Are extremely unreliable. In order to get read times you must sample v$sess_time_model, and then using ASH (+ v$SQLSTATS) to correlate ASH wait counts with session DB time in order to get SQL statemets execution time, resourece consumption in a specified period. I don't think you need so much data (10046 event trace files), at least at the beginning. Just use ASH + AWR to get the interesting periods, when performance is bad and focus on these periods.

HTH. Milen

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Berger
Sent: Dienstag, 21. April 2009 10:38
To: Guillermo Alan Bort
Cc: oracle-l
Subject: Re: how can I proove Oracle doesn't measure every SQL-execution

Hi Alan,

I also thought about ASH, especially with 'colored SQL' in 11g (to get better historical info on dedicated SQLs) BUT ASH does not tell me when the statement was finished (I have to check wether or not there is a matching entry in the next sampling periode)
AND I was also asked for the execution time of statements with expected execution time of 100ms and less - there ASH can not help me with a sampling period of 1s

thank you for your suggestions, anyway,
  Martin

Am 21.04.2009 um 10:30 schrieb Guillermo Alan Bort:

> if using ASH this *might* be possible... I'm a bit burnt out now,
> but I will look into it tomorrow (hopefully)
> Alan Bort
> Oracle Certified Professional
>

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Apr 26 2009 - 14:39:37 CDT