データベース」カテゴリーアーカイブ

RDS for OracleSE でいろいろやってみた

JPOUG Advent Calendar 2020 14日目のエントリーです。13日目はmultilayerさんの記事「Oracle GDSの時代がやって来た!」でした。

最近の当ブログは、ホントに年1回の投稿になってきました。。。

このところAmazon RDS for Oracleに携わる機会が増えてきたので、ちょとした工夫、気づきをいくつか紹介していきたいと思います。

Statspackレポート出力手順を簡単にしてみた

オンプレミスからAWS RDS for Oracleへのクラウドリフトを考えた場合、ライセンスコストに鑑みてStandard Editionへの移行というケースが意外に多いのではないかと思います。

必然的にAWRの代わりにStatspackを使うことになりますが、AWSのドキュメントでStatspackに関して以下のガイドがあります。

Oracle Statspack

Statspackレポートを出力する頻度が多い割りにガイドにある手順が面倒なので、簡略化のため以下の3つのステップ毎にスクリプトを作成しました。

1. Snap Shot Idを確認する

最初は、レポート出力に必要なSNAP IDの確認です。

下のように、「showsnap.sql」を開始日付、終了日付の2つの引数を指定して実行します。

SQL> @showsnap <開始日付> <終了日付>  ←yyyymmddで指定。同一日は同じ値を指定。

例
SQL> @showsnap 20201209 20201209
旧   2: where trunc(SNAP_TIME) between to_date(&BEGINDT,'yyyymmdd') and to_date(&ENDDT,'yyyymmdd') order by 1,2,3
新   2: where trunc(SNAP_TIME) between to_date(20201209,'yyyymmdd') and to_date(20201209,'yyyymmdd') order by 1,2,3

STARTUP             SDATE      STIME       SNAP_ID
------------------- ---------- -------- ----------
2020/11/13 19:02:28 2020/12/09 00:00:32       7806
                               00:09:26       7807
                               01:00:32       7808
                               02:00:32       7809
                               03:00:32       7810
                               04:00:32       7811
                               05:00:32       7812
                               06:00:32       7813
                               07:00:32       7814
                               08:00:32       7815
                               09:00:32       7816
                               10:00:32       7817
                               10:28:19       7818
                               11:00:32       7819
                               12:00:32       7822
                               13:00:32       7823
                               14:00:32       7824
                               15:00:32       7825
                               16:00:32       7826
                               17:00:33       7827
                               18:00:32       7828
                               19:00:32       7829
                               20:00:32       7830
                               21:00:32       7831
                               22:00:32       7832
                               23:00:32       7833


26行が選択されました。

「showsnap.sql」の実際は以下のScrapboxのリンク先を参照してください。

(今後、本ブログで紹介するスクリプトやコマンドは原則的にScrapboxにて管理&公開することにします。)

https://scrapbox.io/scripts/showsnap.sql

2. Statspackレポートを作成する

得られたSNAP IDを指定して、以下のように「mksprep.sql」を実行します。

このスクリプトにより、指定されたSNAP IDの範囲でStatspackレポートが作成され、RDSADMIN.TRACEFILE_TABLE表に格納されます。

SQL> @mksprep <前snapid> <後snapid>

例)
SQL> @mksprep 7830 7832

PL/SQLプロシージャが正常に完了しました。


PL/SQLプロシージャが正常に完了しました。


FILENAME                                 TYPE           FILESIZE MTIME
---------------------------------------- ------------ ---------- -------------------------
XXXXXXX_spreport_7796_7803.lst           file                164 2020-12-09 11:49
XXXXXXX_spreport_7830_7832.lst           file                188 2020-12-10 15:10

上例のように、(過去に作成されたものを含む)複数のレポートが表示されることがありますが、MTIME列を見て次のステップでファイル出力するレポートを特定します。(mksprep.sql

3. Statspackレポートをファイルに出力する

次に、SQL*Plusのスプール機能を使用して、レポートをテキストファイルに出力します。(getsprep.sql

SQL> @getsprep XXXXXXX_spreport_7830_7832.lst

PL/SQLプロシージャが正常に完了しました。


STATSPACK report for

Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          2169958367 XXXXXXX             1 13-Nov-20 19:02 19.0.0.0.0  NO

Host Name             Platform                CPUs Cores Sockets   Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
     ip-xxx-xx-xx-xxx  Linux x86 64-bit          16     8       1         62.1

..............................................................................

standby_file_management       AUTO
undo_tablespace               UNDO_T1
use_large_pages               ONLY
          -------------------------------------------------------------

End of Report ( /rdsdbdata/log/trace/XXXXXXX_spreport_7830_7832.lst )


2990行が選択されました。

Statspackデータからの実行計画の確認を簡単にしてみた

1. PLAN HASH VALUEを確認する

StatspackレポートにはSQL_IDではなく、「OLD HASH VALUE」が出力されています。

この値をキーとして実行計画を特定する「PLAN HASH VALUE」を確認します。(getphv.sql

SQL> @getphv 3464557777

OLD_HASH_VALUE    SNAP_ID PLAN_HASH_VALUE TEXT_SUBSET
-------------- ---------- --------------- -------------------------------
    3464557777       7796      1601398191 select  /*+  USE_HASH(t2)  */ t

1つのOLD HASH VALUEに複数のPLAN HASH VALUEが存在する可能性がありますが、その場合はSNAP_IDを見てどのPLAN HASH VALUEが対象なのかを判断します。

2. 実行計画を確認する

得られたPLAN HASH VALUEをキーにして、実行計画を確認します。(showspplan.sql)

SQL> @showspplan 1601398191
旧  31: where PLAN_HASH_VALUE = &PHV
新  31: where PLAN_HASH_VALUE = 1601398191

PLAN_HASH_VALUE    SNAP_ID    ID Operation                 Name                      Rows        Query Block Name / Object Alias          Bytes      Cost         Srch Cols
--------------- ---------- ----- ------------------------- ------------------------- ----------- ---------------------------------------- ---------- ----------- ----------
     1601398191       3395     0 SELECT STATEMENT                                                                                                      190                0
     1601398191       3395     1  SORT ORDER BY                                           7      SEL$1                                     5789        190                0
     1601398191       3395     2   HASH JOIN OUTER                                        7                                                5789        189                0
     1601398191       3395     3    VIEW                                                  3      SET$1         / T1@SEL$1                  2439         60                0
     1601398191       3395     4     UNION-ALL                                                   SET$1                                                                    0
     1601398191       3395     5      HASH JOIN                                           1      SEL$2                                      879         27                0
     1601398191       3395     6       NESTED LOOPS OUTER                                 6                                                4530         24                0
     1601398191       3395     7        NESTED LOOPS                                      6                                                2532         23                0
     1601398191       3395     8         NESTED LOOPS                                     6                                                1218         11                0
     1601398191       3395     9          NESTED LOOPS                                    6                                                 618         11                0
...........................................................................................................................................................................

オンプロミス環境で $ORACLE_HOME/rdbms/admin/sprepsql.sql を使用して出力したSQL詳細レポートでも実行計画は確認できます(早い話スクリプトをSQL*Plusクライアントに持って行けば使えます。)が、簡単に実行計画を得るための方法として参考にしてください。

RDS for OracleのDBAコマンド

セッションの切断(DISCONNECT SESSION)や終了(KILL SESSION)等DBAタスクに関して、RDS for Oracleではシステムテーブル等へのアクセスを制限するため固有のコマンド群が用意されています。(備忘録としてリンクを貼っておきます。)

Oracle DB インスタンスの一般的な DBA タスク

単性能試験実施時にキャッシュのクリアが必要な場合のためにスクリプトを用意してみました。

https://scrapbox.io/scripts/flusys.sql(RDS)

参考までに、通常のALTER SYSTEMコマンドによるスクリプトは

https://scrapbox.io/scripts/flusys.sql

となります。

V$OSSTATからCPU使用率を取得してみた

Amazon Cloudwatchでは、高分解能カスタムメトリクスを使用すれば分解能 1 秒までのメトリクスを取得できます。Amazon CloudWatch よくある質問

同様にRDSのみでCPU使用率を分解能1秒で取得できないかV$OSSTATを使って試してみました。

1. 取得準備

CloudWatchのようにリアルタイムで可視化できない点がイケてませんが、PERFSTATスキーマにデータ蓄積用のテーブル(USER$OSSTAT)を作成します。

構成はV$OSSTATSとほぼ同じですが、先頭に日付時間情報カラム(SDATE)を設けています。

SQL> desc USER$OSSTAT
 Name         Null?    Type
 ------------ -------- ----------------------------
 SDATE                 DATE
 IDLE_TIME             NUMBER
 BUSY_TIME             NUMBER
 USER_TIME             NUMBER
 SYS_TIME              NUMBER
 IOWAIT_TIME           NUMBER
 NICE_TIME             NUMBER
 LOAD                  NUMBER

2. データ取得用スクリプト

まず、USER$OSSTAT表にデータをINSERTするSQLスクリプトです。(osstat.sql

これを1秒間隔で実行させるシェルスクリプトです。(osstat.sh)時間間隔はSLEEP_SEC変数に設定した秒数で調整できます。(引数で指定するのもありですが、煩雑なので固定値にしました。)

さらに、実行時間(分)を指定してosstat.shを起動するシェルスクリプトです。(getcpu)引数を省略すると24時間動きます。

3. データ表示用スクリプト

指定時間後又はCtrl+Cでgetcpuを終了させたのち、蓄積データを一覧表示させるSQLスクリプトです。(cpu.sql

IDLE、USER、SYS、NICE、WIOそれぞれで現在行と1行前の時間差を求め、(IDLE_TIME+BUSY_TIME)を分母としたCPU使用率を算出します。従って1行目は無視します。

SQL> @cpu

SDATE                IDLE%  USER%   SYS%  NICE%   WIO%   LOAD
------------------- ------ ------ ------ ------ ------ ------
2020/08/06 11:09:59                                       0.3
2020/08/06 11:09:59   99.6    0.0    0.1    0.2    0.0    0.3
2020/08/06 11:10:00   93.3    2.2    4.3    0.2    0.0    0.3
2020/08/06 11:10:01   85.9    2.8   11.2    0.1    0.1    0.4
2020/08/06 11:10:02   87.1    2.4   10.3    0.2    0.0    0.4
2020/08/06 11:10:03   90.6    3.8    5.6    0.1    0.0    0.4
2020/08/06 11:10:04   95.4    0.1    0.1    4.4    0.0    0.4
2020/08/06 11:10:05   93.3    0.1    0.2    6.4    0.0    0.4
2020/08/06 11:10:06   93.3    0.1    0.3    6.3    0.0    0.4
2020/08/06 11:10:07   93.3    0.1    0.2    6.4    0.2    0.4
2020/08/06 11:10:08   93.3    0.1    0.2    6.4    0.0    0.4
2020/08/06 11:10:09   93.0    0.4    0.2    6.4    0.0    0.4
2020/08/06 11:10:10   93.3    0.2    0.2    6.3    0.0    0.4
2020/08/06 11:10:11   93.0    0.2    0.4    6.4    0.0    0.4
2020/08/06 11:10:12   93.2    0.2    0.3    6.3    0.0    0.4
.............................................................

以下は、CPU 1コアを使い切るHeavy SQLをSQL*Plusから1→2→3→2→1セッションと同時実行させた場合のCPU使用率の推移です。

このインスタンスは16コアなので、

  • 1セッション:1/16=6.25%
  • 2セッション:2/16=12.5%
  • 3セッション:3/16=18.75%

と計算どおりの結果となりました。

なお、USER%が変化していくと思ったのですが、NICE%が変化していきました。

また、5分間隔でSYS%が約12%、20秒間隔でNICE(USER)%で大小0.5〜6%程度のスパイクが見られました。これらの正体は追いかけていませんが、定期的な監視等によるものと思われます。これらのスパイクが気になったので、NICE%の10区間移動平均を追加したのが黒の実線です。

V$OSSTATから取得したCPU使用率推移をグラフ化

RDS for Oracle なかなか面白いです!!

負荷テストデータ作成に関するTips — 指定率行の抽出・更新 —

JPOUG Advent Calendar 2019の13日目のエントリーです。

負荷テスト用データは件数が重要

最近、負荷テストの支援をすることが多いのですが、負荷テスト用のデータ準備にはいつも悩まされます。
経験から述べると、アプリケーション開発者にデータの準備をお願いすると大抵はうまく行かないことが多いです。
開発者はプログラムが仕様通り動くかどうかを確認する必要最小限のデータを準備することに関心はありますが、実際の業務を想定したのデータを準備することはあまり考えていません。

また、本番環境の実データを使うにはセキュリティ上いろいろな制約があるため、機微な情報をマスク化する等の加工に時間がかかりただでさえ厳しいテスト工程を圧迫します。

結論から言うと、負荷テストでは値のリアルさよりも件数の妥当性の方が重要だと思います。
何年か前に参画したプロジェクトでは、アプリケーションの仕様を十分ヒアリングした上で、想定件数のテストデータをほぼゼロから作りそれなりの成果を得ました。しかも、アプリケーション開発工程の影響に左右されず先行的にデータの準備を行ったため、試験工程も余裕を持って実施することができました。

その経験から今回は「カーディナリティを考慮したデータを作成する」というテーマで、当時得たノウハウを共有したいと思います。

カーディナリティとは

「カーディナリティ」という用語をマニュアルでひもとくと「表の行数に対する個別値の数の比率。100万行を持つ表内で個別値が2つしかない列の場合、カーディナリティは低くなります。」とあります。

Oracle® Database データベース概要 19c  用語集

世の中的には「カーディナリティ」を異なる意味で使う場合もあるようですが、今回はOracleのマニュアルにある定義を使います。

参考:「カーディナリティて何ですの

さきほど、負荷テストデータは値のリアルさは重要でないと書きましたが、検索カラムのカーディナリティは実データから値の割合を調査しそれを基にテストデータを作成するのがよいと思います。

例えば、HR.EMPLYEES表から職種別構成比を表示させると以下のようになります。

SQL> set pages 50
SQL> col COMP_RATIO for 990.9
SQL> select
  2     JOB_TITLE
  3    ,COMP_RATIO
  4  from
  5    (
  6       select
  7         JOB_ID
  8        ,count(*) / TOTAL * 100 COMP_RATIO
  9        ,TOTAL
 10      from
 11        (
 12           select
 13             JOB_ID
 14            ,count(*) OVER() TOTAL
 15          from
 16            EMPLOYEES
 17        )
 18      group by
 19         JOB_ID
 20        ,TOTAL
 21    ) R
 22    inner join
 23      JOBS J
 24    on  R.JOB_ID = J.JOB_ID
 25  order by
 26     2 desc
 27    ,1
 28  ;

JOB_TITLE                           COMP_RATIO
----------------------------------- ----------
Sales Representative                      28.0
Shipping Clerk                            18.7
Stock Clerk                               18.7
Accountant                                 4.7
Programmer                                 4.7
Purchasing Clerk                           4.7
Sales Manager                              4.7
Stock Manager                              4.7
Administration Vice President              1.9
Accounting Manager                         0.9
Administration Assistant                   0.9
Finance Manager                            0.9
Human Resources Representative             0.9
Marketing Manager                          0.9
Marketing Representative                   0.9
President                                  0.9
Public Accountant                          0.9
Public Relations Representative            0.9
Purchasing Manager                         0.9

19行が選択されました。

「販売代理人は全社員の28.0%」、「出荷担当者は18.7%」というような実際の構成比を把握しておけば、EMPLYEES表にどんな大きな件数のテストデータを作成したとしても現実的な比率のJOB_IDを作成することができます。

負荷テスト用データ作成要領の概要

負荷テストデータを作成するには、ExcelでCSVを作成してSQL*Loaderでロードする等いろいろな方法が考えられますが、今回はテーブルを作成した後に、PL/SQLによってPK項目を生成しながら、PK項目以外のカラムに初期値(0やnull)をセットして、必要な行数のデータを作成するものとします。

その後、カラムごとに指定した割合で値を更新していくこととします。

検証用テーブルと初期データの作成

それでは、検証用のテーブルを作成し初期データを作成してみます。

PK項目のID列と任意の値が入るVAL1列、VAL2列を定義し、初期データとして100万件のデータをバルク・インサートで作成します。(話を簡単にするためすべてのカラムはNUMBER型とします。)

件数はループ変数の定義により任意に設定できるのであまり悩むことはないと思います。

SQL> create table TEST (
  2   ID   number
  3  ,VAL1 number
  4  ,VAL2 number
  5  );

表が作成されました。

SQL> declare
  2    type TBL_INS is table of TEST%rowtype index by pls_integer;
  3    W_INS TBL_INS;
  4  begin
  5    for i in 1..1000000 loop
  6      W_INS(i).ID   := i;
  7      W_INS(i).VAL1 := 0;
  8      W_INS(i).VAL2 := 0;
  9    end loop;
 10    forall i in 1..1000000 insert into TEST values W_INS(i);
 11      commit;
 12  end;
 13  /

PL/SQLプロシージャが正常に完了しました。

SQL> select count(*) from TEST;

  COUNT(*)
----------
   1000000

ROWID順に100行取り出してみる

念のため、ROWID順に先頭の100件を取り出してみます。ID=1から始まらないのがおもしろいところですが、ループ処理で配列へデータを順に作成しているので、同じブロックの中ではROWIDとIDの並び順は同じです。

VAL1とVAL2列は0で初期化されています。

SQL> select * from (
  2  select ROWID,ID,VAL1,VAL2 from TEST order by ROWID)
  3  where rownum <= 100;

ROWID                    ID       VAL1       VAL2
------------------ -------- ---------- ----------
AAAXhJAAGAAAADjAAA      567          0          0
AAAXhJAAGAAAADjAAB      568          0          0
AAAXhJAAGAAAADjAAC      569          0          0
AAAXhJAAGAAAADjAAD      570          0          0
AAAXhJAAGAAAADjAAE      571          0          0
AAAXhJAAGAAAADjAAF      572          0          0
AAAXhJAAGAAAADjAAG      573          0          0
AAAXhJAAGAAAADjAAH      574          0          0
AAAXhJAAGAAAADjAAI      575          0          0
AAAXhJAAGAAAADjAAJ      576          0          0
.................................................
AAAXhJAAGAAAADjABa      657          0          0
AAAXhJAAGAAAADjABb      658          0          0
AAAXhJAAGAAAADjABc      659          0          0
AAAXhJAAGAAAADjABd      660          0          0
AAAXhJAAGAAAADjABe      661          0          0
AAAXhJAAGAAAADjABf      662          0          0
AAAXhJAAGAAAADjABg      663          0          0
AAAXhJAAGAAAADjABh      664          0          0
AAAXhJAAGAAAADjABi      665          0          0
AAAXhJAAGAAAADjABj      666          0          0

100行が選択されました。

このTEST表のVAL1、VAL2に対し、全体(100万件)のうち指定した割合で値を更新していきます。

SAMPLE句で指定した%の行を取り出す

割合を指定して行を取り出すにはSAMPLE句の使用が考えられます。

以下は、SAMPLE句の説明の抜粋です。

sample_percent サンプルの選択: 例 参照

「sample_percentには、全体の行またはブロック数のうち、サンプルに入れる割合(%)を指定します。
0.000001以上100未満の範囲の値を指定します。この割合は、各行(ブロック・サンプリングの場合は行の各クラスタ)が、サンプルの一部として選択される可能性を示します。
sample_percentに指定した割合の行がtableから正確に取り出されるわけではありません。」

とりあえず0.1%(1000行になるはず)の行を取り出してみます。

SQL> select ROWID,ID from TEST sample(0.1) order by ROWID;

ROWID                      ID
------------------ ----------
AAAXhJAAGAAAADjAAa        593
AAAXhJAAGAAAADmADd       2465
AAAXhJAAGAAAADoAEb       4204
AAAXhJAAGAAAADpABw       4592
AAAXhJAAGAAAADpAGs       4908
AAAXhJAAGAAAADpAIm       5030
AAAXhJAAGAAAADqAC7       5226
AAAXhJAAGAAAADrABB       5663
AAAXhJAAGAAAADrAEv       5901
AAAXhJAAGAAAADsADX       6372
.............................
AAAXhJAAGAAAAirAGU     988784
AAAXhJAAGAAAAiuABe     980689
AAAXhJAAGAAAAiyACM     981254
AAAXhJAAGAAAAi0AEa     998004
AAAXhJAAGAAAAi0AFQ     998058
AAAXhJAAGAAAAi6AAS     982170
AAAXhJAAGAAAAi8AEQ     999032
AAAXhJAAGAAAAi+ABq     982777
AAAXhJAAGAAAAi+AGR     983072
AAAXhJAAGAAAAi/AEd     991260

982行が選択されました。

SAMPLE句は正確な行数を取り出さない

SAMPLE句では内部的に乱数を使ってサンプルを取得しているようなので、以下のように取得の度に行数が異なります。指定する%の精度を高くしても(小数点以下の桁数を大きくしても)この傾向は変わりません。しかも、けっこう誤差が大きいです。

SQL> select count(*) from (
  2  select ROWID,ID from TEST sample(0.100000) order by ROWID
  3  );

  COUNT(*)
----------
       994

経過: 00:00:00.01
SQL> /

  COUNT(*)
----------
       996

経過: 00:00:00.01
SQL> /

  COUNT(*)
----------
       898

経過: 00:00:00.01
SQL> /

  COUNT(*)
----------
      1006

SAMPLE句はSEEDを指定すれば同じ結果を返す(ただし行数は不正確)

ただし、乱数を発生させている(と思われる)ので、以下のようにシードを指定すると同じ結果になります。ただし、行数は所望の1000行とはなりません。

SQL> select count(*) from (
  2  select ROWID,ID from TEST sample(0.100000) seed(1) order by ROWID
  3  );

  COUNT(*)
----------
       980

経過: 00:00:00.01
SQL> /

  COUNT(*)
----------
       980

経過: 00:00:00.00
SQL> select count(*) from (
  2  select ROWID,ID from TEST sample(0.100000) seed(10) order by ROWID
  3  );

  COUNT(*)
----------
      1014

経過: 00:00:00.00
SQL> /

  COUNT(*)
----------
      1014

経過: 00:00:00.00
SQL> select count(*) from (
  2  select ROWID,ID from TEST sample(0.100000) seed(100) order by ROWID
  3  );

  COUNT(*)
----------
      1005

経過: 00:00:00.01
SQL> /

  COUNT(*)
----------
      1005

SAMPLE句で取り出した行(カラム)をUpdateしてみる

正確に1000行ではないですが、取り出したIDを使ってVAL1列を0→1に更新してみます。

ところがどっこい、ORA-30560エラーが発生してしまいます。

SQL> update TEST set VAL1 = 1 where ID in (select ID from TEST sample(0.100000));
update TEST set VAL1 = 1 where ID in (select ID from TEST sample(0.100000))
                                                          *
行1でエラーが発生しました。:
ORA-30560: SAMPLE句は使用できません

SQL> update TEST set VAL1 = 1 where ID in (select ID from TEST sample(0.100000) seed(1));
update TEST set VAL1 = 1 where ID in (select ID from TEST sample(0.100000) seed(1))
                                                          *
行1でエラーが発生しました。:
ORA-30560: SAMPLE句は使用できません

乱数でランダムに行(ROWID)を指定しているので、更新行が特定できず、つまり何度やっても同じ結果にならないので、更新できないのでしょうか?

シードを指定してもエラーになりましたので、SAMPLE句が使えないのはOracleの仕様なんでしょう。

SAMPLE句の制限事項

実は、マニュアルをよく読むと「DMLの副問い合わせの中ではSAMPLE句を指定できない」という記述があります。

  • SAMPLE句は、DML文の副問合せの中では指定できません
  • SAMPLE句を問合せで指定できるのは、問合せの対象が実表、マテリアライズド・ビューのコンテナ表、またはキー保存であるビューである場合です。

正攻法で考えてみる

SAMPLE句のような変化球ではなく、正攻法で考えてみます。(少し、前振りが長すぎました。)

結論から書きますが、全体行のうち更新したい行数の割合を「更新率」とすると(例えば100万行のうち1000行更新したければ「更新率=0.1%」)、以下のUPDATE文1により更新が可能です。

update 更新TBL set 更新列 = 更新値 where PK列 in (
 select PK列 from (
 select PK列,mod(PK列, (1 / 更新率)) from 更新TBL)
 where rownum <= 全体件数 * 更新率
 );

つまり、このUPDATE文を実行するためには、予め全体件数を(SELECT COUNT(*) FROM ~)で調べておく必要があります。

それでは、TEST表のVAL1列を更新率0.1%で0→1に更新してみましょう。

SQL> update TEST set VAL1 = 1 where ID in (
  2  select ID from (
  3  select ID,mod(ID, (100 / 0.1)) from TEST)  -- 更新率(0.1%)の逆数
  4  where rownum <= 1000000 * (0.1 / 100)      -- 全体件数 * 更新率 = 更新件数
  5  );

1000行が更新されました。

SQL> commit;

コミットが完了しました。

SQL> select VAL1,count(*) from TEST group by VAL1 order by VAL1;

      VAL1   COUNT(*)
---------- ----------
         0     999000
         1       1000

別の値の更新も自由自在

次に同じVAL1列を更新率2%で0→2に更新してみましょう。

注意したいのは先ほど1に更新した行は更新してはいけないことです。

SQL> update TEST set VAL1 = 2 where ID in (
  2  select ID from (
  3  select ID,mod(ID, (100 / 2)) from TEST where VAL1 != 1 and VAL1 = 0)  -- 更新率(2%)の逆数
  4  where rownum <= 1000000 * (2 / 100)                      -- 全体件数 * 更新率 = 更新件数
  5  );

20000行が更新されました。
SQL> select VAL1,count(*) from TEST group by VAL1 order by VAL1;

      VAL1   COUNT(*)
---------- ----------
         0     979000
         1       1000
         2      20000

所望の結果となりました。

別カラムの更新

次に、VAL2列も更新してみます。

以下のUPDATE文では、VAL1列の値とは無関係にVAL2を更新率18.7%で更新しています。

SQL> update TEST set VAL2 = 1 where ID in (
  2  select ID from (
  3  select ID,mod(ID, (100 / 18.7)) from TEST)  -- 更新率(18.7%)の逆数
  4  where rownum <= 1000000 * (18.7 / 100)      -- 全体件数 * 更新率 = 更新件数
  5  );

187000行が更新されました。

SQL> commit;

コミットが完了しました。

SQL> select VAL2,count(*) from TEST group by VAL2 order by VAL2;

      VAL2   COUNT(*)
---------- ----------
         0     813000
         1     187000

SQL> select VAL1,VAL2,count(*) from TEST group by VAL1,VAL2 order by VAL1,VAL2;

      VAL1       VAL2   COUNT(*)
---------- ---------- ----------
         0          0     813000
         0          1     166000
         1          1       1000
         2          1      20000

PK列がNUMBER型でなくても更新できる

今までの例ではPK列(ID)がNUMBER型であったので、mod関数の引数としてそのまま指定できました。

ということで、PK列が暗黙的にでも数値に変換できない値の場合であっても使えるように工夫してみたのが以下のUPDATE2文です。

update 更新TBL set 更新列 = 更新値 where PK列 in (
 select PK列 from (
 select PK列,mod(row_number() over(order by PK列), (1 / 更新率)) from 更新TBL)
 where rownum <= 全体件数 * 更新率
 );

それでは、別の100万行のテーブルTEST2に対して、指定した更新率(28%)でのUPDATEを行ってみます。

SQL> select count(*) from TEST2;

  COUNT(*)
----------
   1000000

1行が選択されました。

SQL> select * from TEST2 where rownum<11;

ID               VAL1
---------- ----------
ABC0000404          0
ABC0000405          0
ABC0000406          0
ABC0000407          0
ABC0000408          0
ABC0000409          0
ABC0000410          0
ABC0000411          0
ABC0000412          0
ABC0000413          0

10行が選択されました。
SQL> update TEST2 set VAL1 = 1 where ID in (
  2  select ID from (
  3  select ID,mod(row_number() over(order by ID), (100 / 28.0)) from TEST2)  -- 更新率(28.0%)の逆数
  4  where rownum <= 1000000 * (28.0 / 100)                                   -- 全体件数 * 更新率 = 更新件数
  5  );

280000行が更新されました。

SQL> commit;

コミットが完了しました。

SQL> select VAL1,count(*) from TEST2 group by VAL1 order by VAL1;

      VAL1   COUNT(*)
---------- ----------
         0     720000
         1     280000

2行が選択されました。

PK列がNUMBER型であっても、飛び番が発生していたりすると期待した結果とならない可能性があるので、どんな場合でもUPDATE文2の使用をおすすめします。

それでは

書評:「SQLデータ分析・活用入門 データサイエンスの扉を開くための技術」② 西潤史郎 (著), 山田祥寛 (監修) 

第10章は、これまでの集大成としてデータ分析の目的と成果について述べられている。

 第10章 データ分析で成果を出すために
  10.1 データ分析が成果を出すために必要なこと
   モデルとは
   モデルはどう作成するのか
   「式」はモデルの代表的な形
   データ分析はプロセスであり成果物ではない
   意思決定者とデータ分析者は分ける
   国家の意思決定から発展した「インテリジェンス」
   意思決定者とデータ分析者の壁
   ビジョンとスキルで壁を越える
   データサイエンスの実践に求められるスキル
   データサイエンスのタスク
   チームとしてのデータサイエンティスト
   社内SQL勉強会のすすめ
  10.2 さらにデータサイエンスのスキルを身につけるための参考書籍
   SQL関連
   エンジニアリング関連
   データサイエンス関連
   ビジネス関連
  10.3 結びにかえて

モデルとは

まず、ここではモデルについて明確化している。モデルとは現実世界を抽象化したものであり、データ分析とはデータからモデルを作成する役割を指す。

そもそも、データとは何であるか?本書に面白い表現があったので紹介する。

「データというものは、現象が放つ光です。そのさまざまな色合いの光を集めて現象を理解します。」P.332

ところで最近、別の本を読んでいて似たような表現を目にした。「私たちは、物に当たった太陽の光や照明の光のうち、吸収されずに反射された光を見ている。どんな種類の光を吸収しやすいかは物質によって異なるため、反射される光の種類も異なる。それによって、物の色が決まる。」~文系でもよくわかる 世界の仕組みを物理学で知る~

つまり、我々が赤いリンゴを視認することは、(太陽等の)光が当たって赤以外の光が吸収された結果、赤の波長の光だけが目に届くという仕組みによる。従って、本物のリンゴではなくて写真のリンゴを見ても同じようにリンゴと認識できる。

言い換えると、光(データ)を注意深く観察することでリンゴという実体(現実世界)を認識することができるということであり、リンゴの特徴を描いた絵や写真がモデルであると理解した。

例えば、顧客の購買データを分析することで、「顧客の買う/買わない」という現象をとらえモデル化を行う。モデルを作ることにより顧客にとってより購買につながる行動とは何かが見えてくる。

このように整理することでデータ分析の目的がストンと腑に落ちた。

データ分析はプロセスであり成果物ではない

つぎに、「データ分析はプロセスであり成果物ではない」という箇所であるが、著者は、
データ分析者によるデータ分析を、料理人による食材の調理にたとえている。

食材(データ)を調理道具(SQLやツール)によって調理し、最終的に美味しい料理(データ分析結果)として客(意思決定者)に提供する。

料理人の役割は客に料理を提供するところまでであり、どんなに自分の作った料理に自信があったとしても客が「美味しい」と思わない限り何の意味もない。

「データ分析も、データを活用して経営に影響を与え、業績に貢献するなどの価値が生まれて、はじめて意味をなす。」(P.337)

意思決定者とデータ分析者は分ける

また、「意思決定者とデータ分析者は分ける」という箇所も興味深い。

これは、意思決定者がデータ分析を行うと、結論ありきの分析に陥りやすいことを示している。(思考バイアス)

「データ分析者は、客観的な立場で分析を行い、データと向き合い客観的な結果を出します。意思決定者は、その分析結果に基づいて意思決定を行うようにします。」(P.338)

国家の意思決定から発展した「インテリジェンス」

そもそも、「インテリジェンス」とは国家の意思決定に必要な「情報分析」であり、インテリジェンス機関の例としてCIA(Central Intelligence Agency)が挙げられる。CIAは米国が国家として安全保障上の重要な決定を下す際に必要な情報を分析する機関である。

もし、大統領を忖度し思考バイアスがかかった分析が行われてしまうと、国家にとって誤った判断を犯す重大なリスクがある。

実際に、米国はCIAがもたらした「イラクには大量破壊兵器が存在する」という誤った分析結果によりイラク戦争を起こした。これは、インテリジェンスの失敗例として検証が必要であろう。

インターネットにせよ米国発の技術は軍事からのスピンオフが多いが、このインテリジェンスをビジネスにおいても活用しようとするのが「ビジネス・インテリジェンス」である。

はじめて 「ビジネス・インテリジェンス」という言葉を聞いた時、なぜ「インテリジェンス」なのだろうかという素朴な疑問を持ったのだが、意思決定者とデータ分析者を分けるという観点では全く同じ発想であることに、本書を読んで大いに納得できた。

チームとしてのデータサイエンティスト

データ分析者が「自分がせっかく作ったデータ分析の価値をわかってもらえない」と嘆き、一方で意思決定者が「データ分析結果がさっぱりわからない」といらだつ。。。というのは著者が今まで沢山目にしてきたことなのだろうが、両者の間に立ちはだかる壁を乗り越える必要がある。

著者は、これを「 ある時点までに達成したいと考える到達点を表明したビジョンを共有すること」と 「 データサイエンスの実践に求められるスキル を磨くこと」で乗り越えられることを強調している。

後者には

  • ビジネス力(Business problem solving)
  • データサイエンス力(Data science)
  • データエンジニアリング力(Data engineering)

という3つのスキルセットが必要だが、いわゆるデータサイエンティストとは一般的に一人でこれらすべてに精通したスーパーマンという印象がある。

”Harvard Business review誌のシニアエディターであるスコット・ベリナートは、「そのような人物はユニコーン級に希少である」と断じています。”(P.346)

ところが、著者は「それぞれの能力を持つ人達が集まり、チームとしてデータサイエンスの価値を生み出す」(P.346)ことができると主張する。つまりスペシャリスト集団としてのチーム力で乗り越えられるとの考えである。

これは、基幹系のエンジニアにとってもスキルシフトする道が開けるのではないかと思った。

まとめ

今更であるが、経済産業省が昨年出した「DXレポート ~ITシステム「2025年の崖」克服とDXの本格的な展開~」が気になって最近読んでいた。

そこに、今回の書評のお話がありデータ分析について学ぶ機会に恵まれたのであるが、「デジタルトランスフォーメーション」を単なるバズワードにしないためには、やはりデータ分析が不可欠であると感じた。

現在の私はデータベースの性能問題解決に特化した仕事が多いのだが、多くのプロジェクトでは「〇〇刷新」と言いながら、インフラ周りをリプレースするだけで、業務アプリケーションは「現行踏襲」が原則で、データを利活用した業務の大幅な見直しなどというケースには残念ながらほとんど遭遇したことがない。

”IT関連費用のうち8割以上が既存システムの運用・保守に充てられている”

”複雑化・老朽化・ブラックボックス化した既存システムが残存した場合、2025年までに予想されるIT人材の引退やサポート終了等によるリスクの高まり等に伴う経済損失は、2025年以降、最大12兆円/年(現在の約3倍)にのぼる可能性がある”

上記レポートにはこのような悲惨な現状および近未来が描かれているが、やはり根本原因は経営者のITに対する無理解と無関心にあるのではないかと思っている。

しかしながら、データ分析が経営にもたらす成果を経営者が実感できれば、世界は大きく変わるとも思う。

つまり、データ分析は真のデジタルトランスフォーメーションにとって必要不可欠と思うのである。

その意味でも、本書の内容は多くに人に知ってもらいたい。

このような機会を与えてくださった西潤史郎さんに感謝しつつ、この書評を締めくくりたいと思う。

終わり

書評:「SQLデータ分析・活用入門 データサイエンスの扉を開くための技術」① 西潤史郎 (著), 山田祥寛 (監修)

はじめに

著者の西潤史郎さんとは、2011年に倉園佳三さんのITガジェット系セミナーでお知り合いになったのだが、当時は歯科開業に関するコンサルティングのお仕事をされていたと記憶している。

Facebookで繋がってたまにメッセージをやり取りする間柄だったのだが、この度SQLの入門書を執筆されたと知り大変驚いた。

早速、購入しようと思っていたのだが、「ブログで紹介してくれたら献本します。」という書き込みがあったので手を挙げることにした。

現在西さんは、データ・サイエンティスト株式会社のチーフデータエンジニアということで、本書は分析系エンジニア向けの入門書という位置付けで書かれている。私はどちらかというと基幹系データベースのエンジニアであるので、自分の周りのエンジニアを意識しながらこの本の紹介をしたいと思う。

基幹系データベース・エンジニアが分析ツールとしてのSQLを学ぶ意義とは

誤解を恐れずに基幹系と分析系の違いを図示すると以下のようになると思う。

両者は相容れない分野という印象を受けるが、共通言語としてのSQL特にウィンドウ関数に精通すれば、基幹系データベース・エンジニアが自らの領域を広げるよいきっかけになるのではということを本書を読んで感じた。(下図)

特定の業務に精通することは大事だが、長いエンジニアとしてのキャリアを考えた場合、汎用的例えばパフォーマンスチューニングやデータモデリング等の知識を得ることは重要だと思う。

汎用的知識の一つとしての分析系領域にSQLをきっかけとして入り込んでいくことを、基幹系に携わる若いエンジニアには是非意識して欲しいと思った。

そのための入門書としても本書は非常に良書と考える。

本書の構成

本書は第一部と第二部に分かれており、 第一部はデータ分析とSQLの世界の全体像の把握と分析SQLの基本的な理解を目的としている。

第二部では、具体的な分析例とデータ分析の目的および実現方法に対する著者の熱い想いが描かれている。

概要と環境

それでは、目次を最初から見ていこう。

第一部 SQLによるデータ分析の基礎
 第1章 SQLデータ分析の世界
  1.1 SQLによるデータ分析とは
   データ分析ニーズの広がり
   データとデータベース
   リレーショナルデータベース管理システムとSQL
   データ分析の流れ
  1.2 分析システムの広がりとSQL
   基幹システムと分析システムの違い
   分析システムのSQLとデータベースの特徴
   コラム ウィンドウ関数という革新で第2の生を得たSQL
  1.3 なぜ分析SQLのスキルが必要なのか
   データベースにある一次データを取得する
   SQLは分析担当者とエンジニアにとっての「英語」
   コラム ビッグデータに対応するデータベースNoSQLによるSQLへの回帰

第1章はSQLによるデータ分析の概要に関する解説となっている。ここで注目したいのは”SQLは分析担当者とエンジニアにとっての「英語」 ”という箇所である。

共通言語としてのSQL を通してデータ分析者とエンジニアが会話できれば、つまり、SQLを使えば何ができるのかということを分析結果のユーザであるデータ分析者が理解していれば、同じ目的を明確化した分析が可能となるということである。

また、2つのコラムも非常に興味深い。ここは是非ミック氏の「達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへー17 順序をめぐる冒険」を併読して欲しい。

第2章はデータ分析環境についての解説である。サンプルコードはDDLと共に書籍内で指定されたURLからダウンロードできるので、第3章以降の内容は手元の検証環境で実際に確認することが可能である。

私はPostgreSQLの環境で、付録の情報を参考にしながら「SQL Workbench/J」を使って確認を行った。

 第2章 データ分析環境を整える
  2.1 データ分析環境の準備
  2.2 アドホック分析とレポーティングの環境
  2.3 その他の分析ツール
  2.4 SQLクライアントをデータ分析で活用する

 付録
  A.1 SQLクライアント「SQL Workbench/J」の導入方法
  A.2 ダッシュボードツール「Redash」の導入方法と可視化手順

SQLの基本

第3章から第6章はSQLの基本についての解説である。SQLをすでに習得している読者であればざっと読み進めてもかまわないが、基本知識のおさらいにはちょうどよい内容である。

 第3章 データの「分ける」「数える」が分析の基本
  3.1 SELECT文でデータを分析する基本
  3.2 WHEREでデータを絞り込む
  3.3 GROUP BYでデータをグループ化する
  3.4 HAVINGでデータをさらに絞り込む
  3.5 ORDER BY句/LIMIT句でデータをさらに整える
 第4章 分析を効率化するSQLによる前処理
  4.1 異なるデータ型への変換
  4.2 数値のデータ加工
  4.3 日付・時間のデータ加工
  4.4 文字列のデータ加工
 第5章 データをさらに活用するためのテクニック
  5.1 サブクエリを使いこなす
  5.2 INとEXISTSによるデータの調査
  5.3 SQLで基本統計量を求める
  5.4 ログデータひとつでできるユーザ分析
 第6章 複数のテーブルを扱うJOINとUNION
  6.1 テーブルの結合とテーブルの正規化
  6.2 JOIN句によるテーブルの結合
  6.3 特殊な結合
  6.4 UNIONで複数のテーブルを扱う

ウィンドウ関数

第7章は第一部の中でも特に強調しておきたい箇所なので小見出しレベルまで紹介する。

 第7章 分析SQLの主役「ウィンドウ関数」徹底入門
  7.1 ウィンドウ関数の概要
   ウィンドウ関数とは
   ウィンドウ関数でランキングを求める
   ウィンドウ関数の構文
   ウィンドウを明記する、別の表記方法
  7.2 ウィンドウの範囲と順序を指定するPARTITION BYとORDER BY
   PARTITION BYでグループ分け
   PARTITION BYを指定しない場合のウィンドウ
   ORDER BYで並び替える
  7.3 ウィンドウ関数に変身する関数と専用の関数
   順序を扱うウィンドウ関数専用の関数
   集約関数からウィンドウ関数に変身する関数
  7.4 フレーム句を使いこなし分析SQLの達人になる
   フレーム句の構文とフレームのイメージ
   フレームで直近の日付を求める
   RANGEによる値単位での行指定

この章のサンプルSQLを実際に試してみるとウィンドウ関数がどのように機能するかよく理解することができる。ウィンドウ関数についてここまで徹底的に解説してくれる入門書にはあまりお目にかかったことがないので、非常に参考になる。

ウィンドウ関数自体に「WINDOW」 という単語は出てこないことが取っつきにくい印象を与えるが、「ウィンドウ=フレーム」という理解をしていればイメージしやすいのではないかと思った。

基幹系のバッチSQLでも集計を行っているものがあるが、各行ごとに合計値列を追加するためだけにテーブルを自己結合しているような事例をよく目にする。

もし、ウィンドウ関数のテクニックがあれば無駄な結合を解消し、よりシンプルなSQLに書き直すことができるかもしれない。

一般的なSQL入門書は数ある機能の一つとしてウィンドウ関数を簡単に 紹介しているものが多いが、本書は第二部以降の実践編へスムーズに入っていくことができるような構成になっているのが非常によいと感じた。

第二部は実践編

本書のメインは第二部の実践編である。第8章はデータ分析の基本的な実践としてアドホック分析を紹介している。

基幹系システムに関わるエンジニアは、SQLとは定型的な業務を実行するためのツールであり、常に期待された正しい結果を正しい時間内に返すクエリーの作成が最も重要であるという共通認識がある。

従って、オンライン処理にせよバッチ処理にせよ、データの内容によって実行計画が変動し、性能が急激に劣化するような状況が最も問題である。

一方、分析系の世界はその場一回限りの(アドホックな)クエリーを対話的に何度も実行することで、求めるモデルを追求していくという特徴がある。

私は本格的な分析業務の経験がないのであるが、第8章のサンプルを丁寧に実行することで、データ分析の実際を簡単に体験することができた。

第二部 SQLによるデータ分析の実践
 第8章 SQLで小さな分析を積み重ねる
  8.1 小さな分析を積み重ねるアドホック分析
  8.2 ファクトデータを活かす時系列分析
   時系列分析とは
   ウィンドウ関数で簡単、SQLで時系列分析
  8.3 グループ分けを組み合わせるクロス集計
   クロス集計とは
   データの確認
  8.4 実践アドホック分析1〜全体から部分へ分析を進める〜
   モデルのイメージ
   全体の把握
   GROUP BY句でグループを分ける
   ウィンドウ関数によるランク付け
   CASE式によるランク付け
   クロス集計で表を整える
  8.5 実践アドホック分析2〜集計と深掘り〜
   指標を追加し、3指標のランクを求める
   3指標でランクを求める
   ランク値ごとに集計する
   集計値を求める

「8.4 実践アドホック分析1」では、「直近購買日」と「購買頻度」という2つの軸(指標)によるクロス集計をゴールとしている。

GROUP BY句で分けられたグループにおけるランク付けを、ウィンドウ関数とCASE式の2つの方法でランク付けする実習を行うことでそれぞれの特徴を理解することができる。

「8.5 実践アドホック分析2」はさらに「購入金額」という指標を加えることで、最終的にRFM分析を体験することができる。

RFM分析というのは本書で初めて知ったのだが、顧客の購買行動をRecency(最新購買日)、Frequency(購買頻度)、Monetary(購買金額)の3指標で顧客動向を分析することである。

第一部から始まって第8章最後のRFM分析に至る流れを感じることができるので、なかなか練られた構成だと思った。

更なる実践Tips

第9章ではより本格的な分析を行う場合に必要なTipsが解説されている。

 
 第9章 長いSQLを読み解く
  9.1 データ分析でよくある長いSQLの読み方
   内側のSELECT文から読む
   SELECT文は句の処理順に読む
  9.2 統計量「四分位数」を求めるSQLを読み解く
   四分位数とSQL全体像
   SQL全体像と読み解き順序
   ランキングを算出するための相関サブクエリ
   ランキングを算出するクエリ
   ランキングをもとに四分位に振り分ける
  9.3 「バスケット分析」のSQLを読み解く
   バスケット分析について
   データとSQLの関係
   SQL全体像と読み解き順序
   1つめの文「商品の組み合わせと購入回数」
  9.4 「ユーザーの利用機能分析」のSQLを読み解く
  9.5 [番外編]既存のSQLをよりよく改善する

「長いSQLを読み解く」というテーマの章であるが、本ブログでも以前 SQLフォーマッターFor WEB というSQL整形ツールを紹介したことがある。

分析系、基幹系問わず長いSQLをいかに的確に読み解けるかが生産性に大きく寄与するが、この章には本当に必要なエッセンスが書いてある。

第10章はまとめであるが長くなりそうなので次回に続く…

実行統計による実践的SQLチューニング(その2)

実行計画を実行順に表示させる

前回の投稿では、DBMS_XPLANパッケージのDISPLAY_CURSOR関数により実行統計を併記した実行計画の表示要領を紹介した。

しかし、実行計画ツリーからどのステップが起点となりどの順番で実行されるかを読み取るのはある程度の経験が必要であり、前回紹介した程度の行数であればともかく、数百ステップにもなる場合はベテランでも投げ出したくなる。

筆者は以前から実行計画ツリーを実行順に表示させることに関して試行錯誤を繰り返してきたが、この度方法を確立するに至ったので紹介したいと思う。

実行順表示スクリプト

DBMS_XPLAN.DISPLAY_COURSORの入力ソースはV$SQL_PLAN_STATISTICS_ALLビューであるので、このビューを使って情報を取得する。

前回投稿の中で aplan.sql スクリプトから呼ばれていた aplans.sql の内容が以下となる。

set lines 1000
col ID for 9999
col Operation for a60
col Name for a20
col Pstart for a13
col Pstop for a13
col A-Time for 9,990.00
col A-Rows for 999,999,999,990
col E-Rows for 999,999,999,990
col Starts for 999,999,999,990
-- 実行順実行統計出力
select
 ID
,"Operation"
,"Name"
,"Starts"
,"E-Rows"
,"A-Rows"
,"A-Time"
,"Buffers"
,"Reads"
,"Writes"
,"Srch Cols"
,"Pstart"
,"Pstop"
,"PartID"
from
(
  select
   rownum NO
  ,ID
  ,lpad(' ',DEPTH) || OPERATION ||' '|| OPTIONS "Operation"
  ,OBJECT_NAME "Name"
  ,LAST_STARTS "Starts"
  ,nvl(CARDINALITY,1) * LAST_STARTS "E-Rows" -- 1回の操作で処理される見積行数 * 見積処理回数 = 見積処理行数
  ,LAST_OUTPUT_ROWS "A-Rows"                 -- 実際の処理行数
  ,LAST_ELAPSED_TIME/1000000 "A-Time"
  ,LAST_CR_BUFFER_GETS "Buffers"
  ,LAST_DISK_READS "Reads"
  ,LAST_DISK_WRITES "Writes"
  ,SEARCH_COLUMNS "Srch Cols"
  --,COST
  ,PARTITION_START "Pstart"
  ,PARTITION_STOP "Pstop"
  ,PARTITION_ID "PartID"
  from
  (
    select a.* from 
     V$SQL_PLAN_STATISTICS_ALL a
    where a.SQL_ID    = '&1'
    and   a.TIMESTAMP = (select max(b.TIMESTAMP) from V$SQL_PLAN_STATISTICS_ALL b where b.SQL_ID = a.SQL_ID)
  )
  start with PARENT_ID is null
  connect by prior ID = PARENT_ID
  order siblings by ID desc
)
order by NO desc
;

解説

  • 49行目のV$SQL_PLAN_STATISTICS_ALLが実行計画情報の取得元となり、50行目のWHERE条件で表示対象のSQL_IDで絞っている。(階層問い合わせでWHERE句を指定してもstart with~connect byの後に評価されるので、このビュー全件が表示対象となり非常に高負荷な問い合わせとなってしまう。)
  • 共有プールをフラッシュせずにこのスクリプトを実行させた場合、1つのSQL_IDに対して2つ以上のPLAN_HASH_VALUEが取得される場合がある。その際実行計画が正しく表示されない可能性があるので、51行目で直近のTIMESTAMPのものだけ1つを表示対象としている。
  • 53〜54行目は階層問い合わせによって、次のIdがNullとなるId=0を起点として実行順にId値をたどる。
  • 55行目のsiblings句により同じ階層(DEPTH)のId値を並び替えているが、desc[endant]を指定することでId値は逆実行順に並ぶ。ちなみに「siblings」とは「きょうだい」を意味する。
  • 35行目はNested Loops Joinにおいて実際の行数(A-Rows)と比較しやすいように見積もり行数(E-Rows)を加工している。(参考: 津島博士のパフォーマンス講座 第68回 TEMP領域の続きとA-Rowsについて
  • 29〜55行目の問い合わせにおいて、30行目のROWNUM疑似列で順序番号(NO列)を取得しているが、Id=0を先頭とした逆実行順の検索結果をNO列の降順に並び替えることで、実行順に表示させている。(当初はこの部分がなく下から順にたどっていく表示にしていたが、わかりやすさに欠けていたので改良した。)

表示結果

実行順実行統計出力スクリプトによって表示させた結果が以下である。

前回投稿の最後でこのSQLにおけるステップの実行順をまとめたが、以下の結果のID列の順序と一致していことを確認してほしい。

実行計画のステップがどんなに多くても、このスクリプトを使えば実行順に表示させることができる。

ID Operation                                         Name           Starts E-Rows A-Rows A-Time Buffers  Reads Writes  Srch Cols Pstart  Pstop PartID
-- ------------------------------------------------- -------------- ------ ------ ------ ------ ------- ------ ------ ---------- ------- ----- ------
 8         INDEX SKIP SCAN                           I_TABLE001_2        1 38,050  3,060   2.96    3619   1917      0          2
 7        TABLE ACCESS BY INDEX ROWID BATCHED        TABLE_001           1 38,046  3,060   3.12    3706   1977      0          0
11          INDEX RANGE SCAN                         I_TABLE004_8    3,060  3,060      1 204.17  117860 114690      0          5 KEY     KEY        9
10         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED TABLE_004       3,060  3,060      1 204.19  117861 114691      0          0 KEY     KEY        9
 9        PARTITION RANGE ITERATOR                                   3,060  3,060      1 204.20  117861 114691      0          0 KEY     KEY        9
 6       NESTED LOOPS                                                    1      1      1 207.33  121567 116668      0          0
12       INDEX RANGE SCAN                            I_TABLE002PK        1      1      0   0.00       1      1      0          2
 5      NESTED LOOPS OUTER                                               1      1      1 207.34  121568 116669      0          0
 4     FILTER                                                            1      1      1 207.34  121568 116669      0          0
 3    FILTER                                                             1      1      1 207.34  121568 116669      0          0
 2   COUNT STOPKEY                                                       1      1      1 207.34  121568 116669      0          0
 1  SORT AGGREGATE                                                       1      1      1 207.34  121568 116669      0          0
 0 SELECT STATEMENT                                                      1      1      1 207.34  121568 116669      0          0

13行が選択されました。

経過: 00:00:00.02

A-Time列を上から順にたどっていき、値が急激に増えている箇所がボトルネックである。
この例ではId=11の「INDEX RANGE SCAN」がそれにあたる。

次回は、この結果から実際にどのようにチューニングを行なっていくかを追ってみる。

(続く)

実行統計による実践的SQLチューニング(その1)

この投稿はJPOUG in 15 minutes #8で発表した内容に加筆・整理したものです。

実行統計とは?

実行統計とは、DBMS_XPLANパッケージのDISPLAY_CURSOR関数における機能拡張で、SQL実行時に実行計画の各ステップ毎に出力行数や実行時間などの統計情報を取得し、実行後(正常終了および強制終了)に実行計画と共に統計情報を併記するものである。

ちなみに、本機能はOracle10g R2以降で使用可能となっている。

実行統計については以下の記事がよくまとまっている。
Oracle DatabaseでSQLの性能計測2(DBMS_XPLAN&DBMS_SQLTUNE編)【Oracle Database or GoldenGate Advent Calendar 2018 Day 8】

Oracle® Database SQLチューニング・ガイド 12c リリース1 (12.1) には以下の記述がある。
V$SQL_PLANビューを使用した計画の評価のガイドライン
ポイントをまとめると以下の2点となる。

  • 出力行数や経過時間など、計画に含まれる操作ごとに実際の実行統計を出力する。
  • 出力行数を除き、すべての統計は累積される。例えば結合操作の統計には、2つの入力の統計も含まれる。

実行統計が使えない時は、SQL文の性能は全体の経過時間と各ステップごとのコスト値で評価するしかなかった。
コスト値はリンクにあるように、性能を評価する絶対的な指標ではなく、実行時間と相関するものではない。
別の言い方をすると、I/OコストとCPUコストで見積もられる「コスト」を最小にするような実行計画を立案するのがコスト・ベース・オプティマイザ(CBO)であるが、コストの大小が必ずしも実行時間の長短でないことがSQLチューニングを難しくしているというのが、実行統計が実装される以前の課題であった。

一方、実行計画の各ステップごとに経過(累積)時間を表示させることができる実行統計により、SQLの中でボトルネックがどこに存在するかを的確に把握することができるので、以前のような「試行錯誤」的チューニングと比べ、より効率的なチューニングが可能となった。

SQL単性能試験の実際

それでは、SQL単性能試験をイメージして実践的なSQLチューニングの実際を考えてみよう。
SQL単性能試験とは、SQL*PlusからSQL文を単体で実行する試験であり、設定した性能目標(レスポンス、スループット)を達成するまでチューニングを行うものである。

アプリケーションが発行しうるすべてのSQL文を予め単体で実行し、性能上の問題点を完全に解決した上で、次の段階(総合試験等)に進むべきである。
カットオーバー直前で致命的な性能問題が発生することのないよう、十分なSQL単体試験を実施することは円滑なプロジェクト遂行にとって重要である。

考慮すべき点

意味のあるSQL単体試験を実施するために考慮すべき点を以下に挙げる。

1. 本番相当データ

SQL単性能試験を行う上で最も重要なのは、量および質で本番と同等のデータを使用することである。量とは将来の増加量を見越した十分なサイズ、質とは現実的な内容(値の分布等)のデータを準備することである。

セキュリティ面から本番データをそのまま試験で使うことは許されないことが多いが、本番データとあまりにもかけ離れたデータを使っては性能試験の妥当性を担保できない。

2. キャッシュ・クリア

SQL単性能試験を行う際、キャッシュをクリア(フラッシュ)した状態で実行時間を計測する。
キャッシュとはDBバッファおよび共有プールである。

キャッシュをクリアした状態でそのSQLの本当の実力を把握することができる。

データがDBバッファ上にあるとボトルネックの検出が困難になる。またパーティション数が非常に多い環境ではParseに要する時間が想定以上に長くなることが多く、共有プールをフラッシュすることでその状況を確認することができる。

3. 占有サーバ

性能測定をするサーバでは極力他の負荷がかかっていない状態であることが望ましく、無風状態で測定できるようサーバを占有できる環境が理想である。

著者が経験したあるプロジェクトでは、開発と性能測定を同じサーバで行わざるを得なかったため、測定結果が負荷により毎回変わってしまい客観的な判断ができない場合があった。

STATISTICS_LEVELパラメータを「ALL」に設定

実行統計を取得するために3つの方法がある

  1. STATISTICS_LEVELパラメータをALLに設定する
  2. SQL文にGATHER_PLAN_STATISTICSヒントを指定し実行する
  3. SQLトレースを有効にしてSQL文を実行する

実際にはSQL*Plusでログインしたセッション単位

alter session set STATISTICS_LEVEL=all;

とするのがよいだろう。

「alter system 〜」によりインスタンス・レベルで設定することも可能だが、実行される全てのSQLの実行統計が取得されSYSAUX表領域が枯渇する可能性があるのでお勧めしない。

キャッシュ・クリア

キャッシュ・クリア(フラッシュ)は以下のスクリプトをSQL実行前に実行することで行う。

pro *** FLUSH SHARED POOL ***
alter system flush shared_pool;
pro *** FLUSH BUFFER CACHE ***
alter system flush buffer_cache;
SQL> @flusys
*** FLUSH SHARED POOL ***
システムが変更されました。

*** FLUSH BUFFER CACHE ***
システムが変更されました。

SQL文の実行と経過時間の確認

それでは、実際にSQLを実行して結果を確認してみよう。

以下のSQLは、実際の業務で実行されたSQLをベースにテーブル名等を書き換えたサンプルSQLである。
コメントにあるように、オンラインSQLとして実行されているが、3分27秒もかかっておりチューニングが必要である。

SELECT /*+ ONLINE_SQL04S
           INDEX(T004 I_TABLE004_8) INDEX(T001 I_TABLE001_2)
           USE_NL(T002)
           LEADING(T001 T004 T002) */
 COUNT(*) AS COUNTNUM
FROM
 TABLE_004 T004
  INNER JOIN
  TABLE_001 T001
  ON  (T004.COL3091 = T001.COL3091
  AND  T004.COLA269 = T001.COLA269)
  LEFT OUTER JOIN
  TABLE_002 T002
  ON  (T002.COLA215 = T001.COLA215
  AND  T002.COL3091 = T004.COL3091)
WHERE
..... 以下省略 ..........
  COUNTNUM
----------
         1

経過: 00:03:27.35

SQL_IDの確認

SQLを実行した後、以下のスクリプトでSQL_IDを確認する。

コメントに記述した文字列を引数として実行する。

SET AUTOT OFF
SET COLSEP ' ' VERIFY OFF LINESIZE 140
COLUMN SQL_TEXT FOR A80
COLUMN SQL_ID FOR A13
COLUMN EXECUTIONS FOR '9999999'
COLUMN ELAPSED_TIME FOR '999999999999'
COLUMN LA_DATE FOR A10
COLUMN LA_TIME FOR A8
SELECT /* THISSQL */
    SUBSTR(SQL_TEXT, 1, 60) SQL_TEXT
  , SQL_ID
  , EXECUTIONS
  , ELAPSED_TIME
  , TO_CHAR(LAST_ACTIVE_TIME, 'YYYY/MM/DD') LA_DATE
  , TO_CHAR(LAST_ACTIVE_TIME, 'HH24:MI:SS') LA_TIME
FROM
  V$SQL
WHERE
  SQL_TEXT LIKE '%&1.%' AND NOT SQL_TEXT LIKE '%THISSQL%'
ORDER BY
  LAST_ACTIVE_TIME ASC
;
SQL> @vsql ONLINE_SQL04S

実行統計を併記した実行計画の表示

上で確認したSQL_IDを使用して、実行統計を併記した実行計画を表示させる。

9行目のDBMS_XPLAN.DISPLAY_CURSORと引数の設定がポイントである。

また、11行目は実行順に実行計画を表示させるスクリプトを呼び出している。(次回解説)

define SQLID=&1
set autot off
set trim on
set pages 10000
set lines 1000
set long 1000000
set longchunksize 1000000
set heading off
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('&SQLID',null,'ALLSTATS LAST'));
set heading on
@aplans &SQLID  --実行順実行計画の表示
set lines 80

表示結果(横スクロールあり)

SQL> @aplan bvrwck53tfgkt

SQL_ID  bvrwck53tfgkt, child number 0
-------------------------------------
SELECT /*+ ONLINE_SQL04S INDEX(T004 I_TABLE004_8) INDEX(T001
I_TABLE001_2) USE_NL(T002) LEADING(T001 T004 T002) */     COUNT(*) AS
COUNTNUM FROM     TABLE_004 T004      INNER JOIN TABLE_001
T001 ON  ( T004.COL3091 = T001.COL3091
                   AND  T004.COLA269 = T001.COLA269 ) 
 LEFT OUTER JOIN TABLE_002 T002 ON  (
T002.COLA215 = T001.COLA215                                         AND
 T002.COL3091 = T004.COL3091 )  WHERE
T001.COLAH15 = '0'      AND   T004.COLAH15 = '0' 
 AND   T001.COLA215 =:B1       AND
(         T001.COLA293 = '2'          OR    (
T001.COLA293 = '1'              AND   T004.COL0157 <> 'B'
         )     )      AND   (
T001.COLA367

Plan hash value: 239732999

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |              |      1 |        |      1 |00:03:27.34 |     121K|    116K|
|   1 |  SORT AGGREGATE                                  |              |      1 |      1 |      1 |00:03:27.34 |     121K|    116K|
|*  2 |   COUNT STOPKEY                                  |              |      1 |        |      1 |00:03:27.34 |     121K|    116K|
|*  3 |    FILTER                                        |              |      1 |        |      1 |00:03:27.34 |     121K|    116K|
|*  4 |     FILTER                                       |              |      1 |        |      1 |00:03:27.34 |     121K|    116K|
|   5 |      NESTED LOOPS OUTER                          |              |      1 |      1 |      1 |00:03:27.34 |     121K|    116K|
|   6 |       NESTED LOOPS                               |              |      1 |      1 |      1 |00:03:27.33 |     121K|    116K|
|*  7 |        TABLE ACCESS BY INDEX ROWID BATCHED       | TABLE_001    |      1 |  38046 |   3060 |00:00:03.12 |    3706 |   1977 |
|*  8 |         INDEX SKIP SCAN                          | I_TABLE001_2 |      1 |  38050 |   3060 |00:00:02.96 |    3619 |   1917 |
|   9 |        PARTITION RANGE ITERATOR                  |              |   3060 |      1 |      1 |00:03:24.20 |     117K|    114K|
|* 10 |         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TABLE_004    |   3060 |      1 |      1 |00:03:24.19 |     117K|    114K|
|* 11 |          INDEX RANGE SCAN                        | I_TABLE004_8 |   3060 |      1 |      1 |00:03:24.17 |     117K|    114K|
|* 12 |       INDEX RANGE SCAN                           | I_TABLE002PK |      1 |      1 |      0 |00:00:00.01 |       1 |      1 |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=TO_NUMBER(:B6)) 3 - filter(:B5>=:B4)
   4 - filter(("T001"."COLA367"='0' OR ("T001"."COLA367"='1' AND INTERNAL_FUNCTION("T004"."COL0310") AND
              "T002"."COLA362"="T004"."COLA363" AND "T002"."COLA364"<=LPAD(NVL("T004"."COLA366",'000'),3,'0') AND "T002"."COLA365">=LPAD(NVL("T004"."COLA366",'000'),3,'0'))))
   7 - filter(("T001"."COLA389"='0' OR "T001"."COLA389"='1' OR "T001"."COLA389"='2'))
   8 - access("T001"."COLA215"=:B1 AND "T001"."COLAH15"='0')
       filter(("T001"."COLA215"=:B1 AND INTERNAL_FUNCTION("T001"."COLA332") AND "T001"."COLAH15"='0'))
  10 - filter((DECODE("T001"."COLA389",'2',NVL("T004"."COLA526",'19000101'),'19000101')<NVL("T001"."COL3277",'21001231')
              AND (INTERNAL_FUNCTION("T001"."COLA389") OR ("T001"."COLA389"='2' AND INTERNAL_FUNCTION("T004"."COLA415"))) AND
              ("T001"."COLA293"='2' OR ("T001"."COLA293"='1' AND "T004"."COL0157"<>'B'))))
  11 - access("T004"."COLA269"="T001"."COLA269" AND "T004"."COL3091"="T001"."COL3091" AND "T004"."COL0017">=:B4 AND
              "T004"."COLA318"=:B3 AND "T004"."COLAH15"='0' AND "T004"."COL0017"<=:B5) filter(("T004"."COLA318"=:B3 AND DECODE("T001"."COLA389",'2',"T004"."COL0017",'21001231')>=:B2 AND
               "T004"."COLAH15"='0' AND INTERNAL_FUNCTION("T004"."COLA415")))
  12 - access("T002"."COLA215"=:B1 AND "T002"."COL3091"="T004"."COL3091")


57行が選択されました。

経過: 00:00:00.39

項目説明

Starts : そのステップが実行された回数
E-Rows : CBOが見積もった(1回あたりの)処理行数
A-Rows : そのステップでの処理行数
A-Time : (累積)実行時間
Buffers : バッファ・アクセス数
Reads : ディスクから読み込まれたブロック数

ステップの実行順

実行計画ツリーの見方は「右から左、上から下」が基本である。

上の実行計画では、インデントの一番深いId=11が一番最初に実行されるように思ってしまうが、実際はId=6「NESTED LOOPS」の最初の入力側(駆動表又は外部表)となるId=8「INDEX SKIP SCAN」が一番最初に実行される。

実行順をまとめると

8 → 7 → 11 → 10 → 9 → 6 → 12 → 5 → 4 → 3 → 2 → 1 → 0

となり、Id=0のA-Time 3:27.34 がこのSQLの実行時間となる。(SQL*Plusのtiming表示の経過時間と若干異なることに注意)

実行統計を併記するようにしても実行順を間違えるとボトルネックの判断を間違えてしまう可能性がある。
ということで、次回は実行計画を実行順に表示させる方法を紹介する。

今回はここまで

INとEXISTSはどちらが速いのか?

問題:「SALARY > 10000」となる社員が所属している部署を表示せよ

今回は、セミジョイン(セミ結合)について考えてみたい。

セミジョインは通常のジョイントと異なり、2つのクエリー間に親子(主従)関係があるのが特徴である。つまり、メインクエリーがあってそれに従属するサブクエリーから成るのがセミジョインで、ジョインにおいて2つのクエリー(テーブル、ビュー)が(実行順はあるが)対等関係にあるのとは明確に異なる。

Oracleでは、IN述語EXISTS述語を使用するものをセミジョイン(セミ結合)NOT IN述語NOT EXISTS述語を使用するものをアンチジョイン(アンチ結合)と呼んでいる。(リンクは12cR1 SQLチューニングガイドの該当箇所)

使用するテーブル

今回はHRサンプルスキーマのDEPARTMENTS(部署)表とEMPLOYEES(社員)表を使ったクエリーを考えてみる。

SQL> desc DEPARTMENTS
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 DEPARTMENT_ID                             NOT NULL NUMBER(4)
 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
 MANAGER_ID                                         NUMBER(6)
 LOCATION_ID                                        NUMBER(4)

SQL> desc EMPLOYEES
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

実行環境は

SQL> select BANNER from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE	12.2.0.1.0	Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

である。

1. IN述語を使ったSQL

1番目はIN述語を使ったSQLである。

DEPARTMENTS表にアクセスするメインクエリーに対して、EMPLOYEES表にアクセスするサブクエリーをIN述語で連結する。CBOが正しく判断できるようにサブクエリーはカッコで囲む。

このSQLを記述通りに解釈すると、最初にサブクエリーが実行され条件を満たすDEPARTMENT_IDの集合が作られ(このサブクエリーのみの実行では重複が発生することに注意)メインクエリーで使用するINリストが作成される。メインクエリーはこのINリストを使用してDEPARTMENTS表から必要な情報を取得する。

select
   DEPARTMENT_ID
  ,DEPARTMENT_NAME
from
  DEPARTMENTS
where
  DEPARTMENT_ID in (
    select
      DEPARTMENT_ID
    from
      EMPLOYEES
    where
      SALARY > 10000
  )
;

2. EXISTS述語を使ったSQL

次はEXISTS述語を使ったSQLである。

このSQLは(文字通りの解釈では)前項と逆でメインクエリーが先に実行される。次にメインクエリーの結果セットの各行に対してサブクエリー側で条件に合致するかを判定する。

条件に合致すれば(真:TRUE)結果セットに残り、合致しなければ(偽:FALSE)結果セットから除外される。つまり、サブクエリーはメインクエリーのフィルタとして機能する。

メインクエリーとサブクエリーの関係は「D.DEPARTMENT_ID = E.DEPARTMENT_ID」の条件で絞り込む必要がある。万一この条件を書き忘れると、EMPLOYEES表の中にDEPARTMENTS表に存在するDEPARTMENT_IDが1つでも存在すれば、EXISTS述語が常に真(TRUE)となりDEPARTMENTS表の全行が返ることになるので、肝心の「SALARY > 10000」という条件が効かない結果となってしまう。

select
   DEPARTMENT_ID
  ,DEPARTMENT_NAME
from
  DEPARTMENTS D
where
  exists (
    select
      *
    from
      EMPLOYEES E
    where
      D.DEPARTMENT_ID = E.DEPARTMENT_ID
    and SALARY > 10000
  )
;

3. 内部結合を使ったSQL

セミジョインのSQLは次のような内部結合を使ったSQLに書き換えることができる。(書き換えることができるというのは違うSQLでも同じ結果を返す、という意味である。)

注意点としては、DISTINCT句忘れてはいけないことである。(DISTINCTが必要な理由は最後に示す。)

select distinct
   D.DEPARTMENT_ID
  ,D.DEPARTMENT_NAME
from
  DEPARTMENTS D
  inner join EMPLOYEES E
  on  D.DEPARTMENT_ID = E.DEPARTMENT_ID
where
  E.SALARY > 10000
;

どの書き方が優れているのか?

このように同じ結果を得る(であろう)3つのSQLを紹介したのだが、実際はどの書き方がより優れているのだろうか?
違いがあるのであれば、開発者はどんな点に注意した方がよいのだろうか?

性能的な優劣を比較するのであれば、大量データを使用して実行時間を比較するのが王道であるのだが、サンプル表を使用しての簡単な検証になるので、今回は実行計画を確認しながら実行する。

1. IN述語を使ったSQL

		  
SQL> select
  2     DEPARTMENT_ID
  3    ,DEPARTMENT_NAME
  4  from
  5    DEPARTMENTS
  6  where
  7    DEPARTMENT_ID in (
  8      select
  9        DEPARTMENT_ID
 10      from
 11        EMPLOYEES
 12      where
 13        SALARY > 10000
 14    )
 15  ;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
           90 Executive
          100 Finance
           30 Purchasing
           80 Sales
           20 Marketing
          110 Accounting

6行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 2317224448

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    11 |   253 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |             |    11 |   253 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMPLOYEES   |    68 |   476 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
   3 - filter("SALARY">10000)


統計
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        778  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

今回の問題に対する解答は、上記6つの部署となることがわかる。

細かい考察は後にしてEXISTS述語を使ったSQLも同様に見てみよう。

2. EXISTS述語を使ったSQL

SQL> select
  2     DEPARTMENT_ID
  3    ,DEPARTMENT_NAME
  4  from
  5    DEPARTMENTS D
  6  where
  7    exists (
  8      select
  9        *
 10      from
 11        EMPLOYEES E
 12      where
 13        D.DEPARTMENT_ID = E.DEPARTMENT_ID
 14      and SALARY > 10000
 15    )
 16  ;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
           90 Executive
          100 Finance
           30 Purchasing
           80 Sales
           20 Marketing
          110 Accounting

6行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 2317224448

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    11 |   253 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |             |    11 |   253 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMPLOYEES   |    68 |   476 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   3 - filter("SALARY">10000)


統計
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        778  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

2つの実行計画を比較するには「Plan hash value」を見ればよいのだが、何と同じ「2317224448」となっていることがわかる。

つまり、セミジョインはどちらの書き方をしても同じ実行計画が選択されるという興味深い結果が明らかになった。

この実行計画はHASH JOINなので、メインクエリーのDEPARTMENTS表がビルド表となり、サブクエリーのEMPLOYEES表がプローブ表となっている。

Id=1のオペレーションは「HASH JOIN SEMI」であるが、対応する述語情報(Predicate Information)は

1 - access("DEPARTMENT_ID"="DEPARTMENT_ID")

となっている。
1.の書き方では、このような結合条件を記述していないにもかかわらず、CBOは裏でちゃんとこのような結合条件を使ったセミジョインを考慮してくれている。

従って、素直に解釈するとOracle12cでは「1. IN述語を使ったSQL」でも内部的には「2. EXISTS述語を使ったSQL」にリライトされているように見える。

ただし、マニュアル(SQLチューニングガイド)の記述を見ると、セミジョイン、アンチジョインは内部的には結合(ジョイン)タイプとして処理されると説明されている。(「…セミ結合とアンチ結合は、それらを実行するSQL構文が副問合せであっても、結合タイプとして考慮されます。これらは、副問合せ構文を結合形式で解決できるようにフラット化するため、オプティマイザによって使用される内部アルゴリズムです。…」)

3. 内部結合を使ったSQL

ここまで来ると、残りの内部結合を使ったSQLも気になる。ひょっとして同じ実行計画となるのであろうか?

SQL> select distinct
  2     D.DEPARTMENT_ID
  3    ,D.DEPARTMENT_NAME
  4  from
  5    DEPARTMENTS D
  6    inner join EMPLOYEES E
  7    on  D.DEPARTMENT_ID = E.DEPARTMENT_ID
  8  where
  9    E.SALARY > 10000
 10  ;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
          100 Finance
           90 Executive
           30 Purchasing
          110 Accounting
           80 Sales
           20 Marketing

6行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 1983137394

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |    11 |   253 |     7  (15)| 00:00:01 |
|   1 |  HASH UNIQUE        |             |    11 |   253 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN SEMI    |             |    11 |   253 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMPLOYEES   |    68 |   476 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   4 - filter("E"."SALARY">10000)


統計
----------------------------------------------------------
        302  recursive calls
          4  db block gets
        324  consistent gets
          0  physical reads
          0  redo size
        778  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         16  sorts (memory)
          0  sorts (disk)
          6  rows processed

今度は違う実行計画となった。(Plan hash value: 1983137394)

このクエリーはセミジョインでないにもかかわらず、Id=2で「HASH JOIN SEMI」となっているのが興味深い。

さらに、この実行計画ではId=1の「HASH UNIQUE」が実行されている。
つまり、セミジョインに比べてDISTINCT付きジョインは、オペレーションが1つ多くなっている分性能的に不利なのではないかと思われる。(あくまでも実行計画を比較した上での見解)

DISTINCT句を外してみると

参考までに、上のクエリーでDISTINCT句を外して実行してみる。
(重複排除すべき行を網掛け表示にしてある。)

SQL> select
  2     D.DEPARTMENT_ID
  3    ,D.DEPARTMENT_NAME
  4  from
  5    DEPARTMENTS D
  6    inner join EMPLOYEES E
  7    on  D.DEPARTMENT_ID = E.DEPARTMENT_ID
  8  where
  9    E.SALARY > 10000
 10  ;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
           90 Executive
           90 Executive
           90 Executive
          100 Finance
           30 Purchasing
           80 Sales
           80 Sales
           80 Sales
           80 Sales
           80 Sales
           80 Sales
           80 Sales
           80 Sales
           20 Marketing
          110 Accounting

15行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 2052257371

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    68 |  1564 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |             |    68 |  1564 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMPLOYEES   |    68 |   476 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   3 - filter("E"."SALARY">10000)


統計
----------------------------------------------------------
          4  recursive calls
          4  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
        860  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed

DISTINCT句を付けないと「HASH JOIN」となることがわかる。

セミジョインの結合方式を考える

上記セミジョインの結合方式は、「HASH JOIN SEMI」というオペレーション名からハッシュ型セミジョインであることがわかるが、以下のようにサブクエリー側にヒント句を使用することで結合方式を変更することができる。

ネステッドループ型セミジョイン

SQL> select
  2     DEPARTMENT_ID
  3    ,DEPARTMENT_NAME
  4  from
  5    DEPARTMENTS D
  6  where
  7    exists(
  8      select
  9        /*+ NL_SJ */
 10        *
 11      from
 12        EMPLOYEES E
 13      where
 14        D.DEPARTMENT_ID = E.DEPARTMENT_ID
 15      and SALARY > 10000
 16    )
 17  ;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
           20 Marketing
           30 Purchasing
           80 Sales
           90 Executive
          100 Finance
          110 Accounting

6行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 2332702268

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    11 |   253 |    41   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |             |    11 |   253 |    41   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMPLOYEES   |    28 |   196 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID" AND "SALARY">10000)


統計
----------------------------------------------------------
          0  recursive calls
         56  db block gets
        220  consistent gets
          0  physical reads
          0  redo size
        778  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

以前のバージョンでは、セミジョインのデフォルトの結合方式であったが、この検証においては3つの中でも最もコストが高くなった。

細かいが、結果がDEPARTMENT_IDの昇順となっている点がハッシュ型と異なっていることがわかる。

ソートマージ型セミジョイン

SQL> select
  2     DEPARTMENT_ID
  3    ,DEPARTMENT_NAME
  4  from
  5    DEPARTMENTS D
  6  where
  7    exists(
  8      select
  9        /*+ MERGE_SJ */
 10        *
 11      from
 12        EMPLOYEES E
 13      where
 14        D.DEPARTMENT_ID = E.DEPARTMENT_ID
 15      and SALARY > 10000
 16    )
 17  ;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
           20 Marketing
           30 Purchasing
           80 Sales
           90 Executive
          100 Finance
          110 Accounting

6行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 2249117780

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |    11 |   253 |     8  (25)| 00:00:01 |
|   1 |  MERGE JOIN SEMI    |             |    11 |   253 |     8  (25)| 00:00:01 |
|   2 |   SORT JOIN         |             |    27 |   432 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE       |             |    68 |   476 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| EMPLOYEES   |    68 |   476 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
       filter("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   5 - filter("SALARY">10000)


統計
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        778  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          6  rows processed

ソートマージ型は、オペレーション数が一番多いにもかかわらず、ネステッドループ型よりもコストが低い。

結果はネステッドループ型と同様にDEPARTMENT_IDの昇順になっている。

セミジョインはサブクエリーによるメインクエリーの存在チェック(フィルタリング)とも言えるが、単純な有無を判定するのであれば並び替えをせずにハッシュテーブル上で比較ができるハッシュ型が有利なのではないかと考える。

筆者の経験では、DBリンク越しのセミジョイン(サブクエリー側のテーブルがリモート表)のあるSQLがネステッドループ型セミジョインとなっていたので、HASH_SJヒントでハッシュ型セミジョインにしたところ、2時間経っても終わらないクエリーがわずか2分強で終了するまでに改善したことがある。

ちなみにHASH_SJヒントは以下のように使用する。

SQL> select
  2     DEPARTMENT_ID
  3    ,DEPARTMENT_NAME
  4  from
  5    DEPARTMENTS D
  6  where
  7    exists(
  8      select
  9        /*+ HASH_SJ */
 10        *
 11      from
 12        EMPLOYEES E
 13      where
 14        D.DEPARTMENT_ID = E.DEPARTMENT_ID
 15      and SALARY > 10000
 16    )
 17  ;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
           90 Executive
          100 Finance
           30 Purchasing
           80 Sales
           20 Marketing
          110 Accounting

6行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 2317224448

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    11 |   253 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |             |    11 |   253 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMPLOYEES   |    68 |   476 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   3 - filter("SALARY">10000)

おまけ:内部結合になぜDISTINCT句が必要なのか?

今回のようなSQLで内部結合を使う場合DISTINCT句が必要なことは先に述べたが、その理由を考えてみたい。

結合(ジョイン)は2つのテーブルから結果を取得するものであるが、「部署を表示せよ」ということであれば片方のテーブルに属するカラムのみを表示させることになる。

問題の主旨からすれば本来は「DEPARTMENT_NAME」のみを取得するだけでよかったのだが、今回はわかりやすくするために「DEPARTMENT_NAME」が従属する主キーである「DEPARTMENT_ID」も表示するようにした。

同様に、「SALARY > 10000」という条件で絞り込んだEMPLOYEES表を結合することを考えると、各行の主キーである「EMPLOYEE_ID」も一緒に結合すると考えることができる。

これを実際のクエリーで示したのが以下となる。

SQL> select
  2     D.DEPARTMENT_ID
  3    ,D.DEPARTMENT_NAME
  4    ,E.EMPLOYEE_ID "(EMPLOYEE_ID)"
  5  from
  6    DEPARTMENTS D
  7    inner join EMPLOYEES E
  8    on  D.DEPARTMENT_ID = E.DEPARTMENT_ID
  9  where
 10    E.SALARY > 10000
 11  ;

DEPARTMENT_ID DEPARTMENT_NAME (EMPLOYEE_ID)
------------- --------------- -------------
           20 Marketing                 201
           30 Purchasing                114
           80 Sales                     145
           80 Sales                     146
           80 Sales                     147
           80 Sales                     148
           80 Sales                     149
           80 Sales                     162
           80 Sales                     168
           80 Sales                     174
           90 Executive                 100
           90 Executive                 101
           90 Executive                 102
          100 Finance                   108
          110 Accounting                205

15行が選択されました。

従って、このクエリーからEMPLOYEE_IDを非表示(SELECTリストから外す)としても、DISTINCT句を付けない限り結果は重複表示される。

これが、DISTINCT句付き内部結合としなければならない理由である。

まとめ

今回のタイトルは「INとEXISTSはどちらが速いのか?」にしてみたが、開発の現場ではこのような疑問が生じることが多々あるかと思う。

どちらでも結果が変わらない記述法があると「果たしてどちらがいいのか?」という議論になり、あまり望ましくない結論として「どちらかに統一してしまえ」ということになったりする。

「実行計画が変わらないのであればどちらでもよいではないか?」それはそれで問題ないのであるが、次のようなやっかいなトラブルが起きる可能性がある。

昔のバージョンにおいてEXISTS述語にすることで大きく性能改善させた成功体験のあるベテランSEがいたとする。

新人SEが一生懸命IN述語で書いてきたSQL文を見て、ベテランSEが「INなんかダメだ!EXISTSに書き直せ!」などと安直に指示したりすると、新人SEは一括置換でEXISTSに直したりするかもしれない。

本文でも紹介したように、EXISTSの場合はサブクエリーに結合条件を記述しなければならないのに、それがスッポリ抜け落ちてしまう可能性があり、しかも構文エラーにならないので間違いに気づかない危険性もある。

どちらかに統一するような不毛な議論をするのではなく、どちらでもよいのだという柔軟さを持つべきなのではないだろうか。

CBOは日々進化しており、以前の常識が通用しないかもしれないということをベテランは自覚し、最新バージョンではどうなっているのかという疑問を常に持ち続ける必要があるのではないかと、自戒を込めて主張したい。

SQLコーディング規約と標準SQLについて考える

SQL表記の標準化を推進するには

最近、とあるプロジェクトにおいて、プロジェクト管理の1つとして「SQLコーディング規約」あるいは「SQLコーディングチェックリスト」などにより、SQL表記の標準化を図ろうという取り組みに関わることになった。

開発責任者にヒアリングすると、テキストエディタでSQL文を記述しているのでどうしても開発者によって記述のゆらぎが発生し、可読性の悪いSQL文が量産されるということが悩みのタネらしい。

このような場合、SQL Developer等のツールを活用するのが賢い方法であるが、「SQLフォーマッターFor WEB」という秀逸なWebツールがあるので紹介したい。

本ツールは2006年に初版が出たとあるが、現在でも頻繁にアップデートが繰り返されている。

SQLフォーマッターFor WEB

このツールは、以下のような非常にシンプルなインターフェースのツールで、テキストボックスに記述されたSQL文を、ラジオボタンによって選択されたフォーマット・ルールによって整形してくれるスグレモノである。

上のテキストボックスにSQL文を手入力あるいはコピー&ペーストで入力し、「整形する」ボタンをクリックすると、下のテキストボックスに整形されたSQL文が自動的に表示される。

さらに、右下の「copy」ボタンをクリックすると、クリップボードに整形されたSQL文がコピーされるので、開発で使用しているエディタ等にペーストすればよい。
次のSQL文をこのツールで実際に整形したところを以下に示す。

整形前

SELECT D.DEPARTMENT_NAME,
CASE WHEN E.FIRST_NAME IS NOT NULL THEN 
  SUBSTR(E.FIRST_NAME,1,1) || '. ' || E.LAST_NAME
ELSE NULL
END AS NAME
FROM DEPARTMENTS D
LEFT OUTER JOIN EMPLOYEES E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
ORDER BY D.DEPARTMENT_NAME ,
  E.LAST_NAME ;

整形後

select
  D.DEPARTMENT_NAME
  ,case
    when E.FIRST_NAME is not null then SUBSTR(E.FIRST_NAME, 1, 1) || '. ' || E.LAST_NAME
    else null
  end as NAME
from
  DEPARTMENTS D
  left outer join EMPLOYEES E
  on  D.DEPARTMENT_ID = E.DEPARTMENT_ID
order by
  D.DEPARTMENT_NAME
  ,E.LAST_NAME
;

ちなみに私は、SQL全文を貼り付ける前に大文字に一括変換し

    • カンマ整形:前
    • AND/OR/ON整形:前
    • インデント:スペース2
    • JOIN形式:パターンB
    • 予約語:小文字
    • 出力先:色付きエディタ

の設定した上で使用するのが好みだ。

もちろん、これはプロジェクトマネージャの考え方で適宜統一してよい。

オフラインでも使用できる

最近の開発環境は、セキュリティの観点からインターネットとは完全に隔絶されている要件が必須だ。

SQLフォーマッターFor WEB」はその名の通りWebツールなので基本的にインターネットに接続された状態で使う。

しかし、このツールはJava Scriptで記述されているので、「ファイル」メニューの「ページを別名で保存…」等で任意の場所にページを丸ごと保存(HTMLファイルとスクリプト等が含まれたフォルダ)したものを開発環境に移送し、HTMLファイルをダブルクリックすることでオフラインでも使用することができる。

この場合、色付きエディタが正常に機能しない可能性があるが、実用上は何ら問題ない。

標準SQLについて考える

SQLは元来、IBMの研究者であったエドガー・F・コッドが考案した関係データベースの実装である、関係データベース管理システム(RDBMS)の操作あるいは定義言語である。

しかし、UNIXあるいはLinuxにおいてOracle RDBMSがシェアを大きく獲得したため、IBMは標準化を主体的に策定することで巻き返しを図ってきたと、筆者は一人のOracle技術者として理解をしている。

一方、Oracle RDBMSも標準SQL(ANSI SQL)に積極的に準拠する戦略により対応している。(SQL言語リファレンスの「Oracleと標準SQL」参照)

標準SQLの内部結合

Oracle技術者にとって標準SQL記法に慣れることは、他RDBMSに移行する場合だけでなく可読性を高める目的でも有益である。

以下は、標準SQLによって記述された内部結合であるが、結合条件と検索条件を明確に区分して記述することができる。

標準SQLは結合条件と検索条件を明確に区別できる

標準SQLにより、開発者にとって可読性が向上し、例えばどのカラムにインデックスを作成するのが適切なのかがより容易になるのではないかと考える。

SQL> select
  2    D.DEPARTMENT_NAME
  3    ,case
  4      when E.FIRST_NAME is not null then SUBSTR(E.FIRST_NAME, 1, 1) || '. ' || E.LAST_NAME
  5      else null
  6    end as NAME
  7  from
  8    DEPARTMENTS D
  9    inner join EMPLOYEES E
 10    on  D.DEPARTMENT_ID = E.DEPARTMENT_ID  -- 結合条件
 11  where
 12    D.DEPARTMENT_NAME like 'IT%'  -- 検索条件
 13  order by
 14    D.DEPARTMENT_NAME
 15    ,E.LAST_NAME
 16  ;

DEPARTMENT_NAME           NAME
------------------------- -------------------------
IT                        D. Austin
IT                        B. Ernst
IT                        A. Hunold
IT                        D. Lorentz
IT                        V. Pataballa


実行計画
----------------------------------------------------------
Plan hash value: 4213409228

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |    29 |   986 |     7  (15)| 00:00:01 |
|   1 |  SORT ORDER BY      |             |    29 |   986 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |             |    29 |   986 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| DEPARTMENTS |     3 |    48 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMPLOYEES   |   107 |  1926 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   3 - filter("D"."DEPARTMENT_NAME" LIKE 'IT%')


統計
----------------------------------------------------------
          4  recursive calls
          4  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
        748  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

ちなみに「inner」は省略が可能であるが、外部結合ではないことを明示するために省略しない方がよいのではないかと考える。

実行環境は

SQL> select BANNER from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE	12.2.0.1.0	Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

である。

Oracle SQL(注:標準SQLでないという意味)で記述した内部結合

Oracle SQLでは結合条件と検索条件がWHERE句に混在しているので、可読性が悪い。

SQL> select
  2    D.DEPARTMENT_NAME
  3    ,case
  4      when E.FIRST_NAME is not null then SUBSTR(E.FIRST_NAME, 1, 1) || '. ' || E.LAST_NAME
  5      else null
  6    end as NAME
  7  from
  8    DEPARTMENTS D
  9    ,EMPLOYEES E
 10  where
 11    D.DEPARTMENT_ID = E.DEPARTMENT_ID
 12  and D.DEPARTMENT_NAME like 'IT%'
 13  order by
 14    D.DEPARTMENT_NAME
 15    ,E.LAST_NAME
 16  ;

DEPARTMENT_NAME           NAME
------------------------- -------------------------
IT                        D. Austin
IT                        B. Ernst
IT                        A. Hunold
IT                        D. Lorentz
IT                        V. Pataballa


実行計画
----------------------------------------------------------
Plan hash value: 4213409228

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |    29 |   986 |     7  (15)| 00:00:01 |
|   1 |  SORT ORDER BY      |             |    29 |   986 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |             |    29 |   986 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| DEPARTMENTS |     3 |    48 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMPLOYEES   |   107 |  1926 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   3 - filter("D"."DEPARTMENT_NAME" LIKE 'IT%')


統計
----------------------------------------------------------
          4  recursive calls
          4  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
        748  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

検索条件をON句に記述

標準SQLでは複合キーが結合条件となっている場合、ON句の中で「AND」を使用することで2番目以降の結合条件を記述することができるが、(少なくともOracleの場合)検索条件をON句の中に書くことができる。(「書くことができる」というのは構文エラーにならずに実行できるという意味)

SQL> select
  2    D.DEPARTMENT_NAME
  3    ,case
  4      when E.FIRST_NAME is not null then SUBSTR(E.FIRST_NAME, 1, 1) || '. ' || E.LAST_NAME
  5      else null
  6    end as NAME
  7  from
  8    DEPARTMENTS D
  9    inner join EMPLOYEES E
 10    on  D.DEPARTMENT_ID = E.DEPARTMENT_ID  -- 結合条件
 11    and D.DEPARTMENT_NAME like 'IT%'  -- 検索条件
 12  order by
 13    D.DEPARTMENT_NAME
 14    ,E.LAST_NAME
 15  ;

DEPARTMENT_NAME           NAME
------------------------- -------------------------
IT                        D. Austin
IT                        B. Ernst
IT                        A. Hunold
IT                        D. Lorentz
IT                        V. Pataballa


実行計画
----------------------------------------------------------
Plan hash value: 4213409228

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |    29 |   986 |     7  (15)| 00:00:01 |
|   1 |  SORT ORDER BY      |             |    29 |   986 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |             |    29 |   986 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| DEPARTMENTS |     3 |    48 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMPLOYEES   |   107 |  1926 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   3 - filter("D"."DEPARTMENT_NAME" LIKE 'IT%')


統計
----------------------------------------------------------
          4  recursive calls
          4  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
        748  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

JOIN句はWHERE句よりも先に評価されるため、DEPARTMENTS表を絞り込んだ後に結合することを考えるとJOINに続くON句に記述することは理にかなっているようにも思えるが、実行計画上はWHERE句に書く場合と全く変わらないことがわかる。

標準SQLの左外部結合

次に、標準SQLでの左外部結合の例を紹介する。

この場合「LEFT JOIN」に先行する(左側にある)DEPARTMENTS表のうち絞り込み条件に合致する全行を表示し、結合キー(E.DEPARTMENT_ID)が存在しないEMPLOYEES表側はNullを表示する。

結合条件はON句において「D.DEPARTMENT_ID = E.DEPARTMENT_ID」を記述する。

「LEFT」を「RIGHT」に書き換えるだけで右外部結合を表現することができ、この例ではどの部署にも属さない従業員を含む従業員一覧となる。

SQL> select
  2    D.DEPARTMENT_NAME
  3    ,case
  4      when E.FIRST_NAME is not null then SUBSTR(E.FIRST_NAME, 1, 1) || '. ' || E.LAST_NAME
  5      else null
  6    end as NAME
  7  from
  8    DEPARTMENTS D
  9    left outer join EMPLOYEES E
 10    on  D.DEPARTMENT_ID = E.DEPARTMENT_ID
 11  where
 12    D.DEPARTMENT_NAME like 'IT%'
 13  order by
 14    D.DEPARTMENT_NAME
 15    ,E.LAST_NAME
 16  ;

DEPARTMENT_NAME           NAME
------------------------- -------------------------
IT                        D. Austin
IT                        B. Ernst
IT                        A. Hunold
IT                        D. Lorentz
IT                        V. Pataballa
IT Helpdesk               (null)
IT Support                (null)

7行が選択されました。

実行計画
----------------------------------------------------------
Plan hash value: 3871261979

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |    10 |   340 |     7  (15)| 00:00:01 |
|   1 |  SORT ORDER BY      |             |    10 |   340 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |             |    10 |   340 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| DEPARTMENTS |     1 |    16 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMPLOYEES   |   107 |  1926 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"(+))
   3 - filter("D"."DEPARTMENT_NAME" LIKE 'IT%')


統計
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        782  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          7  rows processed

このように、部署名が「IT」で始まる「IT Helpdesk」や「IT Support」も表示対象となるが、あいにくどちらも従業員がアサインされていないのでNullが表示されている。

select
  D.DEPARTMENT_NAME
  ,case
    when E.FIRST_NAME is not null then SUBSTR(E.FIRST_NAME, 1, 1) || '. ' || E.LAST_NAME
    else null
  end as NAME
from
  DEPARTMENTS D
  ,EMPLOYEES E
where
  D.DEPARTMENT_ID = E.DEPARTMENT_ID(+)
and D.DEPARTMENT_NAME like 'IT%'
order by
  D.DEPARTMENT_NAME
  ,E.LAST_NAME
;

Oracle SQLで外部結合を記述するには、キーが存在しない行をNullで表示する方(この場合E.DEPARTMENT_ID側)に(+)を記述する。
(必然的に、左外部結合であれば右辺側、右外部結合の場合は左辺側に(+)を記述するのだが、可読性はかなり悪い。)

また、標準SQLで外部結合を記述したSQLの実行計画を見ると、Predicate Informationに
2 – access(“D”.”DEPARTMENT_ID”=”E”.”DEPARTMENT_ID”(+))
を確認することができる。(47行目)

つまり、Oracleの場合は標準SQLで記述してもいったんOracle SQLにリライトされた上でパーサ(Parser)に送られるのではないかと思われる。

検索条件をON句に記述した外部結合

それでは、内部結合と同様に検索条件をON句に記述した結果を確認してみよう。

SQL> select
 2    D.DEPARTMENT_NAME
 3    ,case
 4      when E.FIRST_NAME is not null then SUBSTR(E.FIRST_NAME, 1, 1) || '. ' || E.LAST_NAME
 5      else null
 6    end as NAME
 7  from
 8    DEPARTMENTS D
 9    left outer join EMPLOYEES E
10    on  D.DEPARTMENT_ID = E.DEPARTMENT_ID
11    and D.DEPARTMENT_NAME like 'IT%'
12  order by
13    D.DEPARTMENT_NAME
14    ,E.LAST_NAME
15  ;


DEPARTMENT_NAME           NAME
------------------------- -------------------------
Accounting                (null)
Administration            (null)
Benefits                  (null)
Construction              (null)
Contracting               (null)
Control And Credit        (null)
Corporate Tax             (null)
Executive                 (null)
Finance                   (null)
Government Sales          (null)
Human Resources           (null)
IT                        D. Austin
IT                        B. Ernst
IT                        A. Hunold
IT                        D. Lorentz
IT                        V. Pataballa
IT Helpdesk               (null)
IT Support                (null)
Manufacturing             (null)
Marketing                 (null)

DEPARTMENT_NAME           NAME
------------------------- -------------------------
NOC                       (null)
Operations                (null)
Payroll                   (null)
Public Relations          (null)
Purchasing                (null)
Recruiting                (null)
Retail Sales              (null)
Sales                     (null)
Shareholder Services      (null)
Shipping                  (null)
Treasury                  (null)

31行が選択されました。

実行計画
----------------------------------------------------------
Plan hash value: 3743165598

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |   108 |  4536 |    85   (2)| 00:00:01 |
|   1 |  SORT ORDER BY        |                 |   108 |  4536 |    85   (2)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER  |                 |   108 |  4536 |    84   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | DEPARTMENTS     |    27 |   432 |     3   (0)| 00:00:01 |
|   4 |    VIEW               | VW_LAT_718C084F |     4 |   104 |     3   (0)| 00:00:01 |
|*  5 |     FILTER            |                 |       |       |            |          |
|*  6 |      TABLE ACCESS FULL| EMPLOYEES       |     4 |    72 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  5 - filter("D"."DEPARTMENT_NAME" LIKE 'IT%')
  6 - filter("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")

統計
----------------------------------------------------------
         0  recursive calls
         8  db block gets
        31  consistent gets
         0  physical reads
         0  redo size
      1524  bytes sent via SQL*Net to client
       630  bytes received via SQL*Net from client
         4  SQL*Net roundtrips to/from client
         1  sorts (memory)
         0  sorts (disk)
        31  rows processed

明らかに、意図するものと異なる結果となった。

少なくともOracleにおいては、外部結合で検索条件をON句に書いてしまうと間違った結果を導いてしまう恐れがあるので注意が必要である。

同様に、内部結合においても検索条件はWHERE句に書くことをおすすめする。(我々が心配しなくてもCBOは結合前に適切に検索条件で絞り込んでくれる。)

FULL OUTER JOINを試す

標準SQLの最も優れている点は、完全外部結合が簡単に表記できることである。

以下のように、「full outer join」により、従業員がアサインされていない部署とどの部署にもアサインされていない従業員をまとめて表示させることができる。
(特に最後の2行に注目)

SQL> select
  2    D.DEPARTMENT_NAME
  3    ,case
  4      when E.FIRST_NAME is not null then SUBSTR(E.FIRST_NAME, 1, 1) || '. ' || E.LAST_NAME
  5      else null
  6    end as NAME
  7  from
  8    DEPARTMENTS D
  9    full outer join EMPLOYEES E
 10    on  D.DEPARTMENT_ID = E.DEPARTMENT_ID
 11  order by
 12    D.DEPARTMENT_NAME
 13    ,E.LAST_NAME
 14  ;

DEPARTMENT_NAME           NAME
------------------------- -------------------------
Accounting                W. Gietz
Accounting                S. Higgins
Administration            J. Whalen
Benefits                  (null)
Construction              (null)
Contracting               (null)
Control And Credit        (null)
Corporate Tax             (null)
Executive                 L. De Haan
Executive                 S. King
Executive                 N. Kochhar
Finance                   J. Chen
Finance                   D. Faviet
Finance                   N. Greenberg
Finance                   L. Popp
Finance                   I. Sciarra
Finance                   J. Urman
Government Sales          (null)
Human Resources           S. Mavris
IT                        D. Austin
...................................................

DEPARTMENT_NAME           NAME
------------------------- -------------------------
Shipping                  M. Weiss
Treasury                  (null)
(null)                    K. Grant

123行が選択されました。

実行計画
----------------------------------------------------------
Plan hash value: 3058970667

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |   122 |  5246 |     7  (15)| 00:00:01 |
|   1 |  SORT ORDER BY         |             |   122 |  5246 |     7  (15)| 00:00:01 |
|   2 |   VIEW                 | VW_FOJ_0    |   122 |  5246 |     6   (0)| 00:00:01 |
|*  3 |    HASH JOIN FULL OUTER|             |   122 |  4148 |     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL  | DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL  | EMPLOYEES   |   107 |  1926 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")


統計
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
       4212  bytes sent via SQL*Net to client
        696  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        123  rows processed

実行計画を見ると「HASH JOIN FULL OUTER」というオペレーションを確認することができる。(Id=3)

開発の現場で完全外部結合が必要になった場面を見たことはないのだが、非常にシンプルな記述で複雑な処理を行うことができるのはまさに構造化言語であるSQLらしい記述と言えるかもしれない。

ただし、パーティション化された完全外部結合(FULL)は指定できない等の制約があるので、使用する際は注意が必要である。

Oracleでパーセンタイルを求める

JPOUG Advent Calendar 2017  13日目のエントリーです。

はじめに

今年の後半は「Oracle技術者から見た、SAP HANA」というDB Onlineの記事執筆で忙しかったこともあって、個人ブログの更新ができていませんでしたが、Advent Calendarといういいきっかけをいただいたので久しぶりの投稿です。(去年も同じようなことを言っていたような。。。)

ちなみにSAP HANAの連載はまだまだ続きますので、ご興味のある方は是非見てください!

今回のネタは「パーセンタイル」です。

パーセンタイルは、数学的な定義(Wikipedia)はとりあえず横に置きますが、われわれOracleエンジニアにとってレスポンスタイムの評価などでなじみがあると思います。

簡単に言うと100個の測定値を値の順に並べて、小さい方から90番目の値を「90パーセンタイル」あるいは「90%ile」と表現します。

JMeter等の負荷テストツールでも90%ile値は結果に表示されますが、なぜレスポンスタイムの評価に90%ile値が使われるのでしょうか?

これには諸説あると思いますが、私は以下の記述を参考にしています。

■体感レスポンスタイムとは

「体感レスポンスタイムとは、タスクを実行するのにかかったとユーザが感じる時間のことです。これは、最も長いレスポンスタイムの影響を非常に強く受けます。経験的には、体感レスポンスタイムの平均値はレスポンスタイム分布の90%値近辺と言われています。(後略)」
データベースチューニング256の法則 上 P.49~

蛇足ですが、「キャッシュヒット率が90%を下回ると急激に性能が悪化する。」というのは、これも一因なのではないかと私は解釈しています。

パーセンタイルを求める2つの関数

Oracleにパーセンタイルを求める関数には「PERCENTILE_CONT」、「PERCENTILE_DISC」の2つがあります。(この他に近似値を求める「APPROX_PERCENTILE」がありますがリンクだけ貼っておきます。)
また「MEDIAN」関数も広義にはパーセンタイルを求める関数と言えないこともないですが、これについては後述します。

これらの関数はSQL ServerやPostgresなど他のRDBMSにもあるようですが、「CONT」や「DISC」というのは何の略なのか日本語のマニュアルを見てもよくわかりませんので英語のマニュアルも参照してみましょう。

PERCENTILE_CONT

12cR2マニュアル(英語)
12cR2マニュアル(日本語)

PERCENTILE_CONT(expr) WITHIN GROUP
 (ORDER BY expr [ DESC | ASC ])
 [ OVER (query_partition_clause) ]

Purpose

目的

PERCENTILE_CONT is an inverse distribution function that assumes a continuous distribution model.

PERCENTILE_CONTは、連続分散モデルを想定する逆分散関数です。

It takes a percentile value and a sort specification, and returns an interpolated value that would fall into that percentile value with respect to the sort specification.

このファンクションは、パーセンタイル値およびソート指定を使用し、そのソート指定に従ってそのパーセンタイル値に該当する補間された値を戻します。
(中略)

The first expr must evaluate to a numeric value between 0 and 1, because it is a percentile value.

最初のexprは、パーセンタイル値であるため、0から1の数値で評価します。

This expr must be constant within each aggregation group.

このexprは、各集計グループ内の定数である必要があります。

The ORDER BY clause takes a single expression that must be a numeric or datetime value, as these are the types over which Oracle can perform interpolation.

ORDER BY句には、Oracleが補間を実行できる型である数値または日時値の単一式を指定します

The result of PERCENTILE_CONT is computed by linear interpolation between values after ordering them.

PERCENTILE_CONTの結果は、順序付けされた後の値間の直線補間によって計算されます。

Using the percentile value (P) and the number of rows (N) in the aggregation group, you can compute the row number you are interested in after ordering the rows with respect to the sort specification.

This row number (RN) is computed according to the formula RN = (1+(P*(N-1)).

The final result of the aggregate function is computed by linear interpolation between the values from rows at row numbers CRN = CEILING(RN) and FRN = FLOOR(RN).
The final result will be:

If (CRN = FRN = RN) then the result is
   (value of expression from row at RN)
Otherwise the result is
   (CRN - RN) * (value of expression for row at FRN) +
   (RN - FRN) * (value of expression for row at CRN)

PERCENTILE_DISC

12cR2マニュアル(英語)
12cR2マニュアル(日本語)

PERCENTILE_DISC(expr) WITHIN GROUP
 (ORDER BY expr [ DESC | ASC ])
 [ OVER (query_partition_clause) ]

Purpose

目的

PERCENTILE_DISC is an inverse distribution function that assumes a discrete distribution model.

PERCENTILE_DISCは、不連続分散モデルを想定する逆分散関数です。
(後略)

For a given percentile value P, PERCENTILE_DISC sorts the values of the expression in the ORDER BY clause and returns the value with the smallest CUME_DIST value (with respect to the same sort specification) that is greater than or equal to P.

指定されたパーセンタイル値Pに対して、PERCENTILE_DISCは、ORDER BY句の式の値をソートし、P以上である(同じソート指定に従う)最小CUME_DIST値を持つ値を戻します。

つまり、パーセンタイルが要素の間に存在する場合

    • CONTinuous:連続:補間して算出
    • DISCrete:不連続 :隣り合う要素でソート順で先に来る方

となります。

SQL実行例

それでは、マニュアルに記載されている集計の例をそのまま実行してみます。
この例は50パーセンタイルをPERCENTILE_CONTとPERCENTILE_DISCの両方で算出しています。
SALARY列の降順でソートしていることに注目してください。

SQL> show user
USER is "HR"
SQL> SELECT department_id,
  2         PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) "Median cont",
  3         PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC) "Median disc"
  4    FROM employees
  5    GROUP BY department_id
  6    ORDER BY department_id;

DEPARTMENT_ID Median cont Median disc
------------- ----------- -----------
           10        4400        4400
           20        9500       13000
           30        2850        2900
           40        6500        6500
           50        3100        3100
           60        4800        4800
           70       10000       10000
           80        8900        9000
           90       17000       17000
          100        8000        8200
          110       10154       12008
                     7000        7000

12 rows selected.

結果から、”PERCENTILE_CONT” =< ”PERCENTILE_DISC”となっていることがわかります。
(ちなみにPERCENTILE_CONTは、集計列のソート順に関わらず同じ結果となります。)

MEDIAN関数は50パーセンタイル

SQL> SELECT department_id, MEDIAN(salary)
  2    FROM employees
  3    GROUP BY department_id
  4    ORDER BY department_id;

DEPARTMENT_ID MEDIAN(SALARY)
------------- --------------
           10           4400
           20           9500
           30           2850
           40           6500
           50           3100
           60           4800
           70          10000
           80           8900
           90          17000
          100           8000
          110          10154
                        7000

12 rows selected.

マニュアルにも記述がありますが「MEDIANは、パーセンタイル値がデフォルトで0.5に指定される特別なPERCENTILE_CONTです。」

実際のデータ分布を見てみる

EMPLOYEES表をDEPARTMENT_IDでグルーピングし、それぞれRANK関数で値の順位を確認してみます。(同じ値は当然同じ順位となります。)

順位数が奇数のDEPARTMENT_IDの場合は中央値(M)が存在しますが、偶数の場合は計算の結果50パーセンタイルが決定されます。(D)

また、D値の横に対応するPERCENTILE_CONT(0.5)の値(C)を表示しています。

SQL> set pages 100
SQL> break on department_id skip page
SQL> SELECT department_id,salary
  2  ,RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) rank
  3  FROM employees
  4  ORDER BY department_id,salary DESC;

DEPARTMENT_ID     SALARY       RANK
------------- ---------- ----------
           10       4400          1  ←M

DEPARTMENT_ID     SALARY       RANK
------------- ---------- ----------
           20      13000          1  ←D(C=9500)
                    6000          2

DEPARTMENT_ID     SALARY       RANK
------------- ---------- ----------
           30      11000          1
                    3100          2
                    2900          3  ←D(C=2850)
                    2800          4
                    2600          5
                    2500          6

DEPARTMENT_ID     SALARY       RANK
------------- ---------- ----------
           40       6500          1  ←M

DEPARTMENT_ID     SALARY       RANK
------------- ---------- ----------
           50       8200          1
                    8000          2
                    7900          3
...................................
                    3200         17
                    3100         21
                    3100         21  ←M
                    3100         21
                    3000         24
...................................
                    2200         43
                    2200         43
                    2100         45

DEPARTMENT_ID     SALARY       RANK
------------- ---------- ----------
           60       9000          1
                    6000          2
                    4800          3  ←M
                    4800          3
                    4200          5

DEPARTMENT_ID     SALARY       RANK
------------- ---------- ----------
           70      10000          1  ←M

DEPARTMENT_ID     SALARY       RANK
------------- ---------- ----------
           80      14000          1
                   13500          2
                   12000          3
...................................
                    9500         13
                    9000         16
                    9000         16  ←M
                    8800         18
                    8600         19
                    8400         20
...................................
                    6200         32
                    6200         32
                    6100         34

DEPARTMENT_ID     SALARY       RANK
------------- ---------- ----------
           90      24000          1
                   17000          2  ←M
                   17000          2

DEPARTMENT_ID     SALARY       RANK
------------- ---------- ----------
          100      12008          1
                    9000          2
                    8200          3  ←D(C=8000)
                    7800          4
                    7700          5
                    6900          6

DEPARTMENT_ID     SALARY       RANK
------------- ---------- ----------
          110      12008          1  ←D(C=10154)
                    8300          2

DEPARTMENT_ID     SALARY       RANK
------------- ---------- ----------
                    7000          1  ←M

107 rows selected.

ここからが本題!

RESPONCE_TIME表の作成

それでは、レスポンスタイムデータを擬似的に作成してパーセンタイルを求めるところまでをやってみます。

まず最初に、RESPONCE_TIME表の作成です。ID列とレスポンスタイムを格納するRT列からなる単純なテーブルです。

SQL> conn test/test
Connected.
SQL> CREATE TABLE responce_time (
  2   id NUMBER
  3  ,rt NUMBER);

Table created.

SQL> desc responce_time
 Name  Null?    Type
 ----- -------- ---------
 ID             NUMBER
 RT             NUMBER

レスポンス時間データを作る

次に、DBMS_RANDOMパッケージのNORMALファンクションを使用して標準正規分布の乱数を発生させ、想定する平均レスポンスタイム3秒前後のデータを10000件作成します。

SQL> BEGIN
  2    FOR i IN 1..10000 LOOP
  3      INSERT INTO responce_time
  4      VALUES (i,3+DBMS_RANDOM.NORMAL);
  5    END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

データの確認

念のためID列でソートしてデータの作成状況を確認します。
10000件のデータが作成されていることがわかります。

SQL> col rt for 0.999
SQL> SELECT * FROM responce_time
  2  ORDER BY id;

        ID     RT
---------- ------
         1  3.239
         2  3.613
         3  3.419
         4  3.388
         5  4.443
         6  3.775
         7  2.510
         8  4.597
.................
      9992  3.144
      9993  1.849
      9994  4.021
      9995  4.205
      9996  3.045
      9997  2.383
      9998  4.202
      9999  2.183
     10000  3.771

10000 rows selected.

90パーセンタイルの確認

それでは、90パーセンタイルを求めてみましょう。パーセンタイル値は「0.9」となります。
念のためPERCENTILE_DISCとPERCENTILE_CONT、参考に最小値、中央値、平均値、最大値も確認します。

SQL> col 90%ile_cont for 90.99999
SQL> col 90%ile_disc for 90.99999
SQL> col MAX for 90.99999
SQL> col MIN for 90.99999
SQL> col MED for 90.99999
SQL> col AVG for 90.99999
SQL> SELECT
  2   MIN(rt) MIN
  3  ,MEDIAN(rt) MED
  4  ,AVG(rt) AVG
  5  ,PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY rt) "90%ile_disc"
  6  ,PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY rt) "90%ile_cont"
  7  ,MAX(rt) MAX
  8    FROM responce_time;

      MIN       MED       AVG 90%ile_disc 90%ile_cont       MAX
--------- --------- --------- ----------- ----------- ---------
 -1.00573   2.96475   2.98250     4.24513     4.24515   6.53524

レスポンスタイムの90パーセンタイルは「4.245秒」であることがわかります。

レスポンスタイムの場合は連続分散モデルを想定する方が自然なため「PERCENTILE_CONT」を使用する方が良いと思います。
(RT列の昇順(デフォルト)でソートしているため、
”PERCENTILE_DISC” =< ”PERCENTILE_CONT”となります。)

95パーセンタイルの確認

95パーセンタイルの場合は、引数を「0.95」とするだけです。
4.62秒」となることがわかります。

SQL> SELECT
  2   PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY rt) "95%ile_disc"
  3  ,PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY rt) "95%ile_cont"
  4    FROM responce_time;

95%ile_disc 95%ile_cont
----------- -----------
    4.62042     4.62043

99パーセンタイルの確認

同様に99パーセンタイルは「5.294秒」となります。
つまり、99パーセンタイルよりも90パーセンタイルの方がレスポンスタイム目標としては厳しいものとなります。

SQL> SELECT
  2   PERCENTILE_DISC(0.99) WITHIN GROUP (ORDER BY rt) "99%ile_disc"
  3  ,PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY rt) "99%ile_cont"
  4    FROM responce_time;

99%ile_disc 99%ile_cont
----------- -----------
    5.29363     5.29364

正規分布を可視化する

それではおまけとして、作成した10000件のデータの分布をExcelで可視化してみます。

Excel計算式

  • レスポンス時間データを昇順にソートしB列に貼り付けます。
  • A列は1~10000の順番を示します。90パーセンタイル即ち9000/10000のデータは「4.24513306」となります。
  • C列にはB列を基にした、確率密度を求める式を記述します。「NORM.DIST」関数を使い、平均=3(sec)、標準偏差=1、関数形式=FALSEを指定します。

標準正規分布曲線

横軸にレスポンス時間、縦軸に確率密度となるグラフを描画すると下の図のようになります。
(赤線で90%tileの補助線を書いています。)


実際にやっている事例を見たことはないのですが、アクセスログをデータベースに取り込んで、PERCENTILE_CONT関数でレスポンス時間90パーセンタイルの確認を定期的に行うような運用をすれば、サービスレベルのチェックに使えるのではないかと思います。

明日は、おおのたかしさんの12cR2ネタです。

IPアドレスの管理方法を考える②

はじめに

以前投稿した「IPアドレスの管理方法を考える①」では、IPアドレスを管理する専用のファンクションを作成して、実験的なIPアドレス管理方法を提案してみた。

今回はより実践的なIPアドレス情報の管理方法を考えてみよう。

ネットワークとサーバ(ノード)の関係

コンピュータ・ネットワークは2つ以上のコンピュータ(サーバ)を結び相互に通信することを可能にするので、1つのネットワークには複数のサーバ(ノード)が存在する。

一方、1台のサーバはクライアントにサービスを提供するためのサービス・セグメントだけでなく、運用や監視のために運用管理セグメントに接続されていることが一般的だ。つまり1台のサーバにも複数のネットワークが存在している。(複数のネットワークに「足を出す」)

すなわち、ネットワークとサーバには多対多の関係が成り立つのでリレーショナル・データベースではこれらをそのまま管理することはできない。

このため、ネットワークとサーバの間に別のエンティティを持ってきて、2つの一対多関係を組み合わせるモデルとする必要がある。

この中間エンティティは文字通りの「ネットワーク・インターフェース」となる。具体的にはサーバに挿さった複数のNIC(ネットワーク・インターフェース・カード)上のポートとなる。

そしてこれらのポートを識別するのがIPアドレスとなる。

1枚のNICに仮想IP(VIP)として複数のIPアドレスを割り当てることもできるが、このようなモデルを導入すれば仮想IPも管理することができる。

IPアドレス、ネットワークアドレス、サブネットマスクの関係(おさらい)

TCP/IPは1つのIPアドレスでネットワークとノードをまとめて表すことができるのが特徴である。

IPv4 32bitアドレスは、ネットワークを識別する上位のネットワーク部とノードを識別する下位のホスト部に分けられる。
ホスト部のビットが全て0となっているIPアドレスは、ネットワークを識別するアドレスとして特に「ネットワークアドレス」と呼ばれる。

ネットワーク部とホスト部を示すための情報がサブネットマスクで、IPアドレスの32bit値とBIT AND演算(論理積)を施したものがネットワークアドレスとなる。
つまり、IPアドレスが論理的にあるネットワークに属していることを表すことに必要な情報がサブネットマスクであるとも言える。

例:
 IPアドレス    :192.168.  1.1(11000000 10101000 00000001 00000001)
+
 サブネットマスク  :255.255.255.0(11111111 11111111 11111111 00000000)
------------------------------------------------------------------
 ネットワークアドレス :192.168.  1.0(11000000 10101000 00000001 00000000)

データベースでIPをアドレスを管理するためには、制約等でこれらの関係を考慮する仕組みを考える必要がある。

テーブル等作成(DDL)

上のER図を基に、テーブル等を作成するDDLを紹介する。

DDL

DROP TABLE NETWORK_INTERFACE PURGE;
DROP TABLE NETWORKS PURGE;
DROP TABLE SERVER PURGE;
-- ネットワーク
CREATE TABLE NETWORKS
(
 NETWORK_ADDRESS    NUMBER(12,0) NOT NULL,
 SUBNET_MASK        NUMBER(12,0) NOT NULL,
 DEFAULT_GATEWAY    NUMBER(12,0) NOT NULL,
 BROADCAST_ADDRESS  NUMBER(12,0) NOT NULL,
 NETWORK_NAME       VARCHAR2(30) NOT NULL,
 REMARKS            VARCHAR2(2000)
);
ALTER TABLE NETWORKS
 ADD(CONSTRAINT PK_NETWORK
     PRIMARY KEY (NETWORK_ADDRESS, SUBNET_MASK)
     USING INDEX)
;
-- ネットワークインターフェース
CREATE TABLE NETWORK_INTERFACE
(
 IP_ADDRESS         NUMBER(12,0) NOT NULL,
 NETWORK_ADDRESS    NUMBER(12,0) NOT NULL,
 SUBNET_MASK        NUMBER(12,0) NOT NULL,
 INTERFACE_NAME     VARCHAR2(30) NOT NULL,
 HOST_NAME          VARCHAR2(30),
 SERVER_ID          NUMBER(5,0),
 REMARKS            VARCHAR2(2000)
);
ALTER TABLE NETWORK_INTERFACE
 ADD(CONSTRAINT PK_NETWORK_INTERFACE
     PRIMARY KEY (IP_ADDRESS)
     USING INDEX)
;
ALTER TABLE NETWORK_INTERFACE
 ADD(CONSTRAINT CHK_NETWORK
     CHECK (BITAND(IP_ADDRESS,SUBNET_MASK)=NETWORK_ADDRESS))
;
-- サーバ
CREATE TABLE SERVER
(
 SERVER_ID          NUMBER(5,0)  NOT NULL,
 NODE_NAME          VARCHAR2(30) NOT NULL,
 SERVER_GROUP_ID    NUMBER(5,0)  NOT NULL,
 OS_RELEASE_ID      NUMBER(5,0)  NOT NULL,
 REMARKS            VARCHAR2(2000)
);
ALTER TABLE SERVER
 ADD(CONSTRAINT PK_SERVER
     PRIMARY KEY (SERVER_ID) USING INDEX)
;
-- 参照整合性制約
ALTER TABLE NETWORK_INTERFACE
 ADD(CONSTRAINT FK_NETWORK_INTERFACE
     FOREIGN KEY(NETWORK_ADDRESS, SUBNET_MASK)
     REFERENCES NETWORKS (NETWORK_ADDRESS, SUBNET_MASK))
;
ALTER TABLE NETWORK_INTERFACE
 ADD(CONSTRAINT FK_SERVER_NETWORK_INTERFACE
     FOREIGN KEY(SERVER_ID)
     REFERENCES SERVER (SERVER_ID))
;

解説

ネットワーク(4行目〜)

原則的にIPアドレス関連は32bit値を10進数表現した形で格納する。「IPアドレスの管理方法を考える①」で紹介したinet_aton関数およびinet_ntoa関数を挿入あるいは参照時に利用する。

ネットワーク・インターフェース(19行目〜)

ネットワーク表の子表であるがIPアドレスでユニークに識別できるのでIPアドレスのみを主キーにしている。(サブネットマスクとネットワークアドレスは外部キー)
(多対多リレーションを解決する関連エンティティは主キーを複合主キーとする(この場合はサーバIDとネットワークアドレス+サブネットマスク)場合が多いが、サーバ情報はネットワーク情報がある状態で登録する(つまり後で更新する)イメージなので、あえて非依存関係のモデルとしている。)

チェック制約(CHK_NETWORK)で、挿入されるIPアドレスの論理的妥当性(ネットワークアドレスとの整合性)確認を行っている。

サーバ(39行目〜)

サーバのデータ作成に関しては次回とするので今回は割愛。

参照整合性制約(52行目〜)

参照整合性制約(外部キー制約)を作成する際のTipsとしては一番最後に実行するのがよい。

データ作成と確認

環境が整ったところで、実際にデータを作成してみよう。

サンプルとして
Oracle VM VirtualBox を用いた Oracle Real Application Clusters (RAC) 12c Release 1 環境の構築
の「2.4 ネットワーク p.9〜」にあるネットワーク情報のデータを作成してみる。

NETWORKS表

DML

INSERT INTO NETWORKS VALUES (
 INET_ATON('192.168.56.0')
,INET_ATON('255.255.255.0')
,INET_ATON('192.168.56.1')
,INET_ATON('192.168.56.255')
,'PUBLIC NETWORK'
,'パブリック・ネットワーク'
);
INSERT INTO NETWORKS VALUES (
 INET_ATON('192.168.100.0')
,INET_ATON('255.255.255.0')
,INET_ATON('192.168.100.1')
,INET_ATON('192.168.100.255')
,'PRIVATE NETWORK1'
,'プライベート・ネットワーク1'
);
INSERT INTO NETWORKS VALUES (
 INET_ATON('192.168.200.0')
,INET_ATON('255.255.255.0')
,INET_ATON('192.168.200.1')
,INET_ATON('192.168.200.255')
,'PRIVATE NETWORK2'
,'プライベート・ネットワーク2'
);
COMMIT;

確認

SQL> col DEFAULT_GATEWAY for a16
SQL> col NETWORK_NAME for a17
SQL> SELECT
  2   INET_NTOA(NETWORK_ADDRESS) NETWORK_ADDRESS
  3  ,INET_NTOA(SUBNET_MASK)     SUBNET_MASK
  4  ,INET_NTOA(DEFAULT_GATEWAY) DEFAULT_GATEWAY
  5  ,NETWORK_NAME
  6  FROM
  7   NETWORKS;

NETWORK_ADDRESS  SUBNET_MASK      DEFAULT_GATEWAY  NETWORK_NAME
---------------- ---------------- ---------------- -----------------
192.168.56.0     255.255.255.0    192.168.56.1     PUBLIC NETWORK
192.168.100.0    255.255.255.0    192.168.100.1    PRIVATE NETWORK1
192.168.200.0    255.255.255.0    192.168.200.1    PRIVATE NETWORK2

NETWORK_INTERFACE表

DML

INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.56.101')
,INET_ATON('192.168.56.0')
,INET_ATON('255.255.255.0')
,'eth0');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.56.102')
,INET_ATON('192.168.56.0')
,INET_ATON('255.255.255.0')
,'eth0');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.56.201')
,INET_ATON('192.168.56.0')
,INET_ATON('255.255.255.0')
,'eth0:1');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.56.202')
,INET_ATON('192.168.56.0')
,INET_ATON('255.255.255.0')
,'eth0:2');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.56.203')
,INET_ATON('192.168.56.0')
,INET_ATON('255.255.255.0')
,'eth0:3');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.56.254')
,INET_ATON('192.168.56.0')
,INET_ATON('255.255.255.0')
,'eth0');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.100.101')
,INET_ATON('192.168.100.0')
,INET_ATON('255.255.255.0')
,'eth1');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.100.102')
,INET_ATON('192.168.100.0')
,INET_ATON('255.255.255.0')
,'eth1');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.200.101')
,INET_ATON('192.168.200.0')
,INET_ATON('255.255.255.0')
,'eth2');
INSERT INTO NETWORK_INTERFACE (
 IP_ADDRESS
,NETWORK_ADDRESS
,SUBNET_MASK
,INTERFACE_NAME)
VALUES (
 INET_ATON('192.168.200.102')
,INET_ATON('192.168.200.0')
,INET_ATON('255.255.255.0')
,'eth2');
COMMIT;

確認

SQL> col IP_ADDRESS for a16
SQL> col NETWORK_ADDRESS for a16
SQL> col SUBNET_MASK for a16
SQL> col INTERFACE_NAME for a16
SQL> SELECT
  2   INET_NTOA(IP_ADDRESS)      IP_ADDRESS
  3  ,INET_NTOA(SUBNET_MASK)     SUBNET_MASK
  4  ,INTERFACE_NAME
  5  FROM
  6   NETWORK_INTERFACE;

IP_ADDRESS       SUBNET_MASK      INTERFACE_NAME
---------------- ---------------- ----------------
192.168.56.101   255.255.255.0    eth0
192.168.56.102   255.255.255.0    eth0
192.168.56.201   255.255.255.0    eth0:1
192.168.56.202   255.255.255.0    eth0:2
192.168.56.203   255.255.255.0    eth0:3
192.168.56.254   255.255.255.0    eth0
192.168.100.101  255.255.255.0    eth1
192.168.100.102  255.255.255.0    eth1
192.168.200.101  255.255.255.0    eth2
192.168.200.102  255.255.255.0    eth2

10行が選択されました。

チェック制約の確認

間違ったIPアドレスをエラーとするチェック制約の動作を確認する。

SQL> INSERT INTO NETWORK_INTERFACE (
  2   IP_ADDRESS
  3  ,NETWORK_ADDRESS
  4  ,SUBNET_MASK
  5  ,INTERFACE_NAME)
  6  VALUES (
  7   INET_ATON('192.168.57.101')  <== 第2オクテットをわざと間違えてInsert
  8  ,INET_ATON('192.168.56.0')
  9  ,INET_ATON('255.255.255.0')
 10  ,'eth0');
INSERT INTO NETWORK_INTERFACE (
*
行1でエラーが発生しました。:
ORA-02290: チェック制約(CM.CHK_NETWORK)に違反しました

今回はここまで