Re: Performance tuning of a system

From: Karl Arao <karlarao_at_gmail.com>
Date: Sun, 9 Sep 2012 17:31:24 -0500
Message-ID: <CACNsJneN-m5i4k-zdux038CH=g0_hiLmwusuUv6s9vn846ePng_at_mail.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_at_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_at_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

Received on Sun Sep 09 2012 - 17:31:24 CDT