Oracle 11g:SYSAUX Tablespace purging when abnormally increases by size

Oracle 11g:SYSAUX Tablespace purging when abnormally increases by size

  It is very obvious when we enable feature of sql profiler by

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES or increases the retention period of awr reports.

We can get the all info of space utilization of sysaux tablespace by awrinfo.sql .

Collect the space occupied by objects.

Report o/p look something like this...

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

***************************************************** 
(1a) SYSAUX usage - Schema breakdown (dba_segments) 
***************************************************** 
| 
| Total SYSAUX size 36,037.3 MB ( 88% of 40,937.9 MB MAX with AUTOEXTEND OFF ) 
| 
| Schema SYS occupies 35,717.3 MB ( 99.1% ) 
| Schema SYSMAN occupies 123.3 MB ( 0.3% ) 
| Schema SYSTEM occupies 93.1 MB ( 0.3% ) 
| Schema MDSYS occupies 56.1 MB ( 0.2% ) 
| Schema OLAPSYS occupies 15.6 MB ( 0.0% ) 
| Schema ORDDATA occupies 10.3 MB ( 0.0% ) 
| Schema WMSYS occupies 7.4 MB ( 0.0% ) 
| Schema CTXSYS occupies 6.3 MB ( 0.0% ) 
| Schema EXFSYS occupies 3.9 MB ( 0.0% ) 
| Schema DBSNMP occupies 3.1 MB ( 0.0% ) 
| Schema ORDSYS occupies 0.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 
| -------------------- -------------------- ---------------- 
| SQL_MANAGEMENT_BASE SYS 31,764.9 MB 
| SM/AWR SYS 2,371.9 MB 
| EM SYSMAN 123.3 MB 
| SM/ADVISOR SYS 108.1 MB 
| SM/OPTSTAT SYS 92.9 MB 
| LOGMNR SYSTEM 91.6 MB 
| SDO MDSYS 56.1 MB 
| AO SYS 31.8 MB 
| XSOQHIST SYS 31.8 MB 
| JOB_SCHEDULER SYS 22.2 MB 
| XSAMD OLAPSYS 15.6 MB 
| SM/OTHER SYS 10.8 MB 
| ORDIM/ORDDATA ORDDATA 10.3 MB 
| SMON_SCN_TIME SYS 7.4 MB 
| WM WMSYS 7.4 MB 
| TEXT CTXSYS 6.3 MB 
| EXPRESSION_FILTER EXFSYS 3.9 MB 
| PL/SCOPE SYS 3.8 MB 
| EM_MONITORING_USER DBSNMP 3.1 MB 
| LOGSTDBY SYSTEM 1.4 MB 
| STREAMS SYS 1.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 1,270.7 MB 
| 
| Total space 1,270.7 MB 
| 
************************************* 
(2) Size estimates for AWR snapshots 
************************************* 
| 
| Estimates based on 60 mins snapshot INTERVAL: 
| AWR size/day 52.6 MB (2,245 K/snap * 24 snaps/day) 
| AWR size/wk 368.3 MB (size_per_day * 7) per instance 
| 
| Estimates based on 24 snaps in past 24 hours: 
| AWR size/day 52.6 MB (2,245 K/snap and 24 snaps in past 24 hours) 
| AWR size/wk 368.3 MB (size_per_day * 7) per instance 
| 

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

COMPONENT MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX% 
--------- --------- ------ ------------ ---------- ----------- ---------------- 
ASH 1,525.3 64.3 1,444 33.8 236.8 90% : 10% 
FIXED 462.1 19.5 437 10.2 71.7 55% : 45% 
EVENTS 154.8 6.5 146 3.4 24.0 52% : 48% 
SQL 78.1 3.3 74 1.7 12.1 66% : 34%

Based on report we estimate that  
| SQL_MANAGEMENT_BASE taking more space.

 

So 1st  we must delete any unwanted SQL Plan Baselines from the SMB.

Use below query to remove sql plan baselines from dataabase in chunks.

declare
pgn number;
begin
for I in (select distinct sql_handle from dba_sql_plan_baselines where rownum<1000)
loop
pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>i.sql_handle);
end loop;
end; 

Alternatively change the query by monthwise deletion in parallel session.

declare
pgn number;
begin
for I in (select distinct sql_handle from dba_sql_plan_baselines where trunc(created,'MM')=trunc(sysdate-30,'MM'))
loop
pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>i.sql_handle);
end loop;
end;

Do it for sysdaete-60,90,180...

Now we need to shrink sysaux occupants.

lter table "SYS"."SQL$" enable row movement; 
alter table "SYS"."SQL$TEXT" enable row movement; 
alter table "SYS"."SQLOBJ$AUXDATA" enable row movement; 
alter table "SYS"."SQL$" shrink space cascade; 
alter table "SYS"."SQL$TEXT" shrink space cascade; 
alter table "SYS"."SQLOBJ$AUXDATA" shrink space cascade; 
alter table "SYS"."SQL$" disable row movement; 
alter table "SYS"."SQL$TEXT" disable row movement; 
alter table "SYS"."SQLOBJ$AUXDATA" disable row movement; 
alter table "SYS"."SQLOBJ$" shrink space cascade; 
alter table "SYS"."SQLOBJ$DATA" shrink space cascade; 
alter table "SYS"."SQL$" modify lob ("SPARE2") (shrink space cascade); 
alter table "SYS"."SQL$TEXT" modify lob ("SPARE2") (shrink space cascade); 
alter table "SYS"."SQL$TEXT" modify lob ("SQL_TEXT") (shrink space cascade); 
alter table "SYS"."SQLOBJ$" modify lob ("SPARE2") (shrink space cascade); 
alter table "SYS"."SQLOBJ$DATA" modify lob ("COMP_DATA") (shrink space cascade); 
alter table "SYS"."SQLOBJ$DATA" modify lob ("SPARE2") (shrink space cascade); 
alter table "SYS"."SQLOBJ$AUXDATA" modify lob ("SPARE2") (shrink space cascade);   

Now you may also disable

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESto false.

Reset awr report retention period to 7 days or more.

For complete info by metalink note. 

https://support.oracle.com/epmos/faces/DocumentDisplay?id=1499542.1