質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.48%

  • Oracle Database 11g

    198questions

    Oracle DatabaseはRDBMSの商品です。具体的な発売商品として知られているのが、 Oracle9i、Oracle10g、Oracle 11gとOracle 12cです。

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

解決済

回答 2

投稿

  • 評価
  • クリップ 3
  • VIEW 680

YomiYami

score 5

 前提

oracle 11g
oracle 12c

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

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

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

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

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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+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 を忘れずに。

DECLARE
  tablist DBMS_STATS.ObjectTab;
  i NUMBER;
BEGIN
  DBMS_STATS.SET_SCHEMA_PREFS(USER, 'PUBLISH', 'FALSE'); -- 統計情報の遅延適用を開始
  DBMS_STATS.GATHER_SCHEMA_STATS(USER, options => 'GATHER AUTO', objlist => tablist);
  FOR i IN 1..tablist.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(tablist(i).ownname || '.' || tablist(i).objname);
    DBMS_STATS.DELETE_PENDING_STATS(USER, tablist(i).objname); -- 統計情報の遅延適用を削除
  END LOOP;
  DBMS_STATS.SET_SCHEMA_PREFS(USER, 'PUBLISH', 'TRUE'); -- 統計情報の遅延適用を終了
END;

DBMS_STATS.GATHER_SCHEMA_STATS の options に GATHER AUTO を指定すると自動オプティマイザ統計収集と同じ動作になるはずです。ただ、そのままだと本当に収拾されてしまうので、統計情報の遅延適用を利用して、統計情報を更新しないようにしています。他スキーマの対象を見るには USER のところに適切なスキーマ名を指定してください。


空テーブルの統計情報の取得は行われてしまっているのですね。
正しいインデックスを使えず初回処理が遅くなることを回避するにはヒント句を利用となるのでしょうか?

ヒント句を正しく指定できるのであれば、それも 1 つの解決策となります。ただ、使用するべきヒントは時間経過(データの変化)と共に変わることが多いため、なかなか難しいところもあるかと思います。

・12c
12c では動的統計機能が強化されていますので、動的統計機能を試してみるのもありかと思います。該当テーブルの統計情報を削除しておいた上で統計情報をロックしておくと初期化パラメータで動的統計を無効にしていない限り、動的統計を使用してくれるはずです。
以下の文書にあるようにセッション毎にサンプリングレベルを設定するのが一番良いと思います。
https://docs.oracle.com/cd/E57425_01/121/TGSQL/tgsql_astat.htm#GUID-F1C666DC-5450-4F92-83AA-607327DB5FD8

どのタイミングで統計のロックを解除するかを判断するには、最初に回答した遅延状態で統計を採取し、セッションパラメータ OPTIMIZER_USE_PENDING_STATISTICS で遅延状態の統計を使用して実行時間・実行計画を見て判断されると良いかと思います。
参考:http://www.oracle.com/technetwork/jp/database/articles/shibacho/index-1849103-ja.html

・11g
12c と同じ方法でも良いのですが、なぜか動的統計(11gでは動的サンプリング)を使用すると思うような結果が得られなかったため、実際に入ると想定されるダミーデータをロードして、統計情報を取得後に統計情報をロックして対応したことがあります。ただし、この手法ではダミーデータよりはるかに件数が少ない場合に思うような実行計画が策定されずに困ったこともあります。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/09/16 09:40

    具体的な回答ありがとうございます。
    処理が長いため SQL トレース(セッション指定)ではトレースを開始するタイミングを考えないとほしい情報が取れなかったため、別方法を探していました。

    空テーブルの統計情報の取得は行われてしまっているのですね。正しいインデックスを使えず初回処理が遅くなることを回避するにはヒント句を利用となるのでしょうか?

    キャンセル

  • 2018/09/16 14:13

    回答に追記しました。

    キャンセル

  • 2018/09/16 21:08

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

    キャンセル

+1

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/09/16 09:27

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

    キャンセル

  • 2018/09/16 10:40

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.48%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

  • 解決済

    SQLのチューニングについて

    SQLのチューニングの件で質問なのですが、 実行計画を取る作業まで行ってデータを出力したのですが チューニングをする際に主にどこを見て修正対象を決めるのか? というのがわからないで

  • 解決済

    テキストボックスのエラーです.oracle

    テキストボックスに「'」を入れた時にORA-00911: 文字が無効ですのエラーが出ます. シングルシングルクォーテーションを入れた時にエラーが出ないようにするにはどうしたらいい

  • 解決済

    statspack等の見方

    仕事の配置換えでサーバー上で流れているOLTP処理の 遅延とかを調べなくてはならなくなりました。 statspackやSQLトレースを取って調べる必要があると 思うのですが、

  • 解決済

    ORACLEのチューニング時のSUMの対応について

    テーブルA、B、CをJoinして、Aのカラム1をSUM集計したViewを作成したいです。 Viewの項目は、テーブルAのカラム1、テーブルBのカラム1を使用します。(テーブルCは

  • 解決済

    OracleのLog_Bufferの調整について

    前提・実現したいこと 初投稿の為、不備があればご指摘ください。 環境情報: OS:Windows Server 2012 x64 Database:Oracle 11g

  • 解決済

    Oracleの内部SQLについて

    前提・提示情報 環境情報:  OS:Windows 10 x64  Database:Oracle 11g Standard Edition x64  Tools:sqld

  • 解決済

    実行計画の結果を見ても用語の意味からわかりません。。。

    ORACLEで、動作の遅いSQLがあり、原因究明のため、A5で実行計画を表示させたのですが、用語の意味からわかりません。 UPDATE STATEMENT Cost = 14

  • 受付中

    Oracleでのユーザごとのインデックスについて

    あるユーザで作成したインデックスは他のユーザでも使用されるのでしょうか? それとも、ユーザごとにテーブルのインデックスは全く別のものになるのでしょうか?

同じタグがついた質問を見る

  • Oracle Database 11g

    198questions

    Oracle DatabaseはRDBMSの商品です。具体的な発売商品として知られているのが、 Oracle9i、Oracle10g、Oracle 11gとOracle 12cです。