Re: Performance tuning of a system

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