V$SQL_PLANでCRUD表モドキを作ってみる③

ここからはExcelで

前回のクエリーの結果をMicrosoft Excelのピボットテーブルを利用して集計してみましょう。（Excel2010での方法を説明します。）
今回はある架空アプリケーションにおける実績データを対象とします。

一番簡単なのはSQL*Plusの実行結果を以下のようにExcelのシートにコピー＆ペーストするやり方です。

Excelにコピー＆ペースト

 データをスペースで区切る

データ＞区切り位置 で「区切り位置指定ウィザード」を呼び出し、「スペースによって右または左に揃えられた固定長フィールドのデータ」を選択して、データを区切ります。

区切り位置指定ウィザード

ピボットテーブルで集計

データを区切ったら、A〜C列（CRUD, SQL_ID, OBJECT_NAME）を選択した状態から、挿入＞ピボットテーブル で「ピボットテーブルの作成（ウィザード）」を呼び出します。配置する場所が「新規ワークシート」となっていることを確認し、「OK」をクリックします。

ピボットテーブルウィザード

右側にピボットテーブルのフィールドリストを指定する画面が出てくるので、リストのそれぞれを下のボックスにドラッグして指定します。

  • レポートフィルタ：　CRUD
  • 列ラベル：　OBJECT_NAME
  • 行ラベル：　SQL_ID
  • 値：　CRUD（データの個数となっていることを確認）

ピボットテーブル設定

これで完成ですが、見やすくするためにテーブル名を90°回転させて列幅を揃える等の整形をします。

CRUD表を使ってみる

作成したCRUD表（モドキですが）は縦にSQL(SQL_ID)、横にテーブル名が並んだ表で、それぞれの交差部分に「1」が立っていれば、そのSQL文で該当するテーブルを使っていることを意味します。

まず、下図のB1カラム（レポートフィルタ）で「R」を選択し、SELECT文を見てみましょう。

１つのSELECT文で参照するテーブル数

例えば、SQL_ID = 2p8c09m8rupuk のSQL文は「ABCD_BHA」「NIC」「STU3」という３つのテーブルをアクセスしています。一番右端の総計欄を見るとSQL文でアクセスしているテーブル数の合計がわかります。（表を横に見ます。）

R1

この合計数が多いSQLはジョインしているテーブルの数が多い可能性が高いため、注目する必要があるものです。

あるテーブルを使用しているSQL文とその数

逆にあるテーブルから関連するSQL文とその数を確認するには、表を縦に見ます。

R2

上図の例では、「EFGH_DAY_OUT」というテーブルは一番下の総計欄が「34」になっています。これは34種類の異なるSQL文で使用されていることを示しています。

この数が多いということは

  1. 多くのSQL文でアクセスされるホットなテーブルである。
  2. バインド変数を使わないために類似SQL文が多数存在している。

という２つの可能性を示しています。

いずれにせよ、パフォーマンス・チューニングで注目すべきポイントになりますので、AWRレポート等他の情報と突き合わせて問題の特定を行うようにしましょう。

また、このあるテーブルに関係するSQL文の一覧を得ることは、テーブル定義の変更で影響を与える対象を把握するのにも役立ちます。

SQLテキストと実行計画を確認する

一覧ではSQL文を表示させていません。SQL_IDが特定できたら以下のSQL文を実行してSQLテキストと実行計画を確認します。

set pages 1000
set lines 140
select plan_table_output from table(DBMS_XPLAN.DISPLAY_CURSOR('<SQL_ID>'));

 

応用編

インデックス – SQL

V$SQL_PLANを使えば、テーブルとSQLの関係だけでなく、インデックスとSQLの関係を分析することもできます。

例えば、あるインデックスの定義を変更しようとする場合、１つのSQLだけに注目してしまうと他のSQLに影響があることに気づかず新たな問題を引き起こしてしまうかもしれません。

そのような場合、インデックスとSQLの相関表が役に立ちます。

DBA_HIST_*を使う

V$SQL_PLANもX$SQLAREAもメモリ（SGA）上にキャッシュされている情報です。従って、共有プールの使用率が高い場合等にはどんどんキャッシュから追い出されてしまい欲しい情報が得られない場合があります。

もし、AWRが使える環境であれば

  • V$SQL_PLAN　→　DBA_HIST_SQL_PLAN
  • V$SQLAREA　→　DBA_HIST_SQLTEXT

に置き換えれば、AWR（SQL）レポート保存期間以内のデータが保証されますので、事後解析でも有用なデータが得られます。

念のためAWR版の情報取得SQLを以下に紹介します。

select
 decode(CRUD_TYPE_ID,2,'C'
                    ,3,'R'
                    ,6,'U'
                    ,7,'D') CRUD
,SQL_ID
,OBJECT_NAME
,OPTIONS
from
(
select distinct
 SQL_ID
,OBJECT_NAME
,decode(operation,'TABLE ACCESS',3
                 ,'UPDATE',6
                 ,'DELETE',7) CRUD_TYPE_ID
,OPTIONS
from
 DBA_HIST_SQL_PLAN --V$SQL_PLAN
where OPERATION in ('TABLE ACCESS','UPDATE','DELETE')
and   OBJECT_OWNER ='xxxx'
union all
select
 SQL_ID
,ltrim(
  replace(
   upper(
    substr(
     regexp_substr(DBMS_LOB.SUBSTR(SQL_TEXT, 1000, 1),'INTO[[:space:]]+[^[:space:]\(]+',1,1,'i')
        ,6)
         )
    ,'"')
      ) OBJECT_NAME
,COMMAND_TYPE CRUD_TYPE_ID
,null OPTIONS
from
 DBA_HIST_SQLTEXT --V$SQLAREA
where COMMAND_TYPE = 2
) I
,DBA_TABLES D
where I.OBJECT_NAME = D.TABLE_NAME
and   D.OWNER       = 'xxxx'
order by
 CRUD_TYPE_ID
,SQL_ID
,OBJECT_NAME
;

最後に注意事項

V$SQL_PLAN等は負荷の高い状況ではアクセスしないことをお勧めします。DBAが手動で負荷状況を見ながら注意深く実行して下さい。

従って、スクリプトを組んで定期的に自動実行するような運用は行わない方が望ましいと思います。