威凡网全力打造:网页编程、软件开发编程、平面设计、服务器端开发、操作系统等在线学习平台!学编程,上威凡网!
数据库>> Mysql Sqlserver Oracle SQlite Access Sybase SQL其他
当前位置:首页 > 数据库 > Sqlserver
下一节
 如何使用awr_set_report_thresholds控制AWR报告里的sql语句数量

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


申明:本教程内容由威凡网编辑整理并提供IT程序员分享学习,如文中有侵权行为,请与站长联系(QQ:254677821)!
下一节
相关教程  
其他教程  
Mysql
Sqlserver
Oracle
SQlite
Access
Sybase
SQL其他

违法和不良信息举报中心】邮箱:254677821@qq.com
Copyright©威凡网 版权所有 苏ICP备2023020142号
站长QQ:254677821