Script:诊断SYSAUX表空间使用情况

Script:以下脚本可以用于诊断SYSAUX表空间使用情况

./opatch lsinventory -detail

@?/rdbms/admin/awrinfo

select dbms_stats.get_stats_history_retention from dual;

select dbms_stats.get_stats_history_availability from dual;

select min(SAVTIME), max(SAVTIME) from WRI$_OPTSTAT_TAB_HISTORY;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_ind_history;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_histhead_history;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_histgrm_history;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_aux_history;

select count(*) from sys.wri$_optstat_tab_history;

select count(*) from sys.wri$_optstat_ind_history;

select count(*) from sys.wri$_optstat_histhead_history;

select count(*) from sys.wri$_optstat_histgrm_history;

select count(*) from sys.wri$_optstat_aux_history;

select count(*) from sys.wri$_optstat_opr;

示例输出：

~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~

Report generated at
08:57:12 on Jun 01, 2012 ( Friday ) in Timezone -04:00

select count(*) from sys.wri$_optstat_aux_history;

select count(*) from sys.wri$_optstat_opr;

Warning: Non Default AWR Setting!
--------------------------------------------------------------------------------
Snapshot interval is 60 minutes and Retention is 8 days

       DB_ID DB_NAME   HOST_PLATFORM                             INST STARTUP_TIME      LAST_ASH_SID PAR
------------ --------- ---------------------------------------- ----- ----------------- ------------ ---
* 195600696  PROD      maclean1.oracle.com - Linux x86 64-bit       1 07:42:19 (06/01)         65130 YES
  195600696  PROD      maclean2.oracle.com - Linux x86 64-bit       2 07:41:20 (06/01)         65226 YES

########################################################
(I) AWR Snapshots Information
########################################################

*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size                        193.6 MB ( 1% of 32,768.0 MB MAX with AUTOEXTEND ON )
|
| Schema  SYS          occupies            101.1 MB (  52.2% )
| Schema  SYSMAN       occupies             74.5 MB (  38.5% )
| Schema  SYSTEM       occupies             13.7 MB (   7.1% )
| Schema  WMSYS        occupies              3.5 MB (   1.8% )
| Schema  DBSNMP       occupies              0.8 MB (   0.4% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
| EM                   SYSMAN                        74.5 MB
| SM/AWR               SYS                           56.2 MB
| LOGMNR               SYSTEM                        12.3 MB
| SM/ADVISOR           SYS                            8.9 MB
| SM/OPTSTAT           SYS                            7.7 MB
| SM/OTHER             SYS                            6.9 MB
| WM                   WMSYS                          3.5 MB
| SQL_MANAGEMENT_BASE  SYS                            1.7 MB
| PL/SCOPE             SYS                            1.6 MB
| AO                   SYS                            1.5 MB
| XSOQHIST             SYS                            1.5 MB
| LOGSTDBY             SYSTEM                         1.4 MB
| STREAMS              SYS                            1.0 MB
| EM_MONITORING_USER   DBSNMP                         0.8 MB
| JOB_SCHEDULER        SYS                            0.5 MB
| SMON_SCN_TIME        SYS                            0.5 MB
| AUTO_TASK            SYS                            0.3 MB
| AUDIT_TABLES         SYS                            0.0 MB
| EXPRESSION_FILTER    EXFSYS                         0.0 MB
| ORDIM                ORDSYS                         0.0 MB
| ORDIM/ORDDATA        ORDDATA                        0.0 MB
| ORDIM/ORDPLUGINS     ORDPLUGINS                     0.0 MB
| ORDIM/SI_INFORMTN_SC SI_INFORMTN_SCHEMA             0.0 MB
| SDO                  MDSYS                          0.0 MB
| STATSPACK            PERFSTAT                       0.0 MB
| TEXT                 CTXSYS                         0.0 MB
| TSM                  TSMSYS                         0.0 MB
| ULTRASEARCH          WKSYS                          0.0 MB
| ULTRASEARCH_DEMO_USE WK_TEST                        0.0 MB
| XDB                  XDB                            0.0 MB
| XSAMD                OLAPSYS                        0.0 MB
|
| Others (Unaccounted space)                         12.9 MB
|

******************************************
(1c) SYSAUX usage - Unregistered Schemas
******************************************

| This section displays schemas that are not registered
| in V$SYSAUX_OCCUPANTS
|
|
| Total space                                0.0 MB
|

*************************************************************
(1d) SYSAUX usage - Unaccounted space in registered schemas
*************************************************************
|
| This section displays unaccounted space in the registered
| schemas of V$SYSAUX_OCCUPANTS.
|
| Unaccounted space in SYS/SYSTEM           12.9 MB
|
| Total space                               12.9 MB
|
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 60 mins snapshot INTERVAL:
|    AWR size/day                          674.3 MB (28,768 K/snap * 24 snaps/day)
|    AWR size/wk                         4,719.8 MB (size_per_day * 7) per instance
|    AWR size/wk                         9,439.5 MB (size_per_day * 7) per database
|
| Estimates based on 1 snaps in past 24 hours:
|    AWR size/day                          533.8 MB (28,768 K/snap and 1 snaps in past 1.3 hours)
|    AWR size/wk                         3,736.5 MB (size_per_day * 7) per instance
|    AWR size/wk                         7,472.9 MB (size_per_day * 7) per database
|

**********************************
(3a) Space usage by AWR components (per database)
**********************************

COMPONENT        MB  % AWR  KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%
--------- --------- ------ ------------ ---------- ----------- ----------------
FIXED          35.8   63.7       18,336      340.2     2,381.5    56% : 44%
EVENTS          5.8   10.3        2,976       55.2       386.5    43% : 57%
SQL             4.0    7.1        2,048       38.0       266.0    72% : 28%
SPACE           3.9    7.0        2,016       37.4       261.8    65% : 35%
SQLPLAN         2.3    4.1        1,184       22.0       153.8    86% : 14%
RAC             1.3    2.2          640       11.9        83.1    65% : 35%
SQLTEXT         0.6    1.0          288        5.3        37.4    89% : 11%
ASH             0.6    1.0          288        5.3        37.4    67% : 33%
SQLBIND         0.3    0.6          160        3.0        20.8    60% : 40%

**********************************
(3b) Space usage within AWR Components (> 500K)
**********************************

COMPONENT        MB SEGMENT_NAME - % SPACE_USED                                           SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
FIXED           3.0 WRH$_SYSMETRIC_HISTORY_INDEX                                  -  33%  INDEX
FIXED           3.0 WRH$_SYSMETRIC_HISTORY                                        -   6%  TABLE
FIXED           2.0 WRH$_LATCH.WRH$_LATCH_195600696_0                             -  56%  TABLE PARTITION
FIXED           2.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_195600696_0                       -  53%  INDEX PARTITION
FIXED           0.9 WRH$_LATCH_PK.WRH$_LATCH_195600696_0                          -  78%  INDEX PARTITION
FIXED           0.8 WRH$_SYSSTAT.WRH$_SYSSTA_195600696_0                          -  85%  TABLE PARTITION
FIXED           0.7 WRH$_PARAMETER_PK.WRH$_PARAME_195600696_0                     -  80%  INDEX PARTITION
FIXED           0.7 WRH$_PARAMETER.WRH$_PARAME_195600696_0                        -  85%  TABLE PARTITION
FIXED           0.6 WRH$_SYSMETRIC_SUMMARY                                        -   8%  TABLE
FIXED           0.6 WRH$_WAITCLASSMETRIC_HIST_IND                                 -  29%  INDEX
FIXED           0.6 WRH$_WAITCLASSMETRIC_HISTORY                                  -  13%  TABLE
EVENTS          2.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__195600696_0               -  59%  INDEX PARTITION
EVENTS          0.9 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__195600696_0                  -  85%  TABLE PARTITION
EVENTS          0.5 WRH$_SYSTEM_EVENT.WRH$_SYSTEM_195600696_0                     -  63%  TABLE PARTITION
SQL             2.0 WRH$_SQLSTAT.WRH$_SQLSTA_195600696_0                          -  47%  TABLE PARTITION
SPACE           0.6 WRH$_SEG_STAT.WRH$_SEG_ST_195600696_0                         -  80%  TABLE PARTITION
SQLPLAN         2.0 WRH$_SQL_PLAN                                                 -  65%  TABLE
SQLTEXT         0.5 WRH$_SQLTEXT                                                  -  74%  TABLE

**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************

COMPONENT        MB SEGMENT_NAME                                                          SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR         4.0 SYSMAN.MGMT_MESSAGES                                                  TABLE
NON_AWR         4.0 SYSTEM.SYS_LOB0000001147C00009$$                                      LOBSEGMENT
NON_AWR         2.0 SYSMAN.MGMT_METRICS                                                   TABLE
NON_AWR         2.0 SYSMAN.MGMT_MESSAGES_PK                                               INDEX
NON_AWR         2.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST                                     INDEX
NON_AWR         1.0 SYS.SYS$SERVICE_METRICS_TAB                                           TABLE
NON_AWR         1.0 SYS.SYS_LOB0000006339C00038$$                                         LOBSEGMENT
NON_AWR         0.9 SYSMAN.MGMT_METRICS_PK                                                INDEX
NON_AWR         0.9 SYS.SYS_LOB0000005097C00005$$                                         LOBSEGMENT
NON_AWR         0.9 SYSMAN.MGMT_METRICS_IDX_03                                            INDEX
NON_AWR         0.9 SYSMAN.MGMT_METRICS_IDX_01                                            INDEX
NON_AWR         0.8 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY                                     TABLE
NON_AWR         0.8 SYSMAN.SYS_LOB0000015321C00004$$                                      LOBSEGMENT
NON_AWR         0.6 SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS                                     TABLE
NON_AWR         0.6 SYSMAN.MGMT_METRICS_RAW_PK                                            INDEX
NON_AWR         0.6 SYSMAN.MGMT_POLICIES                                                  TABLE
NON_AWR         0.6 SYS.I_WRI$_OPTSTAT_HH_ST                                              INDEX
NON_AWR         0.5 SYSMAN.MGMT_JOB_STEP_PARAMS                                           TABLE
NON_AWR         0.5 SYSMAN.MGMT_POLICY_ASSOC_CFG                                          TABLE
NON_AWR         0.5 SYSMAN.PK_MGMT_JOB_STEP_PARAMS                                        INDEX
NON_AWR         0.5 SYS.SYS_LOB0000006331C00004$$                                         LOBSEGMENT

**********************************
(5a) AWR snapshots - last 50
**********************************

Total snapshots in DB 195600696 Instance 2 = 1
Total snapshots in DB 195600696 Instance 1 = 1

      DBID    SNAP_ID  INST FLUSH_ELAPSED        ENDTM             STARTUP_TIME      STATUS ERRCNT
---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------
 195600696         20     1 +00000 00:00:13.4    07:52:14 (06/01)  07:42:19 (06/01)       0      0
 195600696         20     2 +00000 00:00:11.8    07:52:15 (06/01)  07:41:20 (06/01)       0      0

**********************************
(5b) AWR snapshots with errors or invalid
**********************************

no rows selected

**********************************
(5c) AWR snapshots -- OLDEST Non-Baselined snapshots
**********************************

      DBID  INST    SNAP_ID ENDTM             STATUS ERROR_COUNT
---------- ----- ---------- ----------------- ------ -----------
 195600696     1         20 07:52:14 (06/01)       0           0

**********************************
(6) AWR Control Settings - interval, retention
**********************************

       DBID  LSNAPID LSPLITID LSNAPTIME      LPURGETIME      FLAG INTERVAL          RETENTION         VRSN
----------- -------- -------- -------------- -------------- ----- ----------------- ----------------- ----
  195600696       20        0 06/01 07:52:28 06/01 08:11:13     2 +00000 01:00:00.0 +00008 00:00:00.0    5

**********************************
(7a) AWR Contents - row counts for each snapshots
**********************************

   SNAP_ID  INST        ASH        SQL      SQBND      FILES      SEGST     SYSEVT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
        20     1          3         99        257          5         55        155
        20     2         12        105        300          5         58        156

**********************************
(7b) AWR Contents - average row counts per snapshot
**********************************

SNAP_COUNT  INST        ASH    SQLSTAT    SQLBIND      FILES    SEGSTAT   SYSEVENT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
         1     2         12        105        300          5         58        156
         1     1          3         99        257          5         55        155

**********************************
(7c) AWR total item counts - names, text, plans
**********************************

   SQLTEXT    SQLPLAN   SQLBMETA     SEGOBJ   DATAFILE   TEMPFILE
---------- ---------- ---------- ---------- ---------- ----------
       407       4506       1267        297          5          1

########################################################
(II) Advisor Framework Info
########################################################

**********************************
(1) Advisor Tasks - Last 50
**********************************

OWNER/ADVISOR  TASK_ID/NAME                     CREATED          EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK       07:52:03 (05/20)                       AUTO  INITIAL
SYS/ADDM       24/ADDM:195600696_3              11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       12/ADDM:195600696_1_3            11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       23/ADDM:195600696_2_3            11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       13/ADDM:195600696_1_4            12:00:23 (05/20)          1          1 AUTO  COMPLETED
SYS/ADDM       25/ADDM:195600696_2_4            12:00:24 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       26/ADDM:195600696_4              12:00:24 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       14/ADDM:195600696_1_5            13:00:29 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       28/ADDM:195600696_5              13:00:29 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       27/ADDM:195600696_2_5            13:00:29 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       29/ADDM:195600696_2_6            14:00:17 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       15/ADDM:195600696_1_6            14:00:17 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       16/ADDM:195600696_6              14:00:17 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       17/ADDM:195600696_1_7            15:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       30/ADDM:195600696_2_7            15:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       18/ADDM:195600696_7              15:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       20/ADDM:195600696_8              16:00:34 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       19/ADDM:195600696_1_8            16:00:34 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       31/ADDM:195600696_2_8            16:00:34 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       32/ADDM:195600696_2_9            17:00:40 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       42/ADDM:195600696_9              17:00:40 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       41/ADDM:195600696_1_9            17:00:40 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       33/ADDM:195600696_2_10           18:00:51 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       44/ADDM:195600696_10             18:01:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       43/ADDM:195600696_1_10           18:01:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       45/ADDM:195600696_1_11           19:00:01 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       34/ADDM:195600696_2_11           19:00:01 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       35/ADDM:195600696_11             19:00:01 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       36/ADDM:195600696_2_12           20:00:02 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       46/ADDM:195600696_1_12           20:00:02 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       47/ADDM:195600696_12             20:00:02 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       37/ADDM:195600696_2_13           21:00:09 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       48/ADDM:195600696_1_13           21:00:09 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       49/ADDM:195600696_13             21:00:09 (05/20)          9          9 AUTO  COMPLETED
SYS/ADDM       50/ADDM:195600696_1_14           22:00:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       38/ADDM:195600696_2_14           22:00:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       51/ADDM:195600696_14             22:00:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       39/ADDM:195600696_2_15           23:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       52/ADDM:195600696_1_15           23:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       53/ADDM:195600696_15             23:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       54/ADDM:195600696_1_16           00:00:25 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       40/ADDM:195600696_2_16           00:00:25 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       55/ADDM:195600696_16             00:00:25 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       57/ADDM:195600696_17             01:00:28 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       56/ADDM:195600696_1_17           01:00:28 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       61/ADDM:195600696_2_17           01:00:28 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       62/ADDM:195600696_2_18           02:00:32 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       59/ADDM:195600696_18             02:00:32 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       58/ADDM:195600696_1_18           02:00:32 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       71/ADDM:195600696_19             03:00:41 (05/21)          1          1 AUTO  COMPLETED
SYS/ADDM       60/ADDM:195600696_1_19           03:00:41 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       63/ADDM:195600696_2_19           03:00:41 (05/21)          0          0 AUTO  COMPLETED

**********************************
(2) Advisor Task - Oldest 5
**********************************

OWNER/ADVISOR  TASK_ID/NAME                     CREATED          EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
SYS/ADDM       11/ADDM:195600696_1_2            10:00:13 (05/20)          1          1 AUTO  COMPLETED
SYS/ADDM       21/ADDM:195600696_2_2            10:00:13 (05/20)          1          1 AUTO  COMPLETED
SYS/ADDM       22/ADDM:195600696_2              10:00:14 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       23/ADDM:195600696_2_3            11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       12/ADDM:195600696_1_3            11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       24/ADDM:195600696_3              11:00:19 (05/20)          0          0 AUTO  COMPLETED

**********************************
(3) Advisor Tasks With Errors - Last 50
**********************************

OWNER/ADVISOR  TASK_ID/NAME                     CREATED          EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
TASK_DESC
--------------------------------------------------------------------------------------------------------------
ERROR_MSG
--------------------------------------------------------------------------------------------------------------
SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK       07:52:03 (05/20)                       AUTO  INITIAL
Description: Automatic SQL Tuning Task
Error Msg  :

########################################################
(III) ASH Usage Info
########################################################

**********************************
(1a) ASH histogram (past 3 days)
**********************************

**********************************
(1b) ASH histogram (past 1 day)
**********************************

**********************************
(2a) ASH details (past 3 days)
**********************************

**********************************
(2b) ASH details (past 1 day)
**********************************

**********************************
(2c) ASH sessions (Fg Vs Bg) (past 1 day across all instances in RAC)
**********************************

Foreground %
Background %
MMNL %

End of Report
Report written to awrinfo.txt
SQL>
GET_STATS_HISTORY_RETENTION
---------------------------
                         31

SQL> SQL>
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
20-MAY-12 07.52.20.633129000 AM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.52.27.091525 AM -04:00
20-MAY-12 06.01.15.033444 PM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.52.28.548542 AM -04:00
20-MAY-12 06.01.15.071814 PM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.52.27.103629 AM -04:00
20-MAY-12 06.01.15.048213 PM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.53.50.386756 AM -04:00
20-MAY-12 07.57.17.388624 AM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.57.47.200604 AM -04:00
20-MAY-12 07.57.47.205012 AM -04:00

SQL> SQL>
  COUNT(*)
----------
      2191

SQL> SQL>
  COUNT(*)
----------
      2631

SQL> SQL>
  COUNT(*)
----------
     21962

SQL> SQL>
  COUNT(*)
----------
      5206

SQL> SQL>
  COUNT(*)
----------
        18

SQL> SQL>
  COUNT(*)
----------
       126

若发现statistics统计信息占用了SYSAUX上的大量空间，则可以考虑 使用dbms_stats.purge_stats过程实施清理

Modify retention period: DBMS_STATS.ALTER_STATS_HISTORY_RETENTION
Purge old statistics: DBMS_STATS.PURGE_STATS This procedure purges old versions of statistics saved in the dictionary. To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.

1. Stats Retention is set to 31 days. By Default it is 7 days. I suggest you can consider reducing the retention days to 10.

2. The number of rows exits in the SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY is 42 Million Rows , where the data exists from “09-JUN-11 01.52.06.895132 PM -05:00 ” to “22-AUG-11 02.53.34.754747 PM -05:00”

3. For other tables ie: SYS. WRI$_OPTSTAT_TAB_HISTORY, SYS.WRI$_OPTSTAT_HISTGRM_HISTORY, the data exists for a month, this is just because the retention set to 31.

ACTION PLAN:
=========

A) Purge the Snapshot . Retain data for 10 days and then purge all the other data. (The number of days data to be kept depends on your Business needs)

SQL> SPOOL CHECK1.OUT

1) Try to force the execution of the purge operations :

SQL> alter session set “_swrf_test_action” = 72;

2) Purging snapshots :

SQL> exec dbms_stats.purge_stats(sysdate-&days);

using &days = n, n-1, n-2, …, n-x

3) Then again execute the below set of SQL’s and upload the spool output file

SQL> SELECT MIN(SAVTIME),MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY;

SQL> SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SQL> SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SQL> SPOOL OFF

B) Change the No. of Retention days from 31 to 10. For performing the same, use the below command : (The new retention time is specified in minutes.)

SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>14400);

NOTE : The parameter value is in minutes so 10 daysx 24 hours x 60 minutes = 14400 minutes

C) Check the value of STATISTICS_LEVEL

If the above value is Set to ALL, then consider changing to TYPICAL.

The reason for requesting to change it to TYPICAL , is because statistics_level=ALL will gather lot of additional information in AWR repository which would consume more space.

Most of the cases, if the statistics_level is set to TYPICAL then the growth would be stopped.

Once the above actions performed, please keep us posted on the status of the same