10 Replies Latest reply: Aug 4, 2014 10:09 AM by Suntrupth RSS

    alter system set _ash_size=512m

    Rabi786

      Hi Expert

       

      My database 11.2.0.3 in AIX

       

      Getting the following errors in alert.log

       

      Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 266338304 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:

      select total_size,awr_flush_emergency_count from v$ash_info;

       

       

       

      Then I tried increase the value and getting error

       

      SQL> alter system set _ash_size=512m;

      alter system set _ash_size=512m

                       *

      ERROR at line 1:

      ORA-00911: invalid character

        • 1. Re: alter system set _ash_size=512m
          JohnWatson

          orclz>

          orclz> alter system set _ash_size=512m;

          alter system set _ash_size=512m

                          *

          ERROR at line 1:

          ORA-00911: invalid character

           

           

          orclz> alter system set "_ash_size"=512m;

           

          System altered.

           

          orclz>

          • 2. Re: alter system set _ash_size=512m
            Aman....

            Rabi786 wrote:

             

            Hi Expert

             

            My database 11.2.0.3 in AIX

             

            Getting the following errors in alert.log

             

            Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 266338304 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:

            select total_size,awr_flush_emergency_count from v$ash_info;

             

             

             

            Then I tried increase the value and getting error

             

            SQL> alter system set _ash_size=512m;

            alter system set _ash_size=512m

                             *

            ERROR at line 1:

            ORA-00911: invalid character

            And what did you do further to find the solution of the error? Did it occur to you that the leading underscore is an issue and you need to do something about it?

             

            Well, John gave the reply already so there you go.

             

            Aman....

            • 3. Re: alter system set _ash_size=512m
              Rabi786

              SQL> alter system set "_ash_size"=512m;

              alter system set "_ash_size"=512m

              *

              ERROR at line 1:

              ORA-02097: parameter cannot be modified because specified value is invalid

              ORA-02097: parameter cannot be modified because specified value is invalid

              • 4. Re: alter system set _ash_size=512m
                Aman....

                Rabi786 wrote:

                 

                SQL> alter system set "_ash_size"=512m;

                alter system set "_ash_size"=512m

                *

                ERROR at line 1:

                ORA-02097: parameter cannot be modified because specified value is invalid

                ORA-02097: parameter cannot be modified because specified value is invalid

                You are just being lazy, completely! Which part of "parameter cannot be modified because specified value is invalid" you didn't understand? And did you notice that the exact command was shown to you by John?     

                 

                Aman....

                • 5. Re: alter system set _ash_size=512m
                  Srini Chavali-Oracle

                  Parameters that begin with underscore should only be set under the guidance/recommendation of Oracle Support

                  • 6. Re: alter system set _ash_size=512m
                    JohnWatson

                    Exactly. As recommended in Doc ID 1385872.1

                    • 7. Re: alter system set _ash_size=512m
                      Harmandeep Singh

                      https://community.oracle.com/thread/2454999?tstart=0

                       

                       

                      This is old thread related to same issue

                       

                      Regards,

                      Harman

                      • 8. Re: alter system set _ash_size=512m
                        Rabi786

                        Hi Aman

                         

                        What difference have you got between my command and John command? I didn't find it. Could you please show me the difference ?

                        • 9. Re: alter system set _ash_size=512m
                          EdStevens

                          Rabi786 wrote:

                           

                          Hi Aman

                           

                          What difference have you got between my command and John command? I didn't find it. Could you please show me the difference ?

                          Seriously?

                           

                          Look at where the double-quotes are placed.

                          • 10. Re: alter system set _ash_size=512m
                            Suntrupth

                            Hi Ed,

                             

                            I don't see any difference in the double quotes used by Rabi786 and JohnWatson.

                             

                            Rabi786:

                            SQL> alter system set "_ash_size"=512m;

                            alter system set "_ash_size"=512m

                            *

                            ERROR at line 1:

                            ORA-02097: parameter cannot be modified because specified value is invalid

                             

                            JohnWatson:

                            orclz> alter system set "_ash_size"=512m;

                             

                            System altered.

                             

                            I don't think the issue is with the double quotes. Please correct me , if  I am wrong.

                             

                            Anyway , Rabi786, Please check :

                             

                            SQL> alter system set "_ash_size"=1m;

                            alter system set "_ash_size"=1m

                            *

                            ERROR at line 1:

                            ORA-02097: parameter cannot be modified because specified value is invalid

                            ORA-02097: parameter cannot be modified because specified value is invalid

                             

                            SQL> alter system set "_ash_size"=2m;

                            alter system set "_ash_size"=2m

                            *

                            ERROR at line 1:

                            ORA-02097: parameter cannot be modified because specified value is invalid

                            ORA-02097: parameter cannot be modified because specified value is invalid

                             

                            SQL> alter system set "_ash_size"=5m;

                            alter system set "_ash_size"=5m

                            *

                            ERROR at line 1:

                            ORA-02097: parameter cannot be modified because specified value is invalid

                            ORA-02097: parameter cannot be modified because specified value is invalid

                             

                             

                            SQL> alter system set "_ash_size"=10m;

                            System altered.

                             

                            SQL> alter system set "_ash_size"=150m;

                            System altered.

                             

                            SQL> alter system set "_ash_size"=167706624;

                            System altered.

                             

                            Hope you have got a clue now.

                             

                            Regards,

                            Suntrupth