Re: Performance tuning of a system

even not tested I am _verry_ sure from the moment the ASH and/or AWR snapshot intervals are manipulated, all of Oracles standard reports and also EM pages goes crazy.
This is one more reason _NOT_ to do this without any real need and to be aware of it's real danger.

Still it's possible and if done based on a well made decission, I'm fine.

I'm somehow suspicious about
> "...the metric for flushing this was carefully calculated to work with the rest of the design..."

as this would mean 'one size fits all' comes true. More likely I'd call it a good compromise between many different needs. And, as a human, it's just easier to think '1 ASH sample - 1 second; 1 AWR sample - 10 seconds' instead of
'1 ASH sample - 0.653 seconds; 1 AWR-sample - 4.935 seconds'. Every back-of-an-envelope calculation would be much more complicated for humans!

Martin

On Sun, Sep 9, 2012 at 11:22 PM, Kellyn Pot'vin <kellyn.potvin_at_ymail.com> wrote:
> I don't know the answer to this, but I would be interested to know- If you
> DID change the parameters for how often ASH flushed to AWR, would the ASH
> and AWR data calculations still be correct in the reports? Would it be
> essential to query the distinct ASH data directly to perform correct
> analysis?
>
> I was surprised to see this, as in the last "Oracle Supported" ASH/AWR
> presentation on this subject, the guys were quite clear that we should NOT
> be changing how often it was flushed and "...the metric for flushing this
> was carefully calculated to work with the rest of the design..."
>
> Something to think about and query...
>
> Thanks!
>
>
> Kellyn Pot'Vin
> Senior Technical Consultant
> Enkitec
> DBAKevlar.com
> RMOUG Director of Training Days 2013
>
>
>
> ~Tombez sept fois, se relever huit!
> ________________________________
> From: Martin Berger <martin.a.berger_at_gmail.com>
> To: s.cislaghi_at_gmail.com
> Cc: kyle Hailey <kylelf_at_gmail.com>; Oracle L <oracle-l_at_freelists.org>
> Sent: Sunday, September 9, 2012 2:50 PM
> Subject: Re: Performance tuning of a system
>
> Ste,
>
> do you have any chance to pin down a 'transaction' for sure?
> Something like dbms_application_info is setting?
>
> My problem:
> * curently we do not know which part of your 'transactions' is slow.
> * it's rare and can not be reproduced at will
> * a target of 5 sec, and even a 'bad' query of 30 sec is hard to find
> in dba_hist_active_session_history - you would need to sample
> v$active_session_history manually immediately after the issue raises.
>
> Doug Burns wrote about ASH and AWR sampling can be manipulated:
> http://oracledoug.com/serendipity/index.php?/archives/1395-ASH-and-the-psychology-of-Hidden-Parameters.html
> But I'd not like to go that way in production without good reason and
> support.
>
> That's why I come back to identify the bad 'transactions'. If you can
> pin those down, it should still give enough samples over some days so
> they can be used in statistical methods?
>
> Do you have a tracking table where all 'transactions' - and their
> timing is tracked by the application?
> If you have, you can create a trigger there and do your own 'flush
> v$active_sesion_history into persistent table'
>
> hth,
> Martin
>
>
> On Sun, Sep 9, 2012 at 6:16 PM, Stefano Cislaghi <s.cislaghi_at_gmail.com>
> wrote:
>> Hi Kyle,
>>
>> your queries are very interesting and I will check them ASAP. Anyway I
>> gained some more information about the problem.
>> The constraint is to have all transactions performed in less than 5
>> seconds. Transaction is not a single statement but, from the
>> application point of view,
>>
>> - few selects into the db
>> - elaborate data (by appserver)
>> - run 2 or 3 store proc
>>
>> So we know that almost 99% are under 2 sec but sometimes others are
>> above 5sec, 20/30sec. DB is not directly incriminated but it's been
>> asked to provide, if any, any information for debugging. IMHO I think
>> it is quite hard because the 'long' transactions happen everyday in
>> totally different time windows; I do not have enough data, even with
>> AWR, ecc to say what was happening for instance yesterday at 16:44:56
>> sec where a transaction took 33sec instead 2. From the application we
>> know that during the long transaction no abnormal load was done on db,
>> I mean no abnormal number of transaction in the same second. Disk
>> speed? maybe, but how to get the disk queue or any other interesting
>> info from the DB?
> --
> http://www.freelists.org/webpage/oracle-l

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