1 2 Previous Next 22 Replies Latest reply on Mar 13, 2013 3:00 PM by Mr.D.

    Sysaux Tablespace keep on increasing

    S27
      Sysaux tablespace keeps on increasing not able to control it, am keep on adding datafiles to control it but its keep on growing. Please tell me some suggestion.

      Edited by: 992890 on Mar 10, 2013 5:07 AM
        • 1. Re: Sysaux Tablespace keep on increasing
          Srini Chavali-Oracle
          Pl post details of OS and database versions. Determine what objects in SYSAUX are using up most of the space. Pl see if these can help

          Suggestions if your SYSAUX Tablespace grows rapidly or too large [ID 1292724.1]
          General Guidelines for SYSAUX Space Issues [ID 552880.1]
          SYSAUX Grows Because Optimizer Stats History is Not Purged [ID 1055547.1]
          Sysaux Tablespace Size Increases Continuously Due to WRH$_SQL_PLAN Table and WRH$_SQL_PLAN_PK Index Growth [ID 1243058.1]
          AWR Data Uses Significant Space in the SYSAUX Tablespace [ID 287679.1]
          Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER [ID 329984.1]


          HTH
          Srini
          • 2. Re: Sysaux Tablespace keep on increasing
            JohnWatson
            Your first step should be to query v$sysaux_occupants to determine what product is taking up the space.
            --
            John Watson
            Oracle Certified Master DBA
            http://skillbuilders.com
            • 3. Re: Sysaux Tablespace keep on increasing
              S27
              Hi

              My environment details
              OS Enterprise Linux 4 update 5
              Database Version 11.1.0.7.0
              Application release 12.1.1


              Regards
              Sham
              • 4. Re: Sysaux Tablespace keep on increasing
                S27
                Hi

                This two occupants only consuming too much spaces

                SM/AWR     2,265,856
                SM/OPTSTAT     39,726,144


                Regards
                Sham
                • 5. Re: Sysaux Tablespace keep on increasing
                  JohnWatson
                  You need to check (and probably change) your statistics history retention, then purge the older statistics. Read up on the dbms_stats package. The procedures are get_stats_history_retention, alter_stats_history_retention, and purge_stats.
                  --
                  John Watson
                  Oracle Certified Master DBA
                  http://skillbuilders.com
                  • 6. Re: Sysaux Tablespace keep on increasing
                    9135487
                    Pls read
                    [http://oraworklog.wordpress.com/2010/07/24/handling-smoptstat-component-growth-in-sysaux-tablespace/]
                    • 7. Re: Sysaux Tablespace keep on increasing
                      S27
                      I changed the retention period by default it shown as 31days then I changed it to 10 and also I tried to purge the data using the command

                      exec dbms_stats.purge_stats(sysdate-20);

                      but when I executed this command I got error like this

                      ORA-08102: index key not found, obj# 363982, file 28, and block 2919823(2)
                      ORA-06512: at "SYS.DBMS_STATS_INTERNAL",line 107
                      ORA-06512: at "SYS.DBMS_STATS_INTERNAL",line 5897
                      ORA-06512: at "SYS.DBMS_STATS", line 21896
                      ORA-06512: at line 1     

                      then we tried to rebuild the index then tried purging again it shown error in someother object id.
                      thanks in advance.

                      Regards
                      Sham
                      • 8. Re: Sysaux Tablespace keep on increasing
                        606331
                        Hi,


                        The following table provides guidelines on sizing the SYSAUX tablespace based on the system configuration and expected load.

                        Parameter/Recommendation     Small     Medium     Large
                        Number of CPUs     2     8     32
                        Number of concurrently active sessions     10     20     100
                        Number of user objects: tables and indexes     500     5,000     50,000
                        Estimated SYSAUX size at steady state with default configuration     500 MB     2 GB     5 GB

                        You can control the size of the AWR by changing the snapshot interval and historical data retention period. i mean purging old data....
                        please refer below link....

                        http://mrothouse.wordpress.com/2011/12/03/sysaux-tablespace-growing-rapidly/
                        http://docs.oracle.com/cd/B28359_01/server.111/b28274/autostat.htm#PFGRF02601
                        • 9. Re: Sysaux Tablespace keep on increasing
                          Mr.D.
                          Hi,
                          reduce AWR and Stats retention or increase SYSAUX tablespaces.
                          Note that oracle perform a delete from stats and awr tables so space may remain allocated, so
                          you need to do an alter table move or shrink space.
                          Please post the result of these queries:

                          select * from DBA_HIST_WR_CONTROL;

                          select dbms_stats.get_stats_history_retention from dual;


                          Regards
                          • 10. Re: Sysaux Tablespace keep on increasing
                            S27
                            Hi

                            I have executed the following query and attached the output

                            select * from DBA_HIST_WR_CONTROL;

                            DBID     SNAP_INTERVAL     RETENTION     TOPNSQL
                            3471086047     +00 01:00:00.000000     +07 00:00:00.000000     DEFAULT
                            3,944,815,540     +00 01:00:00.000000     +07 00:00:00.000000     DEFAULT
                            2,882,923,506     +00 01:00:00.000000     +07 00:00:00.000000     DEFAULT
                            1,387,452,485     +00 01:00:00.000000     +07 00:00:00.000000     DEFAULT
                            2,061,558,320     +00 01:00:00.000000     +07 00:00:00.000000     DEFAULT
                            4,288,048,108     +00 01:00:00.000000     +07 00:00:00.000000     DEFAULT
                            3,558,022,128     +00 01:00:00.000000     +07 00:00:00.000000     DEFAULT
                            412,070,703     +00 01:00:00.000000     +08 00:00:00.000000     DEFAULT
                            3,516,040,524     +00 01:00:00.000000     +08 00:00:00.000000     DEFAULT
                            3,580,520,771     +00 01:00:00.000000     +08 00:00:00.000000     DEFAULT
                            2,350,953,892     +00 01:00:00.000000     +08 00:00:00.000000     DEFAULT
                            3,922,397,616     +00 01:00:00.000000     +08 00:00:00.000000     DEFAULT
                            3,923,062,658     +00 01:00:00.000000     +08 00:00:00.000000     DEFAULT
                            217,995,194     +00 01:00:00.000000     +08 00:00:00.000000     DEFAULT
                            218,007,544     +00 01:00:00.000000     +08 00:00:00.000000     DEFAULT
                            2,106,485,754     +00 01:00:00.000000     +08 00:00:00.000000     DEFAULT

                            Regards
                            Sham
                            • 11. Re: Sysaux Tablespace keep on increasing
                              S27
                              Hi

                              I have executed the following query and attached the output

                              select dbms_stats.get_stats_history_retention from dual;

                              GET_STATS_HISTORY_RETENTION
                              10


                              Regards
                              Sham
                              • 12. Re: Sysaux Tablespace keep on increasing
                                TSharma-Oracle
                                This seems to be a bug in 11.1.0.7.

                                Bug 6394861 - WRH$_SQLTEXT and WRH$_SQL_PLAN are not purged when baselines are present [ID 6394861.8]
                                11.1.0.7 is one of the affecte dversion.

                                There is an event you need to set. You need to work with Oracle Support.
                                • 13. Re: Sysaux Tablespace keep on increasing
                                  Mr.D.
                                  992890 wrote:
                                  Hi

                                  I have executed the following query and attached the output

                                  select dbms_stats.get_stats_history_retention from dual;

                                  GET_STATS_HISTORY_RETENTION
                                  10


                                  Regards
                                  Sham
                                  Please use [ code ] and [ code ] tag (without spaces) when you post a query or the result of the query.

                                  Execute this:
                                  SELECT *
                                    FROM (  SELECT bytes / 1024 / 1024 dim_Mb, segment_name, segment_type
                                              FROM dba_segments
                                             WHERE tablespace_name = 'SYSAUX'
                                          ORDER BY dim_Mb DESC)
                                   WHERE ROWNUM < 11;
                                  The delete do not release the space under the HWM, so probably you need to do an index rebuild and
                                  an alter table move.

                                  Regards,
                                  Davide
                                  • 14. Re: Sysaux Tablespace keep on increasing
                                    jgarry
                                    You can use the {noformat} tag to post tags without extra spaces. Putting
                                     around posted code (no need for slash with ending tag) works too (and these work as literals in this post because they have no ending tag).                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    1 2 Previous Next