Oracle」カテゴリーアーカイブ

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

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

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

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

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

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

作成用スクリプト

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

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

補足説明

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

実行例

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

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

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

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

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

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

明日はYousuke Yadaさんです。

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

不可視索引のその後

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


INDEXヒント+USE_INVISIBLE_INDEXESヒント

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


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

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

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

検証環境

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

SQL> select * from v$version;

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

SQL> show user
USER is "SH"

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

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

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

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

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

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

基本問合せ

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

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

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

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

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

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

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

索引SALES_CUST_BIXを不可視にする

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

SQL> alter index SALES_CUST_BIX invisible;

Index altered.

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

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

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

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

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

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

索引SALES_CHANNEL_BIXを不可視にする

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

SQL> alter index SALES_CHANNEL_BIX invisible;

Index altered.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

索引SALES_CUST_BIXを可視に変更する

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

SQL> alter index SALES_CUST_BIX visible;

Index altered.

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

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

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

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

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

まとめ

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

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

今回はここまで

インデックス領域を含まない全体バックアップ(RMAN)

マニュアルを読んでいて気がついた

先日、12cの概要マニュアルを何気なく読んでいたら以下の文章が目に入ってきた。

Oracle® Database概要
12cリリース1 (12.1)
B71299-07

索引記憶域
「索引セグメントの表領域は、所有者のデフォルト表領域またはCREATE INDEX文で明示的に指定された表領域です。管理を容易にするために、索引をその表とは別の表領域に格納できます。たとえば、索引のみを含む表領域は再構築できるため、これらの表領域をバックアップしないよう指定することによって、バックアップに必要な時間と記憶域を削減できます。」

バックアップ領域の問題

以前関わっていた某超巨大システムでは、バックアップ領域は常に問題を抱えていた。

時間の経過と共にデータ量が増加し、バックアップ時間が長くなるだけでなく、バックアップ先のディスク容量が逼迫して新たにストレージを追加するという問題が深刻になっていた。

データ量が多いシステムほどバックアップに関する悩みは大きいが、上に紹介したマニュアルの記述はまさに目からウロコであった。仮にインデックスが占めるサイズが全体の3割であれば、バックアップ容量と時間を一気に3割削減できることになる。これは大きい。

万一ストレージが全損してバックアップからリカバリすることになった場合、インデックスを再作成する手間と時間がかかるが、日常のバックアップ容量と時間を削減することのメリットの方が大きい。

テーブルとインデックスの表領域を分ける意味

昔は、テーブルとインデックスのI/O競合を避けるために、両者を格納する表領域を分けるということはごく当たり前に行われていた。

しかし、ASMが一般的になりSAMEアーキテクチャによって理論的にはI/Oホットスポットが発生しなくなってからは、管理が容易という理由でデータとインデックスを1つの大きな表領域で管理することが普通になった。

テーブル用とインデック用の表領域をサイジングを間違って空き容量にアンバランスを発生させたりということに頭を使うよりも、全部を1つにしてしまえば単純である。

ところが、バックアップ対象を分けるという意味では、テーブルとインデックス用の表領域を別個に設けるという考え方が新しく成り立つ。

インデックス用表領域を含まない全体バックアップ

インデックスをインデックス用表領域に移動する

それでは実際にインデックス表領域を全体バックアップに含まない方法を確認してみよう。

まず、インデックスをインデックス用表領域に移動させる。これは個々のインデックスに対して必要であるが、一度行ってしまえばよい。

SQL> alter index SCOTT.PK_EMP rebuild tablespace USER_INDEX;

Index altered.

RMANの起動とデフォルト設定の確認

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on 金 6月 3 06:11:07 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1439336626)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

除外表領域の指定

特定の表領域をバックアップ対象から外すためには「CONFIGURE EXCLUDE FOR TABLESPACE」コマンドによって設定する。

これによりハイライト行に示すように除外表領域が指定される。

RMAN> configure exclude for tablespace USER_INDEX;

Tablespace USER_INDEX will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
..........................................................
CONFIGURE EXCLUDE FOR TABLESPACE 'USER_INDEX';
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

バックアップの実行

除外設定を行った後は、通常と同様に「BACKUP DATABASE」コマンドで全体バックアップを取得する。

RMAN> backup database;

Starting backup at 16-06-03
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
file 6 is excluded from whole database backup
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/opt/oracle/app/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/opt/oracle/app/oradata/orcl/system01.dbf
input datafile file number=00003 name=/opt/oracle/app/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/opt/oracle/app/oradata/orcl/example01.dbf
input datafile file number=00004 name=/opt/oracle/app/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-06-03
channel ORA_DISK_1: finished piece 1 at 16-06-03
piece handle=/opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T061125_co187xbq_.bkp tag=TAG20160603T061125 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-06-03
channel ORA_DISK_1: finished piece 1 at 16-06-03
piece handle=/opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T061125_co189s2r_.bkp tag=TAG20160603T061125 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-06-03

「LIST BACKUP」コマンドでバックアップの内容を確認すると、USER_INDEX表領域が含まれていないことがわかる。

RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    9.36M      DISK        00:00:01     16-06-03
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T060715
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T060715_co18053x_.bkp
  SPFILE Included: Modification time: 16-06-02
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2328889      Ckp time: 16-06-03

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1.24G      DISK        00:00:49     16-06-03
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T061125
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T061125_co187xbq_.bkp
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time Name
  ---- -- ---- ---------- -------- ----
  1       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/system01.dbf
  2       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/sysaux01.dbf
  3       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/undotbs01.dbf
  4       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/users01.dbf
  5       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    9.36M      DISK        00:00:04     16-06-03
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T061125
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T061125_co189s2r_.bkp
  SPFILE Included: Modification time: 16-06-03
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2329721      Ckp time: 16-06-03

除外設定を無効にする

NOEXCLUDE句を指定すると除外設定を無効にしてバックアップを取得することができる。

バックアップのログにUSER_INDEX表領域が含まれている。(12行目)

RMAN> backup database noexclude;

Starting backup at 16-06-03
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/opt/oracle/app/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/opt/oracle/app/oradata/orcl/system01.dbf
input datafile file number=00003 name=/opt/oracle/app/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/opt/oracle/app/oradata/orcl/example01.dbf
input datafile file number=00006 name=/opt/oracle/app/oradata/orcl/user_index01.dbf    --バックアップ対象となっている
input datafile file number=00004 name=/opt/oracle/app/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-06-03
channel ORA_DISK_1: finished piece 1 at 16-06-03
piece handle=/opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T065225_co1bnslm_.bkp tag=TAG20160603T065225 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-06-03
channel ORA_DISK_1: finished piece 1 at 16-06-03
piece handle=/opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T065225_co1boxno_.bkp tag=TAG20160603T065225 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-06-03

LIST BACKUPでバックアップの内容を確認すると、やはりUSER_INDEX表領域がバックアップに含まれていることがわかる。

RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    9.36M      DISK        00:00:01     16-06-03
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T060715
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T060715_co18053x_.bkp
  SPFILE Included: Modification time: 16-06-02
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2328889      Ckp time: 16-06-03

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1.24G      DISK        00:00:49     16-06-03
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T061125
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T061125_co187xbq_.bkp
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time Name
  ---- -- ---- ---------- -------- ----
  1       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/system01.dbf
  2       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/sysaux01.dbf
  3       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/undotbs01.dbf
  4       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/users01.dbf
  5       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    9.36M      DISK        00:00:04     16-06-03
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T061125
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T061125_co189s2r_.bkp
  SPFILE Included: Modification time: 16-06-03
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2329721      Ckp time: 16-06-03

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    1.24G      DISK        00:00:27     16-06-03
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T065225
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T065225_co1bnslm_.bkp
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time Name
  ---- -- ---- ---------- -------- ----
  1       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/system01.dbf
  2       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/sysaux01.dbf
  3       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/undotbs01.dbf
  4       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/users01.dbf
  5       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/example01.dbf
  6       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/user_index01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    9.36M      DISK        00:00:01     16-06-03
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T065225
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T065225_co1boxno_.bkp
  SPFILE Included: Modification time: 16-06-03
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2332008      Ckp time: 16-06-03

今日はここまで。

不可視索引の裏でオプティマイザはどう動いているか?

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

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

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

コストベース・オプティマイザの挙動を調べるには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は実行計画算出に使用されるようになる。

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

10053トレースの終了

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

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

Session altered.

今日はここまで。

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

不可視索引とは

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

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

不可視索引の用途

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

上記マニュアルには

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

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

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

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

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

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

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

不可視索引関連のヒント

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

  • USE_INVISIBLE_INDEXES(NO_USE_INVISIBLE_INDEXES)

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

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

ヒント句を検証してみた

不可視索引の作成

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

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

Index created.

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

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

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

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

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

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


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

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

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

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

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

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

SQL> show parameter optimizer_use_invisible_indexes

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

SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> show parameter optimizer_use_invisible_indexes

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

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

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

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


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

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

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

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

ヒント句を試してみる

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

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

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

INDEXヒントのみ

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

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

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


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

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

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

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

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

USE_INVISIBLE_INDEXESヒントのみ

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

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

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


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

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

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

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

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

INDEXヒント+USE_INVISIBLE_INDEXESヒント

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

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

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


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

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

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

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

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

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

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

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

今日はここまで。

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

前回の投稿他

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

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


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


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

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

調査用SQL

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

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

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

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

実行例

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

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

7 rows selected.

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

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

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

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

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

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

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

今日はここまで

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

sys.col_usage$とは

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

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

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

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

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

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

sys.col_usage$表のカラム

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

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

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

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

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

Flashback Dropの検証④(最終回)

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が結果として間違った方向にミスリードし、小さな問題を大きくしてしまうとすればこれほど残念なことはない。

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

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

もう20年近く前のことで私がまだ経験の浅いエンジニアだったころ、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」のものがあることに気がついた。

試しにこのテーブルをフラッシュバックしたところ、テーブル構造(メタデータ)が復元されたにも関わらずデータが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;

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

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

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

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

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

終わり

Flashback Dropの検証③

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

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

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

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

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

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

JPOUG20160223.001

初期状態

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を作成したところまでを図にすると以下のようになる。

JPOUG20160223.018

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

JPOUG20160223.019
エクステントの空き領域が初期状態と同じ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

JPOUG20160223.020
この時点で、セグメント使用率が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

JPOUG20160223.021
表領域としては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

JPOUG20160223.022

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

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


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

JPOUG20160223.023

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


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


JPOUG20160223.024

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

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

今日はここまで

Flashback Dropの検証②

前回のおさらい

JPOUG20160223.001

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

オブジェクトはテーブルとインデックス、あわせて5つ。すべて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した状態を図に示すと以下のようになる。

JPOUG20160223.012

JPOUG20160223.013
参照整合性制約(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エラーが発生する。

JPOUG20160223.014

JPOUG20160223.015

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

今回はここまで。