Sunday, June 10, 2012

SCHEDULER[0x51B5]:DEFAULT_MAINTENANCE_PLAN via scheduler window

Oracle Database Resource Manager (the Resource Manager) : 
enables you to optimize resource allocation among the many concurrent database sessions.


When database resource allocation decisions are left to the operating system, you may encounter the following problems:

  • Excessive overhead.
Excessive overhead results from operating system context switching between Oracle Database server processes when the number of server processes is high.

  • Inefficient scheduling
 The operating system deschedules database servers while they hold latches, which is inefficient.

  • Inappropriate allocation of resources
The operating system distributes resources equally among all active processes and is unable to prioritize one task over another.

  • Inability to manage database-specific resources, such as parallel execution servers and active sessions

Check The Resource Manager On your Database :

SQL> show parameter resource_manager_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string      SCHEDULER[0x3003]:DEFAULT_MAIN
                                                 TENANCE_PLAN

All this is Introduction about the Error that appear when you upgrade to 11g , you will getting the following messages in the alert.log.


Setting Resource Manager plan SCHEDULER[0x51B5]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Thu Feb 05 22:00:03 2009
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Thu Feb 05 22:00:39 2009
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"


Solution to  Disable the resource manager , Steps to Do this :

1-set the current resource manager plan to null

alter system set resource_manager_plan='' scope=both 
2-change the active windows to use the null resource manager plan 

    execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
    execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');


3-run :

SQL> execute dbms_scheduler.set_attribute('','RESOURCE_PLAN','');



You Can Also Disable SQL tuning By :

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/


Thank you
Osama Mustafa




17 comments:

  1. Hi Osama,

    I am noticing that my application gets really very slow every day 8:00am in the morning for around 1 hour. I have noticed the following in my alert.log file,

    Wed Jun 27 08:00:00 2012
    Setting Resource Manager plan SCHEDULER[0x56D90]:DEFAULT_MAINTENANCE_PLAN via scheduler window
    Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
    Wed Jun 27 08:00:04 2012
    Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
    Wed Jun 27 08:18:21 2012

    Is this the reason my application gets slow?

    ReplyDelete
  2. First I would thank you for your comment ...

    Add me on skype to give you some tips since i need more information
    Skype id : osamazx

    ReplyDelete
  3. Setting Resource Manager plan SCHEDULER[0x3009]:DEFAULT_MAINTENANCE_PLAN via scheduler window
    Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
    Sun Jul 01 07:00:00 2012
    Starting background process VKRM
    Sun Jul 01 07:00:00 2012
    VKRM started with pid=27, OS id=4920
    Sun Jul 01 09:46:29 2012
    Thread 1 advanced to log sequence 629 (LGWR switch)
    Current log# 2 seq# 629 mem# 0: /u02/oradata/UATDB/redo02.log
    Sun Jul 01 18:46:51 2012
    Thread 1 advanced to log sequence 630 (LGWR switch)
    Current log# 3 seq# 630 mem# 0: /u02/oradata/UATDB/redo03.log
    Mon Jul 02 03:00:00 2012
    Clearing Resource Manager plan via parameter
    Mon Jul 02 03:46:04 2012
    Thread 1 advanced to log sequence 631 (LGWR switch)
    Current log# 1 seq# 631 mem# 0: /u02/oradata/UATDB/redo01.log
    Mon Jul 02 06:52:28 2012
    Errors in file /u01/app/oracle/diag/rdbms/uatdb/UATDB/trace/UATDB_p000_26059.trc:
    ORA-01403: no data found
    Errors in file /u01/app/oracle/diag/rdbms/uatdb/UATDB/trace/UATDB_p000_26059.trc:
    Mon Jul 02 08:29:00 2012
    Errors in file /u01/app/oracle/diag/rdbms/uatdb/UATDB/trace/UATDB_p000_29800.trc:
    ORA-01403: no data found
    Errors in file /u01/app/oracle/diag/rdbms/uatdb/UATDB/trace/UATDB_p000_29800.trc:
    Mon Jul 02 10:03:23 2012
    Errors in file /u01/app/oracle/diag/rdbms/uatdb/UATDB/trace/UATDB_p000_4261.trc:

    when i checked

    show parameter resource_manager_plan

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    resource_manager_plan string

    can u please help i am not able to find the solution for this

    ReplyDelete
  4. try to check the reource manager plan on your database level via enterprise manager , Sql
    And set the parameter .
    by default name simple_plan1 make sure .

    ReplyDelete
  5. Asak,

    We are unable to run your script which is given by you for this solution:

    execute dbms_scheduler.set_attribute('','RESOURCE_PLAN','');

    What should i do now, Please help me out...

    ReplyDelete
    Replies
    1. Hello,

      I am getting this error in alertlog file:

      SCHEDULER[0x51B5]:DEFAULT_MAINTENANCE_PLAN via scheduler window

      Delete
    2. Database version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      Platfrom: Windows

      Any solution?

      Delete
    3. I' m getting this error in alertlog file, what kind of error, is it impact on database?

      Error:

      Private strand flush not complete


      <msg time='2014-05-14T08:00:27.204+01:00' org_id='oracle' comp_id='rdbms'
      client_id='' type='UNKNOWN' level='16'


      Current database and OS version is same,which is posted on my earlier queries?

      Plz help

      Delete
  6. Buenas tardes Soy Graciela Mena Murillo, acabo de encontrar esta misma inquietud que se presenta en una base de datos ORacle11g r2(11.2..0.1.0) al ejecutar las Ășltimas instrucciones me marca:
    SQL> execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

    PL/SQL procedure successfully completed.

    SQL> execute dbms_scheduler.set_attribute(' ','RESOURCE_PLAN','');
    BEGIN dbms_scheduler.set_attribute(' ','RESOURCE_PLAN',''); END;

    *
    ERROR at line 1:
    ORA-27452: is an invalid name for a database object.
    ORA-06512: at "SYS.DBMS_ISCHED", line 4426
    ORA-06512: at "SYS.DBMS_SCHEDULER", line 2861
    ORA-06512: at line 1

    Mi correo es graciela.mena@antioquia.gov.co

    ReplyDelete
  7. Hello Osama,

    Nice post on Oracle Apps DBA/DBA, I appreciate if you post how cloning process will be done on realtime, they take any downtime or perform hot clone, please provide the complete steps for application cloning and post cloning steps in detail


    Regards
    Mohd Qadar

    ReplyDelete
  8. Graciela Mena Murillo,

    You have a space in the first parameter of dbms_scheduler.set_attribute where there shouldn't be one.

    ReplyDelete
  9. Hi Osama,

    i ran into this error on executing the 3rd step

    SQL> execute dbms_scheduler.set_attribute('','RESOURCE_PLAN','');
    BEGIN dbms_scheduler.set_attribute('','RESOURCE_PLAN',''); END;

    *
    ERROR at line 1:
    ORA-27452: is an invalid name for a database object.
    ORA-06512: at "SYS.DBMS_ISCHED", line 4478
    ORA-06512: at "SYS.DBMS_SCHEDULER", line 2862
    ORA-06512: at line 1

    my email is vitosanchess@yahoo.com

    ReplyDelete
  10. Hi Osama,

    i ran into this error on executing the 3rd step

    SQL> execute dbms_scheduler.set_attribute('','RESOURCE_PLAN','');
    BEGIN dbms_scheduler.set_attribute('','RESOURCE_PLAN',''); END;

    *
    ERROR at line 1:
    ORA-27452: is an invalid name for a database object.
    ORA-06512: at "SYS.DBMS_ISCHED", line 4426
    ORA-06512: at "SYS.DBMS_SCHEDULER", line 2861
    ORA-06512: at line 1

    my email is konarkable@gmail.com

    ReplyDelete
  11. SQL> select banner from v$version;

    BANNER
    --------------------------------------------------------------------------------
    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

    ReplyDelete
  12. [root@db dpdump]# cat /proc/version
    Linux version 2.6.32-504.12.2.el6.x86_64 (mockbuild@x86-027.build.eng.bos.redhat.com) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-9) (GCC) ) #1 SMP Sun Feb 1 12:14:02 EST 2015

    ReplyDelete