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

（続く）

パフォーマンス問題の犯人探し？

情報システムに問題（特にパフォーマンス問題）が発生した場合、運用を担っているインフラ・チームが真っ先にやり玉に挙げられることが多いようです。

そんな時のアプリ・チームの主張は

• 自分たちはアプリケーションに一切手を加えていないので悪くない。悪いはずがない。
• インフラ・チームが何かやらかしたのではないか？（過去に痛い経験があればなおさら）
• 隠しパラメータみたいな秘密のスイッチがあるらしいから、それをうまく使って切り抜けてくれ！
• インフラには高い金をつぎ込んでいるのだから何とかしろ！

というようなものが多いのではないでしょうか？

しかも、大抵の場合アプリ・チームの方が人数が多いので、多勢に無勢いつの間にか問題はインフラ・チームのせいになってしまっている、というようなことはよくあります。

最近は、新卒でインフラ・チームに配属されるというケースも多くなっていますが、ベテラン開発者から運用者に転向しているような技術者は総じてスキルのレベルが高いので（好意的に解釈して）重要な問題の解決を期待されているということの裏返しなのかもしれません。

ただし、インフラ・チームの名誉のために補足しますが、私はパフォーマンス問題の8〜9割近くはSQLつまりアプリケーションの問題のような印象を持っています。（なぜアプリケーションに手を加えていないのに問題が発生するのかは改めて触れたいと思います。）

DBAは問題解決の要

DBAは多くの場合インフラ・チームの一員として数えられていますが、データベースはミドルウェアと言いながらOSから見れば一種のアプリケーションであるので、DBAがアプリ・チームとの仲介役としてうまく機能できれば問題解決に対して非常に強い組織になります。

専任のDBAを置いている組織は少ないかもしれません。サーバ管理者が兼任していたりしてDBAの職務が曖昧になっている場合、インフラ・チームとアプリ・チームの溝が深くなってしまうことはよくあります。

DBA自らがアプリケーションを理解する

問題が発生してからアクションを起こすのでは遅すぎます。DBAは常日頃からアプリケーションに対して関心を持つ必要があります。理想的にはアプリ・チームの責任としてDBAが理解できるドキュメントを整備するのがベストですが、現実的にはなかなか難しいことが多いかと思います。

従って、DBA側から能動的に情報を収集することが必要です。DBAから見た最も身近なアプリケーションはSQL文です。Oracleでは実行されているSQL文に関するさまざまな情報をV$SQL*ビュー（Oracle11gR2であれば30種）から得ることができます。

V$SQL_PLAN

それではV$SQL_PLANを使用してSQL文とテーブルの相関表を作ってみましょう。あるSQL文がどのテーブルを使用しているか、あるいはあるテーブルはどのようなSQL文によって使用されているかを一覧にしたものです。これはDB設計におけるCRUD表に近いものですが、実際に動いているSQL文の情報を使って作るというのがポイントです。

 実行計画のおさらい（SELECT）

以下の簡単な実行計画の例で考えてみます。

-----------------------------------------------------------------------------------
| Id  | Operation                     |  Name        | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     3 |   189 |    10  (10)|
|   1 |  NESTED LOOPS                 |              |     3 |   189 |    10  (10)|
|   2 |   NESTED LOOPS                |              |     3 |   141 |     7  (15)|
|*  3 |    TABLE ACCESS FULL          | EMPLOYEES    |     3 |    60 |     4  (25)|
|   4 |    TABLE ACCESS BY INDEX ROWID| JOBS         |    19 |   513 |     2  (50)|
|*  5 |     INDEX UNIQUE SCAN         | JOB_ID_PK    |     1 |       |            |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS  |    27 |   432 |     2  (50)|
|*  7 |    INDEX UNIQUE SCAN          | DEPT_ID_PK   |     1 |       |            |
-----------------------------------------------------------------------------------

まず中程の「Name」列は、その実行計画でアクセス（SELECT）されるテーブルあるいはインデックスとなります。テーブルなのかインデックスなのか名前から判断することもできますが確実なのは左隣りの「Operation」列が

• TABLE ACCESS  で始まっているものが「テーブル」
• INDEX で始まっているものが「インデックス」

です。

V$SQL_PLANからSELECT対象のテーブル一覧を取得する

V$SQL_PLANから情報を取得するクエリーを考えてみます。

select distinct
 SQL_ID
,OBJECT_NAME
,3 CRUD_TYPE_ID
,OPTIONS
from
 V$SQL_PLAN
where OPERATION like 'TABLE%'
and   OBJECT_OWNER = 'xxxx'
order by
 OBJECT_NAME
,CRUD_TYPE_ID
,SQL_ID;

8行目のWHERE条件でテーブルアクセスのオペレーションのみを取り出しています。
9行目ではテーブルの所有者で絞り込みをしています。

4行目で何をしているのかというと、V$SQL_PLANにはない V$SQL.COMMAND_TYPE に相当する情報をここで付加しています。
SELECTであれば「3」になります。この理由は追って説明します。

以下は実行例です。

SQL> select distinct
  2   SQL_ID
  3  ,OBJECT_NAME
  4  ,3 CRUD_TYPE_ID
  5  ,OPTIONS
  6  from
  7   V$SQL_PLAN
  8  where OPERATION like 'TABLE%'
  9  and   OBJECT_OWNER = 'SCOTT'
 10  order by
 11   OBJECT_NAME
 12  ,CRUD_TYPE_ID
 13  ,SQL_ID;

SQL_ID        OBJECT_NAME  CRUD_TYPE_ID OPTIONS
------------- ------------ ------------ --------
0cc0hqvr7kpgx BONUS                   3 FULL
30hdyzpfrzgbf BONUS                   3 FULL
3154rqzb8xudy DEPT                    3 FULL
678kpdu03jc8x DEPT                    3 FULL
1srhq04p4x0zz EMP                     3 FULL
a2dk8bdn0ujx7 EMP                     3 FULL
25tuw6725a9h7 SALGRADE                3 FULL
30p00zk91247c SALGRADE                3 FULL

8行が選択されました。

（続く）