oracle修改每页显示数量,awr管理：修改awr top sql的数量

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