How to Fix the SYSAUX Tablespace Size Issue in Oracle 11g

The SYSAUX tablespace in Oracle Database 11g is an auxiliary tablespace to the SYSTEM tablespace. As an auxiliary tablespace, the SYSAUX tablespace is intended for storing LOB (large object) data such as graphics and videos, as well as information that is rarely accessed and thus better suited for this “secondary storage.”

Although the SYSAUX tablespace is tremendously useful for improving the performance of the SYSTEM tablespace, it can also come with its own performance issues. This post will discuss a major problem that one Datavail client encountered with the size of the SYSAUX tablespace in Oracle 11g, and how Datavail’s team helped them resolve it.

Problem: SYSAUX Tablespace Too Large

One of Datavail’s clients, an Oracle 11g customer, had a SYSAUX tablespace that was already 600 gigabytes and growing in size, with no end seemingly in sight.

Datavail’s team investigated and found that the issue was caused by a silent failure during the internal processing of partition creation for the WRI$ and WRH$ tables. These tables are used for storing historical information about the database, as well as data related to advisory functions.

The internal job that creates these new partitions runs for a long period of time, and it can fail silently if the size of the tablespace is too large. As a result, the historical information is not properly purged, and the WRI$ and WRH$ tables grow to an unreasonable size.

Datavail discovered this issue when our team came across orphaned rows of data that fell outside of the preset retention period for Automatic Workload Recovery (AWR) statistics.

Once the problem was isolated and ascertained, our Oracle team planned and tested a method to fix it. The solution involved setting an internal procedure in motion to create the missing partitions on the WRI$ and WRH$ objects. After this procedure is enacted, the MMON process is able to purge the correct data from these tables.

The Oracle SQL command below was used to delete old AWR data:

begin
execute immediate ‘alter session set “_swrf_test_action” = 72’;
end;

However, this command didn’t form a complete solution to the problem. In addition, we did the following:

• After the internal procedure created these table partitions, we also had to change the retention of historical statistics from 31 days to 14 days to ensure a complete purge of the lingering data.
• Once the purge was complete, this setting was changed back to its original value.
• In addition, we had to perform some manual purging of orphaned data related to AWR snapshots.

After roughly a week’s time, our team successfully observed that the sizes of the WRI$ and WRH$ tables were gradually beginning to shrink.

Upon confirming that their solution worked, we also helped the client with the process of online table redefinition and shrinking segment space in order to reduce the overall object sizes of the SYSAUX tablespace.

Conclusion

Like any highly complex IT environment, Oracle Database 11g requires a gentle touch and a great deal of in-depth expertise. The best long-term solution to mitigating these issues is to look at upgrading to the latest version. However, the good news is that most issues can be resolved with only a few adjustments. If you’re interested in fine-tuning the performance of your Oracle database, reach out to a qualified, experienced Oracle MSP like Datavail.