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

[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