MOS关于syszux表空间_329984.1

张德鲁斯

981

2页

1次

2020-04-23

25墨值下载

UsageandStorageManagementofSYSAUXtablespaceoccupantsSM/AWR,SM/ADVISOR,

SM/OPTSTATandSM/OTHER(文档ID329984.1)

InthisDocument

Purpose

Scope

Details

References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later

Information in this document applies to any platform.

PURPOSE

This note explains some of the background to the SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER occupants in the

SYSAUX tablespace. It explains what kind of information is kept by the different sections, and how to control the contents.

SCOPE

The information in this note is applicable to any database from version 10.1 onwards.

DETAILS

In Oracle10 the SYSAUX tablespace was introduced. We can use the view V$SYSAUX_OCCUPANTS to see which options store

data in the SYSAUX tablespace. For example:

SELECT occupant_name, space_usage_kbytes FROM V$SYSAUX_OCCUPANTS;

Many occupants of the SYSAUX tablespace can be moved freely to another tablespace, but for a number of sections this is not

possible. This note will discuss the SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER occupants and their space usage.

NOTE:

These 4 SYSAUX occupants cannot be moved out of the SYSAUX tablespace.

For determining which occupants can be moved, please refer to

Note 243246.1 - SYSAUX New Mandatory Tablespace in Oracle 10g and higher

SM/AWR

This refers to the Automatic Workload Repository (a new feature in Oracle10).

Data in this section is retained for a certain amount of time (default 8 days) and the amount of data is also affected by the

number of AWR snapshots that are taken (default every hour).

We can check the current settings of this through the DBA_HIST_WR_CONTROL view:

select * from DBA_HIST_WR_CONTROL;

The retention time and snapshot interval can be adjusted through the

dbms_workload_repository.modify_snapshot_settings procedure.

Depending on how much activity (including queries) there is in a database, the size of the snapshot will vary. Therefore in

general "ups and downs" in the space usage are normal. For example, making the AWR snapshot interval larger should on

average reduce the amount of space needed for SM/AWR. However, it also means that there is more chance of varying sizes

because at a certain time you might just catch a busy period, which you might miss at other times. Also a shorter retention

time will on average reduce the amount of space needed for this, but it also makes any differences in the amount of collected

data on certain days more pronounced (for example a very different usage pattern during the weekend).

So in general we can say that we can adjust the parameters to increase or decrease the average amount of data stored, but

文档 329984.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_...

第1页共2页 2017/3/16 13:24

decreasing it will mean that more variation is to be expected.

SM/ADVISOR

This refers to the advisor frameworks tables. Advisors like SQL Tuning Advisor, SQL Access Advisor, ADDM use this

infrastructure. These advisors use the data in the AWR to build their advise, but once created this will be stored in their own

tables, and that is classed under this heading.

Deleting data from this component can be more complicated. Using the DB Console (or Grid Control) you can remove old

advisor results, which is simple to do. However if you find that a specific table that relates to the advisors consistently uses up

too much space then more action is needed. If you do not use this advisor, then most tables that store this information will

contain a TASK_ID, which can be used in conjunction with the DBA_ADVISOR_TASKS to find the task name, after which the

task can be stopped using DBMS_ADVISOR.DELETE_TASK (or using DB Control or Grid Control).

SM/OPTSTAT

This stores older version of optimizer statistics.

We have new capability in Oracle Database 10g that allows you restore old stats in case you encounter execution plan

regression when the stats are refreshed. The default retention for this data is 31 days. More details on how to use this can be

found in Note 452011.1. Strictly speaking, this is not part of AWR and is therefore not controlled by AWR retention parameter,

instead we can use the ALTER_STATS_HISTORY_RETENTION procedure of the DBMS_STATS package to set and change the

retention time for this data. As an example you can first check the current retention time and then change this to a different

amount (in days), for example 10 days:

select dbms_stats.get_stats_history_retention from dual;

exec dbms_stats.alter_stats_history_retention(10);

If you like to specifically purge data from before a certain timestamp you can use the PURGE_STATS procedure for this. For

example:

exec DBMS_STATS.PURGE_STATS(to_timestamp_tz('01-09-2006 00:00:00 Europe/London','DD-MM-YYYY HH24:MI:SS

TZR'));

SM/OTHER

This includes components like DB Feature Usage, Alert History, and other small miscellaneous components.

Note: If you are having sysaux space issues, please run following diagnostics:

Document 1934108.1 SRDC - How to Collect Standard Information for an Issue where Excessive SYSAUX Space is Used by

the Automatic Workload Repository (AWR)

REFERENCES

NOTE:243246.1 - SYSAUX New Mandatory Tablespace in Oracle 10g and higher

NOTE:287679.1 - How to Address Issues Where AWR Data Uses Significant Space in the SYSAUX Tablespace

NOTE:452011.1 - Restoring Table Statistics

NOTE:243245.1 - 10G New Storage Features and Enhancements

未找到您要查找的产品?

文档 329984.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_...

第2页共2页 2017/3/16 13:24