Information, tips, tricks and sample code for Big Data Warehousing in an autonomous, cloud-driven world

Workload Management – Based on Execution Times

Jean-Pierre Dijcks
Master Product Manager

Previously on this channel, mapping users to consumer groups is easy. Ok, so what if I do not have any users? Well you could use application context and other nice things in DBRM, but really, what if you want this to be driven by the amount of work to be done?

That is what we are going to quickly sketch out here, a way of dynamically moving queries from one resource plan to the other BEFORE it executes. Doing the switch before the statement actually starts is beneficial in many cases. It certainly helps with getting the right resources allocated from the start. If all goes well, it also helps with runaway query prevention by choosing the right group (resource profile) for the statement.

The following code (for credits read on) creates a dynamic switching system based on the optimizer's estimate of the execution time. We create 3 resource groups (one of which is OTHER_GROUPS - default on each system) which are assigned a query time. One group is for "long running" queries, the other for "medium" and one more for "short". All these are in "" because it is up to you (and me) to define what short means for you. So if your groups are 10, 100 and 1000 seconds than that is what we use as switch boundaries.



When a user submits a statement the database optimizer determines the estimated duration of that statement. Based on that estimate DBRM then assigns a query to a specific resource group, the statement then runs with the appropriate resources (parallel statement queuing, max parallel degree, CPU and IO etc.).

To build something like this, have a look at this code example (credits to the DBRM development manager!):

  /* Create consumer groups.
   * By default, users will start in OTHER_GROUPS, which is automatically
   * created for every database.

    'Medium-running SQL statements, between 1 and 15 minutes.  Medium  priority.');

    'Long-running SQL statements of over 15 minutes.  Low priority.');
  /* Create a plan to manage these consumer groups */

    'Plan for daytime that prioritizes short-running queries');

    'REPORTS_PLAN', 'SYS_GROUP', 'Directive for sys activity',
    mgmt_p1 => 100);

    'REPORTS_PLAN', 'OTHER_GROUPS', 'Directive for short-running queries',
    mgmt_p2 => 70,
    parallel_degree_limit_p1 => 4,
    switch_time => 60, switch_estimate => TRUE, switch_for_call => TRUE,
    switch_group => 'MEDIUM_SQL_GROUP');

    'REPORTS_PLAN', 'MEDIUM_SQL_GROUP', 'Directive for medium-running queries',
    mgmt_p2 => 20,
    parallel_target_percentage => 80,
    switch_time => 900, switch_estimate => TRUE, switch_for_call => TRUE,
    switch_group => 'LONG_SQL_GROUP');

    'REPORTS_PLAN', 'LONG_SQL_GROUP', 'Directive for medium-running queries',
    mgmt_p2 => 10,
    parallel_target_percentage => 50,
    parallel_queue_timeout => 14400);


/* Allow all users to run in these consumer groups */

exec dbms_resource_manager_privs.grant_switch_consumer_group( -
  'public', 'MEDIUM_SQL_GROUP', FALSE);
exec dbms_resource_manager_privs.grant_switch_consumer_group( -
  'public', 'LONG_SQL_GROUP', FALSE);

Join the discussion

Comments ( 2 )
  • rnm1978 Wednesday, October 20, 2010
    I'm enjoying this series of blog posts.
    What happens if a query runs over its estimated time, will it still get switched to the next consumer group as it would if switch_estimate=>false?
    Is the switch_time literally elapsed time (wall clock), or is it DB time?
  • jean-pierre.dijcks Wednesday, October 20, 2010
    Thanks, glad someone is enjoying this!
    The switch_time is the optimizer's estimate of the execution time and it is wall clock time. At this moment you can only choose to either switch based on that estimate or to switch on elapsed time, so there is only a single switch "point". That is why gathering all your stats is crucial to get a good estimate...
    Now, if you come in as OTHERS, and get switched to a new group, you can daisy chain (I have not actually tried this yet). So you switch a statement from others into SHORT because it runs in less than 10 seconds. The statement will now run in SHORT, which can have a limit set on actual time. After 30 seconds you can switch again because you are living in the SHORT group.
    Keep in mind that the second switch will bring its parallel execution servers along. So you start potentially "starving" the query for CPU while it has a high DOP. Could cause it to run a bit longer than with the regular CPU allocation...
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.