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.

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

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

Best RAID For SQL Server | RAID 0, RAID 1, RAID 5, RAID 10

Which RAID should you use with SQL Server? Learn the differences between RAID 0, RAID 1, RAID 5, and RAID 10, along with best practices.

Eric Russo | June 8, 2015
Oracle DBA Skills

8 Things Every Beginner Oracle DBA Should Know

A checklist of eight critical skills and areas an entry-level Oracle DBA should be familiar with to succeed on the job.

Patrick Gates | November 30, 2016

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