Tips」タグアーカイブ

実行統計による実践的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表示の経過時間と若干異なることに注意)

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

今回はここまで

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.

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

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

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

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

グラノーラの朝食

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

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

分量を計るのは面倒

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

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

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

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

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

50gずつ計る

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

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

朝食を10秒で用意する

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

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

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

お試しください!!