6 Replies Latest reply: Oct 21, 2013 1:22 AM by RanjeetSohale RSS

    Resource_manager_plan and Reserved pool size  parameter changing every time

    RanjeetSohale

      Hello All,

       

      In my production database (Oracle 11g  RAC )Resource_manager_plan and Reserved pool size  parameter changing every time .

       

      Below is my question .

      1. This parameter changed automatically  or it require manual intervention .
      2. In what case this parameter changed if it automatically changed.
      3. I had checked dba_hist_parameter a, dba_Hist_snapshot b table for parameter changed history parameter changed .
      4. This parameter linked with process and sql performances?.

       

      Please help me . Thanks .

       

       

       

      Regards

      Ranjeet

        • 1. Re: Resource_manager_plan and Reserved pool size  parameter changing every time
          Mihael

          Resource plan is changing according to scheduler windows :

           

          select WINDOW_NAME, RESOURCE_PLAN, REPEAT_INTERVAL from DBA_SCHEDULER_WINDOWS;

           

          WINDOW_NAME      RESOURCE_PLAN            REPEAT_INTERVAL

          ---------------- ------------------------ ---------------------------------------------------------------------

          MONDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0

          TUESDAY_WINDOW   DEFAULT_MAINTENANCE_PLAN freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0

          WEDNESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0

          THURSDAY_WINDOW  DEFAULT_MAINTENANCE_PLAN freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0

          FRIDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0

          SATURDAY_WINDOW  DEFAULT_MAINTENANCE_PLAN freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0

          SUNDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0

          WEEKNIGHT_WINDOW                          freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0

          WEEKEND_WINDOW                            freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0

          • 2. Re: Resource_manager_plan and Reserved pool size  parameter changing every time
            RanjeetSohale

            Hello Mihael

             

            Thanks for the information but this parameter linked with sql performances. what correct used of this parameter in DB side.



             

            RESOURCE_MANAGER_PLAN

             

              

            PropertyDescription
            Parameter typeString
            SyntaxRESOURCE_MANAGER_PLAN = plan_name
            Default valueThere is no default value.
            ModifiableALTER SYSTEM
            Range of valuesAny valid character string

             

            This parameter changed  daily on my production data base , please help me why this parameter changed daily .

            • 3. Re: Resource_manager_plan and Reserved pool size  parameter changing every time
              Mihael

              When scheduler window opens, its resource plan becomes active. For example, MONDAY_WINDOW begins on monday at 22:00. At this time current plan is changed to DEFAULT_MAINTENANCE_PLAN. At 00:00 (Tuesday) plan that was active before monday 22:00, becomes active. DEFAULT_MAINTENANCE_PLAN is used for Autotask clients :

               

              select client_name,WINDOW_GROUP from DBA_AUTOTASK_CLIENT ;

               

              CLIENT_NAME                     WINDOW_GROUP

              ------------------------------- --------------

              auto optimizer stats collection ORA$AT_WGRP_OS

              auto space advisor              ORA$AT_WGRP_SA

              sql tuning advisor              ORA$AT_WGRP_SQ

               

              select * from DBA_SCHEDULER_WINGROUP_MEMBERS where WINDOW_GROUP_NAME in (select WINDOW_GROUP from DBA_AUTOTASK_CLIENT);

               

              WINDOW_GROUP_NAME WINDOW_NAME

              ----------------- ----------------

              ORA$AT_WGRP_OS    MONDAY_WINDOW

              ORA$AT_WGRP_OS    TUESDAY_WINDOW

              ORA$AT_WGRP_OS    WEDNESDAY_WINDOW

              ORA$AT_WGRP_OS    THURSDAY_WINDOW

              ORA$AT_WGRP_OS    FRIDAY_WINDOW

              ORA$AT_WGRP_OS    SATURDAY_WINDOW

              ORA$AT_WGRP_OS    SUNDAY_WINDOW

              ORA$AT_WGRP_SA    MONDAY_WINDOW

              ORA$AT_WGRP_SA    TUESDAY_WINDOW

              ORA$AT_WGRP_SA    WEDNESDAY_WINDOW

              ORA$AT_WGRP_SA    THURSDAY_WINDOW

              ORA$AT_WGRP_SA    FRIDAY_WINDOW

              ORA$AT_WGRP_SA    SATURDAY_WINDOW

              ORA$AT_WGRP_SA    SUNDAY_WINDOW

              ORA$AT_WGRP_SQ    MONDAY_WINDOW

              ORA$AT_WGRP_SQ    TUESDAY_WINDOW

              ORA$AT_WGRP_SQ    WEDNESDAY_WINDOW

              ORA$AT_WGRP_SQ    THURSDAY_WINDOW

              ORA$AT_WGRP_SQ    FRIDAY_WINDOW

              ORA$AT_WGRP_SQ    SATURDAY_WINDOW

              ORA$AT_WGRP_SQ    SUNDAY_WINDOW

              • 4. Re: Resource_manager_plan and Reserved pool size  parameter changing every time
                RanjeetSohale

                Hi

                How  I correlated  RESOURCE_MANAGER_PLAN  with scheduler window because as per  oracle  blog this parameter value changed only alter systems