Re: Performance tuning of a system

From: kyle Hailey <kylelf_at_gmail.com>
Date: Fri, 7 Sep 2012 08:56:26 -0700
Message-ID: <CADsdiQjA-Lggrj4LxSYkmyvAo49ii35O+wpwQHMpKQgXBmbK9w_at_mail.gmail.com>



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
Received on Fri Sep 07 2012 - 10:56:26 CDT

Original text of this message