19c新機能検証の第5回になります。
今回はSQL検疫について検証します。ドキュメントによっては「ランナウェイSQL文の自動隔離」と記述されています。
この機能はExadataのみで使用可能です。19cの新機能でめぼしいものって殆どExadat Onlyですね。
検証に使用した環境は以下の通りです。
【ピアソンVUE専用】Oracle会場試験用受験チケット(電子チケット)
- 価格: 27280 円
- 楽天で詳細を見る
まず、概要です。
・機能について
本機能を使うと、プロセッサやI/Oリソースの過剰な消費が原因で、リソース・マネージャによって終了させられた、リソース集中型SQL文を自動で検疫できるようになる。そのため、そのようなリソース集中型SQL文が再度実行されるのを防ぐことができ、Oracle Database 19cは、パフォーマンス低下の一般的な原因となるものから保護される。
・処理概要
Oracle Databaseは、リソースの制限を超えたためにOracle Database Resource Manager (Resource Manager)によって終了されたSQL文の計画を自動的に隔離する。その計画を「ブラックリスト」に登録することで、大量にリソースを消費する文が再度実行されないようにする。
Resource Managerでは、SQL文の最大推定実行時間を設定できる(例: 20分)。この制限を超える文は、Resource Managerによって終了される。ただし、文は終了されるまでに繰返し実行され ることがあり、そのたびにリソースが20分間消費されてしまう。
Oracle Database 19c以降では、指定したリソース制限を超えた文は、Resource Managerに よって実行が終了され、その計画は「隔離」される。計画の隔離とは、計画を計画のブラックリストに登録すること。その計画は、データベースで実行されなくなる。
文自体ではなく計画が隔離される点に 注意する必要がある。
かいつまんで言うと、長時間かかってリソースを消費するような実行計画を隔離し、そのようなSQL実行させないようにする機能ですね。
長時間かかるからと言って隔離してしまって良いの…？って疑問がまず沸きますが…
疑問を抱きつつ、隔離には自動と手動があるので、まず手動隔離の手順です。
DECLARE
quarantine_config VARCHAR2(30);
BEGIN
quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_
ID(SQL_ID => '9dj51rzzr648f');
DBMS_SQLQ.ALTER_QUARANTINE(QUARANTINE_NAME => quaranti
ne_config,
PARAMETER_NAME => 'ELAPSED_TIME',
PARAMETER_VALUE => '60');
END;
/
SQL> select name,ELAPSED_TIME from DBA_SQL_QUARANTINE;
NAME
-------------------------------------------------------------------------------
ELAPSED_TIME
-------------------------------------------------------------------------------
SQL_QUARANTINE_6y0gfuq2thpms
60
これでsqlid＝9dj51rzzr648fのSQLの実行時間が60秒以上になった時に隔離される設定ができました。
と言っても、これだけでは隔離されず、リソースマネージャの設定が必要になります。
やや長くなりますが、リソースマネージャの設定例は以下の通りです。
begin
dbms_resource_manager.create_pending_area();
end;
/
begin
dbms_resource_manager.create_plan(
plan => 'LIMIT_RESOURCE');
end;
/
begin
dbms_resource_manager.create_consumer_group(
consumer_group => 'TEST_RUNAWAY_GROUP');
end;
/
begin
dbms_resource_manager.create_plan_directive(
plan => 'LIMIT_RESOURCE',
group_or_subplan => 'TEST_RUNAWAY_GROUP',
switch_group => 'CANCEL_SQL',
switch_for_call => true,
switch_elapsed_time => 60);
dbms_resource_manager.create_plan_directive(
plan => 'LIMIT_RESOURCE',
group_or_subplan => 'OTHER_GROUPS');
end;
/
begin
dbms_resource_manager.validate_pending_area();
end;
/
begin
dbms_resource_manager.submit_pending_area();
end;
/
begin
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => 'TEST',
consumer_group => 'TEST_RUNAWAY_GROUP',
grant_option => FALSE);
end;
/
begin
dbms_resource_manager.set_initial_consumer_group(
user => 'TEST',
consumer_group => 'TEST_RUNAWAY_GROUP');
end;
/
SQL> alter system set resource_manager_plan='LIMIT_RESOURCE';
System altered.
SQL> sho parameter resource_manager_plan
NAME TYPE VALUE
---------------------- ----------- ------------------
resource_manager_plan string LIMIT_RESOURCE
手動の隔離設定による隔離機能確認を実施します。実行後すぐエラーとなることが確認できます。
SQL> SELECT * FROM （略） WHERE ROWNUM <= 300; *
ERROR at line 1:
ORA-56955: quarantined plan used
自動隔離設定による隔離機能を確認します。
1、2回目はリソースマネージャ設定により制限時間まで実行されエラーとなることが確認できました。
SQL> SELECT * FROM （略） WHERE ROWNUM <= 500;
ERROR at line 1:
ORA-56735: elapsed time limit exceeded - call aborted
Elapsed: 00:01:01.17
3回目で隔離されて即座にエラーになりました。
60秒もリソースを無駄にせずにすぐに隔離する、というのがこの機能の肝ですが、その通り一瞬でエラーになりました。
SQL> SELECT * FROM （略） WHERE ROWNUM <= 500
ERROR at line 1:
ORA-56955: quarantined plan used
Elapsed: 00:00:00.65
隔離の状態は以下のSQLで確認できます。SQL_QUARANTINEが隔離された実行計画、AVOIDED_EXECUTIONSがSQL検疫の機能によって実行されなかった回数です。
SQL> select sql_id,SQL_QUARANTINE,AVOIDED_EXECUTIONS,PLAN_HASH_VALUE from v$sql where sql_text like '%SELECT T1.topic_objid%'
SQL_ID SQL_QUARANTINE AVOIDED_EXECUTIONS PLAN_HASH_VALUE
------------- ---------------------------------------- ------------------ ---------------
57rjskwv2p62n 0 2589991747
57rjskwv2p62n SQL_QUARANTINE_ggt6zwchf0m9vafdb0fad 0 2950369197
57rjskwv2p62n SQL_QUARANTINE_ggt6zwchf0m9vb547a64b 2 3041371723
57rjskwv2p62n 0 574883468
cfqxbzfr0u2k2 0 3074207202
9dj51rzzr648f SQL_QUARANTINE_6y0gfuq2thpmsafdb0fad 0 2950369197
9dj51rzzr648f SQL_QUARANTINE_6y0gfuq2thpmsafdb0fad 15 2950369197
7gdd7y00w2pww 0 3074207202
9wdfmj3x7gr13 0 3074207202
SQLID＝57rjskwv2p62nのSQLには隔離された実行計画と、隔離されない「良い」実行計画があるので、良い実行計画で実行できますが、SQLID＝9dj51rzzr648fには隔離された実行計画しか無いので、ORA-56955が発生してしまって実行できなくなってしまいます。
実行されなくなってしまったSQLに復活のチャンスを与えるべく、統計情報を取り直してテストしました。
SQL> SELECT * FROM （略） WHERE ROWNUM <= 300;
ERROR at line 1:
ORA-56955: quarantined plan used
Elapsed: 00:00:01.95
SQL> SELECT * FROM （略） WHERE ROWNUM <= 500;
no rows selected
Elapsed: 00:00:01.03
ROWNUM <= 500 はさらに早くなったのに、ROWNUM <= 300はエラーのまま、ROWNUM <= 200の新規SQLは早いのに、ROWNUM <= 300はエラーのままです。
かなり長くて複雑なSQLなので途中省略していますが、最後の「ROWNUM <=」のリテラル以外は全く同じSQLです。
次に、switch_group => ‘LOG_ONLY’で、制限時間に達してもSQLキャンセルを行わないリソースプランを作成し、テストしてみました。
設定は長いので、同じ部分は省略しています。
（略）
begin
dbms_resource_manager.create_plan_directive(
plan => 'NO_LIMIT',
group_or_subplan => 'TEST_NO_GROUP',
switch_group => 'LOG_ONLY',
switch_for_call => true,
switch_elapsed_time => 60);
dbms_resource_manager.create_plan_directive(
plan => 'NO_LIMIT',
group_or_subplan => 'OTHER_GROUPS');
end;
/
（略）
SQL> alter system set resource_manager_plan='NO_LIMIT';
System altered.
Elapsed: 00:00:01.92
SQL> sho parameter resource_manager_plan
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan string NO_LIMIT
リソースプランを変更してテストです。
SQL> SELECT * FROM （略） WHERE ROWNUM <= 300;
ERROR at line 1:
ORA-56955: quarantined plan used
Elapsed: 00:00:00.00
SQLキャンセルを行わないリソースプランでもエラーになってしまいました…。ご無体な…；
さすがにSQL実行時間に制限のないプランであれば、エラーにならないだろうと思いますが、念のため、確認してみます。
SQL> alter system set resource_manager_plan='DEFAULT_PLAN';
System altered.
SQL> SELECT * FROM （略） WHERE ROWNUM <= 300;
ERROR at line 1:
ORA-01013: user requested cancel of current operation
Elapsed: 00:04:22.15
さすがにこちらは隔離されませんでした。そのまま放っておくといつ終わるか分からないSQLなので、Ctrl+Cで強制終了しました。
「文自体ではなく計画が隔離される点に 注意する必要がある」とマニュアル（SQLチューニング・ガイド）に書いてありますし、長時間かかるSQLの実行そのものを阻害するのではなく、「良くない」実行計画を隔離するのがこの機能の目的です。
とは言え、リソースマネージャで実行時間の上限を設けてそれに引っかかったら隔離されるのであれば、実行計画に問題が無くても隔離されてしまう可能性があります。
例えばロック待機です。
という訳で、さっそく検証です。
セッションA
SQL> sho parameter resource_manager_plan
NAME TYPE VALUE
-------------------------- ----------- ----------------------
resource_manager_plan string LIMIT_RESOURCE
SQL> delete tab2 where col1=1;
16 rows deleted.
セッションB
SQL> sho parameter resource_manager_plan
NAME TYPE VALUE
-------------------------- ----------- -----------------------
resource_manager_pla string LIMIT_RESOURCE
SQL> update tab2 set col2=col2+1 where col1=1;
update tab2 set col2=col2+1 where col1=1
*
ERROR at line 1:
ORA-56735: elapsed time limit exceeded - call aborted
Elapsed: 00:00:59.55
SQL> /
update tab2 set col2=col2+1 where col1=1
*
ERROR at line 1:
ORA-56735: elapsed time limit exceeded - call aborted
Elapsed: 00:00:59.58
SQL> update tab2 set col2=col2+1 where col1=1;
update tab2 set col2=col2+1 where col1=1
*
ERROR at line 1:
ORA-56955: quarantined plan used
Elapsed: 00:00:00.65
1,2回目はリソースマネージャによる60秒の制限でエラーになり、3度目は隔離されて即座にエラーが返っています。
ロック解除してみます。
セッションA
SQL> rollback;
Rollback complete.
セッションB
SQL> update tab2 set col2=col2+1 where col1=1;
update tab2 set col2=col2+1 where col1=1
*
ERROR at line 1:
ORA-56955: quarantined plan used
Elapsed: 00:00:00.00
隔離されてしまっているので、ロックが無くなっても実行できません…。上記の例のような単純なSQLだと実行計画がそもそも1つしか無いので、この後、何度トライしてもORA-56955になってしまって実行できません。そんな殺生な……。
検証中に気になったことをサポートに確認してみました。
Q．switch_group => 'LOG_ONLY'の設定は、エラーを発生させずSQLを実行する事を意図していると思いますが、SQL検疫の機能によって自動的に隔離されてエラーになるため、エラーを発生させずSQLを実行する事ができなくなります。
つまり、エラーを発生させずSQLを実行するリソースマネージャの設定が使用できない事になると思いますがこれは想定通りですか？
A．社内で確認致しました。結果として、SQL隔離について、現在に 'LOG_ONLY'の設定であるかどうかに関係なく、自動隔離されます(ORA-56955発生)。これは想定通りの挙動でございます。
Q．念のため確認ですが、「switch_group => 'LOG_ONLY'」の設定であれば、SQLの実行が中断される事は無いので、自動的に隔離される事もない。
「switch_group => ‘CANCEL_SQL’」の設定で、一旦隔離されたSQLの実行計画については、現在のリソースプランが「switch_group =>'LOG_ONLY'」に変更されても隔離によってORA-56955: quarantined plan usedが発生するという認識で正しいですか？
A．はい、お客様のご認識は問題がございません。
まとめると以下の通りです。
・検証結果の総括
手動で隔離設定を行ってもそれだけでは隔離されず、リソースマネージャの設定が必要。
リソースマネージャを設定しておけば自動で隔離されるので、特別な隔離設定は不要。
逆に言えば勝手に隔離されてしまうので、19c以前のリソースマネージャ設定を引き継ぐような時には注意が必要。
・考察
隔離されると処理時間が長くならないので「SQL計画のパフォーマンス低下の自動解決」の新機能により、より良い実行計画が有効になるという恩恵を受けられない。
実行計画の問題ではなく、サーバー高負荷やロック待機等が原因で処理時間が伸びた時にも隔離されてしまい、その実行計画は使われなくなってしまう。
隔離されていない実行計画の無いSQLは、エラーになり続けて実行できない。
制限が緩和されるか、実行計画が変わらない限りエラーになり続けるので、かなり慎重な設定が必要。
ここで言っている「SQL計画のパフォーマンス低下の自動解決」の新機能とは、前回検証したSQL計画ベースラインの新機能の事です。
12cのデフォルトでSQL計画ベースラインを使用していない環境だと、例えば普段は30分で終わるバッチが、実行計画が変わってしまった為に2時間かかっても終わらない、という事がままあります。
SQL計画ベースラインを使用すれば常に同じ実行計画になる訳ですが、データのカーディナリティなどが大きく変わっても同じ実行計画を使用するので、却って遅くなる可能性がありました。
19cのSQL計画ベースラインでは、自動的にSQL計画ベースラインを作成して実行計画をある程度安定させる一方、そのSQLが遅ければ実行計画を見直してより良い実行計画を選択する新機能が追加されました。
しかしながらSQL検疫で隔離してしまうと実行時間が長くならないので、この新機能の恩恵を受けにくくなってしまいます。
何より、 SQL計画ベースラインの新機能ではより良い実行計画があれば、そちらを使うのですが、SQL検疫はより良い実行計画があろうが無かろうが上限に引っかかったSQLを隔離してしまうので、ちょっと強引ですね。
「文自体ではなく計画が隔離される」とは言っても、隔離される実行計画しか持たないSQLは、文自体が隔離されるのと同じです。
上記のバッチの例で2時間かかっても終わらない実行計画を手動隔離して、いつもの30分で終わる実行計画が選択されるようにする使い方なら良いけれど、自動隔離は設定を誤るとORA-56955が発生しまくる阿鼻叫喚に陥りそうだと思ったのでした…
