[20181024]修改awr收集信息设置.txt

SCOTT@test01p> @ ver1
PORT_STRING          VERSION    BANNER                                                                       CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0

SCOTT@test01p> select * from dba_hist_wr_control;
      DBID SNAP_INTERVAL     RETENTION         TOPNSQL CON_ID
---------- ----------------- ----------------- ------- ------
2286984624 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT      0

--//缺省1小时建立一个snapshot,保留8天.
--//生产系统这样设置不合理,建议修改保留45-60天:,假设修改60天(我感觉设置65条比较合适).
SYS@book> select 60*24*60 from dual ;
  60*24*60
----------
     86400
--//65*24*60 = 93600，65天相当于93600分钟。

--//实际上我最想了解的是topnsql的缺省到底是多少，看dbms_workload_repository可以发现如下内容。
--   topnsql (NUMBER)         - Top N SQL size.  The number of Top SQL
--                              to flush for each SQL criteria
--                              (Elapsed Time, CPU Time, Parse Calls,
--                               Shareable Memory, Version Count).
--
--                              The value for this setting will be not
--                              be affected by the statistics/flush level
--                              and will override the system default
--                              behavior for the AWR SQL collection.  The
--                              setting will have a minimum value of 30
--                              and a maximum value of 50000.
--
--                              IF NULL is specified, the
--                              current value is preserved.
--
--   topnsql (VARCHAR2)       - Users are allowed to specify the following
--                              values: ('DEFAULT', 'MAXIMUM', 'N')
--
--                              Specifying 'DEFAULT' will revert the system
--                              back to the default behavior of Top 30 for
--                              level TYPICAL and Top 100 for level ALL.
--
--                              Specifying 'MAXIMUM' will cause the system
--                              to capture the complete set of SQL in the
--                              cursor cache.  Specifying the number 'N' is
--                              equivalent to setting the Top N SQL with
--                              the NUMBER type.
--
--                              Specifying 'N' will cause the system
--                              to flush the Top N SQL for each criteria.
--                              The 'N' string is converted into the number
--                              for Top N SQL.

--//statistics_level=TYPICAL的情况下(一般很少人改这个参数),default＝30有点小，建议设置100－200之间。
--//最大也就是50000.

SYS@test> execute dbms_workload_repository.modify_snapshot_settings( interval => 60,retention => 93600,topnsql => 200);
PL/SQL procedure successfully completed.

SCOTT@test01p> select * from dba_hist_wr_control;
      DBID SNAP_INTERVAL     RETENTION         TOPNSQL    CON_ID
---------- ----------------- ----------------- ---------- ------
2286984624 +00000 01:00:00.0 +00065 00:00:00.0        200      0

--//估计sysaux空间消耗,可以执行:
@$ORACLE_HOME/rdbms/admin/utlsyxsz.sql