This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Apr 25, 2013 11:40 PM by Mihael RSS

Automated Maintenence Tasks

salmanqureshi-2314430 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi,
    Thanks for your help, but it does not anwer my question.

    Salman
  • 3. Re: Automated Maintenence Tasks
    Girish Sharma Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points