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

UPDATEとDELETE

UPDATE文とDELETE文の実行計画については以下の例のとおりです。

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |        |     1 |     8 |     1   (0)| 00:00:01 |
|   1 |  UPDATE            | EMP    |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| PK_EMP |     1 |     8 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |        |     1 |    26 |     1   (0)| 00:00:01 |
|   1 |  DELETE            | EMP    |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| PK_EMP |     1 |    26 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Operation=UPDATE or DELETE となる Name列が示すものが、対象のテーブル名です。

SELECTと同様にV$SQL_PLANから情報を取得するクエリーは以下のとおりです。

select distinct
 SQL_ID
,OBJECT_NAME
,decode(operation,'UPDATE',6
                 ,'DELETE',7) CRUD_TYPE_ID
from V$SQL_PLAN
where OPERATION in ('UPDATE','DELETE')
and   OBJECT_OWNER = 'xxxx'
order by
 OBJECT_NAME
,CRUD_TYPE_ID
,SQL_ID
;

SELECTも一緒に取得するように書きなおしたSQL文の実行例は以下のとおりです。

SQL> select distinct
   2   SQL_ID
   3  ,OBJECT_NAME
   4  ,decode(operation,'TABLE ACCESS',3
   5                   ,'UPDATE',6
   6                   ,'DELETE',7) CRUD_TYPE_ID
   7  ,OPTIONS
   8  from
   9   V$SQL_PLAN
  10  where OPERATION in ('TABLE ACCESS','UPDATE','DELETE')
  11  and   OBJECT_OWNER = 'SCOTT'
  12  order by
  13   OBJECT_NAME
  14  ,CRUD_TYPE_ID
  15  ,SQL_ID;

 SQL_ID        OBJECT_NAME  CRUD_TYPE_ID OPTIONS
 ------------- ------------ ------------ --------
 a2dk8bdn0ujx7 EMP                     3 FULL
 2r07krus3ums3 EMP                     6
 7hr1wxxb8hkqc EMP                     7

INSERTは難しい

INSERT文も同じような要領で情報を取れると思ったのですが、なかなかやっかいです。

以下はINSERT文の実行計画です。

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |     1 |    38 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | EMP  |       |       |            |          |
---------------------------------------------------------------------------------

Operation=”LOAD TABLE CONVENTIONAL” となるName列を持ってくればよさそうな気がします。
しかし、いろいろなINSERT文を見ていて次のような実行計画に出くわしました。

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                           |       |       |       | 47068 (100)|          |
|   1 |  LOAD TABLE CONVENTIONAL |                           |       |       |       |            |          |
|*  2 |   HASH JOIN              |                           | 22794 |    44M|    44M| 47068   (1)| 00:09:25 |
|*  3 |    TABLE ACCESS FULL     | Txxxxxxxxxxxxxxxxxxxxxxxx | 22794 |    44M|       |  1922   (1)| 00:00:24 |
|   4 |    TABLE ACCESS FULL     | Mxxxxxxxxxxx              |  4861K|   148M|       | 32769   (1)| 00:06:34 |
--------------------------------------------------------------------------------------------------------------

これは実際の実行計画のテーブル名をマスクしたものですが、INSERT対象のテーブル名はどこにも出てきていないことがおわかりでしょうか？

つまり、実行計画からINSERT先のテーブルを特定することはできないのです。

正規表現を使う

そこでV$SQLAREAを使って情報を取得することにしました。SQL_TEXT列にSQLテキストの先頭1,000バイトが入っているので、そこから文字列操作でテーブル名を取り出す作戦です。

SUBSTR関数等いろいろ試してみたのですが、最終的に10g以降で使えるようになった正規表現機能で抜き出すことにしました。
以下が情報取得用のクエリーです。

select
 SQL_ID
,ltrim(
  replace(
   upper(
    substr(
     regexp_substr(SQL_TEXT,'INTO[[:space:]]+[^[:space:]\(]+',1,1,'i')
        ,6)
         )
    ,'"')
      ) OBJECT_NAME
,COMMAND_TYPE CRUD_TYPE_ID
,null OPTIONS
from V$SQLAREA
where COMMAND_TYPE = 2
order by
 SQL_ID
,OBJECT_NAME
;

解説：

7行目がもっとも肝になる部分です。「INTO [テーブル名]」となる文字列をregexp_substr関数で抜き出します。
substr関数で抜き出した文字列の６バイト目以降をテーブル名として取り出します。（6行目）
upper関数でテーブル名を大文字に統一します。（5行目）
たまにテーブル名がダブルクォートで囲まれている場合があるのでreplace関数で取り除きます。これが最終的なOBJECT_NAMEとなります（4行目）

以下は実行例です。
V$SQLAREAにはテーブルの所有者を特定できる情報がないので、上記クエリーの結果とDBA_TABLESをジョインしてOWNER列で絞り込む条件を加えています。

SQL> select distinct
  2   SQL_ID
  3  ,OBJECT_NAME
  4  ,CRUD_TYPE_ID
  5  ,OPTIONS
  6  from
  7  (
  8  select
  9   SQL_ID
 10  ,ltrim(
 11    replace(
 12     upper(
 13      substr(
 14       regexp_substr(SQL_TEXT,'INTO[[:space:]]+[^[:space:]\(]+',1,1,'i')
 15          ,6)
 16           )
 17      ,'"')
 18        ) OBJECT_NAME
 19  ,COMMAND_TYPE CRUD_TYPE_ID
 20  ,null OPTIONS
 21  from
 22   V$SQLAREA
 23  where COMMAND_TYPE = 2
 24  ) I
 25  ,DBA_TABLES D
 26  where I.OBJECT_NAME = D.TABLE_NAME
 27  and   D.OWNER       = 'SCOTT'
 28  order by
 29   SQL_ID
 30  ,OBJECT_NAME
 31  ;

SQL_ID        OBJECT_NAME                    CRUD_TYPE_ID OPTIONS
------------- ------------------------------ ------------ --------
7071f28x901k7 EMP                                       2
gjvsdk24ufadk EMP                                       2

UNIONでまとめてみる

最後に、INSERT/SELECT/UPDATE/DELETE の結果をまとめてみましょう。24行目の「union all」に注目して下さい。

補足すると、CRUD_TYPE_IDをdecode関数で「C」「R」「U」「D」に変換しています。（4行目）
なぜ、このような面倒くさいことをやっているのかというと、最初から「C」「R」「U」「D」という文字列にすると、この順番にソートしてくれないのでCRUD_TYPE_IDという数値を使っているのです。（細かいですが私のこだわりです。）

それから、SQL*Plusコマンドを使って、CRUD別のSQL数も集計してみました。（1-2行目）

SQL> break on crud skip page
SQL> compute count of sql_id on crud
SQL> select
  2   decode(CRUD_TYPE_ID,2,'C'
  3                      ,3,'R'
  4                      ,6,'U'
  5                      ,7,'D') CRUD
  6  ,OBJECT_NAME
  7  ,SQL_ID
  8  ,OPTIONS
  9  from
 10  (
 11  select distinct
 12   SQL_ID
 13  ,OBJECT_NAME
 14  ,decode(operation,'TABLE ACCESS',3
 15                   ,'UPDATE',6
 16                   ,'DELETE',7) CRUD_TYPE_ID
 17  ,OPTIONS
 18  from
 19   V$SQL_PLAN
 20  where OPERATION in ('TABLE ACCESS','UPDATE','DELETE')
 21  and   OBJECT_OWNER = 'SCOTT'
 22  union all
 23  select
 24   SQL_ID
 25  ,ltrim(
 26    replace(
 27     upper(
 28      substr(
 29       regexp_substr(SQL_TEXT,'INTO[[:space:]]+[^[:space:]\(]+',1,1,'i')
 30          ,6)
 31           )
 32      ,'"')
 33        ) OBJECT_NAME
 34  ,COMMAND_TYPE CRUD_TYPE_ID
 35  ,null OPTIONS
 36  from
 37   V$SQLAREA
 38  where COMMAND_TYPE = 2
 39  ) I
 40  ,DBA_TABLES D
 41  where I.OBJECT_NAME = D.TABLE_NAME
 42  and   D.OWNER       = 'SCOTT'
 43  order by
 44   CRUD_TYPE_ID
 45  ,OBJECT_NAME
 46  ,SQL_ID
 47  ;

C OBJECT_NAME          SQL_ID        OPTIONS
- -------------------- ------------- ---------------
C EMP                  7071f28x901k7
  EMP                  gjvsdk24ufadk
*                      -------------
c                                  2

C OBJECT_NAME          SQL_ID        OPTIONS
- -------------------- ------------- ---------------
R EMP                  7071f28x901k7 BY INDEX ROWID
  EMP                  a2dk8bdn0ujx7 FULL
  EMP                  b84685tx0a4u0 BY INDEX ROWID
  EMP                  f93ucvwf6d1vh FULL
*                      -------------
c                                  4

C OBJECT_NAME          SQL_ID        OPTIONS
- -------------------- ------------- ---------------
U EMP                  2r07krus3ums3
*                      -------------
c                                  1

C OBJECT_NAME          SQL_ID        OPTIONS
- -------------------- ------------- ---------------
D EMP                  7hr1wxxb8hkqc
*                      -------------
c                                  1

どのテーブル(OBJECT_NAME)がどのSQL(SQL_ID)によってINSERT/SELECT/UPDATE/DELETEの対象になっているかが、この一覧からわかります。
（OPTIONSは単なるおまけですのでなくても構いません）

この例ではSCOTT.EMP表しか出てきていませんが、実際は非常に沢山の結果が返ってくるはずです。

次回は、MS Excelのピボットテーブルを使って CRUD表らしく加工してみます。

（続く）

サイクル＆オラクル

サイクリングとデータベースを楽しむサイト

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

UPDATEとDELETE

INSERTは難しい

正規表現を使う

UNIONでまとめてみる

2014年7月
月	火	水	木	金	土	日
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31