Managing SQL Query Time using Oracle Database Resource Manager

Using Oracle DBRM

The Oracle Database Resource Manager (DBRM) provides a wealth of functionality to control what user workloads are able to consume server and database resources. I have recently used the DBRM to provide the following functionality to the application:

• Provide the ability to limit the duration of reports that are run through the DBMS_SCHEDULER.
• Provide the ability to cancel and/or limit the duration a query submitted from the application user interface can run.

The Oracle Database Resource Manager is often overlooked as an important tool to enable predictable and consistent performance for your database workloads. The Oracle DBRM provides the ability to create service levels across your database workloads – reducing the potential cost of poor Oracle database performance.

Reviewing the Oracle 11.2 documentation for the DBMS_RESOURCE_MANAGER package there exists the ability to switch the session to a built in CANCEL_SQL consumer group or KILL_SESSION consumer group.

To summarise the Oracle docs:

CANCEL_SQL – will cancel the current executing SQL for the session.
KILL_SESSION – will kill the session completely from the database.

Determining the appropriate built in consumer group to switch to will largely depend on the type of application you have and the ability to respond to any errors returned from the database session.

From the consumer group name it would seem fairly self-explanatory what they do however we discovered some interesting observations around the actual runtime behaviour of the DBRM when using this method to control query execution time.

DBRM Configuration

First up I will run through the setup in place to create the consumer group, resource plan, plan directives and user permissions to switch between consumer groups.

For this application we want to have all connections from a connection pool being set to use the consumer group that will have the query time limit. There are other connections to the same database user that we do not wish to restrict. The easiest way to accomplish this is to create a database service and configure the application connection pool to use the SERVICE_NAME instead of the SID.

1. Edit the $ORACLE_HOME/network/admin/tnsnames.ora file to include an alias for the service name that will be configured:

DEV1_UI = 
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bsclin02)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DEV1_UI)
    )
  )

2. Create the database service:

For single instance environments:

Login to SQL*Plus and create the service in the database:

sqlplus / as sysdba

SQL> exec DBMS_SERVICE.CREATE_SERVICE('DEV1','DEV1_UI');
SQL> exec DBMS_SERVICE.START_SERVICE('DEV1_UI');

For RAC environments:

Login as the oracle database software owner:

srvctl add service -d DEV1 -s DEV1_UI -r DEV11,DEV12

3. Create the consumer group, resource plan, plan directive and consumer group mapping:

BEGIN
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  -- create the consumer group that we want to use for all UI sessions connecting to this user
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    CONSUMER_GROUP => 'UI_MAX_QUERY_DURATION',
    COMMENT => 'Maximum query duration group',
    CPU_MTH => 'ROUND-ROBIN');
  -- create the resource plan that will be implemented on the database instance
  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
    PLAN => 'APP_PLAN',
    COMMENT => 'Resource plan to control UI database connections resource consumption',
    MGMT_MTH => 'EMPHASIS');
  -- create the plan directive that limits the execution time for the consumer group to 30 seconds
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    PLAN => 'APP_PLAN',
    GROUP_OR_SUBPLAN => 'UI_MAX_QUERY_DURATION',
    COMMENT => '', 
    MGMT_P1 => 0,
    MGMT_P2 => 0,
    MGMT_P3 => 0,
    MGMT_P4 => 0,
    MGMT_P5 => 0,
    MGMT_P6 => 0,
    MGMT_P7 => 0,
    MGMT_P8 => 0,
    ACTIVE_SESS_POOL_P1 => NULL,
    QUEUEING_P1 => NULL,
    PARALLEL_DEGREE_LIMIT_P1 => NULL,
    SWITCH_GROUP => 'CANCEL_SQL',
    SWITCH_TIME => 30,
    MAX_EST_EXEC_TIME => NULL,
    UNDO_POOL => NULL,
    MAX_IDLE_TIME => NULL,
    MAX_IDLE_BLOCKER_TIME => NULL,
    SWITCH_IO_MEGABYTES => NULL,
    SWITCH_IO_REQS => NULL);
    -- assign the OTHER_GROUPS consumer group to the resource plan
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    PLAN => 'APP_PLAN',
    GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
    COMMENT => '',
    MGMT_P1 => 0,
    MGMT_P2 => 100,
    MGMT_P3 => 0,
    MGMT_P4 => 0,
    MGMT_P5 => 0,
    MGMT_P6 => 0,
    MGMT_P7 => 0,
    MGMT_P8 => 0,
    ACTIVE_SESS_POOL_P1 => NULL,
    QUEUEING_P1 => NULL,
    PARALLEL_DEGREE_LIMIT_P1 => NULL,
    SWITCH_GROUP => '',
    SWITCH_TIME => NULL,
    MAX_EST_EXEC_TIME => NULL,
    UNDO_POOL => NULL,
    MAX_IDLE_TIME => NULL,
    MAX_IDLE_BLOCKER_TIME => NULL,
    SWITCH_IO_MEGABYTES => NULL,
    SWITCH_IO_REQS => NULL);
    -- bind the service to the consumer group
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( 'SERVICE_NAME', 'DEV1_UI', 'UI_MAX_QUERY_DURATION');
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;

We have set a limit of 30 seconds for a query that runs from a session connected via the DEV1_UI service (which in turn has the UI_MAX_QUERY_DURATION consumer group attached).

Sessions Controlled by CPU Time

The implementation of the SWITCH_TIME directive actually uses CPU time consumed by the session and not the elapsed time of the individual SQL statement. This is something to be aware of as you will not see the statement being cancelled even though it may have run longer than 30 seconds – or whatever time limit you have implemented.

Something else to keep in mind with this is that the call will be cancelled at the end of the current SQL operation. For example if you have a hash join operation in the SQL that is executing that runs for 10 minutes and the CPU time threshold is exceeded during this 10 minute window the query will not be cancelled until the end of the hash join operation.

We can easily expand on this functionality to limit the number of active sessions in a pool to control the workload on the database server through changing the ACTIVE_SESS_POOL_P1 resource plan directive. This allows us to control the number of active sessions on the database where its not possible to manage the number of connections through connection pool parameters.

Taking Control of Poor Performance

We have covered off one use of the Oracle DBRM. There are many other DBRM use cases to enable IT Managers to take control and manage poorly performing systems. With the consistent move to consolidation and Database as a Service deployment models fine grained resource management across the Oracle stack will become a necessity.