オプティマイザの動作を確認する

前回の投稿では、不可視索引の実践的な使い方の提言を行った。

今回は、オプティマイザが不可視索引を使用する際に、内部でどのようなことが起きているのかを調べてみる。

コストベース・オプティマイザの挙動を調べるには10053トレースを取得する。
（10053トレースにはハード・パース時の実行計画算出過程が出力されるため、以下の検証はインスタンス再起動直後に実施した。）

トレースファイルの確認と識別文字列の設定

これはトレースを取得する際のTipsだが、識別文字列を設定しておくとトレースファイルをトレース出力ディレクトリで探すのが非常に楽になる。

ここでは「CBO」という識別文字列を設定する。

SQL> select VALUE from V$DIAG_INFO where NAME = 'Default Trace File';

VALUE
--------------------------------------------------------------
/opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_28863.trc

SQL> alter session set tracefile_identifier = 'CBO';

Session altered.

SQL> select VALUE from V$DIAG_INFO where NAME = 'Default Trace File';

VALUE
------------------------------------------------------------------
/opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_25629_CBO.trc

10053トレースの開始

以下のコマンドによりセッション単位で10053トレースの取得を開始する。

SQL> alter session set events '10053 trace name context forever';

Session altered.

デフォルト実行（不可視索引は使われない）

SQL> set autot on
SQL> select
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME
  3  ,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE
  4  from
  5   EMPLOYEES
  6  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'))

10053トレース抜粋

  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = false
....................................................
is_recur_flags                      = 0
optimizer_use_invisible_indexes     = false
flashback_data_archive_internal_cursor = 0
....................................................
***************************************
BASE STATISTICAL INFORMATION
***********************
....................................................
  Index: EMP_HIRE_DATE_IX  Col#: 6
    LVLS: 0  #LB: 1  #DK: 98  LB/K: 1.00  DB/K: 1.00  CLUF: 13.00
    UNUSABLE
....................................................
***************************************
SINGLE TABLE ACCESS PATH 
....................................................
  Best:: AccessPath: TableScan
         Cost: 3.00  Degree: 1  Resp: 3.00  Card: 1.09  Bytes: 0

optimizer_use_invisible_indexesパラメータの値はデフォルトの「false」なので、不可視索引EMP_HIRE_DATE_IXは「UNUSABLE」つまり使用されない状態であることがわかる。

オプティマイザが最終的に選択したアクセスパスはCost=3となるフル・テーブル・スキャンである。

ヒント句で不可視索引を指定

SQL> select /*+ USE_INVISIBLE_INDEXES INDEX(EMPLOYEES EMP_HIRE_DATE_IX)  */
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME
  3  ,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE
  4  from
  5   EMPLOYEES
  6  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'))

10053トレース抜粋

  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = false
....................................................
is_recur_flags                      = 0
optimizer_use_invisible_indexes     = false
flashback_data_archive_internal_cursor = 0
....................................................
***************************************
BASE STATISTICAL INFORMATION
***********************
....................................................
  Index: EMP_HIRE_DATE_IX  Col#: 6
    LVLS: 0  #LB: 1  #DK: 98  LB/K: 1.00  DB/K: 1.00  CLUF: 13.00
    User hint to use this index
....................................................
***************************************
 SINGLE TABLE ACCESS PATH 
  Best:: AccessPath: IndexRange
  Index: EMP_HIRE_DATE_IX
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.09  Bytes: 0

「User hint to use this index」という表示から、不可視索引EMP_HIRE_DATE_IXがヒント句で有効になっていることがわかる。

このインデックスを使用してCost=2となるアクセスパスが選択された。

USE_INVISIBLE_INDEXESパラメータをTRUEに変更

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

SQL> select
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME
  3  ,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE
  4  from
  5   EMPLOYEES
  6  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'))

10053トレース抜粋

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
Compilation Environment Dump
_pga_max_size                       = 471840 KB
optimizer_use_invisible_indexes     = true
Bug Fix Control Environment
....................................................
  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = false
....................................................
is_recur_flags                      = 0
optimizer_use_invisible_indexes     = true
flashback_data_archive_internal_cursor = 0
....................................................
***************************************
BASE STATISTICAL INFORMATION
***********************
....................................................
  Index: EMP_HIRE_DATE_IX  Col#: 6
    LVLS: 0  #LB: 1  #DK: 98  LB/K: 1.00  DB/K: 1.00  CLUF: 13.00
....................................................
***************************************
SINGLE TABLE ACCESS PATH 
....................................................
  Best:: AccessPath: IndexRange
  Index: EMP_HIRE_DATE_IX
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.09  Bytes: 0
....................................................

USE_INVISIBLE_INDEXESパラメータをALTER SESSIONコマンドにより「true」に変更している。これによりインデックスEMP_HIRE_DATE_IXは実行計画算出に使用されるようになる。

２番目の例と同様にCost=2のアクセスパスが選択された。

10053トレースの終了

10053トレースを終了させるためには以下のコマンドを実行させる。

SQL> alter session set events '10053 trace name context off';

Session altered.

今日はここまで。

Flashback Dropのまとめ

Flashback Dropに関して、基本機能と容量管理について簡単な検証で確認をしたが最後におさらいをする。

• Flashback DropはRECYCLEBIN初期化パラメータがON（デフォルト）の場合に有効な機能であり、OFFの場合はOracle9iまでと同じ動作となる。
• テーブルをPURGEオプションなしでDropすると、テーブルは物理的に削除されずにリサイクルビンで管理される。これはリサイクルビン用の表領域に移動されるのではなく、同じ表領域のままデータ・ディクショナリから見えなくなるような仕組みで管理されることを意味する。
• リサイクルビンで管理されているテーブルは「BIN$」で始まる名前にRenameされる。
• テーブルに紐付くインデックスおよびPK制約もリサイクルビンで管理される対象であり、同様に「BIN$」で始まる名前にRenameされる。
• FK制約はDropとともに削除される。
• FLASHBACK TABLE <テーブル名> TO BEFORE DROP文でリサイクルビンにあるテーブルを削除直前に復元することができる。（名前は特に指定しないかぎり元に戻る）
• 同時にインデックスとPK制約も復元される。（ただし名前は「BIN$」で始まる名前のまま。）従って一意制約は有効であり重複行をInsertしようとするとエラーになる。
• FK制約は削除されているので復元されることはない。
• リサイクルビンで管理されているオブジェクトは、セグメントを解放しない状態で表領域内に存在している。
• 療養域の空きがなくなるまでセグメントは解放されないが、新たなオブジェクトのために領域が必要になると、削除時のSCNが古いセグメントから解放される。
• この動作はユーザの操作を必要とせず自動的に行われる。

Flashback Dropは使える機能なのか？

上で述べたとおりFlashback Dropはデフォルトで使用できる機能であるが、積極的にオフにすべきなのだろうか？

検証した限り、私にはオフにする正当な理由が見つからない。というかオフにしてはいけない機能だと思う。

ところが、最近Amazon RDSでOracle11gR2 SE1環境をお試しで構築したところ、Recyclebin初期化パラメータが「OFF」になっていた。

パラメータグループを作成し、デフォルト値の「ON」に変更することは可能なはずなので問題はないのだが、なぜこのような設定値になっているのかは不明だ。

現行踏襲の呪縛

この検証シリーズは、JPOUG Tech Talk Night #6のライトニングトークで披露させていただいたネタなのだが、その中でこの「現行踏襲の呪縛」という言葉に対する反応が意外によかった。

つまり、アップグレードで多くの新機能が使えるようになっても、「新たなバグを引きたくない。」というような消極的な理由から、新機能を使わないということが現場では非常に多い。

「新機能にむやみに飛びつくのは素人だ。」と言わんばかりにベテランDBAが結果として間違った方向にミスリードし、小さな問題を大きくしてしまうとすればこれほど残念なことはない。

• マニュアルをよく読んで誤解をしない。
• 自分で検証して内部動作・仕様を確認する。

こういうことを怠って、自分の思い込みだけで判断するから、技術の進歩を素直に認めない空気が醸成されるのだ。

もう２０年近く前のことで私がまだ経験の浅いエンジニアだったころ、Oracle Master の保有率が国内トップクラスというある開発会社に転職した時の話である。

「ウチの会社では外部キー制約は使わないことになっているから」と言われて面食らった。

リレーショナル・データベースというのものを正しく理解せずに実装しているシステムがどんな結末をたどるか、ということをこの会社にいる間に身をもって経験した。

ある地方におけるプロジェクトが炎上し、何人もの在京エンジニアが投入されたが皆討ち死にするような状況となった末、その会社は遠方で苦しんでいるメンバーに対し、残りの社員全員で励ましの寄せ書きを送ることにした。

今思えばとんでもないブラックな会社だったと思うが、強烈な違和感を持った私はやがて別の会社に転職をした。

その決断は間違っていなかったと確信している。

おまけ

大事なことを忘れたのだが、この検証はWindows7 64bit環境で以下のバージョンで実施した。

SQL> select banner from v$version;

BANNER
------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 -
64bit Production

PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

10gではメタデータがリサイクルビンに残っていた？

古い話だが、Flashback Dropが出た10gでは不思議な仕様だった。

以下は、オペレーション・ログからの抜粋だが、リサイクルビンの中で一番古いオブジェクトのUSER_RECYCLEBIN.SPACEが「0」のものがあることに気がついた。

試しにこのテーブルをフラッシュバックしたところ、テーブル構造（メタデータ）が復元されたにも関わらずデータが０件だった。

SQL> select ORIGINAL_NAME,TYPE,SPACE from user_recyclebin
  2  where CREATETIME = (select min(CREATETIME) from user_recyclebin);

ORIGINAL_NAME                    TYPE                           SPACE
-------------------------------- ------------------------- ----------
TABLE********                    TABLE                              0
INDEX************                INDEX                              0
PK_*************                 INDEX                              0

SQL> flashback table TABLE******** to before drop rename to TABLE********_RCV;

フラッシュバックが完了しました。

SQL> desc TABLE********_RCV
 名前                                        NULL?    型
 ----------------------------------------- -------- ------------------
 COL1                                      NOT NULL NUMBER(22)
 COL2                                               NUMBER(22)
 COL3                                               NUMBER(22)
 COL4                                               DATE
 COL5                                               DATE
 COL6                                               NUMBER(22)
 COL7                                               DATE

SQL> select * from TABLE********_RCV;

レコードが選択されませんでした。

よくよく確認したところ、メタデータが５万件以上もリサイクルビンに溜まっていた。

どうやら夜間バッチ処理の中でDROPを沢山発行していたようだった。（9iからのスクリプトでPURGEオプションなしでDROPを実行していた。）

これをPURGE RECYCLEBINコマンドで一気に削除したのだが、わずか数秒で終わってしまった。

今では見ることができない不思議な現象だった。

終わり

容量管理はどうなっているか？

前回はFlashback Dropの基本的な動作を確認した。

テーブルを削除すると「リサイクルビン」という特別な場所に移動されるわけではなく、同じ表領域にセグメントとして存在しつつもエクステント情報から見えなくなるだけであることがわかった。

また、削除されたテーブルとそれに紐づくインデックスとPK制約は、BIN$で始まる名前にRenameされる。
そして、Flashback Dropによって削除前に戻されたテーブルは、元の名前に戻るが（任意の名前にすることは可能）インデックスとPK制約はBIN$で始まる名前から元に戻ることがないこともわかった。

それでは削除されたオブジェクトが「リサイクルビン」セグメントとして表領域の中に溜まっていった場合、有限な表領域はいずれ一杯になってしまうはずである。
WindowsやMacOSの場合明示的にゴミ箱を空にしないとディスク領域は解放されないが、OracleのリサイクルビンもPERGEコマンドを発行しないと解放されないのであろうか？

マニュアルをよく読めば答えは書いてあるのだが、実際に確かめてみよう。

初期状態

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_SEG%
----------
        60


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


レコードが選択されませんでした。

EMP表をコピーしてEMP1表を作成する

CTAS(Create Table As Select)でEMP表からEMP1表を作成する。

SQL> create table EMP1 as select * from EMP;

表が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP1                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

6行が選択されました。


  TOT_EXT%
----------
        70


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP1                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

6行が選択されました。


  TOT_SEG%
----------
        70


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                56              80           70


レコードが選択されませんでした。

EMP1表にインデックスを作成する

SQL> create unique index PK_EMP1 on EMP1 (EMPNO);

索引が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP1                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP1                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

7行が選択されました。


  TOT_EXT%
----------
        80


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP1                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP1                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

7行が選択されました。


  TOT_SEG%
----------
        80


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP1                          EMP1


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- -----------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                64              80           80


レコードが選択されませんでした。

インデックスPK_EMP1を作成したところまでを図にすると以下のようになる。

EMP1表をDropする

SQL> drop table EMP1;

表が削除されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   INDEX           TS_SMALL                 8
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

7行が選択されました。


  TOT_SEG%
----------
        80


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   PK_EMP1         DROP       INDEX      TS_SMALL      3324538 NO     YES
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   EMP1            DROP       TABLE      TS_SMALL      3324542 YES    YES

エクステントの空き領域が初期状態と同じ40%になっているが、セグメントの空き領域が20%となっていることに注目してもらいたい。

EMP2表とインデックスを作成する

SQL> create table EMP2 as select * from EMP;

表が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP2                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

6行が選択されました。


  TOT_EXT%
----------
        70


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   INDEX           TS_SMALL                 8
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP2                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_SEG%
----------
        90


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                56              80           70


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   PK_EMP1         DROP       INDEX      TS_SMALL      3324538 NO     YES
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   EMP1            DROP       TABLE      TS_SMALL      3324542 YES    YES


SQL> create unique index PK_EMP2 on EMP2 (EMPNO);

索引が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP2                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP2                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

7行が選択されました。


  TOT_EXT%
----------
        80


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   INDEX           TS_SMALL                 8
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP2                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP2                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

9行が選択されました。


  TOT_SEG%
----------
       100


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP2                          EMP2


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                64              80           80


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   PK_EMP1         DROP       INDEX      TS_SMALL      3324538 NO     YES
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   EMP1            DROP       TABLE      TS_SMALL      3324542 YES    YES

この時点で、セグメント使用率が100%に達していることがわかる。

EMP2表をDropする

ここでEMP2表を削除して表領域使用率を初期状態と同じ60%にしてみよう。

SQL> drop table EMP2;

表が削除されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   INDEX           TS_SMALL                 8
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   INDEX           TS_SMALL                 8
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   TABLE           TS_SMALL                 8
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

9行が選択されました。


  TOT_SEG%
----------
       100


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   PK_EMP1         DROP       INDEX      TS_SMALL      3324538 NO     YES
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   EMP1            DROP       TABLE      TS_SMALL      3324542 YES    YES
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   PK_EMP2         DROP       INDEX      TS_SMALL      3324984 NO     YES
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   EMP2            DROP       TABLE      TS_SMALL      3324988 YES    YES

表領域としては40%の空きが確保できたが、セグメントは100%使用されている状態である。

この状態でさらにオブジェクトを作成してどうなるかを確認しよう。

EMP3表を作成する

ここからはオブジェクトを1つずつ作成する。

SQL> create table EMP3 as select * from EMP;

表が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

6行が選択されました。


  TOT_EXT%
----------
        70


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   INDEX           TS_SMALL                 8
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_SEG%
----------
        90


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                56              80           70


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   PK_EMP2         DROP       INDEX      TS_SMALL      3324984 NO     YES
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   EMP2            DROP       TABLE      TS_SMALL      3324988 YES    YES

まず、EMP1に該当するリサイクルビン・セグメントが解放され、EMP1に紐づくPK_EMP1も解放される。

これはDROPSCNの小さい、つまり先にDropされたものから解放される仕様となっているので、正確にはPK_EMP1 → EMP1の順に解放される。

20%分が解放され10%分が消費されたので、セグメントは差し引き90%使用されていることになる。

PK_EMP3を作成する

SQL> create unique index PK_EMP3 on EMP3 (EMPNO);

索引が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

7行が選択されました。


  TOT_EXT%
----------
        80


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   INDEX           TS_SMALL                 8
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

9行が選択されました。


  TOT_SEG%
----------
       100


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP3                          EMP3


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                64              80           80


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   PK_EMP2         DROP       INDEX      TS_SMALL      3324984 NO     YES
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   EMP2            DROP       TABLE      TS_SMALL      3324988 YES    YES

図はリサイクルビンの２つのオブジェクトを解放し、新たな２つのオブジェクトを作成したところである。

EMP4およびPK_EMP4を作成する

SQL> create table EMP4 as select * from EMP;

表が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_EXT%
----------
        90


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_SEG%
----------
        90


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP3                          EMP3


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                72              80           90


レコードが選択されませんでした。

SQL> create unique index PK_EMP4 on EMP4 (EMPNO);

索引が作成されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
PK_EMP4                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

9行が選択されました。


  TOT_EXT%
----------
       100


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
PK_EMP4                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

9行が選択されました。


  TOT_SEG%
----------
       100


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP3                          EMP3
PK_EMP4                          EMP4


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                80              80          100


レコードが選択されませんでした。

EMP2、PK_EMP2に該当するリサイクルビン・オブジェクトが解放され新たなオブジェクトが作成された。表領域の使用率は100%である。

ここまで、明示的なPURGE RECYCLEBINコマンドは一度も発行していない。つまりリサイクルビン・オブジェクトにより表領域中のセグメントが100%使用されていたとしても、Oracleは古いものから適宜解放し、新たなオブジェクトを作成する領域を自動的に確保する。

PK_EMP4のみ削除する

テーブルを削除するとテーブルに紐づくインデックスも自動的にリサイクルビンで管理されることはわかったが、インデックスのみ削除した場合はどうなるだろうか？

SQL> drop index PK_EMP4;

索引が削除されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_EXT%
----------
        90


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8

8行が選択されました。


  TOT_SEG%
----------
        90


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP3                          EMP3


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                72              80           90


レコードが選択されませんでした。

インデックスのみを削除してもリサイクルビンでは管理されない。単純に削除されて領域も解放される。

これは考えてみれば簡単なのだが、テーブルがあればインデックスは何度でも再作成可能なためである。

今日はここまで

前回のおさらい

Flashback Dropの検証を行う環境の初期状態である。

オブジェクトはテーブルとインデックス、あわせて５つ。すべて1エクステント＝8ブロック＝64KBずつを占めている。これの他に表領域の管理情報と思われる1エスクテント分を合せて480KBの領域となるので、全体（640KB）に対する使用率は60%、空き領域は40%となっている。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_SEG%
----------
        60


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


レコードが選択されませんでした。

EMP表をDropする。

それでは、まず最初にEMP表をDropして状況を確認してみる。

SQL> drop table EMP;

表が削除されました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        40


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   INDEX           TS_SMALL                 8
BIN$TduxVYG8Q5mq7BFKwzmeuw==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_SEG%
----------
        60


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
BIN$YIfQE0ilRXy+VvPcGtg1WA==$0   BIN$TduxVYG8Q5mq7BFKwzmeuw==$0   P  BIN$IM3P5MOwSlazE4G+zy6X3w==$0   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                32              80           40


--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   PK_EMP          DROP       INDEX      TS_SMALL      3323577 NO     YES
BIN$TduxVYG8Q5mq7BFKwzmeuw==$0   EMP             DROP       TABLE      TS_SMALL      3323581 YES    YES

テーブルとインデックスが「BIN$」で始まる名前にRenameされているだけでなく、PK制約もRenameされていることがわかる。

しかし、リサイクルビン情報にはFK制約の情報はない。つまり削除されている。

また、USER_RECYCLEBIN.CAN_UNDROP列を見ると、テーブルは「YES」となっているのに、インデックスの方は「NO」となっている。この違いについては次の操作で明らかになるので追って解説する。

さらに、セグメント情報を見ると、Dropされたオブジェクトは依然として「TS_SMALL」表領域内に存在していることがわかる。つまり、Drop（削除）されてゴミ箱（リサイクルビン）に入るというのは概念的なことであって、物理的には同じ表領域に存在しているのである。

SYSAUX表領域などに移動されるわけではない。

EMP表をDropした状態を図に示すと以下のようになる。

参照整合性制約（FK_EMP）については、リサイクルビンで管理されることなく即時に削除されていることがわかる。

Flashback Drop実行

それでは「FLASHBACK TABLE」コマンドにより、EMP表を削除前に戻してみよう。

SQL> flashback table EMP to before drop;

フラッシュバックが完了しました。

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   INDEX           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   INDEX           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_SEG%
----------
        60


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   EMP
PK_DEPT                          DEPT


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
BIN$YIfQE0ilRXy+VvPcGtg1WA==$0   EMP                              P  BIN$IM3P5MOwSlazE4G+zy6X3w==$0   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


レコードが選択されませんでした。

SQL> Insert into FD.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('82-01-23','RR-MM-DD'),1300,null,10);
Insert into FD.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('82-01-23','RR-MM-DD'),1300,null,10)
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(FD.BIN$YIfQE0ilRXy+VvPcGtg1WA==$0)に反しています

エクステントが復活したために、空き領域は初期状態の40%に戻っている。

テーブルは削除前と同じ名前に戻ったが、インデックスはRenameされたままである。さらにPK制約もRenameされたままであることがわかる。つまり、USER_RECYCLEBIN.CAN_UNDROP列が「YES」のものは削除前のオブジェクト名に戻るが、「NO」のものは戻らないことを示している。

さらに、参照整合性制約は削除されたままで元に戻ることはないがPK制約は有効であるので、制約違反となるレコードをInsertしようとするとORA-0001エラーが発生する。

インデックスやPK制約はテーブルが存在すれば、削除／再作成で元の名前に戻すことは可能であるため、Flashbck Dropの中ではRenameに関する動作が行われないのかもしれない。

今回はここまで。

Oracle10gからある地味な機能だが。。。

Flashback DropはOracle10gから登場した機能だが、正直必要性を感じることはなかった。
テーブルを間違って削除してしまうようなことは通常考えられないし、自分自身そのようなことで困ったことがなかったからだ。

ところが、過去3年間に2件ものヒューマン・エラーによるテーブルの削除事例を目にした。

いずれの事例でも幸いなことにバックアップが取得されていたので、Point-in-Timeリカバリによって最終的にはリカバリに成功したのだが、うち1件は本番環境での誤操作で、極めて莫大な損害が出たと聞いた。
他の事例も最終テスト段階での誤操作だったので、リカバリまでにテストを止めざるを得ず大きな影響を与えたらしい。

両方の事例で関係者に状況をヒアリングしたのだが、いずれも「Recyclebin」初期化パラメータはデフォルトの「on」から意図的に「off」となっており、Flashback Dropは使えない状態だった。

もし、Flashback Dropが使えていたら

もし、両方の事例で「Recyclebin」パラメータをデフォルトのままとしていたら、誤って削除してしまったテーブルを簡単な操作で、しかもほんの数秒で元に戻すことができたはずである。

一度でもFlashback Dropを体験したことのある人であればその効果が計り知れないことは容易に理解できるのであるが、残念なことにそれらでは共に使えなく、何時間もかけてバックアップから戻すはめになったのだ。

なぜRecyclebinをoffにしていたのか？

結論から言えばデフォルトのままで何ら問題はないのだが、なぜ意図的に「Recyclebin 」が「off」になっていたのかその理由を聞いてみた。

• テーブルを削除しても「ゴミ箱」に残るということは、SYSAUXかどこかにある「ゴミ箱」領域に移動するのではないか？余計なI/Oが発生するのは嫌だ。
• 「ゴミ箱」がいつの間にか一杯になって無駄な領域を占有するのは嫌だ。どうせ「ゴミ」なんだからさっさと消してしまいたい。
• 新機能にはいつも泣かされてきたから、こいつもきっと悪さをするに違いない。9iまではこんな機能なかったのだから要らない。
• 今のバージョンは11gだけど、10gの時からoffにしているからoffのまま。
• そもそも、間違ってDropするなんてことはしないので、余計な機能は要らない。

マニュアルを熟読すれば全くの誤解であることがわかるのだが、一度誤解してしまうとベテランと言われるエンジニアほど頑ななままになる。

だから、デフォルトからわざわざ変更することになる。

検証してみた

「マニュアルにはこう書いてある。」と言っても誤解を解くのは難しいし、自分自身どのような仕組みで動いているのか知りたかったので検証してみることにした。

準備

容量管理をどのようにしているのかを確認したいので、640KBという小さな表領域を作成することにした。

環境は、Oracle 12c EE (12.1.0.2)for Windows(Non-PDB)を使用した。

CREATE TABLESPACE TS_SMALL
DATAFILE 'C:\APP\ORACLEHOME\ORADATA\ORA12C\TS_SMALL01.DBF' SIZE 640K REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;

データ作成

データはSCOTTスキーマのオブジェクトをインポートすることで簡単に作成した。

確認用スクリプト

stsck.sql

@@uext
@@useg
@@uind
@@ucon
@@usep
@@rbin

uext.sql

TTITLE LEFT '--- エクステント情報 ---'
col EXTENT_NAME for a32
col EXTENT_TYPE for a15
select
 SEGMENT_NAME EXTENT_NAME
,SEGMENT_TYPE EXTENT_TYPE
,TABLESPACE_NAME
,BLOCKS
from
 USER_EXTENTS
order by
 EXTENT_NAME
;
TTITLE OFF
select
 (sum(BLOCKS)+8)/80*100 "TOT_EXT%"
from USER_EXTENTS
/

useg.sql

TTITLE LEFT '--- セグメント情報 ---'
col SEGMENT_NAME for a32
col SEGMENT_TYPE for a15
select
 SEGMENT_NAME
,SEGMENT_TYPE
,TABLESPACE_NAME
,BLOCKS
from
 USER_SEGMENTS
order by
 SEGMENT_NAME
;
TTITLE OFF
select
 (sum(BLOCKS)+8)/80*100 "TOT_SEG%"
from
 USER_SEGMENTS
/

uind.sql

TTITLE LEFT '--- インデックス情報 ---'
col INDEX_NAME for a32
col TABLE_NAME for a32
select
 INDEX_NAME
,TABLE_NAME
from
 USER_INDEXES
order by
 INDEX_NAME
;
TTITLE OFF

ucon.sql

TTITLE LEFT '--- 制約情報 ---'
col CONSTRAINT_NAME for a32
col TABLE_NAME for a32
col INDEX_NAME for a32
select
 CONSTRAINT_NAME
,TABLE_NAME
,CONSTRAINT_TYPE
,INDEX_NAME
,STATUS
from
 USER_CONSTRAINTS
order by
 CONSTRAINT_NAME
;
TTITLE OFF

usep.sql

TTITLE LEFT '--- 表領域使用率 ---'
select * from DBA_TABLESPACE_USAGE_METRICS
where TABLESPACE_NAME = 'TS_SMALL'
/

rbin.sql

TTITLE LEFT '--- リサイクルビン情報 ---'
select
 OBJECT_NAME
,ORIGINAL_NAME
,OPERATION
,TYPE
,TS_NAME
,DROPSCN
,CAN_UNDROP
,CAN_PURGE
from
 USER_RECYCLEBIN
order by
 DROPSCN
/

初期状態

SQL> @stsck

--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_EXT%
----------
        60


--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8


  TOT_SEG%
----------
        60


--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP


--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED


--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60


レコードが選択されませんでした。

続く

APPROX_COUNT_DISTINCT関数とは

APPROX_COUNT_DISTINCT関数とは、重複していない値の近似カウントを集計する関数で、例えば次のような問い合わせにおいて、prod_idでグループ化したうちの重複していないcust_idのカウントを集計する。

SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers"
FROM sales
GROUP BY prod_id;
ORDER BY prod_id;

これを従来のCOUNT関数を使って書くと

SELECT prod_id, COUNT(DISTINCT cust_id) AS "Number of Customers"
FROM sales
GROUP BY prod_id;
ORDER BY prod_id;

となる。

マニュアルからの抜粋

Oracle® Database新機能ガイド
12cリリース1 (12.1)
B71327-05

には以下の記述がある。

1.2 重複していない値の近似カウント

最適化された新しいSQL関数APPROX_COUNT_DISTINCT()により、重複していない値の近似カウントが集約されます。大量のデータの処理速度が完全集約よりも大幅に上がりますが、これは特に、重複していない値が多数含まれるデータ・セットの場合に顕著であり、完全集約結果との偏差は無視できる程度です。

現在のデータ分析では、一般的な操作で重複していない値をカウントすることが求められます。処理時間およびリソース消費を桁違いに最適化すると同時にほとんど完全な結果を提供することにより、既存の処理速度を上げ、分析の洞察力を数段高めることができます。

実際に試してみた

マニュアルに書いてある「桁違いの最適化」とは一体どんなものなのか気になったので、サンプルスキーマの「SH.SALES」表（918,843件）を題材に実際に確認してみた。

以下は、COUNT関数とAPPROX_COUNT_DISTINCT関数の結果を横に並べたものである。
（前者の結果しかないように見えるが、横スクロールして後者の結果と比べてみてほしい。）

１回目にORDER BYあり、２回目にORDER BYなしの結果をそれぞれ確認した。

なお、各測定前に共有プールとバッファ・キャッシュのフラッシュを行ってから、SQL文の実行を行っている。

00001 L|SQL> SELECT prod_id, COUNT(DISTINCT cust_id) AS "Number of Customers"                                         ||00001 R|SQL> SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers"                                  
00002  |  2  FROM sales                                                                                               ||00002  |  2  FROM sales                                                                                               
00003  |  3  GROUP BY prod_id                                                                                         ||00003  |  3  GROUP BY prod_id                                                                                         
00004  |  4  ORDER BY prod_id;                                                                                        ||00004  |  4  ORDER BY prod_id;                                                                                        
00005  |                                                                                                              ||00005  |                                                                                                              
00006  |   PROD_ID Number of Customers                                                                                ||00006  |   PROD_ID Number of Customers                                                                                
00007  |---------- -------------------                                                                                ||00007  |---------- -------------------                                                                                
00008 L|        13                2492                                                                                ||00008 R|        13                2516                                                                                
00009 L|        14                2039                                                                                ||00009 R|        14                2030                                                                                
00010 L|        15                2122                                                                                ||00010 R|        15                2105                                                                                
00011 L|        16                2384                                                                                ||00011 R|        16                2367                                                                                
00012 L|        17                2100                                                                                ||00012 R|        17                2093                                                                                
00013 L|        18                3028                                                                                ||00013 R|        18                2975                                                                                
00014 L|        19                2617                                                                                ||00014 R|        19                2630                                                                                
00015 L|        20                3795                                                                                ||00015 R|        20                3791                                                                                
00016 L|        21                2334                                                                                ||00016 R|        21                2365                                                                                
00017 L|        22                1416                                                                                ||00017 R|        22                1408                                                                                
00018 L|        23                5183                                                                                ||00018 R|        23                5104                                                                                
00019  |                                                                                                              ||00019  |                                                                                                              
00020  |   PROD_ID Number of Customers                                                                                ||00020  |   PROD_ID Number of Customers                                                                                
00021  |---------- -------------------                                                                                ||00021  |---------- -------------------                                                                                
00022 L|        24                4817                                                                                ||00022 R|        24                4754                                                                                
00023 L|        25                5068                                                                                ||00023 R|        25                4991                                                                                
00024 L|        26                4948                                                                                ||00024 R|        26                4894                                                                                
00025 L|        27                3801                                                                                ||00025 R|        27                3726                                                                                
00026 L|        28                4572                                                                                ||00026 R|        28                4537                                                                                
00027 L|        29                2295                                                                                ||00027 R|        29                2305                                                                                
00028 L|        30                6154                                                                                ||00028 R|        30                6134                                                                                
00029 L|        31                5586                                                                                ||00029 R|        31                5463                                                                                
00030 L|        32                4100                                                                                ||00030 R|        32                4025                                                                                
00031 L|        33                5389                                                                                ||00031 R|        33                5278                                                                                
00032 L|        34                4192                                                                                ||00032 R|        34                4142                                                                                
00033  |                                                                                                              ||00033  |                                                                                                              
00034  |   PROD_ID Number of Customers                                                                                ||00034  |   PROD_ID Number of Customers                                                                                
00035  |---------- -------------------                                                                                ||00035  |---------- -------------------                                                                                
00036 L|        35                4965                                                                                ||00036 R|        35                4950                                                                                
00037 L|        36                3302                                                                                ||00037 R|        36                3327                                                                                
00038 L|        37                4802                                                                                ||00038 R|        37                4735                                                                                
00039 L|        38                3395                                                                                ||00039 R|        38                3452                                                                                
00040 L|        39                3433                                                                                ||00040 R|        39                3404                                                                                
00041 L|        40                5972                                                                                ||00041 R|        40                5858                                                                                
00042 L|        41                3738                                                                                ||00042 R|        41                3759                                                                                
00043 L|        42                3951                                                                                ||00043 R|        42                3938                                                                                
00044 L|        43                3016                                                                                ||00044 R|        43                3056                                                                                
00045 L|        44                2571                                                                                ||00045 R|        44                2587                                                                                
00046 L|        45                3549                                                                                ||00046 R|        45                3499                                                                                
00047  |                                                                                                              ||00047  |                                                                                                              
00048  |   PROD_ID Number of Customers                                                                                ||00048  |   PROD_ID Number of Customers                                                                                
00049  |---------- -------------------                                                                                ||00049  |---------- -------------------                                                                                
00050 L|        46                4107                                                                                ||00050 R|        46                4054                                                                                
00051 L|        47                3198                                                                                ||00051 R|        47                3176                                                                                
00052 L|        48                6010                                                                                ||00052 R|        48                5883                                                                                
00053 L|       113                4640                                                                                ||00053 R|       113                4598                                                                                
00054 L|       114                4230                                                                                ||00054 R|       114                4183                                                                                
00055 L|       115                3847                                                                                ||00055 R|       115                3832                                                                                
00056 L|       116                4929                                                                                ||00056 R|       116                4924                                                                                
00057 L|       117                4672                                                                                ||00057 R|       117                4632                                                                                
00058 L|       118                4214                                                                                ||00058 R|       118                4200                                                                                
00059 L|       119                4898                                                                                ||00059 R|       119                4858                                                                                
00060 L|       120                5224                                                                                ||00060 R|       120                5135                                                                                
00061  |                                                                                                              ||00061  |                                                                                                              
00062  |   PROD_ID Number of Customers                                                                                ||00062  |   PROD_ID Number of Customers                                                                                
00063  |---------- -------------------                                                                                ||00063  |---------- -------------------                                                                                
00064 L|       121                3517                                                                                ||00064 R|       121                3499                                                                                
00065 L|       122                1572                                                                                ||00065 R|       122                1559                                                                                
00066 L|       123                4491                                                                                ||00066 R|       123                4441                                                                                
00067 L|       124                4309                                                                                ||00067 R|       124                4310                                                                                
00068 L|       125                4679                                                                                ||00068 R|       125                4650                                                                                
00069 L|       126                4253                                                                                ||00069 R|       126                4202                                                                                
00070 L|       127                4757                                                                                ||00070 R|       127                4710                                                                                
00071 L|       128                5196                                                                                ||00071 R|       128                5135                                                                                
00072 L|       129                2425                                                                                ||00072 R|       129                2419                                                                                
00073 L|       130                5428                                                                                ||00073 R|       130                5336                                                                                
00074 L|       131                5013                                                                                ||00074 R|       131                4907                                                                                
00075  |                                                                                                              ||00075  |                                                                                                              
00076  |   PROD_ID Number of Customers                                                                                ||00076  |   PROD_ID Number of Customers                                                                                
00077  |---------- -------------------                                                                                ||00077  |---------- -------------------                                                                                
00078 L|       132                4770                                                                                ||00078 R|       132                4688                                                                                
00079 L|       133                5201                                                                                ||00079 R|       133                5114                                                                                
00080 L|       134                3629                                                                                ||00080 R|       134                3594                                                                                
00081 L|       135                3996                                                                                ||00081 R|       135                3943                                                                                
00082 L|       136                 397                                                                                ||00082 R|       136                 395                                                                                
00083 L|       137                3993                                                                                ||00083 R|       137                3977                                                                                
00084 L|       138                2763                                                                                ||00084 R|       138                2716                                                                                
00085 L|       139                4217                                                                                ||00085 R|       139                4106                                                                                
00086 L|       140                5097                                                                                ||00086 R|       140                5032                                                                                
00087 L|       141                3546                                                                                ||00087 R|       141                3499                                                                                
00088 L|       142                3136                                                                                ||00088 R|       142                3071                                                                                
00089  |                                                                                                              ||00089  |                                                                                                              
00090  |   PROD_ID Number of Customers                                                                                ||00090  |   PROD_ID Number of Customers                                                                                
00091  |---------- -------------------                                                                                ||00091  |---------- -------------------                                                                                
00092 L|       143                2375                                                                                ||00092 R|       143                2395                                                                                
00093 L|       144                2289                                                                                ||00093 R|       144                2254                                                                                
00094 L|       145                2814                                                                                ||00094 R|       145                2807                                                                                
00095 L|       146                4796                                                                                ||00095 R|       146                4716                                                                                
00096 L|       147                3050                                                                                ||00096 R|       147                3073                                                                                
00097 L|       148                5150                                                                                ||00097 R|       148                5049                                                                                
00098  |                                                                                                              ||00098  |                                                                                                              
00099  |72行が選択されました。                                                                                        ||00099  |72行が選択されました。                                                                                        
00100  |                                                                                                              ||00100  |                                                                                                              
00101 L|経過: 00:00:01.27                                                                                             ||00101 R|経過: 00:00:00.92                                                                                             
00102  |                                                                                                              ||00102  |                                                                                                              
00103  |実行計画                                                                                                      ||00103  |実行計画                                                                                                      
00104  |----------------------------------------------------------                                                    ||00104  |----------------------------------------------------------                                                    
00105 L|Plan hash value: 932069919                                                                                    ||00105 R|Plan hash value: 4109827725                                                                                   
00106  |                                                                                                              ||00106  |                                                                                                              
00107 L|------------------------------------------------------------------------------------------------------------  ||00107 R|----------------------------------------------------------------------------------------------                
00108 L|| Id  | Operation              | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |  ||00108 R|| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                
00109 L|------------------------------------------------------------------------------------------------------------  ||00109 R|----------------------------------------------------------------------------------------------                
00110 L||   0 | SELECT STATEMENT       |           |    72 |  1224 |       |  2638   (2)| 00:00:01 |       |       |  ||00110 R||   0 | SELECT STATEMENT     |       |    72 |   648 |   537   (6)| 00:00:01 |       |       |                
00111 L||   1 |  SORT GROUP BY         |           |    72 |  1224 |       |  2638   (2)| 00:00:01 |       |       |  ||00111 R||   1 |  SORT GROUP BY APPROX|       |    72 |   648 |   537   (6)| 00:00:01 |       |       |                
00112 L||   2 |   VIEW                 | VM_NWVW_1 |   359K|  5966K|       |  2638   (2)| 00:00:01 |       |       |  ||00112 R||   2 |   PARTITION RANGE ALL|       |   918K|  8075K|   514   (1)| 00:00:01 |     1 |    28 |                
00113 L||   3 |    HASH GROUP BY       |           |   359K|  3158K|    17M|  2638   (2)| 00:00:01 |       |       |  ||00113 R||   3 |    TABLE ACCESS FULL | SALES |   918K|  8075K|   514   (1)| 00:00:01 |     1 |    28 |                
00114 L||   4 |     PARTITION RANGE ALL|           |   918K|  8075K|       |   514   (1)| 00:00:01 |     1 |    28 |  ||00114 R|----------------------------------------------------------------------------------------------                
00115 L||   5 |      TABLE ACCESS FULL | SALES     |   918K|  8075K|       |   514   (1)| 00:00:01 |     1 |    28 |  ||       |                                                                                                              
00116 L|------------------------------------------------------------------------------------------------------------  ||       |                                                                                                              
00117  |                                                                                                              ||00115  |                                                                                                              
00118  |                                                                                                              ||00116  |                                                                                                              
00119  |統計                                                                                                          ||00117  |統計                                                                                                          
00120  |----------------------------------------------------------                                                    ||00118  |----------------------------------------------------------                                                    
00121 L|       1936  recursive calls                                                                                  ||00119 R|       1956  recursive calls                                                                                  
00122  |          0  db block gets                                                                                    ||00120  |          0  db block gets                                                                                    
00123 L|       4914  consistent gets                                                                                  ||00121 R|       4921  consistent gets                                                                                  
00124 L|       1772  physical reads                                                                                   ||00122 R|       1729  physical reads                                                                                   
00125  |          0  redo size                                                                                        ||00123  |          0  redo size                                                                                        
00126 L|       2133  bytes sent via SQL*Net to client                                                                 ||00124 R|       2134  bytes sent via SQL*Net to client                                                                 
00127  |        595  bytes received via SQL*Net from client                                                           ||00125  |        595  bytes received via SQL*Net from client                                                           
00128  |          6  SQL*Net roundtrips to/from client                                                                ||00126  |          6  SQL*Net roundtrips to/from client                                                                
00129  |        136  sorts (memory)                                                                                   ||00127  |        136  sorts (memory)                                                                                   
00130  |          0  sorts (disk)                                                                                     ||00128  |          0  sorts (disk)                                                                                     
00131  |         72  rows processed                                                                                   ||00129  |         72  rows processed                                                                                   
00132  |                                                                                                              ||00130  |                                                                                                              
00133  |                                                                                                              ||00131  |                                                                                                              
00134 L|SQL> SELECT prod_id, COUNT(DISTINCT cust_id) AS "Number of Customers"                                         ||00132 R|SQL> SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers"                                  
00135  |  2  FROM sales                                                                                               ||00133  |  2  FROM sales                                                                                               
00136  |  3  GROUP BY prod_id;                                                                                        ||00134  |  3  GROUP BY prod_id;                                                                                        
00137  |                                                                                                              ||00135  |                                                                                                              
00138  |   PROD_ID Number of Customers                                                                                ||00136  |   PROD_ID Number of Customers                                                                                
00139  |---------- -------------------                                                                                ||00137  |---------- -------------------                                                                                
00140 L|        22                1416                                                                                ||00138 R|        22                1408                                                                                
00141 L|        25                5068                                                                                ||00139 R|        25                4991                                                                                
00142 L|        30                6154                                                                                ||00140 R|        30                6134                                                                                
00143 L|        34                4192                                                                                ||00141 R|        34                4142                                                                                
00144 L|        42                3951                                                                                ||00142 R|        42                3938                                                                                
00145 L|        43                3016                                                                                ||00143 R|        43                3056                                                                                
00146 L|       123                4491                                                                                ||00144 R|       123                4441                                                                                
00147 L|       129                2425                                                                                ||00145 R|       129                2419                                                                                
00148 L|       138                2763                                                                                ||00146 R|       138                2716                                                                                
00149 L|        13                2492                                                                                ||00147 R|        13                2516                                                                                
00150 L|        28                4572                                                                                ||00148 R|        28                4537                                                                                
00151  |                                                                                                              ||00149  |                                                                                                              
00152  |   PROD_ID Number of Customers                                                                                ||00150  |   PROD_ID Number of Customers                                                                                
00153  |---------- -------------------                                                                                ||00151  |---------- -------------------                                                                                
00154 L|        29                2295                                                                                ||00152 R|        29                2305                                                                                
00155 L|        44                2571                                                                                ||00153 R|        44                2587                                                                                
00156 L|        47                3198                                                                                ||00154 R|        47                3176                                                                                
00157 L|       113                4640                                                                                ||00155 R|       113                4598                                                                                
00158 L|       116                4929                                                                                ||00156 R|       116                4924                                                                                
00159 L|       120                5224                                                                                ||00157 R|       120                5135                                                                                
00160 L|       128                5196                                                                                ||00158 R|       128                5135                                                                                
00161 L|       147                3050                                                                                ||00159 R|       147                3073                                                                                
00162 L|       121                3517                                                                                ||00160 R|       121                3499                                                                                
00163 L|       134                3629                                                                                ||00161 R|       134                3594                                                                                
00164 L|       144                2289                                                                                ||00162 R|       144                2254                                                                                
00165  |                                                                                                              ||00163  |                                                                                                              
00166  |   PROD_ID Number of Customers                                                                                ||00164  |   PROD_ID Number of Customers                                                                                
00167  |---------- -------------------                                                                                ||00165  |---------- -------------------                                                                                
00168 L|        14                2039                                                                                ||00166 R|        14                2030                                                                                
00169 L|        20                3795                                                                                ||00167 R|        20                3791                                                                                
00170 L|        21                2334                                                                                ||00168 R|        21                2365                                                                                
00171 L|        26                4948                                                                                ||00169 R|        26                4894                                                                                
00172 L|        31                5586                                                                                ||00170 R|        31                5463                                                                                
00173 L|       132                4770                                                                                ||00171 R|       132                4688                                                                                
00174 L|       148                5150                                                                                ||00172 R|       148                5049                                                                                
00175 L|       141                3546                                                                                ||00173 R|       141                3499                                                                                
00176 L|        24                4817                                                                                ||00174 R|        24                4754                                                                                
00177 L|        32                4100                                                                                ||00175 R|        32                4025                                                                                
00178 L|        46                4107                                                                                ||00176 R|        46                4054                                                                                
00179  |                                                                                                              ||00177  |                                                                                                              
00180  |   PROD_ID Number of Customers                                                                                ||00178  |   PROD_ID Number of Customers                                                                                
00181  |---------- -------------------                                                                                ||00179  |---------- -------------------                                                                                
00182 L|       117                4672                                                                                ||00180 R|       117                4632                                                                                
00183 L|       119                4898                                                                                ||00181 R|       119                4858                                                                                
00184 L|       133                5201                                                                                ||00182 R|       133                5114                                                                                
00185 L|       137                3993                                                                                ||00183 R|       137                3977                                                                                
00186 L|       143                2375                                                                                ||00184 R|       143                2395                                                                                
00187 L|        17                2100                                                                                ||00185 R|        17                2093                                                                                
00188 L|        23                5183                                                                                ||00186 R|        23                5104                                                                                
00189 L|        35                4965                                                                                ||00187 R|        35                4950                                                                                
00190 L|        37                4802                                                                                ||00188 R|        37                4735                                                                                
00191 L|        38                3395                                                                                ||00189 R|        38                3452                                                                                
00192 L|        48                6010                                                                                ||00190 R|        48                5883                                                                                
00193  |                                                                                                              ||00191  |                                                                                                              
00194  |   PROD_ID Number of Customers                                                                                ||00192  |   PROD_ID Number of Customers                                                                                
00195  |---------- -------------------                                                                                ||00193  |---------- -------------------                                                                                
00196 L|       125                4679                                                                                ||00194 R|       125                4650                                                                                
00197 L|       135                3996                                                                                ||00195 R|       135                3943                                                                                
00198 L|        33                5389                                                                                ||00196 R|        33                5278                                                                                
00199 L|        40                5972                                                                                ||00197 R|        40                5858                                                                                
00200 L|        41                3738                                                                                ||00198 R|        41                3759                                                                                
00201 L|        45                3549                                                                                ||00199 R|        45                3499                                                                                
00202 L|       114                4230                                                                                ||00200 R|       114                4183                                                                                
00203 L|       130                5428                                                                                ||00201 R|       130                5336                                                                                
00204 L|       131                5013                                                                                ||00202 R|       131                4907                                                                                
00205 L|       136                 397                                                                                ||00203 R|       136                 395                                                                                
00206 L|       140                5097                                                                                ||00204 R|       140                5032                                                                                
00207  |                                                                                                              ||00205  |                                                                                                              
00208  |   PROD_ID Number of Customers                                                                                ||00206  |   PROD_ID Number of Customers                                                                                
00209  |---------- -------------------                                                                                ||00207  |---------- -------------------                                                                                
00210 L|        18                3028                                                                                ||00208 R|        18                2975                                                                                
00211 L|        27                3801                                                                                ||00209 R|        27                3726                                                                                
00212 L|        36                3302                                                                                ||00210 R|        36                3327                                                                                
00213 L|       115                3847                                                                                ||00211 R|       115                3832                                                                                
00214 L|       146                4796                                                                                ||00212 R|       146                4716                                                                                
00215 L|       124                4309                                                                                ||00213 R|       124                4310                                                                                
00216 L|       142                3136                                                                                ||00214 R|       142                3071                                                                                
00217 L|       145                2814                                                                                ||00215 R|       145                2807                                                                                
00218 L|        15                2122                                                                                ||00216 R|        15                2105                                                                                
00219 L|        19                2617                                                                                ||00217 R|        19                2630                                                                                
00220 L|        39                3433                                                                                ||00218 R|        39                3404                                                                                
00221  |                                                                                                              ||00219  |                                                                                                              
00222  |   PROD_ID Number of Customers                                                                                ||00220  |   PROD_ID Number of Customers                                                                                
00223  |---------- -------------------                                                                                ||00221  |---------- -------------------                                                                                
00224 L|       118                4214                                                                                ||00222 R|       118                4200                                                                                
00225 L|       126                4253                                                                                ||00223 R|       126                4202                                                                                
00226 L|       127                4757                                                                                ||00224 R|       127                4710                                                                                
00227 L|        16                2384                                                                                ||00225 R|        16                2367                                                                                
00228 L|       122                1572                                                                                ||00226 R|       122                1559                                                                                
00229 L|       139                4217                                                                                ||00227 R|       139                4106                                                                                
00230  |                                                                                                              ||00228  |                                                                                                              
00231  |72行が選択されました。                                                                                        ||00229  |72行が選択されました。                                                                                        
00232  |                                                                                                              ||00230  |                                                                                                              
00233 L|経過: 00:00:00.90                                                                                             ||00231 R|経過: 00:00:00.73                                                                                             
00234  |                                                                                                              ||00232  |                                                                                                              
00235  |実行計画                                                                                                      ||00233  |実行計画                                                                                                      
00236  |----------------------------------------------------------                                                    ||00234  |----------------------------------------------------------                                                    
00237 L|Plan hash value: 2475333094                                                                                   ||00235 R|Plan hash value: 3604305554                                                                                   
00238  |                                                                                                              ||00236  |                                                                                                              
00239 L|------------------------------------------------------------------------------------------------------------  ||00237 R|----------------------------------------------------------------------------------------------                
00240 L|| Id  | Operation              | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |  ||00238 R|| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                
00241 L|------------------------------------------------------------------------------------------------------------  ||00239 R|----------------------------------------------------------------------------------------------                
00242 L||   0 | SELECT STATEMENT       |           |    72 |  1224 |       |  2638   (2)| 00:00:01 |       |       |  ||00240 R||   0 | SELECT STATEMENT     |       |    72 |   648 |   537   (6)| 00:00:01 |       |       |                
00243 L||   1 |  HASH GROUP BY         |           |    72 |  1224 |       |  2638   (2)| 00:00:01 |       |       |  ||00241 R||   1 |  HASH GROUP BY APPROX|       |    72 |   648 |   537   (6)| 00:00:01 |       |       |                
00244 L||   2 |   VIEW                 | VM_NWVW_1 |   359K|  5966K|       |  2638   (2)| 00:00:01 |       |       |  ||00242 R||   2 |   PARTITION RANGE ALL|       |   918K|  8075K|   514   (1)| 00:00:01 |     1 |    28 |                
00245 L||   3 |    HASH GROUP BY       |           |   359K|  3158K|    17M|  2638   (2)| 00:00:01 |       |       |  ||00243 R||   3 |    TABLE ACCESS FULL | SALES |   918K|  8075K|   514   (1)| 00:00:01 |     1 |    28 |                
00246 L||   4 |     PARTITION RANGE ALL|           |   918K|  8075K|       |   514   (1)| 00:00:01 |     1 |    28 |  ||00244 R|----------------------------------------------------------------------------------------------                
00247 L||   5 |      TABLE ACCESS FULL | SALES     |   918K|  8075K|       |   514   (1)| 00:00:01 |     1 |    28 |  ||       |                                                                                                              
00248 L|------------------------------------------------------------------------------------------------------------  ||       |                                                                                                              
00249  |                                                                                                              ||00245  |                                                                                                              
00250  |                                                                                                              ||00246  |                                                                                                              
00251  |統計                                                                                                          ||00247  |統計                                                                                                          
00252  |----------------------------------------------------------                                                    ||00248  |----------------------------------------------------------                                                    
00253 L|       2432  recursive calls                                                                                  ||00249 R|       2313  recursive calls                                                                                  
00254  |          0  db block gets                                                                                    ||00250  |          0  db block gets                                                                                    
00255 L|       5669  consistent gets                                                                                  ||00251 R|       5458  consistent gets                                                                                  
00256 L|       1794  physical reads                                                                                   ||00252 R|       1765  physical reads                                                                                   
00257  |          0  redo size                                                                                        ||00253  |          0  redo size                                                                                        
00258 L|       2133  bytes sent via SQL*Net to client                                                                 ||00254 R|       2134  bytes sent via SQL*Net to client                                                                 
00259  |        595  bytes received via SQL*Net from client                                                           ||00255  |        595  bytes received via SQL*Net from client                                                           
00260  |          6  SQL*Net roundtrips to/from client                                                                ||00256  |          6  SQL*Net roundtrips to/from client                                                                
00261 L|        239  sorts (memory)                                                                                   ||00257 R|        229  sorts (memory)                                                                                   
00262  |          0  sorts (disk)                                                                                     ||00258  |          0  sorts (disk)                                                                                     
00263  |         72  rows processed                                                                                   ||00259  |         72  rows processed                                                                                   
00264 L|                                                                                                              ||       |                                                                                                              

何が違うのか？

行番号の後に「L」あるいは「R」がついている行は、左右の結果が異なるものを示している。

近似値というからには両者はほとんど同じ結果でたまに違う値となることを予想していたのだが、あにはからんや（72行という結果は同じだが）すべての行で異なるカウント値となった。

また、統計情報はほとんど同じだが、実行計画が大きく異なっている。

COUNT関数の場合「PARTITION RANGE ALL」で全行を取得した後、「HASH GROUP BY」で約35万9千行のビユーを作成（その際17MBのHASH AREAを使用）し、さらに「SORT GROUP BY」で72行に絞り込んでいる。

一方、APPROX_COUNT_DISTINCT関数の場合全行を取得するまでは同じだが、「SORT GROUP BY APPROX」という新たなオペレーションによって一気に72行まで絞り込んでいる。その際コストの上積み分もほとんどない。（514→538）

今回の場合、テーブルサイズがそれほど大きくないので、実行時間で大きな違いはなかったが、巨大なテーブルの場合は「HASH GROUP BY」オペレーションの負荷が甚大になることが予想されるので、両者の違いが顕著になるかもしれない。

正確なカウントは必要なのか？

検索条件を入力して「◯件ヒットしました。」と表示させるアプリケーションは多いが、その値の正確性を１桁単位で求めてもあまり意味のないことが多い。

チューニングの現場ではCOUNT関数の負荷が問題となっていることが多いので、このAPPROX_COUNT_DISTINCT関数は意外と使えるかもしれない。

今週の名言

「自分が多数派の側にいると気付いたら、もう意見を変えてもいい頃だ。」
マーク・トウェイン

最近、マーク・トウェインの名言が多いですが、偶然です。

DBバッファの統計を調査する。

前回は、stats$snapshot表からスナップショット一覧を取得するSQL文を紹介しました。
今回から、応用編としてこの表とパフォーマンス・データが格納された表を結合して、１日の統計情報の推移を見てみます。
最初は取っ付き易いDBバッファについて調べてみましょう。

DBバッファヒット率の計算式

DBバッファの目的は、頻繁にアクセスされるDBブロックをなるべくメモリ（DBバッファ）に保持しておくことで、低速なディスクI/Oを回避することです。DBバッファ・ヒット率とは、要求されたDBブロックがバッファ内に存在していた割合を示すもので、以下の計算式によります。

DBバッファヒット率
＝((CONSISTENT_GETS + DB_BLOCK_GETS)-(PHYSICAL_READS))➗(CONSISTENT_GETS + DB_BLOCK_GETS)✖️100

データ取得SQL文

DBバッファヒット率を取得する方法はいくつかありますが、ここではSTATS$BUFFER_POOL_STATISTICS表を使用する方法を紹介します。
この表はV$BUFFER_POOL_STATISTICSのスナップショットを保持しているものです。
また、DBバッファヒット率だけではなくBuffer Busy Wait統計情報も一緒に取得します。

今回のSTATSPACK環境は前回とは違って１時間ごとにスナップショットを取得している環境です。

select
os.INSTANCE_NUMBER
,to_char(trunc(os.SNAP_TIME,'mi'),'yyyy/mm/dd hh24:mi') sdate
--,os.SNAP_ID SNAP_ID1
--,ns.SNAP_ID SNAP_ID2
,nb.NAME buffer_pool_name
,decode(( (nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
         +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)),0,0,  -- 0除算回避
(trunc((((nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
         +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)
         )
         -(nb.PHYSICAL_READS  - ob.PHYSICAL_READS)
        )
        /((nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
         +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)
         )*100,1
       )
)
) bhr
,nb.BUFFER_BUSY_WAIT - ob.BUFFER_BUSY_WAIT bbw
from
STATS$SNAPSHOT os
,STATS$SNAPSHOT ns
,STATS$BUFFER_POOL_STATISTICS ob
,STATS$BUFFER_POOL_STATISTICS nb
where 1=1
and trunc(os.SNAP_TIME,'mi') between to_date('xxxx/xx/xx 00','yyyy/mm/dd hh24')
                                 and to_date('xxxx/xx/xx 23','yyyy/mm/dd hh24')
and trunc(ns.SNAP_TIME,'mi') = trunc(os.SNAP_TIME,'mi') + 1/24
and os.SNAP_ID               = ob.SNAP_ID
and os.DBID                  = ob.DBID
and os.INSTANCE_NUMBER       = ob.INSTANCE_NUMBER
and ns.SNAP_ID               = nb.SNAP_ID
and ns.DBID                  = nb.DBID
and os.INSTANCE_NUMBER       = ns.INSTANCE_NUMBER
and os.INSTANCE_NUMBER       = nb.INSTANCE_NUMBER
and os.INSTANCE_NUMBER       = 1
order by
os.instance_number
,to_char(os.SNAP_TIME,'yyyy/mm/dd hh24mi');

実行例

実行例は以下のようになります。

SQL> set pages 50
SQL> col BHR for 990.0
SQL> select
  2   os.INSTANCE_NUMBER
  3  ,to_char(trunc(os.SNAP_TIME,'mi'),'yyyy/mm/dd hh24:mi') sdate
  4  --,os.SNAP_ID SNAP_ID1
  5  --,ns.SNAP_ID SNAP_ID2
  6  ,nb.NAME buffer_pool_name
  7  ,decode(( (nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
  8           +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)),0,0,  -- 0除算回避
  9   (trunc((((nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
10           +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)
11           )
12           -(nb.PHYSICAL_READS  - ob.PHYSICAL_READS)
13          )
14          /((nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
15           +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)
16           )*100,1
17         )
18   )
19  ) bhr
20  ,nb.BUFFER_BUSY_WAIT - ob.BUFFER_BUSY_WAIT bbw
21  from
22   STATS$SNAPSHOT os
23  ,STATS$SNAPSHOT ns
24  ,STATS$BUFFER_POOL_STATISTICS ob
25  ,STATS$BUFFER_POOL_STATISTICS nb
26  where 1=1
27  and trunc(os.SNAP_TIME,'mi') between to_date('2015/07/28 00','yyyy/mm/dd hh24')
28                                   and to_date('2015/07/28 23','yyyy/mm/dd hh24')
29  and trunc(ns.SNAP_TIME,'mi') = trunc(os.SNAP_TIME,'mi') + 1/24
30  and os.SNAP_ID               = ob.SNAP_ID
31  and os.DBID                  = ob.DBID
32  and os.INSTANCE_NUMBER       = ob.INSTANCE_NUMBER
33  and ns.SNAP_ID               = nb.SNAP_ID
34  and ns.DBID                  = nb.DBID
35  and os.INSTANCE_NUMBER       = ns.INSTANCE_NUMBER
36  and os.INSTANCE_NUMBER       = nb.INSTANCE_NUMBER
37  and os.INSTANCE_NUMBER       = 1
38  order by
39   os.instance_number
40  ,to_char(os.SNAP_TIME,'yyyy/mm/dd hh24mi');

INSTANCE_NUMBER SDATE            BUFFER_POOL_NAME        BHR        BBW
--------------- ---------------- -------------------- ------ ----------
              1 2015/07/28 00:00 DEFAULT               100.0          0
              1 2015/07/28 01:00 DEFAULT               100.0          1
              1 2015/07/28 02:00 DEFAULT               100.0          0
              1 2015/07/28 03:00 DEFAULT               100.0          0
              1 2015/07/28 04:00 DEFAULT               100.0          0
              1 2015/07/28 05:00 DEFAULT               100.0          0
              1 2015/07/28 06:00 DEFAULT               100.0          1
              1 2015/07/28 07:00 DEFAULT               100.0          0
              1 2015/07/28 08:00 DEFAULT               100.0          0
              1 2015/07/28 09:00 DEFAULT               100.0          0
              1 2015/07/28 10:00 DEFAULT               100.0          0
              1 2015/07/28 11:00 DEFAULT               100.0          0
              1 2015/07/28 12:00 DEFAULT               100.0          0
              1 2015/07/28 13:00 DEFAULT               100.0          0
              1 2015/07/28 14:00 DEFAULT               100.0          1
              1 2015/07/28 15:00 DEFAULT               100.0          0
              1 2015/07/28 16:00 DEFAULT               100.0          2
              1 2015/07/28 17:00 DEFAULT                99.9          5
              1 2015/07/28 18:00 DEFAULT               100.0          0
              1 2015/07/28 19:00 DEFAULT               100.0          0
              1 2015/07/28 20:00 DEFAULT               100.0          0
              1 2015/07/28 21:00 DEFAULT               100.0          0
              1 2015/07/28 22:00 DEFAULT               100.0          0
              1 2015/07/28 23:00 DEFAULT               100.0          0

24行が選択されました。

この環境はアクティビティがほとんどないので、DBバッファヒット率はほぼ100%ですが、お手元のSTATSPACKではどのような結果が得られるでしょうか？
この結果を、MS Excelのシートにコピー＆ペーストしてグラフにしていくのですが、その要領は次回で紹介します。

テーブルの結合列について

STATS$SNAPSHOT表の主キーを構成する列を確認すると「SNAP_ID,DBID,INSTANCE_NUMBER」の３つの列から成っていることがわかります。

SQL> select TABLE_NAME,CONSTRAINT_NAME,COLUMN_NAME,POSITION
  2  from USER_CONS_COLUMNS
  3  where CONSTRAINT_NAME = 'STATS$SNAPSHOT_PK'
  4  order by POSITION;

TABLE_NAME      CONSTRAINT_NAME    COLUMN_NAME       POSITION
--------------- ------------------ --------------- ----------
STATS$SNAPSHOT  STATS$SNAPSHOT_PK  SNAP_ID                  1
STATS$SNAPSHOT  STATS$SNAPSHOT_PK  DBID                     2
STATS$SNAPSHOT  STATS$SNAPSHOT_PK  INSTANCE_NUMBER          3

一方、結合表であるSTATS$BUFFER_POOL_STATISTICS表の外部キーを確認すると、同様に「SNAP_ID,DBID,INSTANCE_NUMBER」となっていることがわかります。

SQL> select TABLE_NAME,CONSTRAINT_NAME,COLUMN_NAME,POSITION
  2  from USER_CONS_COLUMNS
  3  where CONSTRAINT_NAME = 'STATS$BUFFER_POOL_STATS_FK'
  4  order by POSITION;

TABLE_NAME                    CONSTRAINT_NAME              COLUMN_NAME       POSITION
----------------------------- ---------------------------- --------------- ----------
STATS$BUFFER_POOL_STATISTICS  STATS$BUFFER_POOL_STATS_FK   SNAP_ID                  1
STATS$BUFFER_POOL_STATISTICS  STATS$BUFFER_POOL_STATS_FK   DBID                     2
STATS$BUFFER_POOL_STATISTICS  STATS$BUFFER_POOL_STATS_FK   INSTANCE_NUMBER          3

STATSPACKデータのグラフ化は、基本的にSTATS$SNAPSHOT表と関連する表を結合してデータを取得しますが、結合列はこれら３つの列を必ず指定するようにしましょう。

続く

今週の名言

「成功の秘訣は、職業をレジャーとみなすことだ。」
マーク・トウェイン

STATSPACKについて書かれた名著

私の手元に１０年以上前に買った素晴らしい本があります。ORACLE9i ハイパフォーマンスチューニング―STATSPACK編という本で、実に１３年近く前に出版されすでに絶版となっていますが、古本市場ではまだ入手できるようです。

私はこの本を自炊（スキャナで読み込んでPDF化）しiPadに入れているのですが、今でも時々読み返すことがあります。

Oracleは今や12cとなっていますが、個人的にはOracleの基本的なアーキテクチャは8iくらいで出来上がり、9iは各種管理機能の自動化が進み始めたバージョンという認識です。

まだ、ASMは登場しておらずRACもあまり洗練されたものではありませんでしたが、インスタンス単体レベルの基本機能は9iでかなり完成されているので、9i時代に出版された本は今でも十分読むに価する内容です。また、8iや9i時代に出版されたOracle関連本は内容の濃さからより深いOracleを理解する上で非常に役に立ちます。

その中でもこの本にはSTATSPACKデータを活用することで、データベースの状態を把握するノウハウが満載です。

特に、インスタンスの情報をMS Excelでグラフ化することで傾向を分析し、問題が起きる前にプロアクティブな対応をする情報を得るテクニックはかなり有益です。

Oracle Enterprise Manager(OEM)でもAWRデータをグラフ表示させることは可能ですが、特にコスト上の理由からStandard Editionを使っている環境でAWRが使用できない場合でも、STATSPACKデータをExcelでグラフ化できれば、かなり高度な運用が可能になるかもしれません。

定期的にスナップショットを取って、２つのスナップショット間の差分データでレポートを作成するという仕組みはSTATSPACKもAWRも同じなので、実はグラフ化自体はAWRデータでも可能です。

しかし、このブログではORACLE9i ハイパフォーマンスチューニング―STATSPACK編で紹介されたSTATSPACKデータを活用するというコンセプトを継承しつつ、私が考案した独自スクリプトを紹介していきたいと思います。

ちなみに、この方法は以前実際に業務で使用したこともあります。

基本はスナップ番号のリスト

STATSPACKは、AWRのように最初から使えるようになっているものではないので、自分でインストールする必要がありますし、スナップショットを定期的に自動取得する設定も自分で行う必要があります。

この辺の要領は割愛しますが、STATSPACK がインストールされ３０分ごとにスナプッショトが取得されている環境を例に説明を始めます。

スナップショットが実行される度に、stats$snapshot表に１行レコードがインサートされます。

ある時間帯に実行されたスナップショットの番号（stats$snapshot.snap_id）と１つ前のスナップ番号の組み合わせが重要です。ある日付を指定し、スナップショット取得インターバルごとに２つのスナップショット番号を一覧化するSQLスクリプトが以下となります。

スナップ番号一覧取得スクリプト

select
 os.instance_number
,to_char(trunc(os.SNAP_TIME,'mi'),'yyyy/mm/dd hh24:mi') sdate
,os.SNAP_ID SNAP_ID1
,ns.SNAP_ID SNAP_ID2
from
 perfstat.stats$snapshot os
,perfstat.stats$snapshot ns
where 1=1
and trunc(os.SNAP_TIME,'mi') between to_date('xxxx/xx/xx 00','yyyy/mm/dd hh24') and to_date('xxxx/xx/xx+1 00','yyyy/mm/dd hh24') -- 日付で抽出
and trunc(ns.SNAP_TIME,'mi') = trunc(os.SNAP_TIME,'mi') + 1/48 -- インターバル分だけずらす
and os.instance_number = ns.instance_number
order by
 os.instance_number
,to_char(os.SNAP_TIME,'yyyy/mm/dd hh24mi');

実際は、１０行目の「xxxx/xx/xx」の部分に特定の日付を指定します。

１１行目の「1/48」は３０分のインターバルという意味で、多くの場合の１時間のインターバルであれば「1/24」となります。

もし、RAC環境でSTATSPACKを使っている場合は、「os.instance_number =1」などのインスタンス番号を指定する条件を追加してください。

実行例

06:59:43 SQL> run
  1  select
  2   os.instance_number
  3  ,to_char(trunc(os.SNAP_TIME,'mi'),'yyyy/mm/dd hh24:mi') sdate
  4  ,os.SNAP_ID SNAP_ID1
  5  ,ns.SNAP_ID SNAP_ID2
  6  from
  7   perfstat.stats$snapshot os
  8  ,perfstat.stats$snapshot ns
  9  where 1=1
 10  and trunc(os.SNAP_TIME,'mi') between to_date('2015/07/21 00','yyyy/mm/dd hh24') and to_date('2015/07/22 00','yyyy/mm/dd hh24') -- 日付で抽出
 11  and trunc(ns.SNAP_TIME,'mi') = trunc(os.SNAP_TIME,'mi') + 1/48 -- インターバル分だけずらす
 12  and os.instance_number = ns.instance_number
 13  order by
 14   os.instance_number
 15* ,to_char(os.SNAP_TIME,'yyyy/mm/dd hh24mi')

INSTANCE_NUMBER SDATE              SNAP_ID1   SNAP_ID2
--------------- ---------------- ---------- ----------
              1 2015/07/21 00:27         62         63
              1 2015/07/21 00:57         63         64
              1 2015/07/21 01:27         64         65
              1 2015/07/21 01:57         65         66
              1 2015/07/21 02:27         66         67
              1 2015/07/21 02:57         67         68
              1 2015/07/21 03:27         68         69
              1 2015/07/21 03:57         69         70
              1 2015/07/21 04:27         70         71
              1 2015/07/21 04:57         71         72
              1 2015/07/21 05:27         72         73

INSTANCE_NUMBER SDATE              SNAP_ID1   SNAP_ID2
--------------- ---------------- ---------- ----------
              1 2015/07/21 05:57         73         74
              1 2015/07/21 06:27         74         75
              1 2015/07/21 06:57         75         76
              1 2015/07/21 07:27         76         77
              1 2015/07/21 07:57         77         78
              1 2015/07/21 08:27         78         79
              1 2015/07/21 08:57         79         80
              1 2015/07/21 09:58         81         82
              1 2015/07/21 10:28         82         83
              1 2015/07/21 10:58         83         84
              1 2015/07/21 11:28         84         85

INSTANCE_NUMBER SDATE              SNAP_ID1   SNAP_ID2
--------------- ---------------- ---------- ----------
              1 2015/07/21 11:58         85         86
              1 2015/07/21 12:28         86         87
              1 2015/07/21 12:58         87         88
              1 2015/07/21 13:28         88         89
              1 2015/07/21 13:58         89         90
              1 2015/07/21 14:28         90         91
              1 2015/07/21 14:58         91         92
              1 2015/07/21 15:28         92         93
              1 2015/07/21 15:58         93         94
              1 2015/07/21 16:28         94         95
              1 2015/07/21 16:58         95         96

INSTANCE_NUMBER SDATE              SNAP_ID1   SNAP_ID2
--------------- ---------------- ---------- ----------
              1 2015/07/21 17:28         96         97
              1 2015/07/21 17:58         97         98
              1 2015/07/21 18:28         98         99
              1 2015/07/21 18:58         99        100
              1 2015/07/21 19:28        100        101
              1 2015/07/21 19:58        101        102
              1 2015/07/21 20:28        102        103
              1 2015/07/21 20:58        103        104
              1 2015/07/21 21:28        104        105
              1 2015/07/21 22:29        111        112
              1 2015/07/21 22:59        112        113

INSTANCE_NUMBER SDATE              SNAP_ID1   SNAP_ID2
--------------- ---------------- ---------- ----------
              1 2015/07/21 23:29        113        114
              1 2015/07/21 23:59        114        115

46行が選択されました。

手元の環境（ノートPC）で作ったサンプルなので１日２４時間で４８レコードとなるはずなのに２つ足りないのはご愛嬌ですが、３０分間隔で連続する２つのスナップ番号の組が得られていることがおわかりでしょうか？

次回以降で、この基本的な問い合わせをベースとして様々なパフォーマンス・データをグラフ化していきます。

続く