Select Page

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

Steve Thompson | | April 17, 2019

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.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine over one hundred logins in the source server, you need to migrate them to the destination server. Wouldn’t it be awesome if we could automate the process?

JP Chen | October 1, 2015
sharepoint ideas

9 Awesome Things You Can Do with SharePoint

This blog post discusses out-of-the-box uses for SharePoint that you can execute in just a few clicks without using any code.

Amol Gharat | May 23, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS