ITPub博客

首页 > 数据库 > Oracle > 如何使用awr_set_report_thresholds控制AWR报告里的sql语句数量

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

原创 Oracle 作者:oliseh 时间:2015-01-16 20:25:37 0 删除 编辑
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 < snap_id and snap_id <= :eid group by sql_id order by nvl(sum(elapsed_time_delta), -1) desc, sql_id)) where rnum < :tsql_max and (rnum <= :tsql_min or norm_val > :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 < :tsql_max and (rnum <= :tsql_min or norm_val > :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表示了这个百分比值。

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值被忽略


(3)如果top_n_sql=N,top_n_sql_max=M,且满足N+1
返回的sql语句为
elapsed time最长的N条sql
+
从elapsed time最长的M条sql里选出elasped time最短的(M-N)条sql且从中进一步过滤出%total>top_pct_sql值的N1条sql,这里M-N=>N1>=0
这种情况下返回的sql数目为:N+N1


(4)如果top_n_sql=N,top_n_sql_max=M,且N>M
返回elapsed time最长的M-1条sql语句,top_pct_sql值被忽略


(5)如果top_n_sql=N,top_n_sql_max为空;
返回elapsed time最长的N条记录,top_pct_sql值被忽略


(6)如果top_n_sql_max=N,top_n_sql为空;
返回elapsed time最长的10条记录


从上面的第3种情况可见AWR中返回的sql语句可以由指定topn、外加total%值作为过滤的方式共同作用的


至此,我们已经知道如何使用AWR_SET_REPORT_THRESHOLDS来指定AWR报告里所列SQL数量的方法了。
其实AWR报告里的SQL数量取决于两个因素
1、是MMON进程把多少条SQL从内存Flush到AWR里
2、AWR_SET_REPORT_THRESHOLDS从AWR中的过滤出多少条sql生成在AWR报告里。


相比之下前者是基础,从我们已经获得的知识知道statistics_level参数的取值,DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS里topnsql的取值决定了有多少SQL从内存Flush到AWR:
当statistics_level=typical时这个数字是30
当statistics_level=ALL时这个数字是100
topnsql的优先级最高,不当能够直接指定sql数量,而且能够覆盖掉statistics_level的功效。


但在11gR2里实际情况和上述观点还是有不少差异的,上面的三个约束条件似乎都不起作用。我测试的结果是无论statistics_level=typical还是statistics_level=all(只要不是Basic),无论topnsql的值是多少,mmon都会将尽可能多的sql flush到AWR里,我是通过下面的方法来验证的,有兴趣的同学可以尝试一下,这里就不再赘述验证过程了


--当statistics_level=typical时,下面的结果>30;当statistics_level=ALL时,下面的结果>100;当topnsql=N时,下面的结果>N
select count(distinct(sql_id)) from dba_hist_sqlstat where snap_id<:end_snap_id and snap_id>= :begin_snap_id; --这里的end_snap_id和begin_snap_id代表了连续的两个snap_id,中间没有gap

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

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1617168