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: Karl Arao <karlarao@gmail.com>
Date: Sun, 9 Sep 2012 17:16:37 -0500
Message-ID: <CACNsJndE3n6nv3NZjWm-FC4rOHfXLfkRxO3KrxCoZkVJR0rhjw@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
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: karlarao@gmail.com

"So we know that almost 99% are under 2 sec but sometimes others are
above 5sec, 20/30sec. "
Kerry Osborne has this script called whats_changed.sql that could be
applicable to that requirement
http://blog.enkitec.com/enkitec_scripts/whats_changed.sql that SQL
queries DBA_HIST_SQLSTAT
which may not contain all of your SQLs but I think would be good enough
number of samples for what you are trying to achieve.

So let's say you want to have all SQLs that has gone slower for the last 99
days with a minimum elapsed time of 5seconds and at least went twice as
slow.. you'll get the output below.

16:54:28 SYS@dw> @whats_changed
Enter Days ago: 99
Enter value for min_stddev: 2
Enter value for min_etime: 5
Enter value for faster_slower: S%

SQL_ID               EXECS AVG_ETIME_BEFORE AVG_ETIME_AFTER   NORM_STDDEV
RESULT
------------- ------------ ---------------- --------------- -------------
------
5bbwwqft5h36b            2             0.59            8.79        9.8024
Slower
d15cdr0zt3vtp        7,277             0.35            5.58       10.6024
Slower

Then you can investigate if the SQL is having a lot of plan changes by
using the script awr_plan_change.sql
http://blog.enkitec.com/enkitec_scripts/awr_plan_change.sql or you can
drill on specific time frame where the SQLs has gone >5secs by querying ASH
or generating ASH report on those particular periods also Enterprise
Manager is pretty handy if you want to see what's happening (in terms of
workload,CPU,IO) during those periods.


-- 
Karl Arao
karlarao.wordpress.com
karlarao.tiddlyspot.com
twitter.com/karlarao


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