Manually Purge the Optimizer Statistics & AWR Snapshots to Reduce Space Usage of SYSAUX Tablespace (Doc ID 1965061.1)


The Statistics of Optimizer Statistics History tables & AWR snapshots was not automatically purged. Due to that, the
SYSAUX Tablespace grows very large.

SOLUTION

We have to manually purge the Optimizer Statistics, split the partitions of AWR objects and purge the AWR Snapshots
manually.

1.) Run the below script to check the current space usage of Optimizer Statistics Histogram & AWR tables, its relevant
indexes in SYSAUX tablespace.

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

     Output is written to awrinfo.lst in the current working directory

2.) Check the statistics availablility from Optimizer Statistics Histogram table by number of days.

    SQL>  select systimestamp - min(savtime) from sys.wri$_optstat_histgrm_history;

The query will return a result similar to the followiing:

SYSTIMESTAMP-MIN(SAVTIME)
----------------------------
+000000099 22:30:28.418491

3.) Purge the Statistics by altering the number of days. i.e for this case the number of days are 7, it will purge stats more than 7 days old

    SQL>  exec dbms_stats.purge_stats(sysdate - 7);

    Executing DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL) is faster than dbms_stats.purge_stats(sysdate - x),
    as some of the processes are performing truncate for tables rather than deleting, if you does not need all historical stats, you can use PURGE_ALL.

4.) Check the partition details for the table 'WRH$_ACTIVE_SESSION_HISTORY' before split.

    SQL> set lines 150
    SQL> col SEGMENT_NAME for a30
    SQL> col PARTITION_NAME for a50
    SQL> SELECT owner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GB FROM
             dba_segments WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';

5.) Split the AWR partitions so that there is more chance of the smaller partition being purged:

    SQL> alter session set "_swrf_test_action" = 72;

Note : The above command will split partitions for ALL partitioned AWR objects. It also initiates a single split;
          it does not need to be disabled and will need to be repeated if multiple splits are required.

6.) Check the partition details for the table 'WRH$_ACTIVE_SESSION_HISTORY' after split.

    SQL> set lines 150
    SQL> col SEGMENT_NAME for a30
    SQL> col PARTITION_NAME for a50
    SQL> SELECT owner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GB FROM
             dba_segments WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';

Note : With smaller partitions it is expected that some will be automatically removed when the retention period
of all the rows within each partition is reached.

       You can purge the statistics based on the snapshot range. Depending on the snapshots chosen, this may
       remove data that has not yet reached retention limit
       so this may not be suitable for all cases.

  The following output shows the low and high snapshot_id in each partition.

    set serveroutput on
    declare
    CURSOR cur_part IS
    SELECT partition_name from dba_tab_partitions
    WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY';

    query1 varchar2(200);
    query2 varchar2(200);

    TYPE partrec IS RECORD (snapid number, dbid number);
    TYPE partlist IS TABLE OF partrec;

    Outlist partlist;
    begin
    dbms_output.put_line('PARTITION NAME SNAP_ID DBID');
    dbms_output.put_line('--------------------------- ------- ----------');

    for part in cur_part loop
    query1 := 'select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||')
    group by dbid';
    execute immediate query1 bulk collect into OutList;

    if OutList.count > 0 then
    for i in OutList.first..OutList.last loop
    dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid);
    end loop;
    end if;

    query2 := 'select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||')
    group by dbid';
    execute immediate query2 bulk collect into OutList;

    if OutList.count > 0 then
    for i in OutList.first..OutList.last loop
    dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid);
    dbms_output.put_line('---');
    end loop;
    end if;

    end loop;
    end;
    /

7.) From the result of the above query purge the AWR snapshots based on the low & high snap_id's.

     SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( low_snap_id IN NUMBER,high_snap_id IN NUMBER,
       dbid IN NUMBER DEFAULT NULL);

8.) Run the below script to crosscheck whether the space usage of Optimizer Statistics Histogram & AWR tables, its relevant
   indexes in SYSAUX tablespace has reduced or not.

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

     Output is written to awrinfo.lst in the current working directory

Note: Compare the awrinfo.lst output of step 8 with step 1 and make sure the space usage of Optimizer Statistics Histogram
& AWR tables has reduced accordingly.

REFERENCES

NOTE:329984.1 - Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER
NOTE:10279045.8 - Bug 10279045 - Slow Statistics purging (SYSAUX grows)
NOTE:454678.1 - Statistics Space Used by SM/OPTSTAT in the SYSAUX Tablespace is not Reclaimed After Purging
NOTE:1360000.1 - Abnormal High Space Usage in Sysaux Tablespace - Unable to Purge
NOTE:387914.1 - WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy

NOTE:1292724.1 - Tips if Your SYSAUX Tablespace Grows Rapidly or Too Large



 

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more