MIME-Version: 1.0
In-Reply-To: <CADsdiQjA-Lggrj4LxSYkmyvAo49ii35O+wpwQHMpKQgXBmbK9w@mail.gmail.com>
References: <CAFsgGrx1CBidGx+KFBPYvhvyqmVGif7vEA8o7sQ3-4kvaPtDYg@mail.gmail.com>
 <CADsdiQjA-Lggrj4LxSYkmyvAo49ii35O+wpwQHMpKQgXBmbK9w@mail.gmail.com>
From: Stefano Cislaghi <s.cislaghi@gmail.com>
Date: Sun, 9 Sep 2012 18:16:06 +0200
Message-ID: <CAFsgGrx5eiXqG-nC6D_6F5Y0-cAeF+mx4oBeEaB5qXzc+ppCKQ@mail.gmail.com>
Subject: Re: Performance tuning of a system
To: kyle Hailey <kylelf@gmail.com>
Cc: 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: s.cislaghi@gmail.com

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?

Thanks
Ste

On 7 September 2012 17:56, kyle Hailey <kylelf@gmail.com> wrote:
>
> If you want to find any SQL executions over 2 seconds you can use something
> like
>
> https://github.com/khailey/ashmasters/blob/master/ash_sql_elapsed_hist_longestid.sql
>
> blogged on
>
> http://dboptimizer.com/2011/05/06/sql-ash-timings-iii/
>
> Since every sample in ASH is 1 second then you want any queries who's
> execution id shows up more than once, ie 2 seconds or more, so you set
>
>  &min_elapsed_time
>
> to one when you run the query
>
> - Kyle
>
>
>
> On Mon, Sep 3, 2012 at 12:55 PM, Stefano Cislaghi <s.cislaghi@gmail.com>
> wrote:
>>
>> Hi all,
>>
>> I'm facing with a nice (at least for me) exercise of performance and
>> tuning. I have an OLTP system must process each transaction in no more
>> than 2 seconds. A transaction is not a single query but consists of
>> some query and store procedure run from an application server. Almost
>> 98% of this complete in less than 2 second.
>> The main concern is that the system is accessed also by third parties
>> application, mainly for enquiries but also for updates. The DBA has
>> never been involved in the evaluation of DML done by third parties
>> applications and there is
>>
>> Now I would like, as starting point, to trace for 24hrs all queries
>> done by user THIRDPARTYAPP1 and THIRDPARTYAPP2 and its duration in
>> order to understand if some query run by these application might in
>> some case impact the performance of the database; also this may offer
>> an opportunity of review cause there is no complete list of which
>> queries are done by other external applications.
>> Moreover any other hints is well accepted.
>>
>> Database is 11.2.0.2 under Linux.
>>
>> Thanks
>> Ste
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>
--
http://www.freelists.org/webpage/oracle-l