12c, Administration, Performance

Oracle 12c – Use Resource Manager to manage resources

Leave a comment

Oracle Database Resource Manager is designed to optimize resource allocation among concurrent database sessions.   It prevents from the OS making resource decisions during high overhead periods without awareness of database needs.  Resource Manager gives the database more control over resource allocation and gives the ability to place sessions into groups and allocated resources to those groups, through resource plans.    Oracle 12c comes with the following Resource Plans by default:

  • DEFAULT_MAINTENANCE_PLAN – default plan for maintenance windows.
  • DEFAULT_PLAN – Default gives priority to SYS_GROUP and gives minimal resources to maintenance and diagnostics operations.
  • DSS_PLAN – for data warehouse gives priority to DDS queries and less to non-DDS and ETL operations.
  • ETL_CRITICAL_PLAN – for data warehouse gives priority to ETL over DDS.
  • INTERNAL_QUIESCE – quiescing the database, must be done manually with QUIESCE command.
  • MIXED_WORKLOAD_PLAN – prioritized interactive processes over batch.

Three elements of Resource Management are:

  • Resource Consumer Group – group of sessions based on resource needs.
  • Resource Plan – directives that detail how resources are allocated to group.
  • Resource Plan Directive – Associates resource consumer group with a particular plan and how resources are allocated to consumer group.

Resource plan directives can limit CPU and I/O for sessions in the group.   This is done by a processes call switching, which specifies an action to take when a call exceeds the limit.  Resource plan directive attribute SWITCH_GROUP determines which action to take.  If the attribute is a consumer group name action 1 will be taken, if the attribute is KILL_SESSION action 2, and if attribute is CANCEL_SQL action 3.

The possible actions are:

  • Session is switched to a consumer group with lower resource allocation.
  • Session is killed.
  • Sessions current SQL is aborted.

Attributes which control I/O and CPU are as follow:   NOTE: unlimited means no limit.

  • SWITCH_TIME – CPU time in seconds before switch
  • SWITCH_IO_MEGABYTES – amount of I/O read and writes before switch.
  • SWITCH_IO_REQS – number of I/O requests before switch.

Two attributes that can modify behavior of resource plan switching:

  • SWITCH_ESTIMATE – If TRUE – database estimate execution time of each call. if it exceeds SWITCH_TIME attribute, the session is moved to    Default is FALSE.
  • SWITCH_FOR_CALL – If TRUE – session switched because of exceeding resource is returned to original group once completed. Default is NULL.

 

Larry Catt

OCP

 

Leave a Reply