High Table space usage in SYSAUX

Source: Internet
Author: User

High Table space usage in SYSAUX

The SYSAUX tablespace is an auxiliary tablespace of the system tablespace. It mainly stores EM-related content and table statistics, AWR snapshots, and audit information, if SYSAUX tablespace is not configured by default, it will expand over time.

After several times of continuous expansion, the table space of SYSAUX has been increased. Currently, 20 Gbit/s, so it is time to consider losing weight.

 

1. View table space usage

SQL>

Select Tablespace_Name,

Sum_m,

Max_m,

Count_Blocks Free_Blk_Cnt,

Sum_Free_m,

To_Char (100 * Sum_Free_m/Sum_m, '99. 9999 ') |' % 'As Pct_Free,

100-To_Char (100 * Sum_Free_m/Sum_m, '99. 9999 ') |' % 'As Pct_used

From (Select Tablespace_Name, Sum (Bytes)/1024/1024 AsSum_m

From Dba_Data_Files

Group By Tablespace_Name)

Left Join

(Select Tablespace_Name As Fs_Ts_Name,

Max (Bytes)/1024/1024 As Max_m,

Count (Blocks) As Count_Blocks,

Sum (Bytes/1024/1024) As Sum_Free_m

From Dba_Free_Space

Group ByTablespace_Name)

On Tablespace_Name = Fs_Ts_Name

Order by Sum_Free_m/Sum_m;

 

TABLESPACE_NAME SUM_M MAX_M FREE_BLK_CNT SUM_FREE_MPCT_FREE PCT_USED

---------------------------------------------------------------------------------------------

SYSAUX 21652 1031 13 1032.5 4.7686% 95.2314%

NTICKET_DB 235520 7798 4922 46894 19.9108%

NTICKET_INDEX 112640 2418 764 25382 22.5337%

SYSTEM 2798 1884 7 1916.125 68.4820% 31.518%

USERS 5 3.6875 1 3.6875 73.7500% 26.25%

PORTALMAIL 200 168.125 2 170 85.0000% 15%

UNDOTBS2 10265 3596 200 9936.8125 96.8028%

UNDOTBS3 10265 3593 248 9983 97.2528%

UNDOTBS1 24455 3968 184 24280.625. 99.2870%

STRATEGY 20480 3968 6 20478 99.9902%. 0098%

 

 

2. view the proportion of each category project in the SYSAUX tablespace to the storage space. We can see that AWR snapshots occupy about 20 GB of space and the statistical information is about mb. Meanwhile, the database disables audit audit_trail, therefore, the audit table aud $ does not occupy space.

 

SQL> col Item For a30

SQL> col "Space Used (GB)" For a10

SQL> col Schema For a20

SQL> col "MoveProcedure" For a200

SQL>

SQL> SELECT occupant_name "Item ",

Round (space_usage_kbytes/1024/1024, 3) "Space Used (GB )",

Schema_name "Schema ",

Move_procedure "MoveProcedure"

FROM v $ sysaux_occupants

Order by 2 Desc;

 

Item Space UsedSchema Move Procedure

---------------------------------------------------------------------------------------------------

SM/AWR 19.103 SYS

SM/OPTSTAT 0.311 SYS

SM/ADVISOR 0.248 SYS

XDB 0.124 XDB. DBMS_XDB.MOVEXDB_TABLESPACE

SDO 0.073 MDSYS. MOVE_SDO

EM 0.045 SYSMAN emd_maintenance.move_em_tblspc

XSOQHIST 0.037 SYS DBMS_XSOQ.OlapiMoveProc

AO 0.037SYS DBMS_AW.MOVE_AWMETA

ORDIM/ORDDATA 0.013 ORDDATA ordsys. ord_admin.move_ordim_tblspc

LOGMNR 0.013 system sys. DBMS_LOGMNR_D.SET_TABLESPACE

AUDIT_TABLES 0 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables

 

 

3. Modify the retention time of statistical information. The default value is 31 days. Here, the value is changed to 7 days. Expired statistics are automatically deleted.

SQL> selectdbms_stats.get_stats_history_retention from dual;

 

GET_STATS_HISTORY_RETENTION

---------------------------

31

 

SQL> exec dbms_stats.alter_stats_history_retention (15 );

 

PL/SQL procedure successfullycompleted

 

SQL> selectdbms_stats.get_stats_history_retention from dual;

 

GET_STATS_HISTORY_RETENTION

---------------------------

15

4. Change the AWR snapshot retention period to 7 days (7*24*60). The snapshot is collected every hour. You can also view and modify the snapshot on the EM page.

 

-- Check that the current system is retained for 8 days, sampling once an hour

SQL> select * fromdba_hist_wr_control;

 

DBID SNAP_INTERVAL RETENTION TOPNSQL

--------------------------------------------------------------------------------------------------

1494575446 + 0000001: 00: 00.0 + 0000800: 00: 00.0 DEFAULT

 

 

--

SQL>

Begin

Dbms_workload_repository.modify_snapshot_settings (

Interval => 60,

Retention => 10080, -- minute

Topnsql = & gt; 100

);

End;

 

ORA-13541: system move window baseline size (691200) greater than retention time (604800)

ORA-06512: In "SYS. DBMS_WORKLOAD_REPOSITORY", line 174

ORA-06512: In "SYS. DBMS_WORKLOAD_REPOSITORY", line 222

ORA-06512: In line 2

Here, 691200 (8*24*60*60) and 604800 (7*24*60*60) are in seconds.

 

An error is reported because the current system moving window is larger than the current time window.

 

-- View the current MOVING_WINDOW_SIZE of the system,

SQL> selectdbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

 

DBID BASELINE_NAME BASELINE_TYPEMOVING_WINDOW_SIZE

----------------------------------------------------------------------------------

1494575446SYSTEM_MOVING_WINDOW MOVING_WINDOW 8

 

-- Modify the size to 7, that is, 7 days.

SQL> execdbms_workload_repository.modify_baseline_window_size (7 );

 

PL/SQL procedure successfullycompleted

 

 

-- Modify the retention time of the AWR snapshot again

SQL> execDBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (interval => 60, retention => 7*24*60 );

 

PL/SQL procedure successfullycompleted

 

Note: You can also use EM to modify AWR settings.

 

 

5. Delete the AWR snapshot and view the SYSAUX tablespace usage again.

 

-- Query the minimum and maximum snapshot IDS

SQL> selectmin (snap_id), max (snap_id) from dba_hist_snapshot;

 

MIN (SNAP_ID) MAX (SNAP_ID)

------------------------

8188 8397

-- Delete the oldest 24 AWR snapshots, that is, the oldest 24-hour snapshot.

SQL> execdbms_workload_repository.drop_snapshot_range (low_snap_id => 8188, high_snap_id => 8188 + 24 );

 

 

Finally, check the tablespace again and find that the usage has been reduced.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.