Re: Performance tuning of a system

From: Karl Arao <karlarao_at_gmail.com>
Date: Sun, 9 Sep 2012 17:16:37 -0500
Message-ID: <CACNsJndE3n6nv3NZjWm-FC4rOHfXLfkRxO3KrxCoZkVJR0rhjw_at_mail.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_at_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

Received on Sun Sep 09 2012 - 17:16:37 CDT