タグ別アーカイブ: Tips

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ネタです。

1日分のAWRレポートをまとめて出力する

このエントリは「JPOUG Advent Calendar 2016」の13日目です。
昨日はcharade_oo4oさんの「Oracle on Hyper-V 2016」 でした。

複数のAWRレポートを作成するのは面倒

AWRスナップショットはデフォルトで1時間に1回取得されるので、レポート期間1時間のAWRレポートは1日分で24個になる。
1日分のレポートを1個ずつ作成するのは面倒なので、一度に作成する方法を考えてみた。

考慮する仕様は以下の3つ

  • 本日からn日前以降のレポートを全て出力する。(ただし本日分は含まない)
  • レポート期間は1時間で連続するスナップショットIDを指定する。
  • スナップショットはMMONプロセスで自動的に取得されたものを対象とする。(定常運用をイメージしているので、負荷テスト時のようにスナップショットをアドホックに取得していないことを前提。ちなみに手動で取得すると毎正時の自動取得がスキップされる場合があるので注意。)

作成用スクリプト

以下のPL/SQLスクリプトに適当な名前を付けて保存する。(例では「mkscr.sql」)

set echo off
set feedback off
set verify off
set trimspool on
set serveroutput on
spool getawrr.sql replace
DECLARE
  num_day NUMBER := &1;
  CURSOR sid_cur IS
    select
    -- es.DBID
     es.INSTANCE_NUMBER
    ,to_char(round(bs.END_INTERVAL_TIME,'mi'),'yyyy/mm/dd hh24') BEGIN_HOUR
    ,bs.SNAP_ID BEGIN_SNAP
    ,es.SNAP_ID END_SNAP
    from
     DBA_HIST_SNAPSHOT bs
    ,DBA_HIST_SNAPSHOT es
    where 1=1
    and bs.END_INTERVAL_TIME >= trunc(sysdate-num_day)
    and bs.END_INTERVAL_TIME <  trunc(sysdate)
--  and trunc(es.END_INTERVAL_TIME,'mi') = trunc(bs.END_INTERVAL_TIME,'mi') + 1/24
    and abs(round(es.END_INTERVAL_TIME,'mi') - round(bs.END_INTERVAL_TIME,'mi') < (1/24)*1.1
    and bs.SNAP_ID < es.SNAP_ID
    and bs.DBID = es.DBID
    and bs.INSTANCE_NUMBER = es.INSTANCE_NUMBER
    and bs.SNAP_FLAG = 0
    and es.SNAP_FLAG = 0
    order by
     bs.END_INTERVAL_TIME;
BEGIN
  FOR sid_rec IN sid_cur LOOP
    dbms_output.put_line('-- '||sid_rec.BEGIN_HOUR);
    dbms_output.put_line('define report_type=html');
    dbms_output.put_line('define num_days='||num_day);
    dbms_output.put_line('define begin_snap='||sid_rec.BEGIN_SNAP);
    dbms_output.put_line('define end_snap='||sid_rec.END_SNAP);
    dbms_output.put_line('define report_name=awrrpt_'||sid_rec.INSTANCE_NUMBER||'_'||sid_rec.BEGIN_SNAP||'_'||sid_rec.END_SNAP||'.html');
    dbms_output.put_line('@?/rdbms/admin/awrrpt.sql');
  END LOOP;
END;
/
spool off
set echo on
set feedback on
set verify on

補足説明

  • 8行目:n日前の「n」は実行時に引数で置換変数に渡す。
  • 20行目:n日前以降の条件。AWRリポジトリに残っている以上の日数も指定できるが、データがないので残っている分しかレポートはできない。
  • 22行目:1時間ごとにスナップショットを取得している場合。30分間隔であれば「1/48」に書き換える。
  • 23,24行目:22行目の条件だとスナップショット間隔がきっかり1時間でない場合のレポートが欠損してしまうため、10%(1時間であれば6分)未満の誤差にも対応した。
  • 27,28行目:PL/SQLパッケージ(DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT)により手動で取得されたスナップショットを除外している。(この条件はなくてもよいかも。)
  • 34行目:レポートファイル名は対話型で実行した場合のデフォルト名で出力するようにした。

実行例

実行方法は簡単。作成用スクリプトを実行(1行目)し、SQL*Plusを起動したカレント・ディレクトリに出力されたスクリプト(例では「getawrr.sql」、内容は上書きされるので最後に実行した内容が保存される。)を実行(35行目)するだけでよい。
注意点としては、日数を引数で必ず指定することである。(未指定の場合のハンドリングは特に考慮していない。)

SQL> @mkscr 1   <=== 必ず日数を引数で指定する SQL> set echo off
-- 2016/12/06 00
define report_type=html
define num_days=1
define begin_snap=4686
define end_snap=4687
define report_name=awrrpt_1_4686_4687.html
@?/rdbms/admin/awrrpt.sql
-- 2016/12/06 01
define report_type=html
define num_days=1
define begin_snap=4687
define end_snap=4688
define report_name=awrrpt_1_4687_4688.html
.................................................
-- 2016/12/06 22
define report_type=html
define num_days=1
define begin_snap=4708
define end_snap=4709
define report_name=awrrpt_1_4708_4709.html
@?/rdbms/admin/awrrpt.sql
-- 2016/12/06 23
define report_type=html
define num_days=1
define begin_snap=4709
define end_snap=4710
define report_name=awrrpt_1_4709_4710.html
@?/rdbms/admin/awrrpt.sql
SQL> set feedback on
SQL> set verify on
SQL> !ls -l getawrr.sql
-rw-r--r--. 1 oracle oinstall 4128 12月 7 17:24 2016 getawrr.sql
SQL> @getawrr.sql    <== 生成されたスクリプトを実行する
(以下、AWRレポート作成)

これで複数(1日分であれば24個)のAWRレポートが一気に作成される。(レポート内容によっては時間がかかるので、実行タイミングはDBサーバの負荷状況に留意したほうがよいだろう。)

特定の時間帯だけが必要であれば、該当部分をコピペで選択して実行してもよい。

カレントディレクトリに出力されるので、実行するディレクトリをどこにするかを考慮した方がよいかもしれない。

もっと手抜きして、作成用スクリプトの最終行に「@生成スクリプト名」を追記すれば、一気に作成まで行うことができる。(今回は生成スクリプトの内容を一度確認することも考慮して別に実行することとした。)

明日はYousuke Yadaさんです。

USE_INVISIBLE_INDEXESヒントについて(続編)

不可視索引のその後

先日、不可視索引はUSE_INVISIBLE_INDEXESヒントと共に使おうという記事を書いたのだが、以下の記述に関してどうやら違う挙動となるらしいことがわかった。


INDEXヒント+USE_INVISIBLE_INDEXESヒント

基本的にUSE_INVISIBLE_INDEXESヒントを指定するだけでよいのだが、もし複数の不可視索引が定義されていたりする場合は、どのインデックスを使用するべきかをINDEXヒントで明確に指定することができる。


具体的には、複数の不可視索引が定義してある場合、INDEXヒントで明確に指定している不可視索引以外の不可視索引も使用されるようだ。

この部分を詳細に再検証してみたいと思う。

複数の不可視索引が存在する場合を検証する

検証環境

今回の検証で使用した環境は以下の通りである。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> show user
USER is "SH"

複数の索引を同時に使用するケースを考える

B*ツリー索引は、原則として1つの問合せブロックの中で1つだけ使用される。1つのSQL文の中で同時に2つ以上のB*ツリー索引を使うためには2つ以上の問合せブロックを組み合わせる必要がある。

今回の検証では、問合せ自体はなるべく簡単にしたいので、B*ツリー索引ではなくビットマップ索引を使用する。

SH.SALES表に定義してある(ビットマップ)索引の状況を確認すると以下のようになる。

SQL> select
  2   ui.TABLE_NAME
  3  ,ui.INDEX_NAME
  4  ,uic.COLUMN_NAME
  5  ,ui.INDEX_TYPE
  6  ,ui.VISIBILITY
  7  from
  8   USER_INDEXES     ui
  9  ,USER_IND_COLUMNS uic
 10  where ui.TABLE_NAME = 'SALES'
 11  and   ui.TABLE_NAME = uic.TABLE_NAME
 12  and   ui.INDEX_NAME = uic.INDEX_NAME
 13  order by
 14   ui.INDEX_NAME;

TABLE_NAME  INDEX_NAME         COLUMN_NAME  INDEX_TYPE  VISIBILIT
----------- ------------------ ------------ ----------- ---------
SALES       SALES_CHANNEL_BIX  CHANNEL_ID   BITMAP      VISIBLE
SALES       SALES_CUST_BIX     CUST_ID      BITMAP      VISIBLE
SALES       SALES_PROD_BIX     PROD_ID      BITMAP      VISIBLE
SALES       SALES_PROMO_BIX    PROMO_ID     BITMAP      VISIBLE
SALES       SALES_TIME_BIX     TIME_ID      BITMAP      VISIBLE

基本問合せ

基本となる問合せは以下のとおり。
2つの絞り込み条件により、SALES表にアクセスする。

SQL> select count(*) from SALES
  2  where CUST_ID    = 25939
  3  and   CHANNEL_ID = 3;

  COUNT(*)
----------
       159

Execution Plan
----------------------------------------------------------
Plan hash value: 228738440

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |     8 |    58   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE               |                   |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL         |                   |    33 |   264 |    58   (0)| 00:00:01 |     1 |    28 |
|   3 |    BITMAP CONVERSION COUNT    |                   |    33 |   264 |    58   (0)| 00:00:01 |       |       |
|   4 |     BITMAP AND                |                   |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE| SALES_CUST_BIX    |       |       |            |          |     1 |    28 |
|*  6 |      BITMAP INDEX SINGLE VALUE| SALES_CHANNEL_BIX |       |       |            |          |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("CUST_ID"=25939)
   6 - access("CHANNEL_ID"=3)

2つのビットマップ索引を使い、それぞれ絞り込んだ結果を「BITMAP AND」操作(Id=4)により両方の条件を満たす集合を作り、件数に変換して結果を得ていることがわかる。(SALES表には一切アクセスしていない。)

索引SALES_CUST_BIXを不可視にする

次に、索引SALES_CUST_BIXを不可視に変更し、同じ問合せを行ってみよう。

SQL> alter index SALES_CUST_BIX invisible;

Index altered.

SQL> select count(*) from SALES
  2  where CUST_ID    = 25939
  3  and   CHANNEL_ID = 3;

  COUNT(*)
----------
       159

Execution Plan
----------------------------------------------------------
Plan hash value: 3519235612

----------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |     8 |   489   (2)| 00:00:06 |       |       |
|   1 |  SORT AGGREGATE      |       |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|       |    33 |   264 |   489   (2)| 00:00:06 |     1 |    28 |
|*  3 |    TABLE ACCESS FULL | SALES |    33 |   264 |   489   (2)| 00:00:06 |     1 |    28 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CUST_ID"=25939 AND "CHANNEL_ID"=3)

CUST_IDに比べ、CHANNEL_IDのカーディナリティが低いため、CUST_IDの絞り込みに索引が使えなくなった途端、実行計画はSALES表に対する全件検索へと変わっていることがわかる。

索引SALES_CHANNEL_BIXを不可視にする

引き続き、索引SALES_CHANNEL_BIXを不可視にする。

SQL> alter index SALES_CHANNEL_BIX invisible;

Index altered.

SQL> select count(*) from SALES
  2  where CUST_ID    = 25939
  3  and   CHANNEL_ID = 3;

  COUNT(*)
----------
       159

Execution Plan
----------------------------------------------------------
Plan hash value: 3519235612

----------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |     8 |   489   (2)| 00:00:06 |       |       |
|   1 |  SORT AGGREGATE      |       |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|       |    33 |   264 |   489   (2)| 00:00:06 |     1 |    28 |
|*  3 |    TABLE ACCESS FULL | SALES |    33 |   264 |   489   (2)| 00:00:06 |     1 |    28 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CUST_ID"=25939 AND "CHANNEL_ID"=3)

索引SALES_CUST_BIXが使用不可(不可視)となっていることで、既に実行計画は全件検索となっているので、実行計画に変化はない。

ここまでで、SALES表の索引のうち2つを不可視に変更したことになる。

SQL> select
  2   ui.TABLE_NAME
  3  ,ui.INDEX_NAME
  4  ,uic.COLUMN_NAME
  5  ,ui.INDEX_TYPE
  6  ,ui.VISIBILITY
  7  from
  8   USER_INDEXES     ui
  9  ,USER_IND_COLUMNS uic
 10  where ui.TABLE_NAME = 'SALES'
 11  and   ui.TABLE_NAME = uic.TABLE_NAME
 12  and   ui.INDEX_NAME = uic.INDEX_NAME
 13  order by
 14   ui.INDEX_NAME;

TABLE_NAME  INDEX_NAME         COLUMN_NAME  INDEX_TYPE  VISIBILIT
----------- ------------------ ------------ ----------- ---------
SALES       SALES_CHANNEL_BIX  CHANNEL_ID   BITMAP      INVISIBLE
SALES       SALES_CUST_BIX     CUST_ID      BITMAP      INVISIBLE
SALES       SALES_PROD_BIX     PROD_ID      BITMAP      VISIBLE
SALES       SALES_PROMO_BIX    PROMO_ID     BITMAP      VISIBLE
SALES       SALES_TIME_BIX     TIME_ID      BITMAP      VISIBLE

USE_INVISIBLE_INDEXESヒントを指定する(INDEXヒントは使用しない)

ここで、USE_INVISIBLE_INDEXESヒントを指定して問合せを実行してみる。
2つの不可視索引が使えるようになるので、最初と同じ実行計画となるはずである。

SQL> select /*+ USE_INVISIBLE_INDEXES */
  2   count(*) from SALES
  3  where CUST_ID    = 25939
  4  and   CHANNEL_ID = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 228738440

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |     8 |    58   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE               |                   |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL         |                   |    33 |   264 |    58   (0)| 00:00:01 |     1 |    28 |
|   3 |    BITMAP CONVERSION COUNT    |                   |    33 |   264 |    58   (0)| 00:00:01 |       |       |
|   4 |     BITMAP AND                |                   |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE| SALES_CUST_BIX    |       |       |            |          |     1 |    28 |
|*  6 |      BITMAP INDEX SINGLE VALUE| SALES_CHANNEL_BIX |       |       |            |          |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("CUST_ID"=25939)
   6 - access("CHANNEL_ID"=3)

想定通りの結果となった。

USE_INVISIBLE_INDEXESヒントとINDEXヒントを明示的に指定する

次に、INDEXヒントでSALES SALES_CUST_BIXのみの使用を明示的に指定してみる。
INDEXヒントで使用される索引を限定することが出来るのであれば、実行計画は別のものになることが予想される。

SQL> select /*+ USE_INVISIBLE_INDEXES
  2             INDEX(SALES SALES_CUST_BIX) */
  3   count(*) from SALES
  4  where CUST_ID    = 25939
  5  and   CHANNEL_ID = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 228738440

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |     8 |    58   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE               |                   |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL         |                   |    33 |   264 |    58   (0)| 00:00:01 |     1 |    28 |
|   3 |    BITMAP CONVERSION COUNT    |                   |    33 |   264 |    58   (0)| 00:00:01 |       |       |
|   4 |     BITMAP AND                |                   |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE| SALES_CUST_BIX    |       |       |            |          |     1 |    28 |
|*  6 |      BITMAP INDEX SINGLE VALUE| SALES_CHANNEL_BIX |       |       |            |          |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("CUST_ID"=25939)
   6 - access("CHANNEL_ID"=3)

INDEXヒントに指定した索引とは別の索引SALES_CHANNEL_BIXも使用されていることがわかる。

つまりINDEXヒントだけでは使用される索引を特定することが出来ないことがわかった。

使用しない索引をNO_INDEXヒントで明示する

使用したくない方の不可視索引を明示的に指定するには、以下のようにNO_INDEXヒントを使う。

SQL> select /*+ USE_INVISIBLE_INDEXES
  2             INDEX(SALES SALES_CUST_BIX)
  3             NO_INDEX(SALES SALES_CHANNEL_BIX) */
  4   count(*) from SALES
  5  where CUST_ID    = 25939
  6  and   CHANNEL_ID = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 2288362790

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |     8 |    54   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                     |                |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL               |                |    33 |   264 |    54   (0)| 00:00:01 |     1 |    28 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |    33 |   264 |    54   (0)| 00:00:01 |     1 |    28 |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE      | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CHANNEL_ID"=3)
   5 - access("CUST_ID"=25939)

索引SALES_CUST_BIXのみを使用する実行計画となった。

索引SALES_CUST_BIXを可視に変更する

今まで不可視だった索引SALES_CUST_BIXを可視に変更して問合せを実行してみる。
この状態では索引SALES_CHANNEL_BIXのみが使用不可である。

SQL> alter index SALES_CUST_BIX visible;

Index altered.

SQL> select count(*) from SALES
  2  where CUST_ID    = 25939
  3  and   CHANNEL_ID = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 2288362790

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |     8 |    54   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                     |                |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL               |                |    33 |   264 |    54   (0)| 00:00:01 |     1 |    28 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |    33 |   264 |    54   (0)| 00:00:01 |     1 |    28 |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE      | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CHANNEL_ID"=3)
   5 - access("CUST_ID"=25939)

1つ前と同じ実行計画となっていることがわかる。

まとめ

  • USE_INVISIBLE_INDEXESヒントを指定するとSQL文単位で使える不可視索引が全てCBOの評価対象となるので、INDEXヒントで使用したい索引を特定しようとしても結果として無視される。
  • 複数の不可視索引を作成し順番にテストするような場合、使いたくない索引をNO_INDEXヒントで指定しないと意図したテストとならない可能性があるので注意が必要である。

これらは、マニュアルにもMy Oracle Supportにも記述されていなかったので、不可視索引を使いこなす場合に覚えておきたい事実である。

今回はここまで

不可視索引はUSE_INVISIBLE_INDEXESヒントと共に使おう

不可視索引とは

不可視索引とはオプティマイザから「見えない」という意味で不可視である。(12c概要 索引の使用可能性と可視性 参照)

オプティマイザに影響を与えないので、通常の索引(可視索引)作成で既存の実行計画を不用意に変えてしまうリスクを回避することができる、11g以降で実装されている機能である。

不可視索引の用途

本番運用が始まってから、「この列にインデックスが必要だ。」とか「このインデックスはどうも使われていないようなので削除しよう。」という定義変更のニーズが発生することは多々ある。

上記マニュアルには

  1. 索引を削除する前に削除をテストする場合
  2. アプリケーション全体に影響を与えることなく一時的に索引を使用する場合

という2つの使い方の例が示されているが、2.の「一時的」とはそのインデックスの有効性を確認するテストなので、(恒常的な)運用に乗せるためには

SQL> ALTER INDEX <インデックス名> VISIBLE;

として、不可視可視とする必要があると、マニュアルや多くのブログ記事等には書いてある。

というようなことを先日あるプロジェクトの人に話したところ「可視化した時点でアプリケーション全体に影響を与える可能性があるので望ましくない。SQL単位で可視化を制御する方法はないのか?」と質問された。

確かにもっともな意見である。普段いろいろ教える機会が多いのだが、実際に使う側の目線で本質的な問いを投げかけられるとハッとさせられる。

SQL単位でとなると答えはヒント句による制御しかない。

不可視索引関連のヒント

結論から先に言うと、不可視索引関連のヒントは

  • USE_INVISIBLE_INDEXES(NO_USE_INVISIBLE_INDEXES)

であるが、残念ながらSQLリファレンス・マニュアルにはこのヒントに関する記述がない。

(以前、Oracleバージョンによるヒント句の変遷 という記事を書いたのでそちらを参照してもらいたい。)

ヒント句を検証してみた

不可視索引の作成

検証を行うためにEMP表のHIRE_DATE列に不可視索引を作成する。

SQL> create index EMP_HIRE_DATE_IX on EMPLOYEES (HIRE_DATE) invisible;

Index created.

SQL> select TABLE_NAME,INDEX_NAME,VISIBILITY from user_indexes
  2  where VISIBILITY != 'VISIBLE'
  3  order by TABLE_NAME,INDEX_NAME;

TABLE_NAME                     INDEX_NAME                     VISIBILIT
------------------------------ ------------------------------ ---------
EMPLOYEES                      EMP_HIRE_DATE_IX               INVISIBLE

基本動作(不可視索引はそのままでは使われない)

不可視索引はデフォルトではオプティマイザに使用されないので、条件検索は全件検索からのフィルタ処理となる。(Id=1)

SQL> select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
  2  where HIRE_DATE >= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
----------- -------------------- ------------------------- ----------------
        167 Amit                 Banda                     2008/04/21 00:00
        173 Sundita              Kumar                     2008/04/21 00:00


Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    27 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    27 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("HIRE_DATE">=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

OPTIMIZER_USE_INVISIBLE_INDEXESパラメータをセッション単位で変更

不可視索引を使用するためにはALTER SESSIONコマンドにより、OPTIMIZER_USE_INVISIBLE_INDEXESパラメータをセッション単位で変更する。
(ALTER SYSTEMコマンドによりインスタンスで使用可能に変更することもできるが、不可視索引として作成する意味がないので現実的ではない。)

SQL> show parameter optimizer_use_invisible_indexes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE

SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> show parameter optimizer_use_invisible_indexes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     TRUE

この状態で先ほどの問合せを実行すると、使用されなかったインデックスEMP_HIRE_DATE_IXが使用されるようになったことがわかる。

SQL> select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES  
  2  where HIRE_DATE >= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
----------- -------------------- ------------------------- ----------------
        167 Amit                 Banda                     2008/04/21 00:00
        173 Sundita              Kumar                     2008/04/21 00:00


Execution Plan
----------------------------------------------------------
Plan hash value: 3345584716

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    27 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES        |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_HIRE_DATE_IX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - access("HIRE_DATE">=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

ヒント句を試してみる

ヒント句を試す前に、セッションを再接続することでクリアし、OPTIMIZER_USE_INVISIBLE_INDEXESパラメータがデフォルトの「FALSE」に戻っていることを確認する。

SQL> conn hr/hr
Connected.
SQL> show parameter optimizer_use_invisible_indexes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE

INDEXヒントのみ

まず、INDEXヒントにより不可視索引を指定した場合どのような挙動になるのかを確認する。

SQL> select /*+ INDEX (employees emp_hire_date_ix) */
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
  3  where HIRE_DATE >= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
----------- -------------------- ------------------------- ----------------
        167 Amit                 Banda                     2008/04/21 00:00
        173 Sundita              Kumar                     2008/04/21 00:00


Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    27 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    27 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("HIRE_DATE">=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

オプティマイザがこのインデックスを使用するようになっていないため、ヒント句でインデックス名を指定したとしても無視されることがわかる。

USE_INVISIBLE_INDEXESヒントのみ

次に、USE_INVISIBLE_INDEXESヒントをヒント句で指定してみる。

SQL> select /*+ USE_INVISIBLE_INDEXES */
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
  3  where HIRE_DATE >= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
----------- -------------------- ------------------------- ----------------
        167 Amit                 Banda                     2008/04/21 00:00
        173 Sundita              Kumar                     2008/04/21 00:00


Execution Plan
----------------------------------------------------------
Plan hash value: 3345584716

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    27 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES        |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_HIRE_DATE_IX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - access("HIRE_DATE">=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

今度は、インデックスを使用するようになった。

INDEXヒント+USE_INVISIBLE_INDEXESヒント

基本的にUSE_INVISIBLE_INDEXESヒントを指定するだけでよいのだが、もし複数の不可視索引が定義されていたりする場合は、どのインデックスを使用するべきかをINDEXヒントで明確に指定することができる。 (この部分は事実と異なる。NO_INDEXヒントにより使いたくない索引も明示的に指定する必要がある。 USE_INVISIBLE_INDEXESヒントについて(続編) 参照)

SQL> select /*+ USE_INVISIBLE_INDEXES INDEX(EMPLOYEES EMP_HIRE_DATE_IX)  */
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
  3  where HIRE_DATE >= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
----------- -------------------- ------------------------- ----------------
        167 Amit                 Banda                     2008/04/21 00:00
        173 Sundita              Kumar                     2008/04/21 00:00


Execution Plan
----------------------------------------------------------
Plan hash value: 3345584716

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    27 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES        |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_HIRE_DATE_IX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - access("HIRE_DATE">=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

SQL文の可読性を向上させるために、使用したいインデックスを明示的に指定した方がよいかもしれない。

前々回、前回とWHERE句とインデックスの関係を調査する

という2つの記事を書いたが、新しく作成するインデックスが及ぼす影響を網羅性を担保しつつ調査することは、インデックスの数が多いほど大変な作業である。

ヒント句で有効にした不可視索引はアプリケーション全体に影響を与えることなく性能を改善することができる。不可視索引を使うには原則としてヒント句を共に使うべきであるというのが私の提言である。

今日はここまで。

インデックスとSQLの関係を調査する

前回の投稿他

前回の投稿ではsys.col_usage$表を使って、あるカラムに関するWHERE句(Predicate)の状況を分析する要領を紹介した。

一方、昨年「V$SQL_PLANでCRUD表モドキを作ってみる③」という記事を書いたのだが、応用編として


インデックス – SQL
V$SQL_PLANを使えば、テーブルとSQLの関係だけでなく、インデックスとSQLの関係を分析することもできます。
例えば、あるインデックスの定義を変更しようとする場合、1つのSQLだけに注目してしまうと他のSQLに影響があることに気づかず新たな問題を引き起こしてしまうかもしれません。
そのような場合、インデックスとSQLの相関表が役に立ちます。


ということを紹介しただけで終わっていた。

最近、実業務でインデックスとSQL(SQL_ID)の関係を一覧化する機会があったので、その要領を紹介しておこうと思う。

調査用SQL

基本はV$SQL_PLANでこれにDBA_INDEXESを結合して所有者とテーブルの情報を取得している。

21行目の「OPERATION = ‘INDEX’」の条件でインデックス検索に関わるオペレーションに絞り込み
22行目(ハイライト行)で分析対象となるスキーマ名(例ではSCOTT)を指定している。

前述のCRUD表を作る試みでは、INSERT,SELECT,UPDATE,DELETEを区別していたが、インデックスとSQLの関係においては、WHERE条件によりインデックスを使用してデータにアクセスしたりフィルタリングしたりすることはINSERTを除き同じ挙動であるので、この調査用SQLではSELECT,UPDATE,DELETEの区別を特に行っていない。

select distinct
i.OWNER
,i.TABLE_NAME
,s.OBJECT_NAME INDEX_NAME
,s.SQL_ID
,s.PLAN_HASH_VALUE
,s.ID
,s.OPERATION ||' '|| s.OPTIONS operation
,case when s.ACCESS_PREDICATES is not null
then 'access: '||ACCESS_PREDICATES
when s.FILTER_PREDICATES is not null
then 'filter: '||FILTER_PREDICATES
else null
end as PREDICATES
from
V$SQL_PLAN  s
join
DBA_INDEXES i
on    s.OBJECT_OWNER = i.OWNER
and   s.OBJECT_NAME  = i.INDEX_NAME
where OPERATION      = 'INDEX'
and   i.OWNER        = 'SCOTT'
order by
i.OWNER
,i.TABLE_NAME
,s.OBJECT_NAME
,s.SQL_ID
,s.PLAN_HASH_VALUE
,s.ID
;

実行例

SQL> select distinct
2   i.OWNER
3  ,i.TABLE_NAME
4  ,s.OBJECT_NAME INDEX_NAME
5  ,s.SQL_ID
6  ,s.PLAN_HASH_VALUE
7  ,s.ID
8  ,s.OPERATION ||' '|| s.OPTIONS operation
9  ,case when s.ACCESS_PREDICATES is not null
10          then 'access: '||ACCESS_PREDICATES
11        when s.FILTER_PREDICATES is not null
12          then 'filter: '||FILTER_PREDICATES
13        else null
14   end as PREDICATES
15  from
16   V$SQL_PLAN  s
17  join
18   DBA_INDEXES i
19  on    s.OBJECT_OWNER = i.OWNER
20  and   s.OBJECT_NAME  = i.INDEX_NAME
21  where OPERATION      = 'INDEX'
22  and   i.OWNER        = 'SCOTT'
23  order by
24   i.OWNER
25  ,i.TABLE_NAME
26  ,s.OBJECT_NAME
27  ,s.SQL_ID
28  ,s.PLAN_HASH_VALUE
29  ,s.ID
30  ;

OWNER  TABLE_NAME  INDEX_NAME  SQL_ID        PLAN_HASH_VALUE         ID OPERATION            PREDICATES
------ ----------- ----------- ------------- --------------- ---------- -------------------- ---------------------
SCOTT  EMP         PK_EMP      0dmdbcy6g6qsf       169057108          2 INDEX RANGE SCAN     access: "EMPNO">0
SCOTT  EMP         PK_EMP      64xm083yya03t       204855851          2 INDEX UNIQUE SCAN    access: "EMPNO"=7876
SCOTT  EMP         PK_EMP      861msw504avnz      1749432681          2 INDEX FULL SCAN
SCOTT  EMP         PK_EMP      9gs6uhh2jvrb1      2949544139          2 INDEX UNIQUE SCAN    access: "EMPNO"=7369
SCOTT  EMP         PK_EMP      caggwncwwcugc      3659136155          2 INDEX UNIQUE SCAN    access: "EMPNO"=7369
SCOTT  EMP         PK_EMP      dq4cyhcs58mys      2400378433          2 INDEX FULL SCAN
SCOTT  EMP         PK_EMP      fmt934hxtucpa      2949544139          2 INDEX UNIQUE SCAN    access: "EMPNO"=7566

7 rows selected.

この例では、PK_EMPというインデックスのみであるが、インデックス毎にSQL_IDが複数存在している。1つのインデックスに紐付くSQLが多いものほど、インデックスを変更することによる影響範囲が広いことがわかる。

さらに、1つのSQL_IDに複数のPLAN_HASH_VALUEが存在している場合があれば、一つのPLAN_HASH_VALUEが複数のSQL_IDに紐付くこともある。(例:PLAN_HASH_VALUE=2949544139、ちなみにPLAN_HASH_VALUEとは実行計画に紐付くユニークな値である。)

つまり、SQL_IDとPLAN_HASH_VALUEは多対多の関係にある。

ID列は、実行計画ツリーから容易に当該インデックスの使用箇所を追跡しやすように表示させてみた。

OPERATION列はインデックス・スキャンの様々な種類を示している。

PREDICATES列は、「ACCESS/FILTER」の違いと実際のWHERE条件の抜粋を示している。

この情報と前回紹介したsys.col_usage$表を使ったWHERE句の分析結果を突き合わせることにより、パフォーマンス問題解決上有益な情報が得られるかもしれない。

今日はここまで

sys.col_usage$表でWHERE句を分析する

sys.col_usage$とは

sys.col_usage$を理解するためにはまずヒストグラムを理解する必要がある。

ヒストグラムとはCBOが使用する列分布情報を保持するものであり、列データの分布が不均一な場合はヒストグラムの情報を使用してより良い実行計画を選択する。

ヒストグラムは列データの偏りが高い場合に有用なので、次のような状況では有用ではなく、つまりヒストグラムを作成する意味がない。

  • WHERE句内で指定しない列
    • 絞り込み条件として使用しない列にヒストグラムを作っても無駄
  • 均一な分布
    • データの偏りがない場合
  • 一意な列を含む等価述語

OracleはDBMS_STATSパッケージよって統計情報を取得する際、ヒストグラムを取得すべき列を特定する情報を収集している。
この情報はSMONによって取得されsys.col_usage$表に保持される。

「CBOに関する統計情報は、バックグラウンドプロセスのシステムモニタ(SMON)によってテーブルに記録される。そのようなテーブルの1つ、COL_USAGE$テーブルは、SELECTクエリで使用される述語、つまり、WHERE節で使用される列、および、等号、LIKE、範囲など、述語の種類に関する情報を記録するのに使われる。10g Release 2では、SMONプロセスが20分ごとにこのテーブルを更新する。」
Oracleフォレンジック 第5部 無監査時のデータ窃盗の証拠調査 から

sys.col_usage$表のカラム

sys.col_usage$表各カラムには当該WHERE条件が実行された回数が格納される

  • EQUALITY_PREDS :等価条件
  • EQUIJOIN_PREDS:等価結合条件
  • NONEQUIJOIN_PREDS:不等価結合条件
  • RANGE_PREDS:範囲検索条件
  • LIKE_PREDS:LIKE(またはNOT LIKE)検索条件
  • NULL_PREDS:NULL(またはNOT NULL)検索条件

sys.col_usage$表でWHERE句の分析をする。

sys.col_usage$表は前述のとおり、本来はOracleがヒストグラム作成のために情報を格納する内部表であるが、この表を使えば、(スキーマ)、テーブル、カラム単位でWHERE句にどのような条件が指定されているかを一覧表示させることができる。
19〜33行目で表示させたくないスキーマを指定している。
(右スクロールしてCOLUMN_NAME列よりも右側を表示させる。)

SQL> select
  2   u.NAME             OWNER
  3  ,o.NAME             TABLE_NAME
  4  ,c.NAME             COLUMN_NAME
  5  ,us.EQUALITY_PREDS
  6  ,us.EQUIJOIN_PREDS
  7  ,us.NONEQUIJOIN_PREDS
  8  ,us.RANGE_PREDS
  9  ,us.LIKE_PREDS
10  ,us.NULL_PREDS
11  --,to_char(us.TIMESTAMP,'yyyy/mm/dd hh24:mi:ss') TIMESTAMP
12  from
13        sys.col_usage$ us
14   join sys.obj$       o  on us.OBJ#    = o.OBJ#
15   join sys.col$       c  on us.OBJ#    = c.OBJ#
16                         and us.INTCOL# = c.INTCOL#
17   join sys.user$      u  on o.OWNER#   = u.USER#
18  where u.NAME not in (
19   'APEX_030200'
20  ,'CTXSYS'
21  ,'DBSNMP'
22  ,'EXFSYS'
23  ,'FLOWS_FILES'
24  ,'IX'
25  ,'MDSYS'
26  ,'OLAPSYS'
27  ,'ORDDATA'
28  ,'ORDSYS'
29  ,'SYS'
30  ,'SYSMAN'
31  ,'SYSTEM'
32  ,'WMSYS'
33  ,'XDB'
34  )
35  order by
36   u.NAME
37  ,o.NAME
38  ,c.INTCOL#
39  ;
 
OWNER  TABLE_NAME                     COLUMN_NAME                    EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS
------ ------------------------------ ------------------------------ -------------- -------------- ----------------- ----------- ---------- ----------
OE     CATEGORIES_TAB                 CATEGORY_ID                                 1              0                 0           0          0          0
OE     INVENTORIES                    PRODUCT_ID                                  1              0                 0           0          0          0
OE     INVENTORIES                    WAREHOUSE_ID                                0              1                 0           0          0          0
OE     PRODUCT_INFORMATION            CATEGORY_ID                                 1              0                 0           0          0          0
OE     WAREHOUSES                     WAREHOUSE_ID                                0              1                 0           0          0          0
SCOTT  EMP                            EMPNO                                       2              0                 0           1          0          0
SH     CHANNELS                       CHANNEL_ID                                  0              1                 0           0          0          0
SH     CHANNELS                       CHANNEL_CLASS_ID                            0              1                 0           0          0          0
SH     CHANNELS                       CHANNEL_TOTAL_ID                            0              1                 0           0          0          0
SH     COUNTRIES                      COUNTRY_ID                                  0              1                 0           0          0          0
SH     COUNTRIES                      COUNTRY_SUBREGION_ID                        0              1                 0           0          0          0
SH     COUNTRIES                      COUNTRY_REGION_ID                           0              1                 0           0          0          0
SH     COUNTRIES                      COUNTRY_TOTAL_ID                            0              1                 0           0          0          0
SH     CUSTOMERS                      CUST_ID                                     0              1                 0           0          0          0
SH     CUSTOMERS                      CUST_CITY_ID                                0              1                 0           0          0          0
SH     CUSTOMERS                      CUST_STATE_PROVINCE_ID                      0              1                 0           0          0          0
SH     CUSTOMERS                      COUNTRY_ID                                  0              1                 0           0          0          0
SH     CUSTOMERS                      CUST_TOTAL_ID                               0              1                 0           0          0          0
SH     PRODUCTS                       PROD_ID                                     0              1                 0           0          0          0
SH     PRODUCTS                       PROD_SUBCATEGORY_ID                         0              1                 0           0          0          0
SH     PRODUCTS                       PROD_CATEGORY_ID                            0              1                 0           0          0          0
SH     PRODUCTS                       PROD_TOTAL_ID                               0              1                 0           0          0          0
SH     PROMOTIONS                     PROMO_ID                                    0              1                 0           0          0          0
SH     PROMOTIONS                     PROMO_SUBCATEGORY_ID                        0              1                 0           0          0          0
SH     PROMOTIONS                     PROMO_CATEGORY_ID                           0              1                 0           0          0          0
SH     PROMOTIONS                     PROMO_TOTAL_ID                              0              1                 0           0          0          0
SH     SALES                          PROD_ID                                     1              1                 0           0          0          0
SH     SALES                          TIME_ID                                     1              1                 0           0          0          0
SH     TIMES                          TIME_ID                                     0              1                 0           0          0          0
SH     TIMES                          WEEK_ENDING_DAY_ID                          0              1                 0           0          0          0
SH     TIMES                          CALENDAR_MONTH_ID                           0              1                 0           0          0          0
SH     TIMES                          FISCAL_MONTH_ID                             0              1                 0           0          0          0
SH     TIMES                          CALENDAR_QUARTER_ID                         0              1                 0           0          0          0
SH     TIMES                          FISCAL_QUARTER_ID                           0              1                 0           0          0          0
SH     TIMES                          CALENDAR_YEAR_ID                            0              1                 0           0          0          0
SH     TIMES                          FISCAL_YEAR_ID                              0              1                 0           0          0          0
 
36 rows selected.

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

JPOUG Advent Calendar 2015 13日目のエントリーです。このイベントは初めての参加、かつブログ投稿自体久しぶりとなりましたのでよろしくお願いします。前日はTakahiro YAMADAさんでした。

Oracleの実行計画ツリーって分かりやすい。。。だけどたどりにくい?

実行計画ツリー(マニュアルでは「行ソース・ツリー」)の解析は、OracleデータベースのSQLチューニング作業においては基本中の基本です。

実行計画ツリーは、OracleのSQL実行が階層的に逐次処理されていることを直感的に把握しやすく、どこにボトルネックが存在するかも分かりやすい表示だと思います。

実行計画ツリーの表示方法は以下のリンクになります。
12.4 PLAN_TABLE出力の表示

右から左、上から下へ

11gR2パフォーマンス・チューニング・ガイドの19.2.3 グローバル表のヒントの指定方法の例を題材に実行計画ツリーの見方をおさらいします。

まずは、ビューの作成から。

SQL> CREATE OR REPLACE VIEW V AS
  2    SELECT
  3       e1.first_name
  4     , e1.last_name
  5     , j.job_id
  6     , sum(e2.salary) total_sal
  7    FROM
  8       employees                     e1
  9     , ( SELECT * FROM employees e3) e2
 10     , job_history                   j
 11    WHERE e1.employee_id = e2.manager_id
 12    AND   e1.employee_id = j.employee_id
 13    AND   e1.hire_date   = j.start_date
 14    AND   e1.salary      = ( SELECT
 15                               max(e2.salary)
 16                              FROM
 17                               employees e2
 18                              WHERE e2.department_id = e1.department_id
 19                            )
 20    GROUP BY e1.first_name, e1.last_name, j.job_id
 21    ORDER BY total_sal;

ビューが作成されました。

次に、Expain Plan文でSQL文の解析結果をPLAN TABLEに格納し、DBMS_XPLANプロシージャで実行計画ツリーを確認します。

SQL> explain plan for
  2  SELECT /*+ INDEX(v.e2.e3 emp_job_ix) */  * FROM V;
 
解析されました。

SQL> select plan_table_output from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 447943362
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                         |     1 |    46 |    17  (18)| 00:00:01 |
|   1 |  VIEW                              | V                       |     1 |    46 |    17  (18)| 00:00:01 |
|   2 |   SORT ORDER BY                    |                         |     1 |    79 |    17  (18)| 00:00:01 |
|   3 |    HASH GROUP BY                   |                         |     1 |    79 |    17  (18)| 00:00:01 |
|   4 |     NESTED LOOPS                   |                         |       |       |            |          |
|   5 |      NESTED LOOPS                  |                         |     1 |    79 |    15   (7)| 00:00:01 |
|   6 |       NESTED LOOPS                 |                         |     1 |    71 |     6  (17)| 00:00:01 |
|   7 |        NESTED LOOPS                |                         |     1 |    50 |     5  (20)| 00:00:01 |
|   8 |         VIEW                       | VW_SQ_1                 |     1 |    16 |     4  (25)| 00:00:01 |
|*  9 |          FILTER                    |                         |       |       |            |          |
|  10 |           HASH GROUP BY            |                         |     1 |     7 |     4  (25)| 00:00:01 |
|  11 |            TABLE ACCESS FULL       | EMPLOYEES               |   107 |   749 |     3   (0)| 00:00:01 |
|* 12 |         TABLE ACCESS BY INDEX ROWID| EMPLOYEES               |     1 |    34 |     1   (0)| 00:00:01 |
|* 13 |          INDEX RANGE SCAN          | EMP_DEPARTMENT_IX       |    10 |       |     0   (0)| 00:00:01 |
|  14 |        TABLE ACCESS BY INDEX ROWID | JOB_HISTORY             |     1 |    21 |     1   (0)| 00:00:01 |
|* 15 |         INDEX UNIQUE SCAN          | JHIST_EMP_ID_ST_DATE_PK |     1 |       |     0   (0)| 00:00:01 |
|  16 |       INDEX FULL SCAN              | EMP_JOB_IX              |   107 |       |     1   (0)| 00:00:01 |
|* 17 |      TABLE ACCESS BY INDEX ROWID   | EMPLOYEES               |     1 |     8 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   9 - filter(MAX("E2"."SALARY")>0)
  12 - filter("E1"."SALARY"="MAX(E2.SALARY)")
  13 - access("ITEM_1"="E1"."DEPARTMENT_ID")
  15 - access("E1"."EMPLOYEE_ID"="J"."EMPLOYEE_ID" AND "E1"."HIRE_DATE"="J"."START_DATE")
  17 - filter("E1"."EMPLOYEE_ID"="E3"."MANAGER_ID")
 
33行が選択されました。

行ソース・オペレーション(Operation)の実行順をIDで表すと

11 → 10 → 9 → 8 → 13 → 12 → 7 → 15 → 14 → 6 → 16 → 5 → 17 → 4 → 3 → 2 → 1 → 0

となります。

原則は「右から左、同じレベルは上から下」です。

実際にOracleの中でこの順番に処理が行われているかどうかはわかりませんが、論理的な道筋としてはこのようになります。

SQL文のコストは先に行われたオペレーションのコストに後続のコストが積み上がっていくことによって決まるので、コストが高いオペレーションの中でもより早く実行されるものに着目する必要があります。

しかし、実行計画ツリーは実行計画全体を直感的に把握するには役立ちますが、実際のチューニング作業で実行順序を理解するのは難しく感じます。

例えば、ID=6とID=16が同じ階層にあるということを確認するために、ディスプレイに透明な定規を当ててみた、なんて経験はありませんか?私はよくあります。

秘密兵器を開発してみた

以前から実行順序をもっと簡単に確認する術はないだろうかということを考えていたのですが、以下の要領で確認用スクリプトを実行した結果を紹介します。
(結果をHTMLファイルにスプール出力しますので、一時的に画面出力をオフにしています。)

SQL> set termout off
SQL> @execplan
SQL> set termout on

↓実行結果はこちら
execplan.html

左端(SEQ列)は実行順序になります。そして右端(ID列)は通常の実行計画ツリーにおけるID列と同じです。つまり、実行計画ツリーとこのスクリプトの実行結果を併用して使うことを意識しています。

上から順番に読んでいって、コストが急激に増加しているオペレーションに注目します。(この例は問題ありませんが)

PREDICATES列やPROJECTION列を参考にコストをより小さくするようにチューニングします。(これらの列を表示したいためにあえてHTMLで出力しました。)

スクリプト公開

実際のスクリプトは以下のとおりです。

PLAN_TABLEの内容を加工して出力しているだけですので悪さはしませんが、自己責任でお使いください。

execplan.sql

SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP OFF -
HEAD "<TITLE>Execution Plan</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFFF} --> -
</STYLE>" -
BODY "TEXT='#000000'" -
TABLE "WIDTH='90%' BORDER='1'"
set pages 200
set lines 300
set feed off
spool execplan.html
select
 rownum seq
,pt.OPERATION
,pt.OBJECT_NAME
,pt.ALIAS
,pt.TYPE
,pt.COST
,pt.CARDINALITY
,pt.PREDICATES
,pt.PROJECTION
,pt.ID
from (
select distinct
 ID
,PARENT_ID PID
,POSITION
,DEPTH
,case ID - DEPTH when 0 then DEPTH
                        else DEPTH -1
 end as XDEPTH
,OPERATION||' '||OPTIONS OPERATION
,OBJECT_NAME
,OBJECT_ALIAS ALIAS
,OBJECT_TYPE TYPE
,OBJECT_INSTANCE
,COST
,CARDINALITY
,case when ACCESS_PREDICATES is not null
        then 'access: '||ACCESS_PREDICATES
      when FILTER_PREDICATES is not null
        then 'filter: '||FILTER_PREDICATES
      else null
 end as PREDICATES
,PROJECTION
from PLAN_TABLE
order by
 XDEPTH desc
,POSITION desc
,ID
) pt;
spool off
SET MARKUP HTML OFF

ポイントはDEPTH

このスクリプトのポイントは「DEPTH」列の扱いです。単純にDEPTH列の降順でソートしても正しい結果が得られないのです。

そこで、ID <> DEPTH となる場合のみ DEPTH値から1引くというロジックを入れてみました。(XDEPTH列)

以下のクエリー、特にorder by句に注目してみてください。

SQL> select
  2   ID
  3  ,PARENT_ID PID
  4  ,DEPTH
  5  ,case ID - DEPTH when 0 then DEPTH
  6                          else DEPTH -1
  7   end as XDEPTH
  8  ,POSITION
  9  from PLAN_TABLE
10  order by
11   XDEPTH desc
12  ,POSITION desc
13  ,ID;
 
ID  PID DEPTH XDEPTH POSITION
--- --- ----- ------ --------
 11  10    11     11        1
 10   9    10     10        1
  9   8     9      9        1
  8   7     8      8        1
 13  12     9      8        1
 12   7     8      7        2
  7   6     7      7        1
 15  14     8      7        1
 14   6     7      6        2
  6   5     6      6        1
 16   5     6      5        2
  5   4     5      5        1
 17   4     5      4        2
  4   3     4      4        1
  3   2     3      3        1
  2   1     2      2        1
  1   0     1      1        1
  0         0      0       17
 
18行が選択されました。

  • 常にexecplan.htmlというファイルに出力している。
  • V$SQL_PLANに対応していない。
  • パラレルクエリーに対応していない。

など、改良の余地ありありですが、ご自由にカスタマイズしてください。

最近チューニングの場面に遭遇する機会がめっきり減ってしまって、私自身このスクリプトを実践で試してはいないのですが、使用してみてコメントをいただけたら幸いです。

明日は渡部亮太さんです。(最近すごい勢いで投稿してるみたいですね!w)

日曜の夜に翌週の朝食を準備する

朝食はカルビーのフルーツグラノーラ

私はだいたい朝食にカルビーのフルーツグラノーラを食べています。

時々気分を変えてご飯やパンを食べたりもしますが、ここ15年以上はグラノーラに牛乳をかけたものとヨーグルトかチーズというのが定番の朝食です。

グラノーラの朝食

  • 栄養のバランスがいい(気がする)。
  • シンプルで飽きない。
  • すぐに用意できる。

というのがその理由ですが、仕事に出かける前の忙しい時でも気軽に食べられるのでとても気に入っています。

分量を計るのは面倒

確かに「すぐに用意できる」というメリットはあるのですが、牛乳200g・グラノーラ50gをその都度スケールで計るのはちょっと面倒です。

50gというのは微妙な量で、なかなか目分量で器に入れるのは難しいです。

そこで、忙しい朝のためにちょっと工夫をしてみることにしました。

50gずつ小分けにしておく

牛乳200gは計量カップで食べる直前すぐに計ることができるので、グラノーラを50gずつ小分けにしてジップロックの小さい方の袋に入れておきます。

50gずつ計る

コーヒー豆を計るスプーン1杯が約10gなので、5杯分を袋に入れて重さを確認し微調整します。

これを一週間分以上日曜日の夜に準備して、大き目のジップロックに入れておきます。

朝食を10秒で用意する

朝になったら一袋分のグラノーラを器にあけて、カップで計った牛乳を上からかけます。これだけだと10秒もかかりません。

この他にバナナをスプーンで切って入れたり、ヨーグルトやチーズを冷蔵庫から出す時間を合わせても1分以内に朝食の準備ができます。

工夫次第で忙しい朝も朝食を手早く準備できれば、余裕を持ってスタートできます。

お試しください!!