Resource Manager plan from OEM vs. command line

By Franck Pachot

.
You are rather GUI or command line? Let’s compare what you can do with them when you want to create a Resource Manager plan, and what is missing in the GUI.

I’m using EM13c here on a 12c database. Doc for command line API is here.

I’ll explain what you can set in the GUI and the matching arguments generated by OEM:

Here we have the name and description (comment):

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   plan                      IN VARCHAR2, 
   comment                   IN VARCHAR2 DEFAULT NULL, 

When the ‘Activate this plan’ is checked, it calls the dbms_resource_manager.switch_plan, with the allow_scheduler_plan_switches=>false if you uncheck ‘Automatic Plan Switching Enabled’

The CREATE_PLAN_DIRECTIVE is called for each group or sub plan:

   group_or_subplan          IN VARCHAR2, 

The ‘Utilization limit %’ defines the max_utilization_limit wich is now utilization_limit:

   max_utilization_limit        IN NUMBER   DEFAULT NULL,  -- deprecated
   utilization_limit            IN NUMBER   DEFAULT NULL,

The number or shares that we set are converted to percentage of total shares in order to define the cpu_p1 which is now mgmt_p1.

   cpu_p1                    IN NUMBER   DEFAULT NULL, -- deprecated
   mgmt_p1                   IN NUMBER   DEFAULT NULL,

Actually, OEM put the share number and not the percentage when generating the SQL, but that’s ok.

Parallel Query DOP and queuing

Here are the parallel settings. ‘bypass queue’ sets parallel_stmt_critical to ‘bypass_queue’ to avoid statement queuing for this consumer group.

   parallel_stmt_critical       IN VARCHAR2 DEFAULT NULL);

and the settings (using the deprecated parallel_target_percentage instead of parallel_sever_limit )

   parallel_degree_limit_p1  IN NUMBER   DEFAULT NULL,
   parallel_target_percentage   IN NUMBER   DEFAULT NULL,  -- deprecated 
   parallel_queue_timeout       IN NUMBER   DEFAULT NULL,

The timeout is the number of seconds the statement can remain queued.

Per session or per-call limits

The limits set the following arguments (in respective order):

   switch_elapsed_time          IN NUMBER   DEFAULT NULL,
   switch_time                  IN NUMBER   DEFAULT NULL,
   switch_io_megabytes          IN NUMBER   DEFAULT NULL,
   switch_io_logical            IN NUMBER   DEFAULT NULL,
   switch_io_reqs               IN NUMBER   DEFAULT NULL,

The actions sets a consumer group to switch to, or KILL_SESSION or CANCEL_SQL:

   switch_group              IN VARCHAR2 DEFAULT NULL,

The ‘track by statement’ sets to true the switch_for_call (it switches to group only until the end of the call) and the ‘use estimate’ sets switch_estimate to true:

   switch_for_call           IN BOOLEAN  DEFAULT NULL,
   switch_estimate           IN BOOLEAN  DEFAULT FALSE,

Idle time limits

This sets the following time in seconds:

   max_idle_time                IN NUMBER   DEFAULT NULL,
   max_idle_blocker_time        IN NUMBER   DEFAULT NULL,

What is missing?

It seems that we cannot set here the limit based on CBO estimation:

   max_est_exec_time         IN NUMBER   DEFAULT NULL,

Same for the maximum number of active sessions limit

   active_sess_pool_p1       IN NUMBER   DEFAULT NULL,

And the transaction undo size limit:

   undo_pool                 IN NUMBER   DEFAULT NULL,