General guidelines for SYSAUX space issues [ID 552880.1]

查看AWR各详细使用空间描述

SQL> connect / as sysdba
SQL> @?/rdbms/admin/awrinfo.sql

In this Document
Purpose
Scope and Application
General guidelines for SYSAUX space issues

---

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.5 to 11.1.0.6
Information in this document applies to any platform.

Purpose

The purpose of this document is to give general guidelines for SYSAUX tablespace space issues to control and decrease the space usage. It refers to several metalink notes for details when necessary.

Scope and Application

This article is intended to help the database administrators to manage space issues in SYSAUX tablespace.

General guidelines for SYSAUX space issues

SYSAUX is a mandatory tablespace in 10g which stores all auxiliary database metadata related to various product options and features. Old data within SYSAUX tablespace is purged accordingly with retention policy and other settings of different components and tablespace shouldn't grow indefinitely. If retention period is set too high or settings to capture data is chosen at detailed level, tablespace can become too large. In this case, space can be reclaimed and growth rate can be controlled with choosing correct retention period and doing some maintenance tasks.

The following sections give a general roadmap for SYSAUX tablespace space issues to identify, control and decrease the space usage. It refers to several metalink notes for details when necessary.

1- Articles for general description of SYSAUX components, space usage and retention

The following articles provide general description of most common SYSAUX components, retention settings, their space usage and corresponding move procedures to reclaim space.

Note Description
Note 329984.1 Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER Note explains what kind of information is kept by the most common components, such as AWR, OPTSTAT, ADVISOR and OTHER and how to control the contents.
Note:243246.1 10G : SYSAUX Tablespace Note helps to identify the component with the high space usage from V$SYSAUX_OCCUPANTS and to see if there exists a move procedure for the component. If an occupant of the tablespace SYSAUX takes too much space, you can move it to another tablespace and move it back to SYSAUX to reclaim the unused space.
Note 287679.1 Space Management In Sysaux Tablespace with AWR in use Some of the components have no move procedure and some speacial tasks are required to delete old data. Note explains how to change retention period from Grid Control, identify large AWR objects in SYSAUX tablespace, how to delete / cancel old segment advisory data to control and reduce the space usage.

2- Determine AWR objects taking large space.

Script awrinfo.sql can be used to determine the AWR objects taking large space and check awrinfo.sql.

SQL> connect / as sysdba
SQL> @?/rdbms/admin/awrinfo.sql

Output is written to awrinfo.lst in the current working directory. Check section (3a) and (3b) in awrinfo.lst is used to find the component and segments with high space usage within AWR.

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

COMPONENT MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX
--------- --------- ------ ------------ ---------- ----------- ----------------
FIXED         136.9   67.3          394        9.2        64.6    53% : 47%
SQL            21.3   10.4           61        1.4        10.0    71% : 29%
EVENTS         17.9    8.8           52        1.2         8.5    52% : 48%
SPACE          13.6    6.7           39        0.9         6.4    67% : 33%
RAC             5.4    2.7           16        0.4         2.6    56% : 44%
SQLPLAN         3.6    1.8           10        0.2         1.7    83% : 17%
ASH             2.5    1.2            7        0.2         1.2    73% : 28%
SQLTEXT         0.7    0.3            2        0.0         0.3    91% : 9%
SQLBIND         0.4    0.2            1        0.0         0.2    57% : 43%

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

COMPONENT        MB SEGMENT_NAME - % SPACE_USED                                           SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
FIXED           8.0 WRH$_WAITCLASSMETRIC_HISTORY                                  -  87%  TABLE
FIXED           7.0 WRH$_WAITCLASSMETRIC_HIST_IND                                 -  67%  INDEX
FIXED           5.0 WRH$_SYSMETRIC_SUMMARY                                        -  72%  TABLE
FIXED           4.0 WRH$_SYSMETRIC_SUMMARY_INDEX                                  -  58%  INDEX
FIXED           3.0 WRH$_SYSMETRIC_HISTORY_INDEX                                  -  46%  INDEX
FIXED           3.0 WRH$_LATCH.WRH$_LATCH_1104313605_767                          -  32%  TABLE PARTITION
FIXED           3.0 WRH$_SYSMETRIC_HISTORY                                        -  45%  TABLE
...

If space usage is growing steadily for a segment even after retention period is expired and a partition is dropped, then this could be a known problem. You can search for known issues using component and segment name in the report output.

Bug:5974572 is about a similar problem where WRH$_SERVICE_STAT grows indefinitely in RAC environment when there is an application creating and dropping services with dbms_service create_service/delete_service calls repeatedly.

Bug:5974572 WRH$_SERVICE_STAT CAN GROW VERY LARGE ON RAC
Note 427529.1 EXTREME NUMBER OF ROWS ARE ACCUMULATED IN WRH$_SERVICE_STAT

3- If Enterprise Manager (EM, owner:SYSMAN) component takes too much space, you need to follow "Offline Monthly Tasks" to purge the repository tables. Some of the EM components may need to be shutdowned in order to purge the old data. This is documented in Oracle Enterprise Manager manuals and the following is given as a reference for 10.2 Grid Control.

Oracle® Enterprise Manager Advanced Configuration
10g Release 4 (10.2.0.4.0)
Part Number E10954-01

11 Sizing and Maximizing the Performance of Oracle Enterprise Manager
    11.2.3.2 Offline Monthly Tasks

Refer to the following link for details.
Offline Monthly Tasks

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9225895/viewspace-1031179/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9225895/viewspace-1031179/