パラレル・キュー内の文を管理するシナリオ例

このシナリオでは、Oracle Database Resource Managerを使用してコンシューマ・グループを設定してパラレル・キュー内の文を管理する方法について説明します。このシナリオでは、3種類のSQL文で構成されるデータ・ウェアハウスのワークロードについて考えます。

• 実行時間の短いSQL文

  実行時間が短い文とは、実行時間が1分以内の文を指します。このような文はレスポンスが非常によいことが予想されます。

• 実行時間が中程度のSQL文

  実行時間が中程度の文とは、実行時間が1分より長く15分以内の短い文を指します。このような文はレスポンスがある程度よいことが予想されます。

• 実行時間の長いSQL文

  実行時間の長い文とは、実行時間が15分より長い非定型の問合せや複雑な問合せを指します。このような文は長時間かかると予想されます。

このデータ・ウェアハウス・ワークロードでは、実行時間の短い文でよりよいレスポンスが求められています。この目標を達成するには、次の点について確認する必要があります。

• 実行時間の長い文がパラレル・サーバー・リソースをすべて使用して、実行時間のより短い文がパラレル文のキューで待機させられることがないようにします。

• 実行時間の短い文と長い文の両方がキューに入っている場合は、実行時間の短い文を長い文よりも先にデキューするようにします。

• 実行時間の短い問合せのDOPを制限します。これはDOPを高くすることで得られる高速化は、多数のパラレル・サーバーの使用を正当化するほど重要ではないためです。

例8-3に、Oracle Database Resource Managerを使用してコンシューマ・グループを設定して、パラレル文キュー内の文に優先順位を設定する方法を示します。この例については、次の内容に注意してください。

• デフォルトでは、ユーザーはコンシューマ・グループOTHER_GROUPSに割り当てられています。SQL文の推定実行時間が1分(60秒)より長い場合は、ユーザーはMEDIUM_SQL_GROUPに切り替わります。switch_for_callがTRUEに設定されている場合、その文の完了後、ユーザーはOTHER_GROUPSに戻ります。ユーザーがMEDIUM_SQL_GROUPにあり、文の推定実行時間が15分(900秒)より長い場合は、ユーザーはLONG_SQL_GROUPに切り替わります。同様に、switch_for_callがTRUEに設定されている場合は、問合せの完了後、ユーザーはOTHER_GROUPSに戻ります。切替え処理の実行に使用されるディレクティブは、switch_time、switch_estimate、switch_for_callおよびswitch_groupです。

• アクティブなパラレル・サーバーの数が初期化パラメータPARALLEL_SERVERS_TARGETの値に達すると、それ以降のパラレル文はキューに入れられます。mgmt_p[1-8]ディレクティブは、パラレル・サーバーが使用可能になったときにパラレル文をデキューする順番を制御します。この例では、SYS_GROUPのmgmt_p1が100%に設定されているため、SYS_GROUPのパラレル文は常に最初にデキューされます。SYS_GROUPのパラレル文がキューにない場合、OTHER_GROUPSのパラレル文は70%、MEDIUM_SQL_GROUPの文は20%、LONG_SQL_GROUPの文は10%の確率でデキューされます。

• OTHER_GROUPSから発行されるパラレル文は、parallel_degree_limit_p1ディレクティブの設定により、DOPが4に制限されます。

• LONG_SQL_GROUPグループのパラレル文がパラレル・サーバーをすべて使用して、OTHER_GROUPSやMEDIUM_SQL_GROUPのパラレル文を長時間待機させることのないようにするには、そのparallel_server_limitディレクティブを50%に設定します。つまり、LONG_SQL_GROUPが初期化パラメータPARALLEL_SERVERS_TARGETで設定したパラレル・サーバーの50%まで使用すると、それ以降はそのグループのパラレル文を強制的にキューで待機させます。

• LONG_SQL_GROUPグループのパラレル文はかなり長い時間キューに留まる可能性があるため、14400秒(4時間)のタイムアウトが設定されます。LONG_SQL_GROUPのパラレル文は、キューで4時間待機すると、エラーORA-7454により停止されます。

例8-3 コンシューマ・グループを使用したパラレル文キュー内の優先順位の設定

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
 
  /* Create consumer groups.
   * By default, users start in OTHER_GROUPS, which is automatically
   * created for every database.
   */
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    'MEDIUM_SQL_GROUP',
    'Medium-running SQL statements, between 1 and 15 minutes.  Medium priority.');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    'LONG_SQL_GROUP',
    'Long-running SQL statements of over 15 minutes.  Low priority.');
 
  /* Create a plan to manage these consumer groups */
  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
    'REPORTS_PLAN',
    'Plan for daytime that prioritizes short-running queries');
 
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'REPORTS_PLAN', 'SYS_GROUP', 'Directive for sys activity',
    mgmt_p1 => 100);
 
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    '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');
 
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'REPORTS_PLAN', 'MEDIUM_SQL_GROUP', 'Directive for medium-running queries',
    mgmt_p2 => 20,
    parallel_server_limit => 80,
    switch_time => 900, switch_estimate => TRUE, switch_for_call => TRUE,
    switch_group => 'LONG_SQL_GROUP');
 
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'REPORTS_PLAN', 'LONG_SQL_GROUP', 'Directive for medium-running queries',
    mgmt_p2 => 10,
    parallel_server_limit => 50,
    parallel_queue_timeout => 14400);
 
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

/* 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);