MIME-Version: 1.0
In-Reply-To: <CACNsJndE3n6nv3NZjWm-FC4rOHfXLfkRxO3KrxCoZkVJR0rhjw@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> <CACNsJndE3n6nv3NZjWm-FC4rOHfXLfkRxO3KrxCoZkVJR0rhjw@mail.gmail.com>
From: Karl Arao <karlarao@gmail.com>
Date: Sun, 9 Sep 2012 17:31:24 -0500
Message-ID: <CACNsJneN-m5i4k-zdux038CH=g0_hiLmwusuUv6s9vn846ePng@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

BTW you can also do the STDDEV trick on Kyle's script.


-- CREATE A TEMP TABLE THAT SHOWS AVG,MIN,MAX,STDDEV RESPONSE TIME OF SQLS

define begin='03/08/2012 14:40'
define end='03/08/2012 14:45'


SYS@fsprd2> create table karl_sql_id2 as
select sql_id,
  2    3                  count(*) count,
  4          round(avg(EXTRACT(HOUR FROM run_time) * 3600
  5                      + EXTRACT(MINUTE FROM run_time) * 60
  6                      + EXTRACT(SECOND FROM run_time)),2) avg ,
  7          round(min(EXTRACT(HOUR FROM run_time) * 3600
  8                      + EXTRACT(MINUTE FROM run_time) * 60
  9                      + EXTRACT(SECOND FROM run_time)),2) min ,
10          round(max(EXTRACT(HOUR FROM run_time) * 3600
11                      + EXTRACT(MINUTE FROM run_time) * 60
12                      + EXTRACT(SECOND FROM run_time)),2) max,
13          round(stddev(EXTRACT(HOUR FROM run_time) * 3600
14                      + EXTRACT(MINUTE FROM run_time) * 60
15                      + EXTRACT(SECOND FROM run_time)),2) stddev
16  from  (
17          select
18                 sql_id,
19                 max(sample_time - sql_exec_start) run_time
20          from
21                 dba_hist_active_sess_history
22          where
23                 sql_exec_start is not null
24                                         and sample_time
25                                         between to_date('&begin',
'MM/DD/YY HH24:MI:SS')
26                                         and to_date('&end', 'MM/DD/YY
HH24:MI:SS')
27          group by sql_id,SQL_EXEC_ID
28          order by sql_id
29         )
30  group by sql_id
31  order by avg desc
32  /

Table created.


define _start_time='03/08/2012 14:40'
define _end_time='03/08/2012 14:45'


SYS@fsprd2> select * from karl_sql_id2
where sql_id in
  2    3        (select sql_id from
  4     dba_hist_active_sess_history
  5     where sample_time
  6                                            between
to_date('&_start_time', 'MM/DD/YY HH24:MI')
  7                                            and to_date('&_end_time',
'MM/DD/YY HH24:MI')
  8     and lower(module) like 'ex_%')
  9  order by stddev asc;

SQL_ID             COUNT        AVG        MIN        MAX     STDDEV
------------- ---------- ---------- ---------- ---------- ----------
aadkvg74cknvc          1         .8         .8         .8          0
c96tdmv2wu0mb          1        .81        .81        .81          0
03zk40yazk2cj          1        .81        .81        .81          0
89s2kmgjcyg08          1       1.96       1.96       1.96          0
cb5gq5xu04sbb          3        2.6       1.92       3.93       1.15
991y15af5jxx9          5       2.07        .96       5.93       2.16
c2fn0swka653f          6      18.94       9.99      28.99       7.28

7 rows selected.


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


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