[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