[JPOUG Tech Talk Night #12]SQL Plan Management(SPM)とAdaptive Cursor Sharing(ACS)の組み合わせ

SQL Plan Management(SPM)とAdaptive Cursor Sharing(ACS)の組み合わせ
JPOUG Tech Talk Night #12
• SQL Plan Management(SPM)
• Adaptive Cursor Sharing(ACS)
• SPMとACSの組み合わせ

Transcript

1. SQL Plan Management(SPM)とAdaptive Cursor Sharing(ACS)の組み合わせ JPOUG Tech Talk Night #12 辻 研一郎 日本オラクル株式会社 2025年4月17日
2. Copyright © 2025, Oracle and/or its affiliates 2 • 名前：辻 研一郎 • 日本オラクル株式会社に2007年に新卒入社し、2020年に再入社。現在データベース・ソリューショ ン部にてOracle DB関連の提案支援を実施。日本オラクルでは、Oracle Databaseの検証やPoCなど も含めたOracle Databaseに関連する支援、プリセールスエンジニアとして案件支援やOCIの提案も 経験。 前職では、DBエンジニアとしてOracle DatabaseやPostgreSQLの要件定義、移行プロジェクト、構築、 運用、トラブル対応などを経験 • 趣味：アニメ 異世界転生ものをついつい見てしまう 自己紹介
3. Copyright © 2025, Oracle and/or its affiliates 3 • SQL Plan Management(SPM) • Adaptive Cursor Sharing(ACS) • SPMとACSの組み合わせ アジェンダ
4. SQL Plan Management(SPM) Copyright © 2025, Oracle and/or its affiliates 4
5. 機能概要 SQLパフォーマンスの安定性を保証するため、承認済みの実行計画の中から低コストなものを選択して使用する機能。オプティマイザが 未知の実行計画を生成した場合、非承認という形で保持しておき、パフォーマンスの改善が見込める場合に承認済み実行計画として 加える機能 • SQL計画ベースライン：承認済みの実行計画が入るところ（実行計画はアウトラインの形で保持されます） • SQL計画履歴：非承認も含めた実行計画が入るところ SPM利用の3つの過程 1. 取得(自動/手動) • 手動で実行計画をベースラインに取得 • 取得元は主にカーソルキャッシュ、 STS、AWR • 自動で繰り返し実行されるSQLの実行計画をベースラインにロード • optimizer_capture_sql_plan_baselines =TRUE（デフォルト FALSE） 2. 選択(SQL実行時) • SQL計画ベースラインの承認済みのプランからコストの低いものが選択される 新しいSQL実行計画は未承認としてSQL計画履歴に記録 3. 展開/改良 • 未承認のSQL実行計画の性能を評価し、より優れた 性能のSQL実行計画をSQL計画ベースラインに組み込む SQL計画管理(SQL Plan Management: SPM) Copyright © 2024, Oracle and/or its affiliates 5 選択 取得 展開 SQL計画履歴 SQL計画1 SQL計画ベースライン SQL計画2 SQL計画3 承認 新しい計画 今回は時間の関係で自動SPMに関しては対象外となります。以下を参照ください "Oracle Database Technology Night #64 Automatic SQL Plan Management は使 えるのか" https://speakerdeck.com/oracle4engineer/oracle-database-technology-night- number-64-automatic-sql-plan-management-hashi-erunoka 承認 承認 非承認
6. シグネチャ SPMのSQL文の識別 Copyright © 2023, Oracle and/or its affiliates 6 SPMではSQL文を識別するためにシグネチャ（正規化されたSQLテキスト）を使用する • 正規化されたSQLテキスト（大文字小文字を区別せず、空白を削除）から生成される一意のSQL識別子 • 以下はどちらも同じシグネチャになる • SQL_IDとの違い（SQL_IDが異なってもシグネチャが同じ場合も） • V$SQLAREA/V$SQLのEXACT_MATCHING_SIGNATURE がSPMのシグネチャと同じ • cursor_sharing=forceだとバインド変数に置き換わるのでFORCE_MATCHING_SIGNATUREと同じ値になる SQL> SELECT sql_id, sql_text, exact_matching_signature, force_matching_signature 2 FROM v$sqlarea WHERE UPPER(sql_text) like '%DUMMY%' ; SQL_ID SQL_TEXT EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE ------------- ------------------------------------ ------------------------ ------------------------ b8fj5dkrqzkrq select 1 from dual where DUMMY= ‘A’ 13015969835749972382 13154199455204052618 6vum4z2c1rpua select 1 from dual where dummy= ‘A' 13015969835749972382 13154199455204052618 18k1ys5nhrrbk select 1 from dual where DUMMY='B' 1525540498770831959 13154199455204052618 gfrsz0vuczzag select 1 from dual where DUMMY='A' 13015969835749972382 13154199455204052618 SELECT a.data, b.data FROM TAB1 a JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = :1 SELECT a.data, b.data FROM tab1 a JOIN tab2 b ON b.tab1_id = a.id WHERE a.code = :1
7. DBA_SQL_PLAN_BASELINESのENABLED、ACCEPTED、FIXED • SQL計画ベースラインに入るということは、DBA_SQL_PLAN_BASELINES.ACCEPTEDがYESということ • SIGNATUREに対応するACCEPTEDがYESのプランがすでにあり、新規で未知の実行計画が生成されるとACCEPTED＝NOで追加され る（プランが追加されるのは、自動取得の動きと誤解する方がいるので注意） • SIGNATUREに対応するプランがすべてFIXED＝YESのとき、未知のプランの追加がなくなります。また、固定された計画は、固定されて いない計画より優先されます （FIXED＝YESを指定せずとも、ACCEPTED＝YESからのみプラン選択される動作となります。プラン固定したい方が、固定はFIXED＝ YESのみの動作と勘違いされることがあるので注意） • ACCEPTEDをYESからNOに変える手段はありません。管理者が使いたくないプランはENABLEDをNOにします。 ENABLEDにNOが設定さ れている場合でも新規プランの取得は行なわれます。FIXED＝YES、ENABLED＝NOのプランは固定プランとはみなされません • 取得時にACCEPTEDがYESで入るときは管理者の意思で設定したときで、ACCEPTEDをYESからNOに変える手段はありません。 • 手動取得(DBMS_SPM.LOAD_PLANS_FROM_XXX (CURSOR_CACHE|SQLSET|AWR)など)では、ACCEPTEDがYESで入ります。プロシージャ実行時 にENABLED＝NO(デフォルトYES)、 FIXED＝YES (デフォルトNO)を指定することも可能 • 自動取得では、デフォルトFALSEのパラメータoptimizer_capture_sql_plan_baselines を管理者がTRUEに変えた結果、プランが追加されるので ACCEPTED＝YESで入ってきます。2個目以降は、通常のSPMの動作に従ってACCEPTED＝NOで入ってきます SPM ベースラインのENABLED、ACCEPTED、FIXEDについて Copyright © 2025, Oracle and/or its affiliates 7 SIGNATURE PLAN_NAME ENA ACC FIX ------------------------------ ------------------------------ --- --- --- 2667826512551042531 SYS_SQL_PLAN_12f95de371befc37 YES YES NO 2667826512551042531 SYS_SQL_PLAN_12f95de3b157e6ce YES YES NO 2667826512551042531 SYS_SQL_PLAN_12f95de36123a45c YES NO NO 9739979040767481433 SYS_SQL_PLAN_12f95de3b157e6ce YES YES YES
8. OPTIMIZER_USE_SQL_PLAN_BASELINE=TRUE(デフォルト)のとき SPM選択の動作の詳細 Copyright © 2023, Oracle and/or its affiliates 8 • ①オプティマイザがコストベースの計画を生成 • ②OPTIMIZER_USE_PLAN_BASELINE＝TRUEであるか、また、 発行されたSQL文と一致するシグネチャがあるか確認 • ③新たに生成された計画がベースラインにあるかを確認する (こ のとき固定計画があると優先される) • 新たな計画がベースラインにある (PLAN_IDが一致した) 場合は この計画で実行する。 • 固定計画がある場合、固定計画を優先。固定計画がすべて再現不 可だった場合、固定でない計画を実行する • ない場合は既知のベースライン計画で実行して、この計画を計画 履歴に保存する。 • 固定計画を含む場合は新しい計画は追加されない • ベースライン内に再現可能な計画がない場合は新たに生成さ れた計画を使用し、この計画を未承認計画としてSQL計画履 歴に保存 ① ② ③
9. Adaptive Cursor Sharing（ACS） ：適応カーソル共有 Copyright © 2025, Oracle and/or its affiliates 9
10. バインドピークの機能拡張 バインド変数の値に応じて、適した実行計画を使い分ける機能。バインド変数を持つ同一SQL文に対して、複数の実行 計画（複数カーソル）をもつことで実現。 複数カーソルを持つ必要があると認識する最初の実行については、適切でない実行計画で実行される可能性あり（これ はカーソルが増えすぎないための措置） 11gR1で実装されデフォルト有効（12cR1の一時期のみデフォルト無効） ※適応カーソル共有は、CURSOR_SHARINGパラメータを使う機能とは独立した機能です。こちらは、リテラルをバインド変 数化する機能です Adaptive Cursor Sharing（ACS）：適応カーソル共有概要 Copyright © 2025, Oracle and/or its affiliates 10
11. バインド変数、バインドピーク、バインドピークの問題点 • バインド変数を使う理由 リテラルSQL：リテラルの値の違うSQLの数だけ、ハード・パースしプランを生成、プランを管理する共有プールのメモリ効率も悪い バインド変数を使ったSQL：リテラル値をバインド変数化することでSQLのバリエーションを減らし、ハード・パースおよび共有プールのメモリ効 率を向上 一方、バインド変数の値によらず一律に選択率を決めてコスト計算し、実行計画を作成していたため、実行計画の予測精度が低いとい う問題がありました。 そこで、9iから採用されたのがBind Peek(バインド・ピーク)です。 バインド変数を扱うSQLのOracleの対応の経緯 Copyright © 2025, Oracle and/or its affiliates 11 select COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = 1 -> Plan1 select COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = 2 -> Plan2 select COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = 3 -> Plan3 select COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = 4 -> Plan4 ・・・ select COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = 100 -> Plan100 select COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id -> Plan1 10gR1でのバインド・ピークOFFでの選択率 Sel(Cul=:x) = 1/NDV ※等価条件の場合はデフォルト値ではなくNDV統計を使用 Sel(Cul>:x) = 0.05 Sel(Cul>=:x) = 0.05 Sel(Cul LIKE :x) = 0.05 範囲条件の場合はすべて5％であるため索引が使用されやすいと言えます。 https://www.oracle.com/jp/technical-resources/articles/unexpected-oracle- field/chapter4.html#p01e
12. バインド・ピーク(Bind Peek)の課題 Bind Peek(バインド・ピーク)では、ハード・パース時にバインド変数の値を覗いて(peek)実行計画を作成します。このとき、実際の値をもと に、コスト計算するため実行計画の精度を向上することが可能です。 特に、ヒストグラムや拡張統計を使用することでデータの偏りに応じた実行計画が作成できます。 一方、ハード・パース時の実行計画を、その後バインド変数の値が変わっても同じカーソル（実行計画）を使い続けるため、以下例のよ うな問題点が発生します (バインド・ピークの課題) SELECT * FROM TBL_A WHERE COL1 >= :B1 （COL1は1から10000までの連番） B1=1 (10000件)の時はフルスキャン B1=10000 (1件)の時はインデックススキャン が適切な場合、 ハードパース時にB1=1でフルスキャンの実行計画のカーソルが作られると、次の実行時にB1＝10000が来た場合にも、その実行計画を 使用し、インデックススキャンが適切にも関わらず、フルスキャンで実行されてしまう。 こちらは、実行タイミングの違いで発生し、突然不適切なプランが選択され実行時間が悪化したように見えるため使いこなしが難しく、バ インド・ピークをOFFにすることがしばしばあります。これは精度の低いプランかもしれないが、バインド変数の値が異なっても一律に同じプラ ンを返してくれた方が安定する、という方針の選択となります。 このバインド・ピークの課題に対して、バインド変数の値に応じて適切なプランに切り替えるようにしよう、というのが、ACSです。 バインド変数を扱うSQLの課題 Copyright © 2025, Oracle and/or its affiliates 12
13. SQL説明 次ページからの動作説明のため、以下SQLの実行を考えます department_id 列はヒストグラムあり dept_id := 10のときは行数の0.00099%を占め、索引レンジスキャンが効率的 dept_id := 50のときは行数の99%を占め、全表スキャンが効率的 適応カーソル共有：Adaptive Cursor Sharing（ACS）動作 Copyright © 2025, Oracle and/or its affiliates 13 select COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id VARIABLE dept_id NUMBER EXEC :dept_id := 10; SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id; COUNT(*) MAX(EMPLOYEE_ID) ---------- ---------------- 1 200 EXEC :dept_id := 50; SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id; COUNT(*) MAX(EMPLOYEE_ID) ---------- ---------------- 100045 100999
14. バインド依存（Bind Sensitive）の検出 1. dept_id := 10で実行。バインド値を照合して索引スキャンの実行計画を生成して実行されます このとき、列にヒストグラムがある等価条件の検索のため、このカーソルはバインド依存（Bind Sensitive） としてマークされます。カー ソルがバインド依存とマークされると、別のバインド値には別の計画がより効率的であるかどうかを判断するために、カーソルの動作を 監視します。 Adaptive Cursor Sharing（ACS）：適応カーソル共有の動作 Copyright © 2025, Oracle and/or its affiliates 14 SELECT SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC, BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS, IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE FROM V$SQL WHERE SQL_TEXT LIKE '%mployee%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%'; SQL_TEXT CHILD# EXEC BUFF_GETS BIND_SENS BIND_AWARE SHARABLE ---------------------- ------ ----- ---------- --------- ---------- -------- SELECT COUNT(*), MAX(e 0 1 196 Y N Y mployee_id) FROM hr.em ployees WHERE departme nt_id = :dept_id [バインド依存となる条件]： ・問合せがバインド変数の照合によって実行されている ・バインド変数が演算子 = < > <= >= != ユーザ定義のバインド演算子(例: contains(e.job,:job,1)>0 など)とともに使われている。11.2.0.2以降のリリースでは LIKE 演算子もサポートされます ・バインド変数で条件指定されている列にヒストグラムが存在する 他、バインド依存にならない条件もあるため、”適応カーソル共有: 概要 (Doc ID 2337130.1)”を参照ください
15. バインド依存（Bind Sensitive）＝Yの後、全表スキャン期待のSQL実行->索引スキャン 2. dept_id := 50で実行。全表スキャンを期待するSQLだが、通常のバインド・ピークと同様、ソフトパースにより索引レンジスキャン で実行される。 1回実行されただけでは、オプティマイザはカーソルをバインド対応（Bind Aware）として新規実行計画作成（新規カーソル追加） とはせず、既存カーソルを利用します。 この時点ではBuffer Getsが大きい実行が一回しか実施されない可能性があるためです。カーソルが増えすぎるのを防止する目的も あります。 明示的にバインド対応にしたい場合は、BIND_AWAREヒントを付与します。 Adaptive Cursor Sharing（ACS）：適応カーソル共有の動作 Copyright © 2025, Oracle and/or its affiliates 15 SELECT SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC, BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS, IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE FROM V$SQL WHERE SQL_TEXT LIKE '%mployee%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%'; SQL_TEXT CHILD# EXEC BUFF_GETS BIND_SENS BIND_AWARE SHARABLE ---------------------- ------ ----- ---------- --------- ---------- -------- SELECT COUNT(*), MAX(e 0 2 2359 Y N Y mployee_id) FROM hr.em ployees WHERE departme nt_id = :dept_id
16. 再度、全表スキャン期待のSQL実行->BIND_AWARE=Yとなり、新規全表スキャンのカーソル作成 3. 再度、dept_id := 50で実行すると動作が変わります。新規カーソルが追加され実行計画が効率的な全表スキャンに変わりま す。バインド依存カーソルを含む文を実行する場合、オプティマイザは、内部アルゴリズムを使用して、カーソルをバインド対応として マークするかどうかを判断します。これは、カーソルが生成するデータ・アクセス・パターンが、バインド値によって大幅に異なるかどうかに よって判断されます。最初のカーソルはSHARABLE＝N、新しく生成されたカーソルはBIND_AWARE=Y, SHARABLE＝Yとなり、バインド 対応の動作となります。 SHARABLE＝Nのカーソルは使われずエージアウト対象となります。 バインド対応によって実行されたカーソルの選択率を保持しており、既存の選択率にある程度（±10%ほど）幅を効かせた選択率 に新規バインド変数での選択率が入るかを参照し、新規カーソル作成の要否の判断に使います Adaptive Cursor Sharing（ACS）：適応カーソル共有の動作 Copyright © 2025, Oracle and/or its affiliates 16 SELECT SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC, BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS, IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE FROM V$SQL WHERE SQL_TEXT LIKE '%mployee%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%'; SQL_TEXT CHILD# EXEC BUFF_GETS BIND_SENS BIND_AWARE SHARABLE ---------------------- ------ ----- ---------- --------- ---------- -------- SELECT COUNT(*), MAX(e 0 2 2359 Y N N mployee_id) FROM hr.em ployees WHERE departme nt_id = :dept_id SELECT COUNT(*), MAX(e. 1 1 800 Y Y Y mployee_id) FROM hr.em ployees WHERE departme nt_id = :dept_id select CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH from v$sql_cs_selectivity where sql_id = '6pssvz434zspy'; CHILD_NUMBER PREDICATE. RANGE_ID LOW HIGH ------------ --------------- ---------- ---------- ---------- 1 =DEPT_ID 0 0.899452 1.099330
17. 次に、索引レンジスキャンを期待するdept_id := 10で実行->新しいレンジ索引スキャンのカーソルで実行 4. 次に、dept_id := 10で実行。バインド値10の場合、既知の選択率より、少ない行数がかえってくる予測に基づいて、新しい カーソルの実行計画を作成しレンジ索引スキャンの実行計画で実行されます。 バインド対応カーソルの新規実行計画を作成したが、既存のカーソルに一致した場合は、既知のカーソルの選択率を広げてマージ する、カーソルのマージという動作もあります Adaptive Cursor Sharing（ACS）：適応カーソル共有の動作 Copyright © 2025, Oracle and/or its affiliates 17 SELECT SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC, BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS, IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE FROM V$SQL WHERE SQL_TEXT LIKE '% mployee%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%'; SQL_TEXT CHILD# EXEC BUFF_GETS BIND_SENS BIND_AWARE SHARABLE ---------------------- ------ ----- ---------- --------- ---------- -------- SELECT COUNT(*), MAX(e 0 2 2359 Y N N mployee_id) FROM hr.em ployees WHERE departme nt_id = :dept_id SELECT COUNT(*), MAX(e. 1 1 800 Y Y Y mployee_id) FROM hr.em ployees WHERE departme nt_id = :dept_id SELECT COUNT(*), MAX(e 2 1 3 Y Y Y mployee_id) FROM hr.em ployees WHERE departme nt_id = :dept_id select CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH from v$sql_cs_selectivity where sql_id = '6pssvz434zspy'; CHILD_NUMBER PREDICATE. RANGE_ID LOW HIGH ------------ --------------- ---------- ---------- ---------- 1 =DEPT_ID 0 0.899452 1.099330 2 =DEPT_ID 0 0.000009 0.000011
18. SQL Plan Management(SPM)とAdaptive Cursor Sharing(ACS)の組み合わせ Copyright © 2025, Oracle and/or its affiliates 18
19. ACSは、バインド対応（Bind Aware）となった場合、バインド変数に応じて新規実行計画を追加する機能です。 一方、SPMは、ベースラインの実行計画に選択を限定します。これが相反する部分となります。 両者を同時に使用する場合、ACSによって新しいプランが生成された場合も、使用されるのはSPMのベースラインにあるプランとなり、新規 プランは計画履歴に未承認として追加され、承認後初めて使えるようになります。 SPMを使いながら、ACSの機能もフル活用するには、事前にACSによって生成するプランのパターンをベースラインに登録しておくことが理想 的です。これをするには、あらかじめACSで利用される複数のプランがカーソルにある状態にし、手動でカーソル・キャッシュからSQL計画ベー スラインにロードする方法が推奨となります。 ACSのパターンの事前の網羅は難しいかもしれませんが、代表的なものだけでも登録しておき、定期的に確認して承認する運用とすると よいと考えます。 また、SQL 計画ベースラインに承認済みの計画が 1 つのみ存在する場合、その文では ACS が自動的に無効になります。SPM で問合せ に 1 つの計画のみを選択できる場合は、カーソルはバインド依存としてマークません。そのため、ACSとSPMを同時に機能させたい場合は ベースラインに２つ以上登録してから使い始めることが必要です。 SPMとACSの組み合わせ Copyright © 2025, Oracle and/or its affiliates 19
20. Copyright © 2025, Oracle and/or its affiliates 20 本日は以下の内容をお伝えしました • SQL Plan Management(SPM) • Adaptive Cursor Sharing(ACS) • SPMとACSの組み合わせ まとめ
21. • SQLチューニング・ガイド 23ai • https://docs.oracle.com/cd/G11854_01/tgsql/toc.htm • Oracle Database 19cのSQL計画管理 Oracle ホワイト・ペーパー / 2019 年 3 月 13 日 • https://www.oracle.com/technetwork/jp/database/bi-datawarehousing/twp-sql-plan-mgmt-19c-5324207-ja.pdf • SQL Plan Management Cheat Sheet - Part 1 • https://blogs.oracle.com/optimizer/post/spm-cheat-sheet-part-1 • SQL Plan Management Cheat Sheet - Part 2 • https://blogs.oracle.com/optimizer/post/spm-cheat-sheet-part-2 • Why are there many cursors for queries containing bind variables? • https://blogs.oracle.com/optimizer/post/why-are-there-many-cursors-for-queries-containing-bind-variables • 適応カーソル共有: 概要 (Doc ID 2337130.1) • 適応カーソル共有: 実行例 (Doc ID 2337693.1) • Oracle Database Technology Night #64 Automatic SQL Plan Management は使えるのか • https://speakerdeck.com/oracle4engineer/oracle-database-technology-night-number-64-automatic-sql-plan-management-hashi-erunoka 参考資料 Copyright © 2025, Oracle and/or its affiliates 21