統計情報がない時CBOは何をするだろうか。

この前統計情報がない場合オプティマィザが費用(Cost)をどのように計算するかに対する問い合わせがありました。基本的な方法は次のようです。

動的サンプリング(Dynamic Sampling)が利用できればサンプリングにより統計情報を作った後で費用を計算する。
動的サンプリングが利用できなければ内部的に決められている基本値を使用する。

ここで内部的に決められている基本値ということの意味が気になります。幸いにオラクルマニュアルとWolfgang Breitlingが作成した有名な白書によく説明してあります。

簡単なテストを通じて整理してみます。

まず次のようにテーブルを作ります。


UKJA@ukja1021> -- case1
UKJA@ukja1021> create table t1
  2  as
  3  select level as c1, 'x' as c2
  4  from dual
  5  connect by level <= 10000;

Table created.

Elapsed: 00:00:00.04
UKJA@ukja1021> 
UKJA@ukja1021> create index t1_n1 on t1(c1);

Index created.

Elapsed: 00:00:00.03

動的サンプリングを動作しないようにする上、OPTIMIZER_MODE値をALL_ROWSにするによってオプティマィザが内部的な基本値を使用するようにします。


UKJA@ukja1021> alter session set optimizer_dynamic_sampling = 1;

Session altered.

Elapsed: 00:00:00.00
UKJA@ukja1021> alter session set optimizer_mode = all_rows;

Session altered.

実行計画を見ましょうか。たとえ統計情報はないんですけど、予測行件数が31で結構もっともらしく予測しました。


UKJA@ukja1021> explain plan for
  2  select * from t1
  3  where c1 = 1;

Explained.

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    31 |   496 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    31 |   496 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |    12 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

31の予測行件数がどこから来たのかは10053トレースファイルによく書き込んであります。


***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1  (NOT ANALYZED)
    #Rows: 3104  #Blks:  38  AvgRowLen:  100.00
Index Stats::
  Index: T1_N1  Col#: 1
    LVLS: 1  #LB: 21  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 16.00
***************************************
SINGLE TABLE ACCESS PATH
  Column (#1): C1(NUMBER)  NO STATISTICS (using defaults)
    AvgLen: 22.00 NDV: 97 Nulls: 0 Density: 0.010309
  Table: T1  Alias: T1     
    Card: Original: 3104  Rounded: 31  Computed: 31.04  Non Adjusted: 31.04
  Access Path: TableScan
    Cost:  10.15  Resp: 10.15  Degree: 0
      Cost_io: 10.00  Cost_cpu: 892035
      Resp_io: 10.00  Resp_cpu: 892035
  Access Path: index (AllEqGuess)
    Index: T1_N1
    resc_io: 2.00  resc_cpu: 29893
    ix_sel: 0.004  ix_sel_with_filters: 0.004
    Cost: 2.01  Resp: 2.01  Degree: 1
  Best:: AccessPath: IndexRange  Index: T1_N1
         Cost: 2.01  Degree: 1  Resp: 2.01  Card: 31.04  Bytes: 0

上の内容を分析してみると次のようです。

テーブルのブロック数は33である。
したがってテーブルの基本Cardinality = 38*(8192 - 24)/100 = 3103.84 = 3104になった。
したがって列C1のDensityは1 / ( 3103.84 / 32 ) = 0.01030981 = 0.010309である。
したがってWHERE C1 = 1に該当する予測行件数は3104 * 0.010309 = 31になる。

すなわち、テーブルブロック数だけ手に入ればその後は上で言った二つの文書で説明された内部的な公式によって自然に計算されます。ここで問題はテーブルのブロック数をどう手に入れるのです。オラクルマニュアルによると次のように得られます。

セグメントに存在するExtent Mapからブロック数を手に入れる。
万一上の方法が不可能だったら(たとえばTable Functionを使ったりExternal Tableを使用したばあい)100だと仮定する。

上の説明が正しいかExtent Mapを実際に確認してみます。セグメントヘッダーブロックを見ると次のようにExtent Mapが存在します。


UKJA@ukja1021> col header_file new_value header_file
UKJA@ukja1021> col header_block new_value header_block
UKJA@ukja1021> select header_file, header_block
  2  from dba_segments where owner = user and segment_name = 'T1';

HEADER_FILE HEADER_BLOCK
----------- ------------
          7        40990

Elapsed: 00:00:00.03

UKJA@ukja1021> alter system dump datafile &header_file block &header_block;

...
  Extent Map
  -----------------------------------------------------------------
   0x01c0a009  length: 1280  

UKJA@ukja1021> select to_dec('01c0a009') from dual;

TO_DEC('01C0A009')
------------------
          29401097

Extent Mapによると一つのExtentが存在するし現在0番から37番、すなわち38個のブロックが実際に使用されているのが分かります。したがってオプティマィザは38個のブロックが存在すると認識しているのです。


-- dump_dba.sql : http://sites.google.com/site/ukja/sql-scripts-1/c/dump-dba
UKJA@ukja1021> @dump_dba 29401097
...
Dump of First Level Bitmap Block

  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x01c0a009  Length: 64     Offset: 0      
  
   0:Metadata   1:Metadata   2:Metadata   3:Metadata
   4:Metadata   5:Metadata   6:Metadata   7:Metadata
   8:Metadata   9:Metadata   10:Metadata   11:Metadata
   12:Metadata   13:Metadata   14:Metadata   15:Metadata
   16:Metadata   17:Metadata   18:Metadata   19:Metadata
   20:Metadata   21:Metadata   22:FULL   23:FULL
   24:FULL   25:FULL   26:FULL   27:FULL
   28:FULL   29:FULL   30:FULL   31:FULL
   32:FULL   33:FULL   34:FULL   35:FULL
   36:FULL   37:FULL   38:unformatted   39:unformatted
   40:unformatted   41:unformatted   42:unformatted   43:unformatted
   44:unformatted   45:unformatted   46:unformatted   47:unformatted
   48:unformatted   49:unformatted   50:unformatted   51:unformatted
   52:unformatted   53:unformatted   54:unformatted   55:unformatted
   56:unformatted   57:unformatted   58:unformatted   59:unformatted
   60:unformatted   61:unformatted   62:unformatted   63:unformatted
  --------------------------------------------------------

この情報はdbms_space.unused_spaceプロシージャを通じて手に入れた値を一致します。したがってExtent Mapを通じてテーブルのブロック数を手に入れるというマニュアルの内容が事実であることが分かります。


UKJA@ukja1021> exec show_space('T1');
Free Blocks.............................
Total Blocks............................1280
Total Bytes.............................10485760
Total MBytes............................10
Unused Blocks...........................1242
Unused Bytes............................10174464
Last Used Ext FileId....................9
Last Used Ext BlockId...................52489
Last Used Block.........................38

一つ面白いことはテーブルのブロック数を物理的に得るのでとても正確だということです。このために統計情報が全然無い状態で(そして動的サンプリングが動作しないことにもかかわらず)CBO(RBOではなくて)が作った実行計画が以外に立派なのでびっくりする場合があります。生半な統計情報よりは統計情報が初めからないほうが良い場合がたくさんあります。

Dion Cho - Oracle Performance Storyteller

Saturday, November 21, 2009