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

最新推荐文章于 2021-04-18 15:01:26 发布
最新推荐文章于 2021-04-18 15:01:26 发布
阅读量128 收藏
点赞数

++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***为了后续报表中获取比例，一次性计算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--&gtsession 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--&gtphysical 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--&gtexecute 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--&gtparse 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--&gt
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--&gt
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)--&gtsql_summary.dbcpu_s
Captured SQL accounts for   XXX% of Total DB CPU--&gtsql_summary.cpucappct ***此处Captured SQL指计算总量时sql,即executions_total有变化的所有sql
SQL reported below exceeded  1.0% of Total DB CPU--&gt1.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--&gt
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):          --&gtsql_summary.dbtim_s
Captured SQL accounts for   xxx% of Total DB Time --&gtsql_summary.elacappct
SQL reported below exceeded  1.0% of Total DB Time --&gt1.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--&gt
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: --&gtsql_summary.getsa
Captured SQL accounts for   xxx% of Total Buffer Gets  --&gtsql_summary.bufcappct
SQL reported below exceeded  1.0% of Total Buffer Gets --&gt1.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--&gtsession 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:      --&gtsql_summary.phyra
-> Captured SQL accounts for   xxx% of Total Disk Reads       --&gtsql_summary.phycappct
-> SQL reported below exceeded  1.0% of Total Disk Reads       --&gt1.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--&gtphysical 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:       --&gtsql_summary.exea
-> Captured SQL accounts for   88.7% of Total Executions        --&gtsql_summary.execappct
-> SQL reported below exceeded  1.0% of Total Executions        --&gt1.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--&gtexecute 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:      --&gtsql_summary.prsea
Captured SQL accounts for    xxx% of Total Parse Calls       --&gtsql_summary.prscappct
SQL reported below exceeded  1.0% of Total Parse Calls       --&gt1.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--&gtparse 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--&gtshared 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-701151/

cuian7084
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle 生成 AWR 报告报错：ORA-06502，怎么破？
LuciferLiu_DBA
09-13 2716
每天一个 DBA 小知识，助你更进一步！
Oracle AWR Top SQL section
cixin1802的博客
04-20 138
Top SQL This section displays top SQL ordered by important SQL execution metrics. The top SQL section in the AW...
awr的top sql分析
03-30 195
连续看了两天的awr报表，发现此db中存在大量的等待事件。Log file sync、全表扫描，全索引扫描、latch等待，热点块（很可能是由于全表全索引扫描导致的热点快竞争）、检查点未完成，队列锁的竞争等的。 ...
基于MySQL5.7存储过程实现myawr平台的top sql功能
淡定波的博客
04-18 320
概述 某DBA大师曾说过，一个DBA要像了解自己的老婆一样了解自己管理的数据库，个人认为包含了两个方面的了解： 1、在稳定性层面来说，更多的是关注高可用、读写分离、负载均衡，灾备管理等等high level层面的措施（就好比要保证生活的稳定性） 2、在实例级别的来说，需要关注内存、IO、网络，热点表，热点索引，top sql，死锁，阻塞，历史上执行异常的SQL（好比生活品质细节） 一、MySQL数据库相关配置 my.cnf参考如下配置： performance_schema_max_digest_len
oracle修改每页显示数量,awr管理：修改awr top sql的数量
weixin_42298412的博客
04-10 480
How to 修改awr的topnsql的数量呢？SQL> show parameter statistics_levelNAME TYPE VALUE------------------------------------ ----------- ------------------------------sta...
SQL Long running & 理解 EXECUTIONS_TOTAL, EXECUTIONS_DELTA
有梦就别怕痛
08-07 1074
1. to get long running sql id SELECT A.USERNAME, A.MACHINE, A.SID, A.SERIAL#, A.STATUS, round(A.LAST_CALL_ET/60,2) AS MIN_WAIT, a.sql_id, substr(B.SQL_TEXT,1,100) as sqltxt, ...
AWR报告分析利器 ---兼容10g,11g,12c,18c,19c-附件资源
03-02
AWR报告分析利器 ---兼容10g,11g,12c,18c,19c-附件资源
Oracle-AWR-报告分析实例讲解.docx
最新发布
11-19
Oracle AWR 报告分析实例讲解 Oracle AWR 报告是 Oracle 数据库性能分析的重要工具之一，其可以帮助 DBA 快速了解数据库的性能瓶颈，并提供有价值的信息来优化数据库性能。在本文中，我们将从一个实际的 AWR 报告中...
awr报告awr报告awr报告awr报告
11-19
awr报告awr报告awr报告awr报告
使用脚本查看oracle缓存的sql语句情况
xiaoxin
03-04 1427
select t.sql_id,t.executions_total,t.disk_reads_total,t.parse_calls_total,a.sql_text from sys.wrh$_sqlstat t,sys.wrh$_sqltext a where t.parsing_schema_name='WW_XRAPUSER' and t.sql_id=a.sql_id;  执行这...
awr中一条top sql，查询all_objects
killvoon的专栏
07-07 2895
每天看看库上的awr报告，发现有条sql三个小时内执行了190多万次： SELECT NULL AS table_cat,          o.owner AS table_schem,          o.object_name AS table_name,          o.object_type AS table_type,          NULL
AWR 中 top sql 的信息获取 - 分析
10-22 461
1. 查看数据库AWR报告的生成频率. select * from dba_hist_wr_control; 2. 查看AWR报告中sql_id 及 sql_text. select * from dba_hist_...
Oracle Execute to Parse 执行解析比分析
热门推荐
乐沙弥的世界
10-29 1万+
Execute to Parse%是AWR报告中Instance Efficiency Percentages部分中重要的一个性能指标，反应了数据库SQL解析和执行的比率。这个比率值同时也涉及到了与cursor相关的参数以及硬解析，软解析，软软解析等。本文是围绕这个比率进行展开及描述。
【Oracle】AWR analyse
cojm55771的博客
10-10 212
AWR analyse--SQL ordered by Elapsed Time记录了执行总和时间的TOP SQL(请注意是监控范围内该SQL的执行时间总和，而不是单次SQL执行时间 Elapsed Time = CPU Tim...
Statspack报告收集
clp9519084的博客
07-28 131
俗话说，工欲善其事，必先利其器。做数据库性能分析，也要有一款好的工具。statspack就是oracle自带的一个强大的性能分析工具。安装statspack需要用户具有sysdba的权限。首先以sysdba登陆，为statspa...
查询执行高于10秒的SQL
cuiqin3495的博客
06-23 220
SELECT b.sql_id, a.module, a.snap_id, a.instance_number, a.elapsed_time_total, a.executions_total, b.sql_tex...
AWR TOP SQL实现
cpc84938的博客
07-08 576
1按解析次数排序 selecta.*, to_char(substr(b.sql_text,1,4000)) from (selectdhs.sql_id,...
什么是top sql
haiross的专栏
02-04 6018
请高手帮忙解释下什么是top sql 最好能举例 谢谢 按照各种资源使用量排序的SQL。 比如按照执行次数排序的，按照逻辑读排序的，按照CUP使用时间排序的。 TOP的意思就是取这些排序后的前几个。 ========================================= 其实就是SQL占用资源的降序排列 你可以根据v$sqlarea view 的DISK
Oracle AWR报告分析实例实战：利用DB Time评估系统压力
根据提供的内容，本文将对Oracle AWR报告分析实例进行讲解。AWR（Automatic Workload Repository）是Oracle数据库中的一个重要特性，它可以自动收集数据库的性能数据，包括系统活动、数据库活动、终端用户活动等。...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值