Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle CREATE_PLAN Procedure
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by four of the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

With the consumer groups present, a resource plan can be created using the create_plan procedure, and it can be associated to the consumer groups using the create_plan_directive procedure.

 

PROCEDURE create_plan (

  plan                      IN  VARCHAR2,

  comment                   IN  VARCHAR2,

  cpu_mth                   IN  VARCHAR2 DEFAULT 'EMPHASIS',

  active_sess_pool_mth      IN  VARCHAR2 DEFAULT 'ACTIVE_SESS_POOL_ABSOLUTE',

  parallel_degree_limit_mth IN  VARCHAR2 DEFAULT 'PARALLEL_DEGREE_LIMIT_ABSOLUTE',

  queueing_mth              IN  VARCHAR2 DEFAULT 'FIFO_TIMEOUT')

                        

PROCEDURE create_plan_directive (

  plan                      IN  VARCHAR2,

  group_or_subplan          IN  VARCHAR2,

  comment                   IN  VARCHAR2,

  cpu_p1                    IN  NUMBER DEFAULT NULL,

  cpu_p2                    IN  NUMBER DEFAULT NULL,

  cpu_p3                    IN  NUMBER DEFAULT NULL,

  cpu_p4                    IN  NUMBER DEFAULT NULL,

  cpu_p5                    IN  NUMBER DEFAULT NULL,

  cpu_p6                    IN  NUMBER DEFAULT NULL,

  cpu_p7                    IN  NUMBER DEFAULT NULL,

  cpu_p8                    IN  NUMBER DEFAULT NULL,

  active_sess_pool_p1       IN  NUMBER DEFAULT NULL,

  queueing_p1               IN  NUMBER DEFAULT NULL,

  parallel_degree_limit_p1  IN  NUMBER DEFAULT NULL,

  switch_group              IN  VARCHAR2 DEFAULT NULL,

  switch_time               IN  NUMBER DEFAULT NULL,

  switch_estimate           IN  BOOLEAN DEFAULT FALSE,

  max_est_exec_time         IN  NUMBER DEFAULT NULL,

  undo_pool                 IN  NUMBER DEFAULT NULL,

  max_idle_time             IN  NUMBER DEFAULT NULL,

  max_idle_blocker_time     IN  NUMBER DEFAULT NULL,

  switch_time_in_call       IN  NUMBER DEFAULT NULL)

 

The day_plan.sql script uses these procedures to create a resource plan suitable for daytime processing.  The OLTP operations are associated with 80% of the CPU on level one while batch operations receive 100% of the remaining CPU at level two. 

 

The switch_group and switch_time parameters are used in the OLTP plan directive to specify that OLTP processes lasting more than 60 seconds should be switched to the batch consumer group.  The other_groups consumer group must be included in any valid plan as it provides resource allocation information for any processes that are not explicitly associated with the consumer groups.

 

day_plan.sql

 

BEGIN

  DBMS_RESOURCE_MANAGER.clear_pending_area;

  DBMS_RESOURCE_MANAGER.create_pending_area;

 

  -- Create a new plan

  DBMS_RESOURCE_MANAGER.create_plan(

    plan    => 'day_plan',

    comment => 'Plan suitable for daytime processing.');

 

  -- Assign consumer groups to plan and define priorities

  DBMS_RESOURCE_MANAGER.create_plan_directive (

    plan             => 'day_plan',

    group_or_subplan => 'oltp_consumer_group',

    comment          => 'Give OLTP processes higher priority - level 1',

    cpu_p1           => 80,

    switch_group     => 'batch_consumer_group',

    switch_time      => 60);

 

  DBMS_RESOURCE_MANAGER.create_plan_directive (

    plan             => 'day_plan',

    group_or_subplan => 'batch_consumer_group',

    comment          => 'Give batch processes lower priority - level 2',

    cpu_p2           => 100);

 

  DBMS_RESOURCE_MANAGER.create_plan_directive(

    plan             => 'day_plan',

    group_or_subplan => 'OTHER_GROUPS',

    comment          => 'all other users - level 3',

    cpu_p3           => 100);

 

  DBMS_RESOURCE_MANAGER.validate_pending_area;

  DBMS_RESOURCE_MANAGER.submit_pending_area;

END;

/

 

The night_plan.sql script creates a resource plan suitable for nighttime processing in which the resource allocation is the reverse of the daytime processing, such that batch processes receive 80% of the CPU at level one and OLTP operations receive 100% of the remaining CPU at level two.  Once again, the other_groups consumer group is specified as a catch-all.

 

night_plan.sql

 

BEGIN

  DBMS_RESOURCE_MANAGER.clear_pending_area;

  DBMS_RESOURCE_MANAGER.create_pending_area;

 

  -- Create a new plan

  DBMS_RESOURCE_MANAGER.create_plan(

    plan    => 'night_plan',

    comment => 'Plan suitable for daytime processing.');

 

  -- Assign consumer groups to plan and define priorities

  DBMS_RESOURCE_MANAGER.create_plan_directive (

    plan             => 'night_plan',

    group_or_subplan => 'batch_consumer_group',

    comment          => 'Give batch processes lower priority - level 2',

    cpu_p1           => 80);

 

  DBMS_RESOURCE_MANAGER.create_plan_directive (

    plan             => 'night_plan',

    group_or_subplan => 'oltp_consumer_group',

    comment          => 'Give OLTP processes higher priority - level 1',

    cpu_p2           => 100);

 

  DBMS_RESOURCE_MANAGER.create_plan_directive(

    plan             => 'night_plan',

    group_or_subplan => 'OTHER_GROUPS',

    comment          => 'all other users - level 3',

    cpu_p3           => 100);

 

  DBMS_RESOURCE_MANAGER.validate_pending_area;

  DBMS_RESOURCE_MANAGER.submit_pending_area;

END;

/

 

The resource_plan_directives.sql  script uses the dba_rsrc_plan_directives view to display information about the resource plans currently defined on the system.

 

resource_plan_directives.sql

 

select

   plan,

   group_or_subplan,

   status

from

   dba_rsrc_plan_directives

order by

   plan,

   group_or_subplan;

 

The output from the resource_plan_directives.sql script is displayed below.

 

SQL> @resource_plan_directives.sql

 

PLAN                           GROUP_OR_SUBPLAN               STATUS

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

DAY_PLAN                       BATCH _CONSUMER_GROUP           ACTIVE

DAY_PLAN                       OLTP_CONSUMER_GROUP            ACTIVE

DAY_PLAN                       OTHER_GROUPS                   ACTIVE

INTERNAL_PLAN                  OTHER_GROUPS                   ACTIVE

INTERNAL_QUIESCE               OTHER_GROUPS                   ACTIVE

INTERNAL_QUIESCE               SYS_GROUP                      ACTIVE

NIGHT_PLAN                     BATCH _CONSUMER_GROUP           ACTIVE

NIGHT_PLAN                     OLTP_CONSUMER_GROUP            ACTIVE

NIGHT_PLAN                     OTHER_GROUPS                   ACTIVE

SYSTEM_PLAN                    LOW_GROUP                      ACTIVE

SYSTEM_PLAN                    OTHER_GROUPS                   ACTIVE

SYSTEM_PLAN                    SYS_GROUP                      ACTIVE

 

The resource manager is only activated when a default resource plan is assigned.  Only one resource plan can be active at any given time.  Resource plan switches can be automated using scheduler windows or performed manually by setting the resource_manager_plan parameter using the alter system command as shown below.

 

alter system set resource_manager_plan = day_plan;

 

The currently active resource plan can be identified by querying the v$rsrc_plan view as shown in the active_plan.sql script:

 

active_plan.sql

 

select

   *

from

   v$rsrc_plan;

 

The output from the active_plan.sql script is displayed:

SQL> @active_plan.sql

 

NAME                             IS_TO

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

DAY_PLAN                         TRUE

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.