1 2 Previous Next 17 Replies Latest reply on Nov 21, 2017 8:03 AM by Quanwen Zhao

    Why tablespace SYSAUX's used percent has been increased fast ?

    Quanwen Zhao

      Hello,

       

      experts

       

      Why my oracle db tablespace SYSAUX's used percent has been increased fast for 7 days lately ?

       

      And my check processes below,

       

      Firstly,tablespace used percent currently is as follows,

       

      SYS@orcl29> set linesize 300
      SYS@orcl29> set pagesize 300
      SYS@orcl29> col "Tablespace" FOR a20
      SYS@orcl29> col "Status" FOR a10
      SYS@orcl29> col "Total MB" FOR a15
      SYS@orcl29> col "Used MB" FOR a15
      SYS@orcl29> col "Used (%)" FOR a10
      SYS@orcl29> 
      SYS@orcl29> SELECT
        2      tablespace_name "Tablespace",
        3      d.STATUS "Status",
        4      TO_CHAR((a.bytes / 1048576),'99,999,990.900') "Total MB",
        5      TO_CHAR(((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576),'99,999,990.900') "Used MB",
        6      TO_CHAR((((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576)*100) / (a.bytes / 1048576),'999,999.9') "Used (%)"
        7    FROM
        8      (sys.dba_tablespaces d JOIN sys.sm$ts_avail a USING (tablespace_name))
        9      LEFT OUTER JOIN sys.sm$ts_free f USING (tablespace_name)
       10    ORDER BY 5;
      
      
      Tablespace           Status     Total MB        Used MB         Used (%)
      -------------------- ---------- --------------- --------------- ----------
      MIS_BASE             ONLINE           1,000.000           2.000         .2
      SZD_BASE_V2          ONLINE         647,200.000      60,496.063        9.3
      SYSTEM               ONLINE           6,144.000         693.625       11.3
      UNDOTBS1             ONLINE          25,325.000       4,441.188       17.5
      SZD_BAR_V2           ONLINE           3,072.000         562.000       18.3
      SZD_PERFORMANCE_V2   ONLINE           2,048.000         842.000       41.1
      USERS                ONLINE           2,048.000       1,406.813       68.7
      SYSAUX               ONLINE         161,633.813     161,487.938       99.9
      

       

      Secondly,there has warnings info in alert log,

       

      ......
      Fri Nov 10 16:00:23 2017
      ORA-1688: unable to extend table SYS.WRH$_LATCH_CHILDREN partition WRH$_LATCH__3701130036_0 by 128 in                 tablespace SYSAUX
      ORA-1688: unable to extend table SYS.WRH$_LATCH_CHILDREN partition WRH$_LATCH__3701130036_0 by 8192 in                 tablespace SYSAUX
      ......
      Sat Nov 11 12:04:29 2017
      alter tablespace sysaux add datafile '/u01/oradata/orcl29/sysaux04.dbf' size 31g
      Sat Nov 11 12:06:23 2017
      Completed: alter tablespace sysaux add datafile '/u01/oradata/orcl29/sysaux04.dbf' size 31g
      Sat Nov 11 12:06:49 2017
      alter tablespace sysaux add datafile '/u01/oradata/orcl29/sysaux05.dbf' size 31g
      Sat Nov 11 12:08:47 2017
      Completed: alter tablespace sysaux add datafile '/u01/oradata/orcl29/sysaux05.dbf' size 31g
      ......
      

       

      In the meantime,I added two datafiles in SYSAUX(total size is 64G)

       

      Inexplicably,7 days later(in 2017.11.17),there has also warnings info about insufficient tablespace in SYSAUX,see my check result as follows,

       

      ......
      Fri Nov 17 22:00:26 2017
      ORA-1688: unable to extend table SYS.WRH$_LATCH_CHILDREN partition WRH$_LATCH__3701130036_0 by 128 in                 tablespace SYSAUX
      ORA-1688: unable to extend table SYS.WRH$_LATCH_CHILDREN partition WRH$_LATCH__3701130036_0 by 8192 in                 tablespace SYSAUX
      MMON Flush encountered SYSAUX out of space error(1688).
      MMON (emergency) purge of WR snapshots (12429) and older
      ......
      

       

      During 7 days period this time,why size 64G has been used up so fast ?

       

      Please help me to analyze this issue weird,thanks a lot.

       

      Best Regards,

      Quanwen Zhao

        • 1. Re: Why tablespace SYSAUX's used percent has been increased fast ?
          Hemant K Chitale

          The view V$SYSAUX_OCCUPANTS would show the components that are occupying space in the Tablespace.

           

          e.g. it could be excessive Optimizer Statistics because of frequent collection of statistics.

          it could be high retention period for Optimizer Statistics (default 31 days) or AWR (default 7 days)

           

          Hemant K Chitale

          1 person found this helpful
          • 2. Re: Why tablespace SYSAUX's used percent has been increased fast ?
            SUPRIYO DEY

            have look at the metalink doc id 287679.1

            1 person found this helpful
            • 3. Re: Why tablespace SYSAUX's used percent has been increased fast ?
              Quanwen Zhao

              SUPRIYO DEY wrote:

               

              have look at the metalink doc id 287679.1

               

              Hi,SUPRIYO DEY

               

              Following Oracle MOS 287679.1 you have mentioned,my check process is as follows,

               

              SYS@orcl29> set linesize 400
              SYS@orcl29> set pagesize 200
              SYS@orcl29> col occupant_name for a15
              SYS@orcl29> col occupant_desc for a60
              SYS@orcl29> col space_usage_kbytes for 99999999999
              SYS@orcl29> SELECT occupant_name,occupant_desc,space_usage_kbytes FROM v$sysaux_occupants WHERE occupant_name LIKE '%AWR%';
              
              
              OCCUPANT_NAME   OCCUPANT_DESC                                                SPACE_USAGE_KBYTES
              --------------- ------------------------------------------------------------ ------------------
              SM/AWR          Server Manageability - Automatic Workload Repository                  163732096
              

               

              From the above we can know,actually,AWR data related has occupied 163732096 KB currently(163732096 / 1024 / 1024 = 156.15 GB).

               

              That's to say,it has also been approximately equal to 161,487.938 MB(161,487.938 / 1024 = 157.70 GB) that tablespace SYSAUX has been used about.

               

              Next,I have checked the retention setting.

               

              SYS@orcl29> SELECT retention FROM dba_hist_wr_control;
              
              
              RETENTION
              ------------------
              +00031 00:00:00.0
              

               

              Now,AWR's info has been reserved 31 days and this has been changed manually a few days ago.

               

              Afterwards,I run that SQL script file below,

               

              SYS@orcl29> @?/rdbms/admin/awrinfo.sql
              

               

              Finally,it has generated a file named 'awrinfo.txt'.See my snapshot below,

              1.png

              2.png

              3.png

              4.png

              5.png

              At the same time,about ASH data's info has only occupied 8.0% in AWR. See my snapshot below,

              6.png

               

              Now,due to parameter statistics_level changed to ALL for causing this issue?

               

              Best Regards,

              Quanwen Zhao

              1 person found this helpful
              • 4. Re: Why tablespace SYSAUX's used percent has been increased fast ?
                SUPRIYO DEY

                look for any Orphan record and delete and shrink the table

                WRH$_ACTIVE_SESSION_HISTORY

                1 person found this helpful
                • 5. Re: Why tablespace SYSAUX's used percent has been increased fast ?
                  Quanwen Zhao

                  Hemant K Chitale wrote:

                   

                  The view V$SYSAUX_OCCUPANTS would show the components that are occupying space in the Tablespace.

                   

                  e.g. it could be excessive Optimizer Statistics because of frequent collection of statistics.

                  it could be high retention period for Optimizer Statistics (default 31 days) or AWR (default 7 days)

                   

                  Hemant K Chitale

                   

                  Hello,Hemant K Chitale

                   

                  I checked the retention period of Optimizer Statistics and it's actually 31 days.

                   

                  SYS@orcl29> select dbms_stats.get_stats_history_retention from dual;
                  
                  
                  GET_STATS_HISTORY_RETENTION
                  ---------------------------
                                           31
                  

                   

                  And then,could I change it to 7 days ?

                   

                  Best Regards,

                  Quanwen Zhao

                  • 6. Re: Why tablespace SYSAUX's used percent has been increased fast ?
                    Hemant K Chitale

                    So, your optimizer statistics retention is not the issue.

                     

                    The issue is the changes to AWR (statistics_level ALL and retention 31 days).   

                     

                    Note : Since the change has been made recently, you don't yet have 31 days of AWR data in SYSAUX.  You'd need to extrapolate to estimate how much space would be consumed by 31 days of data .

                    • 7. Re: Why tablespace SYSAUX's used percent has been increased fast ?
                      Quanwen Zhao

                      Hemant K Chitale wrote:

                       

                      So, your optimizer statistics retention is not the issue.

                       

                      The issue is the changes to AWR (statistics_level ALL and retention 31 days).

                       

                      Note : Since the change has been made recently, you don't yet have 31 days of AWR data in SYSAUX. You'd need to extrapolate to estimate how much space would be consumed by 31 days of data .

                       

                      Hi,Hemant K Chitale

                       

                      Retention 31 days has been changed three months ago,but I found that SYSAUX's size has increased fast and near to 100% recently these days.

                       

                      Perhaps,changing parameter statistics_level to ALL is the real reason.Now,I have changed it to typical(default).

                       

                      Furthermore,how to extrapolate to estimate how much space would be consumed by 31 days of AWR data ?

                       

                      Best Regards,

                      Quanwen Zhao

                      1 person found this helpful
                      • 8. Re: Why tablespace SYSAUX's used percent has been increased fast ?
                        Quanwen Zhao

                        SUPRIYO DEY wrote:

                         

                        look for any Orphan record and delete and shrink the table

                        WRH$_ACTIVE_SESSION_HISTORY

                         

                        Hello,SUPRIYO DEY

                         

                        In fact,orphan records have been about 37924657,see my snapshot as follows,

                        7.png

                        In the meantime,deleting operation's speed is so slowly and it hasn't shown deleted result until now.

                         

                        Best Regards,

                        Quanwen Zhao

                        1 person found this helpful
                        • 9. Re: Why tablespace SYSAUX's used percent has been increased fast ?
                          SUPRIYO DEY

                          yes it takes time . I have experienced the same.

                          1 person found this helpful
                          • 10. Re: Why tablespace SYSAUX's used percent has been increased fast ?
                            Quanwen Zhao

                            SUPRIYO DEY wrote:

                             

                            look for any Orphan record and delete and shrink the table

                            WRH$_ACTIVE_SESSION_HISTORY

                             

                            Hi,SUPRIYO DEY

                             

                            A moment ago,because of UNDO tablespace has been insufficient for deleting orphan records and after adding a undo datafile,deleting operation is successful.

                             

                            At the same time,I executed an action of COMMIT.

                             

                            Finally,about used percent of tablespace SYSAUX has been reduced to 92.9%.

                             

                            SYS@orcl29> /
                            
                            
                            Tablespace           Status     Total MB        Used MB         Used (%)
                            -------------------- ---------- --------------- --------------- ----------
                            MIS_BASE             ONLINE           1,000.000           2.000         .2
                            SZD_BASE_V2          ONLINE         647,200.000      60,504.063        9.3
                            SYSTEM               ONLINE           6,144.000         693.625       11.3
                            SZD_BAR_V2           ONLINE           3,072.000         562.000       18.3
                            SZD_PERFORMANCE_V2   ONLINE           2,048.000         842.000       41.1
                            UNDOTBS1             ONLINE          57,069.000      25,646.000       44.9
                            USERS                ONLINE           2,048.000       1,406.813       68.7
                            SYSAUX               ONLINE         161,633.813     150,114.000       92.9  <======
                            
                            
                            8 rows selected.
                            

                             

                            SYS@orcl29> set linesize 400
                            SYS@orcl29> set pagesize 200
                            SYS@orcl29> col occupant_name for a15
                            SYS@orcl29> col occupant_desc for a60
                            SYS@orcl29> col space_usage_kbytes for 99999999999
                            SYS@orcl29> SELECT occupant_name,occupant_desc,space_usage_kbytes FROM v$sysaux_occupants WHERE occupant_name LIKE '%AWR%';
                            
                            
                            OCCUPANT_NAME   OCCUPANT_DESC                                                SPACE_USAGE_KBYTES
                            --------------- ------------------------------------------------------------ ------------------
                            SM/AWR          Server Manageability - Automatic Workload Repository                  152085184
                            

                             

                            SYS@orcl29> SELECT COUNT(1) Orphaned_ASH_Rows FROM wrh$_active_session_history a
                              2  WHERE NOT EXISTS 
                              3  (SELECT 1 FROM wrm$_snapshot
                              4   WHERE snap_id = a.snap_id
                              5   AND dbid = a.dbid
                              6   AND instance_number = a.instance_number
                              7  );
                            
                            
                            ORPHANED_ASH_ROWS
                            -----------------
                                            0
                            

                             

                            Next,how to do for me continually?

                             

                            Best Regards,

                            Quanwen Zhao

                            1 person found this helpful
                            • 11. Re: Why tablespace SYSAUX's used percent has been increased fast ?
                              SUPRIYO DEY

                              now shrink the segement

                              1 person found this helpful
                              • 12. Re: Why tablespace SYSAUX's used percent has been increased fast ?
                                Quanwen Zhao

                                SUPRIYO DEY 撰写:

                                 

                                now shrink the segement

                                 

                                Hello,SUPRIYO DEY

                                 

                                I never can't do this,could you give me an example?

                                 

                                By the way,could that shrink impact business of PRODUCTION SYSTEM?

                                 

                                Best Regards,

                                Quanwen Zhao

                                • 13. Re: Why tablespace SYSAUX's used percent has been increased fast ?
                                  SUPRIYO DEY

                                  Yes you can shrink the WRH$_ACTIVE_SESSION_HISTORY without any problem. YOu can do it in a off-peak hour.

                                   

                                   

                                  alter table WRH$_ACTIVE_SESSION_HISTORY shrink space.

                                  1 person found this helpful
                                  • 14. Re: Why tablespace SYSAUX's used percent has been increased fast ?
                                    Quanwen Zhao

                                    SUPRIYO DEY wrote:

                                     

                                    Yes you can shrink the WRH$_ACTIVE_SESSION_HISTORY without any problem. YOu can do it in a off-peak hour.

                                     

                                     

                                    alter table WRH$_ACTIVE_SESSION_HISTORY shrink space.

                                     

                                    Sorry,SUPRIYO DEY

                                     

                                    I forgot to post you,finally,I have done about shrinking table WRH$_ACTIVE_SESSION_HISTORY yesterday.

                                     

                                    SYS@orcl29> alter table WRH$_ACTIVE_SESSION_HISTORY shrink space;
                                    
                                    
                                    Table altered.
                                    

                                     

                                    As a whole,causing tablespace SYSAUX to be increased fast is because of AWR data.

                                     

                                    Now,could I shrink table about AWR and then,percent occupied in SYSAUX will be reduced?

                                     

                                    Best Regards,

                                    Quanwen Zhao

                                    1 person found this helpful
                                    1 2 Previous Next