MIME-Version: 1.0
In-Reply-To: <CAFsgGrx5eiXqG-nC6D_6F5Y0-cAeF+mx4oBeEaB5qXzc+ppCKQ@mail.gmail.com>
References: <CAFsgGrx1CBidGx+KFBPYvhvyqmVGif7vEA8o7sQ3-4kvaPtDYg@mail.gmail.com>
 <CADsdiQjA-Lggrj4LxSYkmyvAo49ii35O+wpwQHMpKQgXBmbK9w@mail.gmail.com> <CAFsgGrx5eiXqG-nC6D_6F5Y0-cAeF+mx4oBeEaB5qXzc+ppCKQ@mail.gmail.com>
From: Martin Berger <martin.a.berger@gmail.com>
Date: Sun, 9 Sep 2012 22:50:40 +0200
Message-ID: <CALH8A90TRc1Q6zVByRyrV6Nz6+MVMUfo5zL0wre9p4apPUKgNA@mail.gmail.com>
Subject: Re: Performance tuning of a system
To: s.cislaghi@gmail.com
Cc: kyle Hailey <kylelf@gmail.com>, Oracle L <oracle-l@freelists.org>
Content-type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: martin.a.berger@gmail.com

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@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