1 2 Previous Next 19 Replies Latest reply: Apr 26, 2013 1:40 AM by Mihael RSS

    Automated Maintenence Tasks

    salmanqureshi-2314430
      Hi,
      I am using 11.1.0.7 on Solaris 10 on SPARC.
      I am requesting if someone can brief me about auto maintenence tasks in oracle 11.1. I know the maintenance windows for week days have a time from 10:00 PM to 2:00 AM midnight. Then for week ends(Saturdays, Sundays), it is from 6:00 AM to next day 2:00 AM (20 hours). I also understand that 3 tasks are done during these windows 1) oprimizer stats gathering, 2) segment advisor, 3) SQL tuning advisor.

      All auto tasks will run every 4 hours on week end window excep "Automatic SQL Tuning Advisor ", Can anyone guide me how can I change my optimizer statis task also to be set to run once only, not every 4 hours during week-end window?

      Thanks
      SAQ

      Edited by: 871174 on Apr 15, 2013 8:01 PM

      Edited by: 871174 on Apr 15, 2013 8:54 PM
        • 1. Re: Automated Maintenence Tasks
          TSharma-Oracle
          This link will help:

          http://www.oracle-base.com/articles/11g/automated-database-maintenance-task-management-11gr1.php
          • 2. Re: Automated Maintenence Tasks
            salmanqureshi-2314430
            Hi,
            Thanks for your help, but it does not anwer my question.

            Salman
            • 3. Re: Automated Maintenence Tasks
              Girish Sharma
              how can I change my optimizer statis task also to be set to run once only, not every 4 hours during week-end window?
              At the moment, I am not at database machine, but I am sure it is easy with the help of using OEM. The link provided by one member above will give you how to change auto task of maintenance window by OEM. Just follow the above link, your answer is there for sure.

              ...
              ...
              Basic Task Configuration
              The "Automated Maintenance Tasks Configuration" screen is the stating point for all maintenance task configuration.
              ...

              Regards
              Girish Sharma
              • 4. Re: Automated Maintenence Tasks
                salmanqureshi-2314430
                Hi,
                My question is as follows
                All auto tasks will run every 4 hours on week end window excep "Automatic SQL Tuning Advisor ", Can anyone guide me how can I change my optimizer statis task also to be set >to run once only, not every 4 hours during week-end window?
                I don't want to change the window, I just want to change the behaviour of "auto optimizer stats collection" to be run only once, not every four hour during the maintenance windows of 20 hours during week end.

                If i query DBA_AUTOTASK_CLIENT, in attribute column for "auto optimizer stats collection" CLIENT_NAME, I see "ON BY DEFAULT, VOLATILE, SAFE TO KILL", whereas, for "sql tuning advisor", it shows me attribute "ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL". Which means that if I change the attribute for "auto optimizer stats collection" to same as "sql tuning advisor", it will also run ONCE PER WINDOW.

                The link mentioned in above post does not address any of the methods to do this.

                I know that if I change my week end maintenance widow from 20 hours to 6 hours, this task will also run once, but without changing the window, I just want "auto optimizer stats collection" to run only once, not every 4 hours.

                I don't have OEM so I can't brows through different screens to find out if this is possible

                SAQ

                Edited by: 871174 on Apr 15, 2013 11:08 PM

                Edited by: 871174 on Apr 15, 2013 11:13 PM

                Edited by: 871174 on Apr 15, 2013 11:47 PM
                • 5. Re: Automated Maintenence Tasks
                  salmanqureshi-2314430
                  I am also able to find a procedure SET_ATTRIBUTE from the following link

                  http://psoug.org/reference/dbms_auto_task_admin.html

                  This is amazingly not documented in Oracle's documentation, I don't know why.

                  This has an example for setting a few values for attribues for auto maintenance tasks and after doing this, change is also reflected in "dba_autotask_client", but still not able to find how to set value "once per window" for auto optimizer stats collection jut like "once per window" is set for sql tuning advisor

                  SAQ
                  • 6. Re: Automated Maintenence Tasks
                    Mihael
                    I am also able to find a procedure SET_ATTRIBUTE from the following link

                    http://psoug.org/reference/dbms_auto_task_admin.html

                    This is amazingly not documented in Oracle's documentation, I don't know why.
                    You can get documentation from package specs:
                     PACKAGE dbms_auto_task_admin AS
                     -- PUBLIC CONSTANTS
                     ...........
                    -- SET_ATTRIBUTE API
                    --
                    -- This API is used to set boolean attributes for a Client, Operation, or Task.
                    -- The following attributes may be set:
                    --   LIGHTWEIGHT
                    --   HEAVYWEIGHT
                    --             - seting either of the above attributes ON, turns the other OFF
                    --   VOLATILE
                    --   STABLE
                    --             - seting either of the above attributes ON, turns the other OFF
                    --   SAFE_TO_KILL
                    --   DO_NOT_KILL
                    --             - seting either of the above attributes ON, turns the other OFF
                    --
                    >
                    This has an example for setting a few values for attribues for auto maintenance tasks and after doing this, change is also reflected in "dba_autotask_client", but still not able to find how to set value "once per window" for auto optimizer stats collection jut like "once per window" is set for sql tuning advisor
                    Attribute 'ONCE PER WINDOW' is got from X$KETCL, so it can't be be set manually.
                    • 7. Re: Automated Maintenence Tasks
                      salmanqureshi-2314430
                      Hi,
                      You can get documentation from package specs:
                      This not suppose to be "Documenation". I can get to this documenation only if I know that this procedure existed, which should be published in the documenation.
                      Attribute 'ONCE PER WINDOW' is got from X$KETCL, so it can't be be set manually.
                      What is X$KETCL>

                      I am working with Oracle support to know if we can set this or not.

                      SAQ
                      • 8. Re: Automated Maintenence Tasks
                        Mihael
                        What is X$KETCL>
                        it is one of memory structures
                        I am working with Oracle support to know if we can set this or not.
                        Can you post here the solution ?
                        • 9. Re: Automated Maintenence Tasks
                          salmanqureshi-2314430
                          Hi,
                          I would share the solution once I get it. Meanwhile can you provide any reference to your following statement plz.
                          Attribute 'ONCE PER WINDOW' is got from X$KETCL, so it can't be be set manually.
                          SAQ
                          • 10. Re: Automated Maintenence Tasks
                            Mihael
                            Hi,
                            I would share the solution once I get it. Meanwhile can you provide any reference to your following statement plz.
                            Attribute 'ONCE PER WINDOW' is got from X$KETCL, so it can't be be set manually.
                            If you investigate dba_autotask_client, you will find that column "ATTRIBUTES" is calculated as :
                            SELECT ... "ATTRIBUTES" ... FROM (
                            ...
                             SELECT ...
                                   DBMS_AUTO_TASK.DECODE_ATTRIBUTES(
                                     DBMS_AUTO_TASK.RECONCILE_ATTRIBUTES(C.ATTR_KETCL,
                                                 CR.ATTRIBUTES, 0, 0, 0, 0)) AS ATTRIBUTES,
                             ...
                             FROM X$KETCL C, KET$_CLIENT_CONFIG CR 
                             ...
                             
                            KET$_CLIENT_CONFIG contains additional attributes that can be set by dbms_auto_task_admin, but attribute 'ONCE PER WINDOW' is hardcoded in X$KETCL.
                            • 11. Re: Automated Maintenence Tasks
                              salmanqureshi-2314430
                              Still didn't get it. Can you write complete query to show only attribute's value which I am talking about, "once per window"

                              Edited by: 871174 on Apr 18, 2013 2:03 AM
                              • 12. Re: Automated Maintenence Tasks
                                Mihael
                                Can you write complete query to show only attribute's value which I am talking about, "once per window"
                                SQL> select DBMS_AUTO_TASK.DECODE_ATTRIBUTES(64) ATTR  from dual;
                                
                                ATTR
                                -------------------------------------------------------------------------------
                                ONCE PER WINDOW
                                • 13. Re: Automated Maintenence Tasks
                                  salmanqureshi-2314430
                                  Hi,
                                  Thanks for your help, but it would be great if you can also explain how are you concluding that it is hard coded? Your query shows a result of once per window. But I would like to know how are we inferring this. Where is it mentioned that this is hard coded and can' be changed. Only this output does not show anything meaningful other than ONCE PER WINDOW which is also visible in dba_autotask_client.

                                  Salman
                                  • 14. Re: Automated Maintenence Tasks
                                    Mihael
                                    871174 wrote:
                                    Hi,
                                    Thanks for your help, but it would be great if you can also explain how are you concluding that it is hard coded? Your query shows a result of once per window. But I would like to know how are we inferring this. Where is it mentioned that this is hard coded and can' be changed. Only this output does not show anything meaningful other than ONCE PER WINDOW which is also visible in dba_autotask_client.
                                    Hi,
                                    SQL> select CNAME_KETCL, ATTR_KETCL a1, ATTR_KETCL + 64 a2, 
                                                DBMS_AUTO_TASK.DECODE_ATTRIBUTES(ATTR_KETCL) ATTR, 
                                                DBMS_AUTO_TASK.DECODE_ATTRIBUTES(ATTR_KETCL+64) ATTR2 from X$KETCL where CNAME_KETCL not like 'Test%';
                                                
                                    CNAME_KETCL                        A1    A2 ATTR                                                           ATTR2
                                    ------------------------------- ----- ----- -------------------------------------------------------------- --------------------------------------------------------------
                                    auto optimizer stats collection 4,357 4,421 STATIC, ON BY DEFAULT, VOLATILE, SAFE TO KILL                  ONCE PER WINDOW, STATIC, ON BY DEFAULT, VOLATILE, SAFE TO KILL
                                    auto space advisor              4,357 4,421 STATIC, ON BY DEFAULT, VOLATILE, SAFE TO KILL                  ONCE PER WINDOW, STATIC, ON BY DEFAULT, VOLATILE, SAFE TO KILL
                                    sql tuning advisor              4,421 4,485 ONCE PER WINDOW, STATIC, ON BY DEFAULT, VOLATILE, SAFE TO KILL STATIC, ON BY DEFAULT, VOLATILE, SAFE TO KILL
                                    As you see, in order to have 'ONCE PER WINDOW', ATTR_KETCL field should be changed.
                                    1 2 Previous Next