パフォーマンスの悪いSQLの調査方法

前提

oracle 11g
oracle 12c

質問SQL発行時の実行計画を取得し、パフォーマンスの悪いSQLの調査する

特定のsession中に発行されたSQLを知りたい。
あるいは、特定の期間内で発行されたSQLを知りたい。

v$sql_planが作成されるタイミング

v$sql_planが作成されるタイミングはSQL発行後すぐなのでしょうか？

計情報を収集されるタイミング

空のテーブルを作成した場合に、自動オプティマイザ統計収集の対象になるのでしょうか？
また、収集対象の確認をすることは可能でしょうか？

行動規範の内容に同意します

回答2件

ベストアンサー

特定のsession中に発行されたSQLを知りたい。
あるいは、特定の期間内で発行されたSQLを知りたい。

すべての SQL を見るなら SQL トレースを採取するしかないと思います。
すべてで無くて良いなら Orlofsky さんも挙げられている STATSPACK を使うのがおすすめです。

v$sql_planが作成されるタイミングはSQL発行後すぐなのでしょうか？

v$sql_plan は SQL 実行中に作成されるようです。
JDBC で言えば Statement.execute や Statement.executeQuery の実行中、
ADO.NET で言えば IDataCommand.ExecuteReader や IDataCommand.ExecuteScalar 等の実行中です。

SQL_ID を SQL から計算して、別セッションから監視した結果ですので、保証はありません。
SQL_ID の算出方法は以下のページを参考にしました。
https://qiita.com/nieve/items/b9f3a4762d3ed684dbbe

空のテーブルを作成した場合に、自動オプティマイザ統計収集の対象になるのでしょうか？

なります。

また、収集対象の確認をすることは可能でしょうか？

完全な対象でなくて良いなら USER_TAB_MODIFICATIONS を参照してください。

完全な対象を取得するには以下のようなストアドプロシージャを SQL*Plus 上で実行して確認してください。
※SET SERVEROUTPUT ON を忘れずに。

PL/SQL
1DECLARE
2  tablist DBMS_STATS.ObjectTab;
3  i NUMBER;
4BEGIN
5  DBMS_STATS.SET_SCHEMA_PREFS(USER, 'PUBLISH', 'FALSE'); -- 統計情報の遅延適用を開始
6  DBMS_STATS.GATHER_SCHEMA_STATS(USER, options => 'GATHER AUTO', objlist => tablist);
7  FOR i IN 1..tablist.COUNT LOOP
8    DBMS_OUTPUT.PUT_LINE(tablist(i).ownname || '.' || tablist(i).objname);
9    DBMS_STATS.DELETE_PENDING_STATS(USER, tablist(i).objname); -- 統計情報の遅延適用を削除
10  END LOOP;
11  DBMS_STATS.SET_SCHEMA_PREFS(USER, 'PUBLISH', 'TRUE'); -- 統計情報の遅延適用を終了
12END;
13```DBMS_STATS.GATHER_SCHEMA_STATS の options に GATHER AUTO を指定すると自動オプティマイザ統計収集と同じ動作になるはずです。ただ、そのままだと本当に収拾されてしまうので、統計情報の遅延適用を利用して、統計情報を更新しないようにしています。他スキーマの対象を見るには USER のところに適切なスキーマ名を指定してください。
14
15---
16
17> 空テーブルの統計情報の取得は行われてしまっているのですね。
18> 正しいインデックスを使えず初回処理が遅くなることを回避するにはヒント句を利用となるのでしょうか？
19
20ヒント句を正しく指定できるのであれば、それも 1 つの解決策となります。ただ、使用するべきヒントは時間経過（データの変化）と共に変わることが多いため、なかなか難しいところもあるかと思います。
21
22・12c
2312c では動的統計機能が強化されていますので、動的統計機能を試してみるのもありかと思います。該当テーブルの統計情報を削除しておいた上で統計情報をロックしておくと初期化パラメータで動的統計を無効にしていない限り、動的統計を使用してくれるはずです。
24以下の文書にあるようにセッション毎にサンプリングレベルを設定するのが一番良いと思います。
25[https://docs.oracle.com/cd/E57425_01/121/TGSQL/tgsql_astat.htm#GUID-F1C666DC-5450-4F92-83AA-607327DB5FD8](https://docs.oracle.com/cd/E57425_01/121/TGSQL/tgsql_astat.htm#GUID-F1C666DC-5450-4F92-83AA-607327DB5FD8)
26
27どのタイミングで統計のロックを解除するかを判断するには、最初に回答した遅延状態で統計を採取し、セッションパラメータ OPTIMIZER_USE_PENDING_STATISTICS で遅延状態の統計を使用して実行時間・実行計画を見て判断されると良いかと思います。
28参考：[http://www.oracle.com/technetwork/jp/database/articles/shibacho/index-1849103-ja.html](http://www.oracle.com/technetwork/jp/database/articles/shibacho/index-1849103-ja.html)
29
30・11g
3112c と同じ方法でも良いのですが、なぜか動的統計（11gでは動的サンプリング）を使用すると思うような結果が得られなかったため、実際に入ると想定されるダミーデータをロードして、統計情報を取得後に統計情報をロックして対応したことがあります。ただし、この手法ではダミーデータよりはるかに件数が少ない場合に思うような実行計画が策定されずに困ったこともあります。

投稿2018/09/14 12:59

編集2018/09/16 05:13

atata0319

総合スコア881

YomiYami

2018/09/16 00:40

具体的な回答ありがとうございます。処理が長いため SQL トレース(セッション指定)ではトレースを開始するタイミングを考えないとほしい情報が取れなかったため、別方法を探していました。空テーブルの統計情報の取得は行われてしまっているのですね。正しいインデックスを使えず初回処理が遅くなることを回避するにはヒント句を利用となるのでしょうか？

atata0319

2018/09/16 05:13

回答に追記しました。

atata0319

2018/09/16 12:08

TUNING PACK 導入済みなら V$SQL_MONITORING と V$SQL_PLAN_MONITORING で効率の悪い SQL はすぐわかります。が、そもそも TUNING PACK 導入済みなら、この類の質問が出ることもないかと思います。

行動規範の内容に同意します

質問SQL発行時の実行計画を取得し、パフォーマンスの悪いSQLの調査する

であればstatspackOTN Japan - Oracleデータベース性能対策機能～ Statspackとを使っては？

投稿2018/09/13 22:55

Orlofsky

総合スコア16415

YomiYami

2018/09/16 00:27

回答ありがとうございます。 statspackは既存のDB環境へインストールをする必要があるということになるのでしょうか？既存環境への変更を極力行わない方法がよいのですが。

Orlofsky

2018/09/16 01:40

はい、インストールが必要です。パフォーマンス・チューニングをすることとデータベースを変更しないことのどちらが大切か考えましょう。上記URLの中にある >このSQLは「SQL ordered by CPU」だけではなく、「SQL ordered by Elapsed」（処理時間の長い順）や「SQL ordered by Gets」（Buffer Gets数の多い順）にも見ることができますので、... がパフォーマンスの悪いSQLです。パフォーマンス・チューニングにはそれなりの経験が必要になる場合が多いですから、数か月でも熟練者を雇われては？

行動規範の内容に同意します

あなたの回答