oracle sysaux表空间暴增

龙仔仔001 2014-12-09 04:38:46

生产库信息：linux5.4---oracle11.2.0.2.0 rac 操作系统内存64g，oracle sga_target=8g
sysaux ：
select *from dba_data_files where tablespace_name='SYSAUX'; ---autoex: No
select *from dba_tablespaces where tablespace_name='SYSAUX'; ---seg:auto

参考MOS文章：文档 ID 287679.1

1.Check the retention period is appropriate

针对下面数据有几个问题：
a.查询出的数据为什么有10条记录？不应该是一条吗?其他数据是用来干啥的？

SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
177568708 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
3471086047 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
3944815540 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
2882923506 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
4018666878 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
3770817663 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
672354195 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
2278779824 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
704380339 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
710677256 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

10 rows selected.
2.sysaux表空间组件信息如下：主要是SM/AWR,SM/OPTSTAT 占有空间较大
Item Space Used (GB) Schema Move Procedure
17 SM/AWR 38.12896729 SYS
18 SM/OPTST 264.769165 SYS

3.首先，我先查下了awr快照的保留时间等下如下
SQL> @/oradb/db/tech_st/11.2.0/rdbms/admin/awrinfo.sql

This script will report general AWR information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrinfo.txt. To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrinfo.txt
No errors.
No errors.
~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~

Report generated at
13:22:19 on Dec 04, 2014 ( Thursday ) in Timezone +08:00

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

Interesting Info: Significant Number of ASH On-demand Flushes!
--------------------------------------------------------------------------------
ASH On-demand Flushing % = ( 221/801 ) = 27.6%

DB_ID DB_NAME HOST_PLATFORM INST STARTUP_TIME LAST_ASH_SID PAR
------------ --------- ---------------------------------------- ----- ----------------- ------------ ---
* 177568708 PROD ebsdb01 - Linux x86 64-bit 1 09:35:16 (11/10) 97321000 YES
177568708 PROD ebsdb02 - Linux x86 64-bit 2 11:17:22 (12/01) 97032782 YES
672354195 R1211BLN rws60052app.us.oracle.com - 1 01:48:23 (11/25) 0 NO
704380339 PRODPACK ap653app - 1 00:32:32 (04/02) 0 NO
710677256 PROD ebsdb01 - 1 14:52:08 (10/28) 0 NO
2278779824 PRODPACK rws60051app.us.oracle.com - 1 03:27:53 (01/22) 0 NO
2882923506 FINPRD19 ap690are.us.oracle.com - 1 14:26:14 (02/09) 0 NO
3471086047 PRD10202 ap6002bld - 1 21:47:58 (03/28) 0 NO
3770817663 PRODPACK ap654app - 1 11:19:44 (01/23) 0 NO
3944815540 PRB19 ap6003bld - 1 23:36:42 (12/29) 0 NO
4018666878 RUP4BLP ap654app - 1 12:24:27 (10/23) 0 NO

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

*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size 304,249.8 MB ( 88% of 344,932.9 MB MAX with AUTOEXTEND OFF )
|
| Schema SYS occupies 304,123.1 MB ( 100.0% )
| Schema XDB occupies 59.8 MB ( 0.0% )
| Schema MDSYS occupies 31.1 MB ( 0.0% )
| Schema SYSTEM occupies 19.7 MB ( 0.0% )
| Schema OLAPSYS occupies 15.6 MB ( 0.0% )
| Schema DMSYS occupies 0.3 MB ( 0.0% )
| Schema APPQOSSYS occupies 0.3 MB ( 0.0% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name Schema Name Space Usage
| -------------------- -------------------- ----------------
| SM/OPTSTAT SYS 264,621.6 MB
| SM/AWR SYS 38,920.1 MB
| SM/ADVISOR SYS 418.3 MB
| XDB XDB 59.8 MB
| SM/OTHER SYS 57.8 MB
| AO SYS 53.4 MB
| XSOQHIST SYS 53.4 MB
| SDO MDSYS 31.1 MB
| SMON_SCN_TIME SYS 19.2 MB
| LOGMNR SYSTEM 17.7 MB
| XSAMD OLAPSYS 15.6 MB
| JOB_SCHEDULER SYS 11.6 MB
| LOGSTDBY SYSTEM 1.9 MB
| SQL_MANAGEMENT_BASE SYS 1.7 MB
| STREAMS SYS 1.2 MB
| PL/SCOPE SYS 0.4 MB
| AUTO_TASK SYS 0.3 MB
| AUDIT_TABLES SYS 0.0 MB
| EM SYSMAN 0.0 MB
| EM_MONITORING_USER DBSNMP 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
| 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
| WM WMSYS 0.0 MB
|
| Others (Unaccounted space) -35.3 MB
|

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

| This section displays schemas that are not registered
| in V$SYSAUX_OCCUPANTS
|
| Schema DMSYS occupies 0.3 MB
| Schema APPQOSSYS occupies 0.3 MB
|
| Total space 0.5 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 -35.8 MB
|
| Total space -35.8 MB
|
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 60 mins snapshot INTERVAL:
| AWR size/day 2,267.2 MB (96,733 K/snap * 24 snaps/day)
| AWR size/wk 15,870.3 MB (size_per_day * 7) per instance
| AWR size/wk 31,740.6 MB (size_per_day * 7) per database
|
| Estimates based on 24 snaps in past 24 hours:
| AWR size/day 2,267.2 MB (96,733 K/snap and 24 snaps in past 24 hours)
| AWR size/wk 15,870.3 MB (size_per_day * 7) per instance
| AWR size/wk 31,740.6 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 15,421.0 39.6 38,328 898.3 6,288.2 50% : 50%
ASH 10,993.6 28.2 27,324 640.4 4,482.8 88% : 12%
EVENTS 8,147.6 20.9 20,250 474.6 3,322.3 43% : 57%
SQLPLAN 1,364.0 3.5 3,390 79.5 556.2 71% : 29%
SPACE 1,218.4 3.1 3,028 71.0 496.8 67% : 33%
RAC 451.9 1.2 1,123 26.3 184.3 61% : 39%
SQL 40.1 0.1 100 2.3 16.3 66% : 34%
SQLBIND 8.0 0.0 20 0.5 3.3 38% : 63%
SQLTEXT 6.4 0.0 16 0.4 2.6 93% : 7%

4.查询过期的ash数据
SQL> SELECT COUNT(1) Orphaned_ASH_Rows
2 FROM wrh$_active_session_history a
3 WHERE NOT EXISTS (SELECT 1
4 FROM wrm$_snapshot
5 WHERE snap_id = a.snap_id
6 AND dbid = a.dbid
7 AND instance_number = a.instance_number);

ORPHANED_ASH_ROWS
-----------------
27145770
5.按上面mos文章所述，接下来应该删除这些数据：
针对这一步有几个问题：a.因为第一步中查询出还有其他数据库的信息,上述查询的27145770行数据能直接删除吗？这样大数据量删除我想到的办法是：此处采用批量删除，每次删除100万条数据，不知怎么样？
SQL> DELETE
FROM wrh$_active_session_history a
WHERE NOT EXISTS
(SELECT 1
FROM wrm$_snapshot
WHERE snap_id = a.snap_id
AND dbid = a.dbid
AND instance_number = a.instance_number
);
烦请大家给看看状况，该怎样处理这个问题？

...全文