SYSAUX Tablespace growing rapidly

[former_member203165]

Hi,

In one of the production system SYSAUX tablespace is growing rapidly,the statistics retention period for 31 days, but the  statistics exists since last year. Optimizer Statistics History size is 77GB.Oracle version is 11.2.0.2.0.

SQL> select trunc(SAVTIME),count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY group by  trunc(SAVTIME) order by 1;

26-JUL-13	93263
27-JUL-13	109740
28-JUL-13	77648

559 rows selected.

The data exists  since last year

SQL> select occupant_desc, space_usage_kbytes  from v$sysaux_occupants  where space_usage_kbytes > 0 order by space_usage_kbytes desc   ;

OCCUPANT_DESC                                                    SPACE_USAGE_KBYTES

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

Server Manageability - Optimizer Statistics History                        81431168

Server Manageability - Automatic Workload Repository                        7363072

Server Manageability - Advisor Framework                                      44288

LogMiner                                                                      12544

Server Manageability - Other Components                                        8896

Transaction Layer - SCN to TIME mapping                                        3328

SQL Management Base Schema                                                     1728

PL/SQL Identifier Collection                                                   1600

Analytical Workspace Object Table                                              1536

OLAP API History Tables                                                        1536

Logical Standby                                                                1408

Oracle Streams                                                                 1024

Enterprise Manager Monitoring User                                              896

Unified Job Scheduler                                                           384

Automated Maintenance Tasks                                                     320

15 rows selected.

If we execute the command exec dbms_stats.purge_stats(sysdate-31);

It will delete the statistics older than 31 days.

Please suggest after deleting the old statistics which actions required.

Regards

Chandrashekar

[former_member203165 · Accepted solution]

Hi, 

I have tried to delete five days statistics files,

but it is taking long time more than a day

Till now only four files deleted.

Please suggest any alternative procedures for quick deletion.

Regards

Chandrashekar

[stefan_koehler · Accepted solution]

Hi Chandrashekar,

> Please suggest any alternative procedures for quick deletion.

Are you serious? I already mentioned the three alternative procedures here: http://scn.sap.com/message/14236108#14236108

... you just need to choose one and do it ...

Regards

Stefan

[former_member203165 · Accepted solution]

Hi Stefan,

Thanks for the suggestion,But i don't have access to oracle support to download the MOS notes.

I am trying to get those notes from oracle team.

Regards

Chandra

[stefan_koehler · Accepted solution]

Hi Chandrashekar,

> But i don't have access to oracle support to download the MOS notes.

You don't need MOS access for SQL script awrinfo.sql and parallel DML on the base tables or CTAS (with dependent structures). The script is located in $ORACLE_HOME/rdbms/admin/ and the other stuff has to be done by yourself.

The mentioned enhancement is a patch (also included in SAP SBPs) and can also be verified without MOS access.

Regards

Stefan

[Former Member]

Hi,

Check this note for more information on tablespace sysaux

Note 872851 - Oracle Database 10g: SYSAUX Tablespace

This explains the usage and details of sysaux since inception on Oracle 10g.

Available freespace in the tablespace SYSAUX:

SQL> select tablespace_name, total_bytes, total_blocks

    from dba_free_space_coalesced

    where tablespace_name = 'SYSAUX';

You can use the view V$SYSAUX_OCCUPANTS to monitor the installed components and their current space consumed in the SYSAUX tablespace.

SQL> select OCCUPANT_NAME, SCHEMA_NAME, SPACE_USAGE_KBYTES

     from v$SYSAUX_OCCUPANTS

     order by space_usage_kbytes;

The largest part of the SYSAUX is usually filled by the Automatic Workload Repository (AWR). The disk space that is required for the AWR depends on various factors, for example

- Snapshot interval

- Historical data retention period

- Number of user sessions

Except for the number of user sessions, you can change these parameters and you can therefore check the space occupied by the AWR and the size of the SYSAUX

Kind Regards,

Johan

[stefan_koehler]

Hi Chandrashekar,

> Please suggest after deleting the old statistics which actions required.

Depends on what you try to achieve? Do you want to reclaim the corresponding space? MOS note #454678.1 describes this procedure for example  ... or you can apply bugfix / enhancement #10279045 and use it.

You can also check the mmon trace files for time-outs like "Runtime exceeded 300 seconds". This would explain the very long statistic history, even if the retention period is set to 31 days. (enhancement #14373728 / 11869207).

MOS note #1055547.1 provides even more details about it.

The mmon process is only given 5 minute to complete the automatic purging of optimizer statistics data. If it has not finished in that time then it aborts and does not produce any sort of error message or trace information. Data has built up because excessive amounts have been created due to a rogue programme and the mmon process has spent 5 minutes every day for a year in trying to  remove that excess but has failed miserably and not told anybody about it.

So the question should be (if you hit that issue) - why do you generate that amount of data? In my experience it is based on a bad configured statistic job (brconnect) or you collect / maintain a lot of histograms.

Regards

Stefan

[former_member203165]

Hi stefan,

Thank you for the suggestion. If we delete the old statistics can we get performance issues.

What should be the solution for deleting the statistics automatically older than 31days.

Thanks

Chandrashekar

[stefan_koehler]

Hi Chandrashekar,

> If we delete the old statistics can we get performance issues

What do you mean? What kind of performance issues? You can try to delete the old data by using the default PL/SQL procedure dbms_stats.purge_stats, but in my experience even the manual purge will never finish until you use the already mentioned enhancement #10279045 or you do it manually by using parallel DML on the base tables or CTAS (with dependent structures).

At first you should find the main driving tables with SQL script awrinfo.sql and then work through this (if the default PL/SQL procedure takes too long).

> What should be the solution for deleting the statistics automatically older than 31days.

Finding the root cause first. You can not apply a solution without knowing the root cause of this issue. The automatism needs to work, but in many cases the application owner or DBA is causing such issues in SAP environments (like already mentioned above).

Regards

Stefan

[Former Member]

Hi  Chandrashekar,

After deleting old statistics execute the commit statement.

Thanks,

Manas