SYSAUX tablespace growing rapidly

Saturday, December 3, 2011

I have an Oracle 11g R2 (11.2.0.1) database where I noticed the SYSAUX tablespace was growing larger every day.  Searched Oracle My Support and found Doc ID 1292724.1 and Doc ID 552880.1 which were helpful.

After running awrinfo.sql, I found the largest consumer to be SM/OPTSTAT at 2.8 GB which is larger and not typical when compared to my other Oracle 10g R2 and Oracle 11g R1 databases.

********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)  
********************************************************
|                                                       
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
| SM/OPTSTAT           SYS                        2,726.8 MB
| SM/AWR               SYS                           76.6 MB
| SM/ADVISOR           SYS                            8.2 MB
| LOGMNR               SYSTEM                         7.9 MB
| SM/OTHER             SYS                            6.8 MB
| JOB_SCHEDULER        SYS                            3.6 MB

**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************

COMPONENT        MB SEGMENT_NAME                            SEGMENT_TYPE  
--------- --------- --------------------------------------- --------------
NON_AWR       800.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST      INDEX         
NON_AWR       752.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY        TABLE         
NON_AWR       424.0 SYS.I_WRI$_OPTSTAT_H_ST                 INDEX         
NON_AWR       240.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY       TABLE         
NON_AWR       192.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST       INDEX         
NON_AWR       104.0 SYS.I_WRI$_OPTSTAT_HH_ST                INDEX

Searching Google, I came upon John Hallas’ “Purging statistics from the SYSAUX tablespace” blog entry quite informative and detailed on managing occupants in the SYSAUX tablespace.

As John mentions:

“Whenever statistics in the dictionary are modified, old versions of statistics are saved automatically for future restoring. The old statistics are purged automatically at regular intervals based on the statistics history retention setting and the time of recent statistics gathering performed in the system. Retention is configurable using the ALTER_STATS_HISTORY_RETENTION procedure. The default value is 31 days.”

To help reduce the amount of space caused by historical data, I set the retention of old statistics to 14 days.

exec dbms_stats.alter_stats_history_retention(14);

To verify the new setting, I executed the statement below.

select dbms_stats.get_stats_history_retention from dual;

I proceeded to incrementally purge statistics from 31 days to 14 days.  Started with 28 days, then moved to 21 days.

exec dbms_stats.purge_stats(sysdate-28);

Then I tried 14 days, however I encountered an UNDO space error requiring me to change my increments using 18, 16, and finally 14 to complete the purge task.

Now to reclaim the space used by the objects shown in section 4 of the AWRINFO report, I will eventually follow John Hallas’ steps using the ALTER TABLE MOVE command.


Microsoft SPLA

Thursday, December 1, 2011

As a SaaS provider, we utilize the Microsoft Services Provider License Agreement (SPLA).  Adding this electronic note for future reference.

SPLA “is for organizations that want to offer hosted software and services to end customers, such as Web hosting, hosted applications, messaging, collaboration, and platform infrastructure.

Hosting providers who want to offer software services to their customers and who will include software licenses as part of their service offering should use SPLA. Microsoft SPLA is the only Microsoft Volume Licensing program that allows Microsoft products to be used for commercial hosting.”


MSDN Explained

Wednesday, November 9, 2011

While attempting to take the time to understand MSDN licensing, I found a blog titled “What is an MSDN Subscription… Really?”  I found the information to be helpful in comprehending an MSDN subscription.

The development teams use Visual Studio along with a server OS, SharePoint, and/or SQL Server.  Visual Studio Premium with MSDN satisfies this requirement.

Found a helpful comparison chart for Visual Studio 2010.


SQL Server Script – Size of all tables in a database

Monday, October 10, 2011

While researching information regarding vCenter database retention policy, I stumbled upon this SQL script which displays the number of rows and the physical size of all tables in a specified database.


Capacity Specs for SQL Server

Tuesday, September 27, 2011

Recently referenced the Maximum Capacity Specifications for SQL Server information.  The link provides information for SQL Server 2008 R2, 2008, 2005, and Denali.


SQL Server Versions

Wednesday, September 7, 2011

Update 27-Oct-2011

Found more recent build information on SQLSecurity.com and sqlserverbuilds.blogspot.com.

********************************************

Found this helpful information regarding SQL Server versions and service packs from Bill Graziano on SQLTeam.com.  Posting the SQL Server 2008 R2 information for my future reference, however Bill lists this and prior SQL server versions as well.

SQL Server 2008 R2
10.50.1765.0 SQL Server 2008 R2 CU6 21 Feb 2011
10.50.1753.0 SQL Server 2008 R2 CU5 20 Dec 2010
10.50.1746.0 SQL Server 2008 R2 CU4 18 Oct 2010
10.50.1734.0 SQL Server 2008 R2 CU3 17 Aug 2010
10.50.1720.0 SQL Server 2008 R2 CU2 25 Jun 2010
10.50.1702.0 SQL Server 2008 R2 CU1 18 May 2010
10.50.1600.1 SQL Server 2008 R2 RTM 12 Apr 2010

Windows OS Versions

Thursday, September 1, 2011

Recently been referring to this information in the MSDN library.  Decided to post in my blog for easy reference in the future.

Operating system Version number
Windows 7 6.1
Windows Server 2008 R2 6.1
Windows Server 2008 6.0
Windows Vista 6.0
Windows Server 2003 R2 5.2
Windows Server 2003 5.2
Windows XP 64-Bit Edition 5.2
Windows XP 5.1
Windows 2000 5.0

Follow

Get every new post delivered to your Inbox.