博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5752529.html
How to 修改awr的topnsql的数量呢？
SQL> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
awr里面关于sql的统计有如下方面：
有时候分析一个负荷较高的数据库，需要能在上述sql统计中中看到更多的SQL语句，以便进行性能分析和调优。需要调整top sql的数量，默认是30.
一般我们用dbms_workload_repository.modify_snapshot_settings来修改默认配置
SQL> desc dbms_workload_repository.modify_snapshot_settings
Parameter Type Mode Default?
--------- -------- ---- --------
RETENTION NUMBER IN Y --设定awr信息保留多长时间，单位是分钟，默认是60分钟
INTERVAL NUMBER IN Y --设定收集时间间隔，单位是分钟，默认是1周
TOPNSQL NUMBER IN Y --设定取多少条耗费资源的sql
DBID NUMBER IN Y
如把awr设置为10分钟收集一次、每次收集50条耗费资源的sql，并保留2天的收集数据，可以用如下方式修改
SQL> exec dbms_workload_repository.modify_snapshot_settings(2*24*60,10,50);
PL/SQL procedure successfully completed
在数据库修改如下：
SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(topnsql=>50);
PL/SQL procedure successfully completed.
修改后的结果如下：
SQL> col SNAP_INTERVAL format a30
SQL> col RETENTION format a30
SQL> set linesize 150
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------------ ------------------------------ ----------
2670872500 +00000 01:00:00.0 +00008 00:00:00.0 50
后来在生成的AWR报告里，在"SQL ordered by Elapsed Time"、"SQL ordered by CPU Time"等SQL 统计部分里sql语句的数量仍然是30条(最小值)，并没有变成50条。
DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS可以用来控制AWR报告里的SQL语句的数量，用法如下
--
PROCEDURE control_restricted_snapshot(allow IN BOOLEAN);
-- *************************************************************************
-- awr_set_report_thresholds (PROCEDURE)
-- Allows configuring of specified report thresholds. Allows control of
-- number of rows in the report.
--
-- Parameters
-- top_n_events - number of most significant wait events to be included
-- top_n_files - number of most active files to be included
-- top_n_segments - number of most active segments to be included
-- top_n_services - number of most active services to be included
-- top_n_sql - number of most significant SQL statements to be included
-- top_n_sql_max - number of SQL statements to be included if their
-- activity is greater than that specified by
-- top_sql_pct.
-- top_sql_pct - significance threshold for SQL statements between
-- top_n_sql and top_n_max_sql
-- shmem_threshold - shared memory low threshold
------------------------------------------------------------------------------------------------------------------------------------------------------
-- versions_threshold - plan version count low threshold
--
-- Note: effect of each setting depends on the type of report being
-- generated as well as on the underlying AWR data. Not all
-- settings are meaningful for each report type.
-- Invalid settings (such as negative numbers, etc,) are ignored.
-- *************************************************************************
PROCEDURE 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
);
如果我们要在生成的AWR报告里包含50条语句，那么可以执行如下修改
SQL> exec DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS(top_n_sql=>50);
PL/SQL procedure successfully completed.
然后再使用@?/rdbms/admin/awrrpt生成报告，就会看到更多的top SQL了。
---the end
阅读(2908) | 评论(0) | 转发(0) |
