Home >Database >Mysql Tutorial >如何使用awr_set_report_thresholds控制AWR报告里的sql语句数量

如何使用awr_set_report_thresholds控制AWR报告里的sql语句数量

WBOY
WBOYOriginal
2016-06-07 16:33:311608browse

AWR报告里和sql语句有关的section有SQL ordered by Elapsed Time、SQL ordered by CPU Time、SQL ordered by User I/O Wait Time

AWR报告里和sql语句有关的section有SQL ordered by Elapsed Time、SQL ordered by CPU Time、SQL ordered by User I/O Wait Time、SQL ordered by Gets等。一次在分析一个负荷较高的数据库时为了能在上述section中看到更多的SQL语句,特意通过DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(topnsql=>50)将AWR捕捉的SQL语句数量上限调整为50,,可以从dba_hist_wr_control里查到调整后的结果:
col SNAP_INTERVAL format a30
 col RETENTION format a30
 set linesize 150
 select * from dba_hist_wr_control;
      DBID SNAP_INTERVAL                  RETENTION                      TOPNSQL
 ---------- ------------------------------ ------------------------------ ----------
  617151977 +00000 01:00:00.0              +00035 00:00:00.0              50

但后来在生成的AWR报告里,在"SQL ordered by Elapsed Time"、"SQL ordered by CPU Time"等section里sql语句的数量仍然是10条,并没有变成50条。后来发现在11gR2里有DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS可以用来控制AWR报告里的SQL语句的数量,用法如下
DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS(
    top_n_events        IN  NUMBER DEFAULT NULL,
    top_n_files          IN  NUMBER DEFAULT NULL,
    top_n_segments      IN  NUMBER DEFAULT NULL,
    top_n_services      IN  NUMBER DEFAULT NULL,
    top_n_sql            IN  NUMBER DEFAULT NULL,
    top_n_sql_max        IN  NUMBER DEFAULT NULL,
    top_sql_pct          IN  NUMBER DEFAULT NULL,
    shmem_threshold      IN  NUMBER DEFAULT NULL,
    versions_threshold  IN  NUMBER DEFAULT NULL);

其中于到sql语句数量有关的参数是top_n_sql、top_n_sql_max、top_sql_pct,如果我们要在生成的AWR报告里包含50条语句,那么可以先执行
exec DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS(top_n_sql=>50),然后再使用@?/rdbms/admin/awrrpt生成报告,如此报告里便能看到top 50的SQL了,记住DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS的执行结果仅在session级别生效。
对@?/rdbms/admin/awrrpt的执行过程进行了10046 trace,从trace结果里摘录了一段生成"SQL ordered by Elapsed Time"信息的sql:

with sqt as (select elap, cput, exec, iowt, norm_val, sql_id, module,rnum from (select sql_id, module, elap, norm_val, cput, exec, iowt,rownum rnum from (select sql_id,max(module) module,sum(elapsed_time_delta) elap,(100 *(sum(elapsed_time_delta) / nullif(:dbtime,0))) norm_val ,sum(cpu_time_delta) cput,sum(executions_delta) exec,sum(iowait_delta) iowt from dba_hist_sqlstat where dbid = :dbid and instance_number = :inst_num and :bid :top_pct_sql)) select /*+ NO_MERGE(sqt) */ nvl((sqt.elap/1000000),to_number(null)),sqt.exec,
decode(sqt.exec, 0,to_number(null),(sqt.elap / sqt.exec / 1000000)),sqt.norm_val,decode(sqt.elap, 0, to_number(null), (100 * (sqt.cput / sqt.elap))),decode(sqt.elap, 0, to_number(null), (100 * (sqt.iowt / sqt.elap))),sqt.sql_id,to_clob(decode(sqt.module, null,null,'Module: ' || sqt.module)),nvl(st.sql_text,to_clob('** SQL Text Not Available **')) from sqt, dba_hist_sqltext st where st.sql_id(+) = sqt.sql_id and st.dbid(+) = :dbid order by sqt.rnum

可以看出其中有一段where rnum :top_pct_sql)这便是AWR_SET_REPORT_THRESHOLDS的设置值对AWR结果起到的过滤作用,也可以看出dba_hist_sqlstat和dba_hist_sqltext是sql统计结果的主要来源。解释一下AWR_SET_REPORT_THRESHOLDS里三个参数top_n_sql、top_n_sql_max、top_sql_pct的用途。抽象一点,top_sql_pct表示某个sql_id对应的sql语句所消耗的资源占整个DB资源的百分比;
 具体一点,
 拿sql ordered by elapsed time里列出的sql来说,top_sql_pct表示每条sql语句消耗的时间占db time的百分比:top_sql_pct_for_elapsed_time%=(sql_elapse_time/db_time)*100%;
 如果拿sql ordered by cpu time里列出的sql来说,top_sql_pct表示每条sql语句消耗的cpu时间占db cpu的百分比:top_sql_pct_for_cpu_time%=(sql_cpu_time/db_cpu)*100%;
以此类推,AWR里用%Total表示了这个百分比值。

如何使用awr_set_report_thresholds控制AWR报告里的sql语句数量

top_n_sql、top_n_sql_max都是表示按照Elapsed Time、CPU Time、Gets等指标数值从高到低排序后返回的sql语句的数量,结合top_sql_pct通过下面几个场景解释一下top_n_sql、top_n_sql_max所起的作用(以sql ordered by elapsed time为例)

(1)如果top_n_sql=top_n_sql_max=N
返回elapsed time最长的N-1条sql,top_sql_pct值被忽略

(2)如果top_n_sql=N,top_n_sql_max=M,且满足N+1=M
返回elapsed time最长的N条sql,top_sql_pct值被忽略

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn