ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 基于AWR实现STATSPACK报告(5-TOPSQL)

基于AWR实现STATSPACK报告(5-TOPSQL)

原创 Linux操作系统 作者:redhouser 时间:2011-07-01 16:53:44 0 删除 编辑

++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***为了后续报表中获取比例,一次性计算sql_summary:
col bufcappct new_value bufcappct noprint
col getsa     new_value getsa     noprint
col phycappct new_value phycappct noprint
col phyra     new_value phyra     noprint
col execappct new_value execappct noprint
col exea      new_value exea      noprint
col prscappct new_value prscappct noprint
col prsea     new_value prsea     noprint
col cpucappct new_value cpucappct noprint
col elacappct new_value elacappct noprint
col dbcpua    new_value dbcpua    noprint
col dbcpu_s   new_value dbcpu_s   noprint
col dbtima    new_value dbtima    noprint
col dbtim_s   new_value dbtim_s   noprint
SELECT decode(&slr,
              0,
              to_number(NULL),
              100 * SUM(CASE ct.command_type
                          WHEN 47 THEN
                           0
                          ELSE
                           e.buffer_gets_total - nvl(b.buffer_gets_total, 0)
                        END) / &slr) bufcappct,
       &slr getsa,
       decode(&phyr,
              0,
              to_number(NULL),
              100 * SUM(CASE ct.command_type
                          WHEN 47 THEN
                           0
                          ELSE
                           e.disk_reads_total - nvl(b.disk_reads_total, 0)
                        END) / &phyr) phycappct,
       &phyr phyra,
       decode(&exe,
              0,
              to_number(NULL),
              100 * SUM(e.executions_total - nvl(b.executions_total, 0)) / &exe) execappct,
       &exe exea,
       decode(&prse,
              0,
              to_number(NULL),
              100 * SUM(e.parse_calls_total - nvl(b.parse_calls_total, 0)) /
              &prse) prscappct,
       &prse prsea,
       decode(&dbcpu,
              0,
              to_number(NULL),
              100 * SUM(e.cpu_time_total - nvl(b.cpu_time_total, 0)) /
              &dbcpu) cpucappct,
       decode(&dbcpu, 0, to_number(NULL), &dbcpu) dbcpua,
       decode(&dbcpu, 0, to_number(NULL), &dbcpu / 1000000) dbcpu_s,
       decode(&dbtim,
              0,
              to_number(NULL),
              100 * SUM(e.elapsed_time_total - nvl(b.elapsed_time_total, 0)) /
              &dbtim) elacappct,
       decode(&dbtim, 0, to_number(NULL), &dbtim) dbtima,
       decode(&dbtim, 0, to_number(NULL), &dbtim / 1000000) dbtim_s
  FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext ct
 WHERE b.snap_id(+) = &bid
   AND e.snap_id = &eid
   AND b.dbid(+) = &dbid
   AND e.dbid = &dbid
   AND b.instance_number(+) = &inst_num
   AND e.instance_number = &inst_num
   AND e.executions_total > nvl(b.executions_total, 0)
   AND b.sql_id(+) = e.sql_id
   AND b.sql_id = ct.sql_id
   AND b.dbid = ct.dbid;
其中:
slr-->session logical reads
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='session logical reads';

phyr-->physical reads
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='physical reads';

exe-->execute count
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='execute count';

prse-->parse count (total)
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='parse count (total)';

dbcpu-->
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sys_time_model b, dba_hist_sys_time_model e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='DB CPU';

dbtim-->
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sys_time_model b, dba_hist_sys_time_model e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='DB time';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


SQL ordered by CPU  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> Total DB CPU (s):           3,803
-> Captured SQL accounts for   39.2% of Total DB CPU
-> SQL reported below exceeded  1.0% of Total DB CPU
    CPU                  CPU per             Elapsd                     Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
    298.66      352,071       0.00    7.9   12043.35       9,287,906 3362116323
      select CURCDE_S,CURCDE_D,BID,ASK,FLAG,CRT_DATE,RATE
      from   EXC_RATE    where  TYPE=:2
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Total DB CPU (s)-->sql_summary.dbcpu_s
Captured SQL accounts for   XXX% of Total DB CPU-->sql_summary.cpucappct ***此处Captured SQL指计算总量时sql,即executions_total有变化的所有sql
SQL reported below exceeded  1.0% of Total DB CPU-->1.0

SELECT aa
  FROM (SELECT /*+ ordered use_nl (b st) */
         decode(0,
                0,
                lpad(nvl(to_char((e.cpu_time_total - nvl(b.cpu_time_total, 0)) /
                                 1000000,
                                 '999990.00'),
                         ' '),
                     10) || ' ' ||
                lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
                             '999,999,999'),
                     12) || ' ' ||
                lpad((to_char(decode(e.executions_total -
                                     nvl(b.executions_total, 0),
                                     0,
                                     to_number(NULL),
                                     ((e.cpu_time_total -
                                     nvl(b.cpu_time_total, 0)) /
                                     (e.executions_total -
                                     nvl(b.executions_total, 0))) / 1000000),
                              '999990.00')),
                     10) || ' ' ||
                lpad(decode(&dbcpu,
                            0,
                            ' ',
                            NULL,
                            ' ',
                            to_char((100 * (e.cpu_time_total -
                                    nvl(b.cpu_time_total, 0)) / &dbcpu),
                                    '990.0')),
                     6) || ' ' ||
                lpad(nvl(to_char((e.elapsed_time_total -
                                 nvl(b.elapsed_time_total, 0)) / 1000000,
                                 '999990.00'),
                         ' '),
                     10) || ' ' || lpad(to_char((e.buffer_gets_total -
                                                nvl(b.buffer_gets_total, 0)),
                                                '99,999,999,999'),
                                        15) || ' ' || lpad(e.sql_id, 10) || ' ' ||
                decode(e.module,
                       NULL,
                       dbms_lob.substr(st.sql_text, 100, 1),
                       rpad('Module: ' || e.module, 80) ||
                       dbms_lob.substr(st.sql_text, 100, 1)),
                dbms_lob.substr(st.sql_text, 100, 1)) aa
          FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
         WHERE b.snap_id(+) = &bid
           AND b.dbid(+) = e.dbid
           AND b.instance_number(+) = e.instance_number
           AND b.sql_id(+) = e.sql_id
           AND e.snap_id = &eid
           AND e.dbid = &dbid
           AND e.instance_number = &inst_num
           AND e.sql_id = st.sql_id
           AND e.dbid = st.dbid
           AND e.executions_total > nvl(b.executions_total, 0)
           AND decode(&dbcpu,
                      0,
                      2,
                      NULL,
                      2,
                      100 * (e.cpu_time_total - nvl(b.cpu_time_total, 0)) /
                      &dbcpu) > decode(&dbcpu, 0, 1, NULL, 2, 1)
         ORDER BY (e.cpu_time_total - nvl(b.cpu_time_total, 0)) DESC,
                  e.sql_id)
 WHERE rownum < 10;
其中:
dbcpu-->
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sys_time_model b, dba_hist_sys_time_model e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='DB CPU';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


SQL ordered by Elapsed  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> Total DB Time (s):          16,987
-> Captured SQL accounts for   81.9% of Total DB Time
-> SQL reported below exceeded  1.0% of Total DB Time
  Elapsed                Elap per            CPU                        Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)  Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
  12043.35      352,071       0.03   70.9     298.66               4 3362116323
      select CURCDE_S,CURCDE_D,BID,ASK,FLAG,CRT_DATE,RATE
      from   EXC_RATE    where  TYPE=:2
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Total DB Time (s):          -->sql_summary.dbtim_s
Captured SQL accounts for   xxx% of Total DB Time -->sql_summary.elacappct
SQL reported below exceeded  1.0% of Total DB Time -->1.0

SELECT aa
  FROM (SELECT /*+ ordered use_nl (b st) */
         decode(0,
                0,
                lpad(nvl(to_char((e.elapsed_time_total -
                                 nvl(b.elapsed_time_total, 0)) / 1000000,
                                 '999990.00'),
                         ' '),
                     10) || ' ' ||
                lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
                             '999,999,999'),
                     12) || ' ' ||
                lpad((to_char(decode(e.executions_total -
                                     nvl(b.executions_total, 0),
                                     0,
                                     to_number(NULL),
                                     ((e.elapsed_time_total -
                                     nvl(b.elapsed_time_total, 0)) /
                                     (e.executions_total -
                                     nvl(b.executions_total, 0))) / 1000000),
                              '999990.00')),
                     10) || ' ' ||
                lpad(decode(&dbtim,
                            0,
                            ' ',
                            to_char((100 * (e.elapsed_time_total -
                                    nvl(b.elapsed_time_total, 0)) / &dbtim),
                                    '990.0')),
                     6) || ' ' ||
                lpad(nvl(to_char((e.cpu_time_total - nvl(b.cpu_time_total, 0)) /
                                 1000000,
                                 '999990.00'),
                         ' '),
                     10) || ' ' ||
                lpad(to_char((e.disk_reads_total - nvl(b.disk_reads_total, 0)),
                             '99,999,999,999'),
                     15) || ' ' || lpad(e.sql_id, 10) || ' ' ||
                decode(e.module,
                       NULL,
                       dbms_lob.substr(st.sql_text, 100, 1),
                       rpad('Module: ' || e.module, 80) ||
                       dbms_lob.substr(st.sql_text, 100, 1)),
                dbms_lob.substr(st.sql_text, 100, 1)) aa
          FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
         WHERE b.snap_id(+) = &bid
           AND b.dbid(+) = e.dbid
           AND b.instance_number(+) = e.instance_number
           AND b.sql_id(+) = e.sql_id
           AND e.snap_id = &eid
           AND e.dbid = &dbid
           AND e.instance_number = &inst_num
           AND e.sql_id = st.sql_id
           AND e.dbid = st.dbid
           AND e.executions_total > nvl(b.executions_total, 0)
           AND decode(&dbtim,
                      0,
                      2,
                      100 *
                      (e.elapsed_time_total - nvl(b.elapsed_time_total, 0)) /
                      &dbtim) > decode(&dbtim, 0, 1, 1)
         ORDER BY (e.elapsed_time_total - nvl(b.elapsed_time_total, 0)) DESC,
                  e.sql_id)
 WHERE rownum < 10;
其中:
dbtim-->
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sys_time_model b, dba_hist_sys_time_model e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='DB time';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


SQL ordered by Gets  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> End Buffer Gets Threshold:     10000 Total Buffer Gets:     120,922,778
-> Captured SQL accounts for   34.8% of Total Buffer Gets
-> SQL reported below exceeded  1.0% of Total Buffer Gets
                                                     CPU      Elapsd     Old
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
      9,287,906      352,071           26.4    7.7   298.66  12043.35 3362116323
      select CURCDE_S,CURCDE_D,BID,ASK,FLAG,CRT_DATE,RATE
      from   EXC_RATE    where  TYPE=:2
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
End Buffer Gets Threshold:     10000 Total Buffer Gets: -->sql_summary.getsa
Captured SQL accounts for   xxx% of Total Buffer Gets  -->sql_summary.bufcappct
SQL reported below exceeded  1.0% of Total Buffer Gets -->1.0

SELECT aa
  FROM (SELECT /*+ ordered use_nl (b st) */
         decode(0,
                0,
                lpad(to_char((e.buffer_gets_total -
                             nvl(b.buffer_gets_total, 0)),
                             '99,999,999,999'),
                     15) || ' ' ||
                lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
                             '999,999,999'),
                     12) || ' ' ||
                lpad((to_char(decode(e.executions_total -
                                     nvl(b.executions_total, 0),
                                     0,
                                     to_number(NULL),
                                     (e.buffer_gets_total -
                                     nvl(b.buffer_gets_total, 0)) /
                                     (e.executions_total -
                                     nvl(b.executions_total, 0))),
                              '999,999,990.0')),
                     14) || ' ' ||
                lpad((to_char(100 * (e.buffer_gets_total -
                              nvl(b.buffer_gets_total, 0)) / &slr,
                              '990.0')),
                     6) || ' ' ||
                lpad(nvl(to_char((e.cpu_time_total - nvl(b.cpu_time_total, 0)) /
                                 1000000,
                                 '9990.00'),
                         ' '),
                     8) || ' ' ||
                lpad(nvl(to_char((e.elapsed_time_total -
                                 nvl(b.elapsed_time_total, 0)) / 1000000,
                                 '99990.00'),
                         ' '),
                     9) || ' ' || lpad(e.sql_id, 10) || '' ||
                decode(e.module,
                       NULL,
                       dbms_lob.substr(st.sql_text, 100, 1),
                       rpad('Module: ' || e.module, 80) ||
                       dbms_lob.substr(st.sql_text, 100, 1)),
                dbms_lob.substr(st.sql_text, 100, 1)) aa
          FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
         WHERE b.snap_id(+) = &bid
           AND b.dbid(+) = e.dbid
           AND b.instance_number(+) = e.instance_number
           AND b.sql_id(+) = e.sql_id
           AND e.snap_id = &eid
           AND e.dbid = &dbid
           AND e.instance_number = &inst_num
           AND e.sql_id = st.sql_id
           AND e.dbid = st.dbid
           AND e.executions_total > nvl(b.executions_total, 0)
           AND 100 * (e.buffer_gets_total - nvl(b.buffer_gets_total, 0)) / &slr > 1
         ORDER BY (e.buffer_gets_total - nvl(b.buffer_gets_total, 0)) DESC,
                  e.sql_id)
 WHERE rownum < 10;
其中:
slr-->session logical reads
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='session logical reads';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


SQL ordered by Reads  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> End Disk Reads Threshold:      1000  Total Disk Reads:       5,579,793
-> Captured SQL accounts for   67.8% of Total Disk Reads
-> SQL reported below exceeded  1.0% of Total Disk Reads
                                                     CPU      Elapsd     Old
 Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
      1,269,083            5      253,816.6   22.7   108.02    299.19 2878843975
  select t.TRANS_ID,    t.STATUS,     t.TRF_CUR,     t.TRF_AMT,     t.SERVICE_ID
  from TRANS_S t  where t.SERVICE_ID=:2         and     t.RETURN_DATE >= :3
  and t.RETURN_DATE < :4+1
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-> End Disk Reads Threshold:      1000  Total Disk Reads:      -->sql_summary.phyra
-> Captured SQL accounts for   xxx% of Total Disk Reads       -->sql_summary.phycappct
-> SQL reported below exceeded  1.0% of Total Disk Reads       -->1.0

SELECT aa
  FROM (SELECT /*+ ordered use_nl (b st) */
         decode(0,
                0,
                lpad(to_char((e.disk_reads_total - nvl(b.disk_reads_total, 0)),
                             '99,999,999,999'),
                     15) || ' ' ||
                lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
                             '999,999,999'),
                     12) || ' ' ||
                lpad((to_char(decode(e.executions_total -
                                     nvl(b.executions_total, 0),
                                     0,
                                     to_number(NULL),
                                     (e.disk_reads_total -
                                     nvl(b.disk_reads_total, 0)) /
                                     (e.executions_total -
                                     nvl(b.executions_total, 0))),
                              '999,999,990.0')),
                     14) || ' ' ||
                lpad((to_char(100 * (e.disk_reads_total -
                              nvl(b.disk_reads_total, 0)) / &phyr,
                              '990.0')),
                     6) || ' ' ||
                lpad(nvl(to_char((e.cpu_time_total - nvl(b.cpu_time_total, 0)) /
                                 1000000,
                                 '9990.00'),
                         ' '),
                     8) || ' ' ||
                lpad(nvl(to_char((e.elapsed_time_total -
                                 nvl(b.elapsed_time_total, 0)) / 1000000,
                                 '99990.00'),
                         ' '),
                     9) || ' ' || lpad(e.sql_id, 10) || '' ||
                decode(e.module,
                       NULL,
                       dbms_lob.substr(st.sql_text, 100, 1),
                       rpad('Module: ' || e.module, 80) ||
                       dbms_lob.substr(st.sql_text, 100, 1)),
                dbms_lob.substr(st.sql_text, 100, 1)) aa
          FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
         WHERE b.snap_id(+) = &bid
           AND b.dbid(+) = e.dbid
           AND b.instance_number(+) = e.instance_number
           AND b.sql_id(+) = e.sql_id
           AND e.snap_id = &eid
           AND e.dbid = &dbid
           AND e.instance_number = &inst_num
           AND e.sql_id = st.sql_id
           AND e.dbid = st.dbid
           AND e.executions_total > nvl(b.executions_total, 0)
           AND &phyr > 0
           AND 100 * (e.disk_reads_total - nvl(b.disk_reads_total, 0)) /
               &phyr > 1.0
         ORDER BY (e.disk_reads_total - nvl(b.disk_reads_total, 0)) DESC,
                  e.sql_id)
 WHERE rownum < 10;
其中:
phyr-->physical reads
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='physical reads';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


SQL ordered by Executions  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> End Executions Threshold:       100  Total Executions:       1,993,974
-> Captured SQL accounts for   88.7% of Total Executions
-> SQL reported below exceeded  1.0% of Total Executions
                                                CPU per    Elap per     Old
 Executions   Rows Processed   Rows per Exec    Exec (s)   Exec (s)  Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
     455,447         444,727              1.0       0.00        0.00 1359734244
  SELECT    SESSION_ID    FROM LOG_CON   WHERE USER_ID=:1
and    USER_TYPE='0'
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-> End Executions Threshold:       100  Total Executions:       -->sql_summary.exea
-> Captured SQL accounts for   88.7% of Total Executions        -->sql_summary.execappct
-> SQL reported below exceeded  1.0% of Total Executions        -->1.0
SELECT aa
  FROM (SELECT /*+ ordered use_nl (b st) */
         decode(0,
                0,
                lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
                             '999,999,999'),
                     12) || ' ' ||
                lpad(to_char((nvl(e.rows_processed_total, 0) -
                             nvl(b.rows_processed_total, 0)),
                             '99,999,999,999'),
                     15) || ' ' ||
                lpad((to_char(decode(nvl(e.rows_processed_total, 0) -
                                     nvl(b.rows_processed_total, 0),
                                     0,
                                     0,
                                     (e.rows_processed_total -
                                     nvl(b.rows_processed_total, 0)) /
                                     (e.executions_total -
                                     nvl(b.executions_total, 0))),
                              '9,999,999,990.0')),
                     16) || ' ' ||
                lpad(nvl(to_char((e.cpu_time_total - nvl(b.cpu_time_total, 0)) /
                                 (e.executions_total -
                                 nvl(b.executions_total, 0)) / 1000000,
                                 '999990.00'),
                         ' '),
                     10) || ' ' ||
                lpad(nvl(to_char((e.elapsed_time_total -
                                 nvl(b.elapsed_time_total, 0)) /
                                 (e.executions_total -
                                 nvl(b.executions_total, 0)) / 1000000,
                                 '9999990.00'),
                         ' '),
                     11) || ' ' || lpad(e.sql_id, 10) || ' ' ||
                decode(e.module,
                       NULL,
                       dbms_lob.substr(st.sql_text, 100, 1),
                       rpad('Module& ' || e.module, 80) ||
                       dbms_lob.substr(st.sql_text, 100, 1)),
                dbms_lob.substr(st.sql_text, 100, 1)) aa
          FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
         WHERE b.snap_id(+) = &bid
           AND b.dbid(+) = e.dbid
           AND b.instance_number(+) = e.instance_number
           AND b.sql_id(+) = e.sql_id
           AND e.snap_id = &eid
           AND e.dbid = &dbid
           AND e.instance_number = &inst_num
           AND e.sql_id = st.sql_id
           AND e.dbid = st.dbid
           AND e.executions_total > nvl(b.executions_total, 0)
           AND 100 * (e.executions_total - nvl(b.executions_total, 0)) / &exe > 1
         ORDER BY (e.executions_total - nvl(b.executions_total, 0)) DESC,
                  e.sql_id)
 WHERE rownum < 10;
其中:
exe-->execute count
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='execute count';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


SQL ordered by Parse Calls  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> End Parse Calls Threshold:      1000 Total Parse Calls:         461,034
-> Captured SQL accounts for   65.2% of Total Parse Calls
-> SQL reported below exceeded  1.0% of Total Parse Calls
                           % Total    Old
 Parse Calls  Executions   Parses  Hash Value
------------ ------------ -------- ----------
      22,615       45,556     4.91 2828026679
  select sysdate from dual
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
End Parse Calls Threshold:      1000 Total Parse Calls:      -->sql_summary.prsea
Captured SQL accounts for    xxx% of Total Parse Calls       -->sql_summary.prscappct
SQL reported below exceeded  1.0% of Total Parse Calls       -->1.0

SELECT aa
  FROM (SELECT /*+ ordered use_nl (b st) */
         decode(0,
                0,
                lpad(to_char((e.parse_calls_total -
                             nvl(b.parse_calls_total, 0)),
                             '999,999,999'),
                     12) || ' ' ||
                lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
                             '999,999,999'),
                     12) || ' ' ||
                lpad(to_char(100 * (e.parse_calls_total -
                             nvl(b.parse_calls_total, 0)) / &prse,
                             '990.09'),
                     8) || ' ' || lpad(e.sql_id, 10) || ' ' || rpad(' ', 34) ||
                decode(e.module,
                       NULL,
                       dbms_lob.substr(st.sql_text, 100, 1),
                       rpad('Module& ' || e.module, 80) ||
                       dbms_lob.substr(st.sql_text, 100, 1)),
                dbms_lob.substr(st.sql_text, 100, 1)) aa
          FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
         WHERE b.snap_id(+) = &bid
           AND b.dbid(+) = e.dbid
           AND b.instance_number(+) = e.instance_number
           AND b.sql_id(+) = e.sql_id
           AND e.snap_id = &eid
           AND e.dbid = &dbid
           AND e.instance_number = &inst_num
           AND e.sql_id = st.sql_id
           AND e.dbid = st.dbid
           AND 100 * (e.parse_calls_total - nvl(b.parse_calls_total, 0)) /
               &prse > 1
         ORDER BY (e.parse_calls_total - nvl(b.parse_calls_total, 0)) DESC,
                  e.sql_id)
 WHERE rownum < 10;
其中:
prse-->parse count (total)
SELECT e.VALUE - b.VALUE
  FROM dba_hist_sysstat b, dba_hist_sysstat e
 WHERE b.dbid = e.dbid
   AND b.instance_number = e.instance_number
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.stat_name = e.stat_name
   AND b.stat_name ='parse count (total)';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


SQL statements ordered by Sharable Memory
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT aa
  FROM (SELECT /*+ ordered use_nl (b st) */
         decode(0,
                0,
                lpad(to_char(e.sharable_mem, '999,999,999,999'), 16) || ' ' ||
                lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
                             '999,999,999'),
                     12) || ' ' ||
                lpad((to_char(100 * e.sharable_mem / &espm, '990.0')), 7) || ' ' ||
                lpad(e.sql_id, 12) || ' ' || rpad(' ', 29) ||
                decode(e.module,
                       NULL,
                       dbms_lob.substr(st.sql_text, 100, 1),
                       rpad('Module& ' || e.module, 80) ||
                       dbms_lob.substr(st.sql_text, 100, 1)),
                dbms_lob.substr(st.sql_text, 100, 1)) aa
          FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
         WHERE b.snap_id(+) = &bid
           AND b.dbid(+) = e.dbid
           AND b.instance_number(+) = e.instance_number
           AND b.sql_id(+) = e.sql_id
           AND e.snap_id = &eid
           AND e.dbid = &dbid
           AND e.instance_number = &inst_num
           AND e.sql_id = st.sql_id
           AND e.dbid = st.dbid
           AND e.executions_total > nvl(b.executions_total, 0)
           AND e.sharable_mem > &esmt
         ORDER BY e.sharable_mem DESC, e.sql_id)
 WHERE rownum < 10;
其中:
espm=esp-->shared pool
  SELECT VALUE
  FROM dba_hist_parameter t
 WHERE t.dbid = &dbid
   AND t.instance_number = &inst_num
   AND t.snap_id = &bid
   AND t.parameter_name = 'shared_pool_size';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


SQL statements ordered by Version Count
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT aa
  FROM (SELECT /*+ ordered use_nl (b st) */
         decode(0,
                0,
                lpad(to_char(e.version_count, '999,999'), 8) || ' ' ||
                lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
                             '999,999,999'),
                     12) || ' ' || lpad(e.sql_id, 12) || ' ' || rpad(' ', 45) ||
                decode(e.module,
                       NULL,
                       dbms_lob.substr(st.sql_text, 100, 1),
                       rpad('Module& ' || e.module, 80) ||
                       dbms_lob.substr(st.sql_text, 100, 1)),
                dbms_lob.substr(st.sql_text, 100, 1)) aa
          FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
         WHERE b.snap_id(+) = &bid
           AND b.dbid(+) = e.dbid
           AND b.instance_number(+) = e.instance_number
           AND b.sql_id(+) = e.sql_id
           AND e.snap_id = &eid
           AND e.dbid = &dbid
           AND e.instance_number = &inst_num
           AND e.sql_id = st.sql_id
           AND e.dbid = st.dbid
           AND e.executions_total > nvl(b.executions_total, 0)
           AND e.version_count > &evc
         ORDER BY e.version_count DESC, e.sql_id)
 WHERE rownum < 10;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


SQL ordered by Cluster Wait Time  DB/Inst: ORADB/oraDB1  Snaps: 66-67
  Cluster      CWT % of     Elapsd        CPU                      Old
Wait Time (s) Elapsd Time   Time (s)    Time (s)    Executions   Hash Value
------------- ----------- ----------- ----------- -------------- ----------
    11,408.89        94.7   12,043.35      298.66        352,071 3362116323
      select CURCDE_S,CURCDE_D,BID,ASK,FLAG,CRT_DATE,RATE
      from   EXC_RATE    where  TYPE=:2
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT aa
  FROM (SELECT /*+ ordered use_nl (b st) */
         decode(0,
                0,
                lpad(nvl(to_char((e.clwait_total - nvl(b.clwait_total, 0)) /
                                 1000000,
                                 '9,999,999.00'),
                         ' '),
                     13) || ' ' ||
                lpad(nvl(to_char(100 *
                                 (e.clwait_total - nvl(b.clwait_total, 0)) /
                                 (e.elapsed_time_total -
                                 nvl(b.elapsed_time_total, 0)),
                                 '990.0'),
                         ' '),
                     11) || ' ' ||
                lpad(nvl(to_char((e.elapsed_time_total -
                                 nvl(b.elapsed_time_total, 0)) / 1000000,
                                 '999,990.00'),
                         ' '),
                     11) || ' ' ||
                lpad(nvl(to_char((e.cpu_time_total - nvl(b.cpu_time_total, 0)) /
                                 1000000,
                                 '999,990.00'),
                         ' '),
                     11) || ' ' ||
                lpad(to_char((e.executions_total - nvl(b.executions_total, 0)),
                             '9,999,999,999'),
                     14) || ' ' || lpad(e.sql_id, 10) || '' || rpad(' ', 5) ||
                decode(e.module,
                       NULL,
                       dbms_lob.substr(st.sql_text, 100, 1),
                       rpad('Module& ' || e.module, 80) ||
                       dbms_lob.substr(st.sql_text, 100, 1)),
                dbms_lob.substr(st.sql_text, 100, 1)) aa,
         e.sql_id hv
          FROM dba_hist_sqlstat e, dba_hist_sqlstat b, dba_hist_sqltext st
         WHERE b.snap_id(+) = &bid
           AND b.dbid(+) = e.dbid
           AND b.instance_number(+) = e.instance_number
           AND b.sql_id(+) = e.sql_id
           AND e.snap_id = &eid
           AND e.dbid = &dbid
           AND e.instance_number = &inst_num
           AND e.sql_id = st.sql_id
           AND e.dbid = st.dbid
           AND e.clwait_total > nvl(b.clwait_total, 0)
         ORDER BY (e.clwait_total - nvl(b.clwait_total, 0)) DESC, e.sql_id)
 WHERE rownum < 10;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-701151/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    812045