Oracle Database オプティマイザ・ヒントの活用

2025年5月23日（金）に開催された Developer Day 2025 の [LT1-1] 「Oracle Database オプティマイザ・ヒントの活用」の登壇資料です。

オプティマイザの実行計画を制御するオプティマイザ・ヒント。
SQL文の内容を書き換えることなくチューニングできる強力な機能ですが、書き方によっては有効に働かない場合もあります。
ここでは、間違いやすいポイントとして、結合操作のヒントについて解説します。

Transcript

1. Oracle Database オプティマイザ・ヒントの活用 辻 研一郎 Principal Cloud Engineer 日本オラクル株式会社 2025年5月23日
2. 辻 研一郎 クラウド事業統括 クラウド・エンジニアリングCOE統括 データベース・ソリューション部 プリンシパル・クラウド・エンジニア 2 Copyright © 2025, Oracle and/or its affiliates Oracle Databaseの検証経験や提案活動、前職での 導入経験をもとに、DBパフォーマンス関連を中心に案件 および技術支援に従事。
3. はじめに ー ヒントの利用は慎重に Copyright © 2025, Oracle and/or its affiliates 3 オプティマイザ・ヒント（ヒント）は、オプティマイザの選択をユーザーが指定することができます。 ヒントを使用すると、通常はオプティマイザによって行われる決定をユーザーが決定できます。 ヒントの短所は、管理、チェックおよび制御のためにコードが増えることです。ヒントが導入されたのはOracle7からですが、当時 は、オプティマイザによって生成された計画が最善ではない場合にユーザーが取れる手段はほとんどありませんでした。データ ベースやホスト環境内の変更によってヒントが非推奨となるか、負の結果をもたらす可能性があるため、ヒントを使用した テストが望ましいものの、他の手法を使用して実行計画を管理することがあります。 オプティマイザでは解決されないパフォーマンスの問題に対処するために、SQLチューニング・アドバイザ、SQL計画管理、 SQLパフォーマンス・アナライザなどの複数のツールが用意されています。これらのツールにはデータやデータベース環境の変 更に対応した新しいソリューションが用意されているため、ヒントのかわりにこれらを使用することを強くお薦めします。 SQLチューニング・ガイド 第VI部 オプティマイザの制御 19 オプティマイザへの影響
4. オプティマイザ・ヒントの使い方 Copyright © 2025, Oracle and/or its affiliates 4 • ヒントは、+記号を追加したコメントの形式でSQLキーワード (SELECT、UPDATE、INSERT、MERGEまたは DELETE)の直後にヒント構文に従って記載します • SELECT /* This is a comment */ count(*) FROM Sales; • SELECT /*+ This is a hint */ count(*) FROM Sales; • この場合は、有効なヒントではないため、ただのコメントとして扱われます • 表別名を利用している場合、ヒントで表別名を指定する必要があります。指定していない場合、無効になります • ◦ SELECT /*+ index(e pk_emp)*/ * FROM emp e; • ◦ SELECT /*+ index(emp pk_emp)*/ * FROM emp; • × SELECT /*+ index(emp pk_emp)*/ * FROM emp e; （表別名を指定していないため無効） • ヒントは複数記述することができます • 文ブロックでは、ヒントを含むコメントは1つしか持つことができませんが、空白区切りでヒントを複数記述できます • SELECT /*+ FULL(hr_emp) CACHE(hr_emp) */ last_name FROM employees hr_emp;
5. オプティマイザ・ヒントの使い方 Copyright © 2025, Oracle and/or its affiliates 5 • ヒントとコメントは混在させることができますが、お勧めしません • SELECT /*+ FULL(s) and a comment */ count(*) FROM Sales; • SELECT /*+ This_is_a_comment FULL(s) */ count(*) FROM Sales; • お勧めしない理由：構文エラー（記述ミス）となるヒントがあると、後続のヒントが無視される動作があります。うっか りヒントと似たコメントで構文エラーを起こす状況を回避するため、コメント混在はお勧めしません • たとえば、ヒント指定/*+ INDEX(t1) FULL(t2) MERG(v) USE_NL(t2) */のMERG(v)には構文エ ラーがあります。この場合、 MERG(v) と USE_NL(t2) は無視されます • ヒントが無効になる場合 • 構文エラー：構文の記述ミス、 上記のMERG(v)など • 未解決のヒント：索引スキャンを指定して、指定した索引がない場合など • 競合するヒント：文でFULL(employees) INDEX(employees)を指定した場合など、ほとんどの場合両方が無 視されます • 変換の影響を受けるヒント：問合せ変換によって該当クエリブロックが変更された場合 • たとえば PUSH_PRED(some_view) MERGE(some_view) を指定している場合、MERGEヒントの ビュー・マージによってsome_viewが上位の問合せにマージされてなくなった場合、 PUSH_PRED(some_view)は適用できなくなります • ご参考：津島博士のパフォーマンス講座 第55回 オプティマイザ・ヒントにいて https://blogs.oracle.com/otnjp/post/tsushima-hakushi-55
6. オプティマイザ・ヒントの4つの型 Copyright © 2025, Oracle and/or its affiliates 6 単一表 - 1つの表またはビューで指定するヒント FULL, INDEX or USE_NLなど マルチ表 - 1つ以上の表またはビューを指定するヒント LEADING or ORDEREDなど 問合せブロック - 単一の問合せブロックに対して操作するのヒント STAR_TRANSFORMATION or UNNESTなど 文 - SQL文全体に適用するヒント ALL_ROWS or OPTIMIZER_FEATURES_ENABLEなど 結合に関するヒントにも関わらず、単一表？と思われた方、本日は こちらの結合操作に関わるヒントは単一表指定という話をメインで行 います
7. 結合操作のヒントとよくある誤解 Copyright © 2025, Oracle and/or its affiliates 7 結合操作のヒント(USE_NL、USE_MERGE、USE_HASH)は単一表ヒントの分類であり、USE_NL(t1)は、 t1が内部表となる場合に、ネステッド・ループ結合を選択する、という意味になります 一方、 USE_NL(t1 t2)というように、表を複数指定することもできます。そのことが以下のような誤解を生んでしま います よくある誤解：USE_NL(t1 t2)と書くとt1、t2の順番でネステッド・ループ結合をする（つまり、t1駆動表、t2内 部表のネステッド・ループ結合となる）という誤解 実際の動作： USE_NL(t1 t2)は、USE_NL(t1) USE_NL(t2)と記述したことと同じとみなされます。このとき、 内部表を含んだ方のUSE_NLが採用、もう片方は不採用となります。結合順序を強制する効果はないことに注意 してください。 推奨する指定の仕方：結合順序のヒント（LEADINGまたはORDERED）と共に使用する。 たとえば、LEADING(t1 t2) USE_NL(t2)とすると、t1、t2の順番でネステッド・ループ結合（t1駆動表、t2内 部表になる）という指定となります
8. クイズ：このヒントでネステッド・ループ結合にならないのはなぜでしょう？ Copyright © 2025, Oracle and/or its affiliates 8 SELECT /*+ USE_NL(s) */ c.cust_last_name, SUM(s.amount_sold) FROM customers c, sales s WHERE c.cust_id = s.cust_id AND c.cust_city = 'Los Angeles' AND c.cust_state_province = 'CA' AND s.time_id='30-SEP-22' GROUP BY c.cust_last_name; ※CUSTOMERSとSALESの索引は削除した状態してあります
9. 実行計画 Copyright © 2025, Oracle and/or its affiliates 9 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 10 (100)| | | 1 | HASH GROUP BY | | 2 | 182 | 10 (20)| 00:00:01| |* 2 | HASH JOIN | | 2 | 182 | 9 (12)| 00:00:01| |* 3 | TABLE ACCESS FULL| SALES | 2 | 34 | 3 (0)| 00:00:01| |* 4 | TABLE ACCESS FULL| CUSTOMERS | 13 | 962 | 5 (0)| 00:00:01| -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C"."CUST_ID"="S"."CUST_ID") 3 - filter("S"."TIME_ID"='30-SEP-22') 4 - filter(("C"."CUST_CITY"='Los Angeles' AND "C"."CUST_STATE_PROVINCE"='CA')) 結合順序のヒント指定はないので、CUSTOMERS → SALES、 SALES → CUSTOMERSの両パター ンの結合順序を評価します。このうち、CUSTOMERS → SALESの結合順では、 USE_NL(s)が効い て、ネステッド・ループ結合のみコストを評価します。一方、SALES → CUSTOMERSの結合順では、各 結合操作（ネステッド・ループ、ハッシュ、マージ）が評価されます。 結果として、 SALES → CUSTOMERSの結合順でハッシュ結合が最もコストが低かったため選択された、 ということになります
10. ヒント・レポートでヒントの使用状況を確認 (19c以降) Copyright © 2025, Oracle and/or its affiliates 10 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 10 (100)| | | 1 | HASH GROUP BY | | 2 | 182 | 10 (20)| 00:00:01| |* 2 | HASH JOIN | | 2 | 182 | 9 (12)| 00:00:01| |* 3 | TABLE ACCESS FULL| SALES | 2 | 34 | 3 (0)| 00:00:01| |* 4 | TABLE ACCESS FULL| CUSTOMERS | 13 | 962 | 5 (0)| 00:00:01| -------------------------------------------------------------------------------------- Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1)) --------------------------------------------------------------------------- 3 - SEL$1 / S@SEL$1 U - USE_NL(s) U – Unusedは、最終プランでヒントが使用されなかった、という意味になります DBMS_XPLAN.DISPLAYなどで確認できます
11. USE_NLヒントの推奨の使用方法 Copyright © 2025, Oracle and/or its affiliates 11 LEADINGヒントと一緒に使用することで、結合順序と結合操作の両方を指定することができます SELECT /*+ LEADING(c s) USE_NL(s) */ c.cust_last_name, SUM(s.amount_sold) FROM customers c, sales s WHERE c.cust_id = s.cust_id AND c.cust_city = 'Los Angeles' AND c.cust_state_province = 'CA' AND s.time_id='30-SEP-22' GROUP BY c.cust_last_name;
12. LEADING ヒントを追記した実行計画 Copyright © 2025, Oracle and/or its affiliates 12 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 12 (100)| | | 1 | HASH GROUP BY | | 1 | 52 | 12 (20)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 52 | 11 (15)| 00:00:01 | |* 3 | TABLE ACCESS FULL | CUSTOMERS | 1 | 35 | 5 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL | SALES | 1 | 17 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------- SALES表が内部表となるネステッド・ループ結合の実行計画になりました
13. まとめ Copyright © 2025, Oracle and/or its affiliates 13 オプティマイザ・ヒントについて、基本的な使い方と単一表ヒントである結合操作ヒントのTIPSをお伝えしました 結合操作のヒントを複数指定していた方は、結合順序までは指定できないことを、ご注意ください。 本日の内容を含む動画が Youtube （英語）にもありますので、ぜひご覧になってください Harnessing the power of Oracle Database optimizer hints | CloudWorld 2022 スピーカー：Maria Colgan https://www.youtube.com/watch?v=fVdFT8y-IX4