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%を下回ると急激に性能が悪化する。」というのは、これも一因なのではないかと私は解釈しています。
パーセンタイルを求める２つの関数
Oracleにパーセンタイルを求める関数には「PERCENTILE_CONT」、「PERCENTILE_DISC」の２つがあります。（この他に近似値を求める「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ネタです。