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表らしく加工してみます。
（続く）
