5 Replies Latest reply: Nov 20, 2012 5:09 AM by Fran RSS

    a question about the rapidly increase of sysaux

    969801
      when i check the free space of tablespaces,i found that sysaux increase so rapidly that i must increate in less than a week!


      when i checked the sysaux using v$sysaux_occupants ,and i found than
         select occupant_name,space_usage_kbytes from v$sysaux_occupants;
      
      the result :
         OCCUPANT_NAME                                                                    SPACE_USAGE_KBYTES
          SM/AWR                                                                                       3159680
      
      
         and my db's version
        Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE    11.2.0.1.0      Production
      TNS for Linux: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      
      
      
         and i also modified the snapshot setting?
       SQL> select * from dba_hist_wr_control;
      
            DBID SNAP_INTERVAL
      ---------- ---------------------------------------------------------------------------
      RETENTION                                                                   TOPNSQL
      --------------------------------------------------------------------------- ----------
       862446272 +00000 02:00:00.0
      +00004 00:00:00.0                                                           DEFAULT
      the oracle will clear the snapshot decide by the settings ? but it only increase?


      i also use the package: exec dbms_workload_repository.drop_snapshot_range(),it din't work!!!!





      in my test db,i rebuild awr,it works ,but is there any risk to rebuild in my real db?


      best regards, niclo!
        • 1. Re: a question about the rapidly increase of sysaux
          Fran
          please check this thread:
          http://dbaspot.com/oracle-server/250882-sysaux-tablespace.html
          • 2. Re: a question about the rapidly increase of sysaux
            969801
            i saw it ,but i had done what it say;

            reduce the retention,or interval

            but i found that the used space doesn't reduce;


            sorry,my poor english, i hope you can understand what i say;


            thk you !
            • 3. Re: a question about the rapidly increase of sysaux
              Fran
              run awrinfo.sql to find what is growing too much and post result of
              -(1a) SYSAUX usage - Schema breakdown (dba_segments)
              -(1b) SYSAUX occupants space usage (v$sysaux_occupants)

              how many days do you have the retention? please, post too:
              select dbms_stats.get_stats_history_retention from dual;
              *by default is 31 days.

              You can purge some days with:
              exec dbms_stats.purge_stats(sysdate-X); --x= number of days

              To reclaim the space of part 4 of awinfo.txt ((4) Space usage by non-AWR components (> 500K))
              you must use "alter table move" option.

              HTH
              • 4. Re: a question about the rapidly increase of sysaux
                969801
                select dbms_stats.get_stats_history_retention from dua
                
                GET_STATS_HISTORY_RETENTION
                ---------------------------
                                         31
                
                
                
                what does non-awr  means? useless? 
                
                'alter table move mean'  reduce the hWM?
                • 5. Re: a question about the rapidly increase of sysaux
                  Fran
                  i would reduce the retention:

                  exec dbms_stats.alter_stats_history_retention(15); --for example 15 days instead of 31
                  exec dbms_stats.purge_stats(sysdate-15);

                  With this you save only 15 days of statistics.

                  what does non-awr  means? useless? 
                  Oracle gets a lot of statistics, but not all was used in AWR report. These statistics not used in AWR report is called non-awr, it will save at tables with their correspondent indexes, ....

                  'alter table move mean'  reduce the hWM?
                  yes, to reclaim space at sysaux