Oracle Database Technology Night #48 津島博士のパフォーマンス講座 - SQLチューニングは実行計画から (Q&A)

Q&A編です。

Transcript

1. Oracle Technology Night #48 津島博士のパフォーマンス講座 SQLチューニングは実行計画から（Q&A） 津島 浩樹 日本オラクル株式会社 COE本部 データベース・ソリューション部 2021年 9月30日
2. Safe harbor statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. 2 Copyright © 2021, Oracle and/or its affiliates.
3. 津島博士のQ&Aコーナー（ask 津島博士） Copyright © 2020, Oracle and/or its affiliates. 3
4. サンプル質問 1. 実行計画からTEMP領域を使用しているかを判断する方法はあ りますでしょうか。 津島博士のQ&Aコーナー（ask 津島博士） Copyright © 2020, Oracle and/or its affiliates. 4 ✓ DBMS_XPLAN.DISPLAY_CURSORの実行時統計の「Used-Tmp」、 SQL監視の「一時(最大)」に数字が出力されているとTEMPが使用さ れていることになります。
5. サンプル質問 2. パーティション・ワイズ結合は分かるのですが、結合処理以外 で"PX PARTITION xxx ALL"/"PARTITION xxxx ALL"が出力され るときがありますが、これは何を行っているのでしょうか。 津島博士のQ&Aコーナー（ask 津島博士） Copyright © 2020, Oracle and/or its affiliates. 5 ✓ パーティション・ワイズ処理は結合以外でも動作します。GROUP BY、DISTINCT、分析ファンクション、ORDER BYなどで動作しま す。ただし、パラレル実行は結合とGROUP BYだけになります（12.2 からDISTINCTも可能になりました）。パーティション・ワイズ処理 については第45回、第60回を参照してください。
6. サンプル質問 3. 副問合せ内でGROUP BYを行っていないのにHASH GROUP BY が2個出力されているときがありますが、これはどのような動作 になるのでしょうか。 津島博士のQ&Aコーナー（ask 津島博士） Copyright © 2020, Oracle and/or its affiliates. 6 ✓ Group-by Pushdownが実行されたということなります。例えば、ス キャン+Group byをパラレル実行した場合に、スキャン・プロセスか らGroup Byプロセスにデータ転送しますが、このデータ転送量が多 い場合にスキャン側でGroup by処理を行って行数を削減してから転 送する処理になります（Group-by Pushdownの詳細は第20回を参照 してください）。
7. サンプル質問 4. 索引アクセスでパラレル実行するときとしないときがあります が、どのような理由が考えられますでしょうか。 津島博士のQ&Aコーナー（ask 津島博士） Copyright © 2020, Oracle and/or its affiliates. 7 ✓ 索引アクセスはパーティション化されていないとパラレル実行されま せん。ただし、ネステッド・ループ結合の内部表アクセスは索引が パーティション化されていなくてもパラレル索引スキャンすることが できます。
8. サンプル質問 5. パラレル実行のデータ分散でデータが偏っているかを知る方法 はありますでしょうか。 津島博士のQ&Aコーナー（ask 津島博士） Copyright © 2020, Oracle and/or its affiliates. 8 ✓ SQL監視の「パラレル」セクションでパラレル・サーバー・プロセス ごとには見ることができますが、どの操作で偏っているかまでは判断 できません。詳細に確認したい場合は'v$pq_tqstat'を出力してくださ い（v$pq_tqstatビューの出力方法については第39回を参照してくだ さい）。
9. サンプル質問 6. 実行計画からヒントが使用されているかを判断できるのでしょ うか。 津島博士のQ&Aコーナー（ask 津島博士） Copyright © 2020, Oracle and/or its affiliates. 9 ✓ 実行計画から判断できませんが、Oracle Database 19cからのヒント 使用状況レポートを使用すると分かります。ヒント使用状況レポート については第72回を参照してください。
10. サンプル質問 7. ヒントが使用されている実行計画に対して、強制的にヒントを 使用しないようにできますでしょうか。 津島博士のQ&Aコーナー（ask 津島博士） Copyright © 2020, Oracle and/or its affiliates. 10 ✓ 以下の初期化パラメータをTRUE（デフォルトはFALSE）に設定する ことでヒントを無視することができます（Autonomous Databaseの デフォルトはTRUEです）。 ・OPTIMIZER_IGNORE_PARALLEL_HINTS（PARALLELヒント） ・OPTIMIZER_IGNORE_HINTS（その他のヒント）
11. サンプル質問 8. 実行時に変化した実行計画を確認する方法はあるのでしょうか。 津島博士のQ&Aコーナー（ask 津島博士） Copyright © 2020, Oracle and/or its affiliates. 11 ✓ 適応問合せ最適化は動作すると以下のようにNOTE部に出力されます。ただ し、実行計画が変化したかまでは出力されませんので、実行計画の“Plan hash value”で確認する必要があります。 - cardinality feedback used for this statement - statistics feedback used for this statement - this is an adaptive plan バインド変数(bind peekなど)はNOTE部には出力されませんが、ハードパース時の バンド変数の値を出力することはできます。 DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>‘PEEKED_BINDS ALLSTATS LAST’)を 指定すると実行計画に「Peeked Binds (identified by position):」セクションが追加 されて出力します（マニュアルには記載されていません）。
12. サンプル質問 9. バインド変数を使用したSQLは最適な実行計画にはならないの でしょうか。 津島博士のQ&Aコーナー（ask 津島博士） Copyright © 2020, Oracle and/or its affiliates. 12 ✓ バインド変数はプラン作成時には実際の値が分かりませんので、最適な実行計画 を作成できないときがあります（ヒストグラムが存在しない場合と同様の動作に なります）。ただし、9iからのbind peek（最初のハード・パースの時にバインド 変数内の値を基に実行計画を作成する）や11gからの優れたカーソル共有 （Adaptive Cursor Sharing）によって、バインド変数の値をチェックして異なる 実行計画が適切の場合には、子カーソルを生成して最適な実行計画を作成する機 能があります。そのため、以下のような目的に合わせてリテラルかバインド変数 を使用してプログラミングを行う必要があることを忘れないでください。 ✓ カーソルを共有したいときはバンド変数 (値によってあまり実行計画が変化しない、例 えばユニーク索引に対する条件など) 、共有する必要がないときはリテラル (リテラルを バインド変数に変換するCURSOR_SHARING機能もあります)
13. サンプル質問 10. オプティマイザが内部でコスト比較した実行計画を知る方法は あるのでしょうか。 津島博士のQ&Aコーナー（ask 津島博士） Copyright © 2020, Oracle and/or its affiliates. 13 ✓ CBOトレース（イベント10053）で出力できます。ただし、それなり の知識がないと見れません。
14. 津島博士のQ&Aコーナー（ask 津島博士） 当日の質問 Copyright © 2020, Oracle and/or its affiliates. 14
15. サンプル質問 1. SQLチューニングの際に Predicate Information の情報をヒント にチューニングするケースがあれば教えて下さい。 津島博士のQ&Aコーナー（ask 津島博士） Copyright © 2020, Oracle and/or its affiliates. 15 ✓ アクセス方法が最適になっているかを確認するのに使用します。例え ば、最適な索引を使用しているかなどです。
16. サンプル質問 2. DBMS_XPLAN の Query Block の読み方辿り方を解説したドキュ メント的なものはありますか？ 津島博士のQ&Aコーナー（ask 津島博士） Copyright © 2020, Oracle and/or its affiliates. 16 ✓ これは「Query Block Name / Object Alias」 セクションの読み方辿り方として回答します。 クエリ・ブロックは、SQLの基本単位（サブ・クエリとメイン・クエリは別クエリ・ブロックに なる）で、キーワードに基づいて、sel$1、ins$2、upd$3、del$4、mrg$5などのように自動的に 名前を付けます。上記SQLは、SELECT文ですので、外側のクエリはSEL$1、内側のクエリは SEL$2となります（どのクエリに対応する名前かは、Object Aliasに出力されている表名を見る と、その表名が含むクエリで判断できるかと思います）。ただし、実行計画が最適化されると名 前が変化しますが（上記ではSEL$5DA710D3となっていますが）、これについて解説している資 料などはありません。10053トレースの最後の「Query Block Registry」にクエリ・ブロック名 の変化が出力されているようですが、これの解説もありません。 SELECET e.name, e.deptno FROM emp e WHERE e.deptno IN (SELECT d.deptno FROM dept d WHERE d.loc='DALLAS'); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'alias')); … Query Block Name / Object Alias (IDENTIFIED BY operation id): ------------------------------------------------------------- 1 - SEL$5DA710D3 2 - SEL$5DA710D3 / E@SEL$1 3 - SEL$5DA710D3 / D@SEL$2
17. サンプル質問 3. orを使う場合indexを使わないと思ってますが認識あってますで そうか。inを使えばindexが使えますでしょうか。 津島博士のQ&Aコーナー（ask 津島博士） Copyright © 2020, Oracle and/or its affiliates. 17 ✓ 同じ列の条件に対するORは「INLIST ITERATOR」で索引を使用しま す。異なる列の条件に対するORは索引を使用しません。索引アクセ スが効果的と判断した場合にはOR拡張 (UNION ALLにQuery Transformation) されます。
18. サンプル質問 4. SPMやSQLプロファイルで実行計画を試みる際に、稀に再現不 可となってしまいます。このようなケースで原因を調査するう まい方法はありますでしょうか？ 津島博士のQ&Aコーナー（ask 津島博士） Copyright © 2020, Oracle and/or its affiliates. 18 ✓ 使用している索引が削除された場合などに発生します。 原因を特定できるツールなどはありませんので、再現できなかった実行計画から調査 するしか方法はないかと思います。SPMは、以下のSQLでベースラインの実行計画を 出力できます。 プラン名は、実行計画のNote部のものか、以下のSQLで求めることができます。 SELECT * FROM table(dbms_xplan.display_sql_plan_baseline(plan_name=>'<プラン名>')); SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE sql_text LIKE '<実行しているSQL>';