Costing ohne Statistiken

Im Zusammenhang eines OTN-Threads ist mir heute aufgefallen, dass ich keine klare Vorstellung davon hatte, wie der CBO die Kosten eines Zugriffs berechnet, wenn er keine Statistiken besitzt und kein dynamic sampling verwenden kann. Nun könnte man einwenden, dass das eine eher theoretische Kombination ist, aber ich finde den Fall doch recht interessant. Dazu hier eine leicht überarbeitete Version des Beispiels aus dem Thread:

-- 11.2.0.1 (aber mit 11.1.0.7 ergibt sich das gleiche Verhalten)
-- 8K blocksize
-- MSSM-Tablespace (für ASSM ergibt sich das gleiche Verhalten,
-- allerdings ist die Anzahl der Tabellenblocks erwartungsgemäß höher)

drop table test_tbl purge ;

create table test_tbl (id number ) tablespace test_ts;

begin
 for i in 1 .. 10000 loop
 insert into test_tbl values (i);
 end loop;
 commit;
 end;
/

alter session set optimizer_dynamic_sampling = 0 ;

explain plan for 
select * from test_tbl where id = 9999;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 602094504

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

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

   1 - filter("ID"=9999)

Ohne Statistiken und ohne dynamic sampling kommt der CBO hier zu einer (anscheinend) akkuraten Schätzung der Cardinality. Aber die Situation ändert sich, wenn man einen zusätzlichen Index anlegt - und wieder löscht. Im Thread hatte der Autor den Index als INVISIBLE definiert und vermutet, dass die Index-Statistiken die Grundlage der guten Cardinality-Schätzung seien, was zwar nicht unplausibel klingt, aber offenbar hier nicht der Fall ist (zumal ich den Index in meinem Test erst nach der ersten Query definiere).

create index test_tbl_ix on test_tbl(id);
-- create unique index test_tbl_ix on test_tbl(id);

explain plan for
select * from test_tbl where id = 9999;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 86670887

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_TBL_IX |     1 |    13 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - access("ID"=9999)

explain plan for
select * from test_tbl where id = 9999;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 602094504

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    16 |   208 |    12   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_TBL |    16 |   208 |    12   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter("ID"=9999)

Demnach ändern sich die Kosten erst nach der Löschung des Index. Aber wie ergeben sich die Kosten in den betrachteten Fällen? Dazu ein paar Angaben aus den CBO-Traces der Varianten:

-- Fall 1: kein Index
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST_TBL  Alias: TEST_TBL  (NOT ANALYZED)
    #Rows: 82  #Blks:  1  AvgRowLen:  100.00
Access path analysis for TEST_TBL
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST_TBL[TEST_TBL] 
  Table: TEST_TBL  Alias: TEST_TBL
    Card: Original: 82.000000  Rounded: 1  Computed: 0.82  Non Adjusted: 0.82
  Access Path: TableScan
    Cost:  2.00  Resp: 2.00  Degree: 0
      Cost_io: 2.00  Cost_cpu: 23521
      Resp_io: 2.00  Resp_cpu: 23521
  Best:: AccessPath: TableScan
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 0.82  Bytes: 0


-- Fall 2: non-uinque Index erzeugt
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST_TBL  Alias: TEST_TBL  (NOT ANALYZED)
    #Rows: 82  #Blks:  1  AvgRowLen:  100.00
Index Stats::
  Index: TEST_TBL_IX  Col#: 1
    LVLS: 1  #LB: 21  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 16.00
Access path analysis for TEST_TBL
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST_TBL[TEST_TBL] 
  Table: TEST_TBL  Alias: TEST_TBL
    Card: Original: 82.000000  Rounded: 1  Computed: 0.82  Non Adjusted: 0.82
  Access Path: TableScan
    Cost:  2.00  Resp: 2.00  Degree: 0
      Cost_io: 2.00  Cost_cpu: 23521
      Resp_io: 2.00  Resp_cpu: 23521
  Access Path: index (index (FFS))
    Index: TEST_TBL_IX
    resc_io: 12.00  resc_cpu: 1849550
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Access Path: index (FFS)
    Cost:  12.00  Resp: 12.00  Degree: 1
      Cost_io: 12.00  Cost_cpu: 1849550
      Resp_io: 12.00  Resp_cpu: 1849550
  Access Path: index (AllEqGuess)
    Index: TEST_TBL_IX
    resc_io: 1.00  resc_cpu: 15971
    ix_sel: 0.004000  ix_sel_with_filters: 0.004000 
    Cost: 1.00  Resp: 1.00  Degree: 1
  Best:: AccessPath: IndexRange
  Index: TEST_TBL_IX
         Cost: 1.00  Degree: 1  Resp: 1.00  Card: 0.82  Bytes: 0


-- Fall 3: unique Index erzeugt
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST_TBL  Alias: TEST_TBL  (NOT ANALYZED)
    #Rows: 82  #Blks:  1  AvgRowLen:  100.00
Index Stats::
  Index: TEST_TBL_IX  Col#: 1
    LVLS: 1  #LB: 20  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 16.00
***************************************
1-ROW TABLES:  TEST_TBL[TEST_TBL]#0
Access path analysis for TEST_TBL
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST_TBL[TEST_TBL] 
  Table: TEST_TBL  Alias: TEST_TBL
    Card: Original: 82.000000  Rounded: 1  Computed: 0.82  Non Adjusted: 0.82
  Access Path: TableScan
    Cost:  2.00  Resp: 2.00  Degree: 0
      Cost_io: 2.00  Cost_cpu: 23521
      Resp_io: 2.00  Resp_cpu: 23521
  Access Path: index (index (FFS))
    Index: TEST_TBL_IX
    resc_io: 12.00  resc_cpu: 1842429
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Access Path: index (FFS)
    Cost:  12.00  Resp: 12.00  Degree: 1
      Cost_io: 12.00  Cost_cpu: 1842429
      Resp_io: 12.00  Resp_cpu: 1842429
  Access Path: index (UniqueScan)
    Index: TEST_TBL_IX
    resc_io: 1.00  resc_cpu: 8171
    ix_sel: 0.012195  ix_sel_with_filters: 0.012195 
    Cost: 1.00  Resp: 1.00  Degree: 1
  Access Path: index (AllEqUnique)
    Index: TEST_TBL_IX
    resc_io: 1.00  resc_cpu: 8171
    ix_sel: 0.004000  ix_sel_with_filters: 0.004000 
    Cost: 1.00  Resp: 1.00  Degree: 1
 One row Card: 1.000000
  Best:: AccessPath: IndexUnique
  Index: TEST_TBL_IX
         Cost: 1.00  Degree: 1  Resp: 1.00  Card: 1.00  Bytes: 0


-- Fall 4: Index wieder gelöscht
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST_TBL  Alias: TEST_TBL  (NOT ANALYZED)
    #Rows: 1634  #Blks:  20  AvgRowLen:  100.00
Access path analysis for TEST_TBL
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST_TBL[TEST_TBL] 
  Table: TEST_TBL  Alias: TEST_TBL
    Card: Original: 1634.000000  Rounded: 16  Computed: 16.34  Non Adjusted: 16.34
  Access Path: TableScan
    Cost:  12.00  Resp: 12.00  Degree: 0
      Cost_io: 12.00  Cost_cpu: 469229
      Resp_io: 12.00  Resp_cpu: 469229
  Best:: AccessPath: TableScan
         Cost: 12.00  Degree: 1  Resp: 12.00  Card: 16.34  Bytes: 0

In den ersten beiden Fällen, also vor und nach der Anlage des non-unique Index, kommt der CBO jeweils zur gleichen Einschätzung: er nimmt an, dass die Tabelle nur einen Block umfasst (#Blks), dass die Satzlänge exakt 100 Byte beträgt (AvgRowLen: 100.00) und dass der eine Tabellenblock 82 rows umfasst (bei 8K Blockgröße und 100 Byte pro Satz ist das zwar etwas optimistisch, aber nicht völlig falsch). In Fall 2 werden die Index-Statistiken offenbar nicht zur Korrektur der Tabellen-Werte herangezogen, denn die Card-Angabe bleibt 0.82 - also 1% der Tabellen-Cardinality.

In Fall 3 weiß der CBO, dass der Index eindeutig ist, dass also zu einer ID (höchstens) ein Satz vorliegt - daher ergibt sich: Card: 1.00.

Nach der Löschung des Index ändert sich die Rechnung in Fall 4: jetzt geht der CBO von 20 Blocks aus und kommt auf 1634 rows (20 * 82 = 1640; vermutlich ist da noch Rundung im Spiel) für die Tabelle, woraus sich dann die Card-Angabe 16.34 für die Bedingung id = 9999 ergibt. Es bleiben zwei Fragen:

1. wie kommt der CBO darauf, dass die Tabelle 20 Blocks umfasst?
2. wieso ändert sich das Verhalten erst nach der Löschung des Index?

Frage 1 lässt sich anscheinend relativ leicht beantworten: die Anzahl der Blocks wird anscheinend aus der HWM im segment header ermittelt:

Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 7     
                  last map  0x00000000  #maps: 0      offset: 4128  
      Highwater::  0x01400205  ext#: 1      blk#: 5      ext size: 8     
  #blocks in seg. hdr's freelists: 5     
  #blocks below: 20    
  mapblk  0x00000000  offset: 1     
      Disk Lock:: Locked by xid:  0x0005.016.00003072
     Map Header:: next  0x00000000  #extents: 1    obj#: 101566 flag: 0x42000000
  Extent Map
  -----------------------------------------------------------------

Die #blocks below: 20 passen exakt zur Angabe des CBO Trace und der Zusammenhang kann auch bei veränderter Segmentgröße beobachtet werden. Schwieriger ist es mit Frage 2: ich sehe weder im CBO Trace noch im Block Dump eine Veränderung, die erklären könnte, warum der CBO erst nach der Löschung des Index die tatsächliche Segmentgröße berücksichtigt. Dabei kann man die Änderung des Costings auch auf anderem Weg erreichen, etwa durch Ergänzung eines NOT NULL Constraints, was dann unmittelbar zum realistischeren Costing unter Berücksichtigung der Objektgröße führt. Ein einfacher table comment genügt an dieser Stelle allerdings nicht, so dass es offenbar nicht um beliebige DDL-Operationen geht (in dem Fall müsste ja auch schon das CREATE INDEX die Änderung im Costing hervorrufen).

Wer schreibt mir jetzt einen Schluss? Ich hoffe mal: der Herr Lewis (oder auch der Herr Geist).

Nachtrag 04.01.2013: Jonathan Lewis hat im Thread noch die Beobachtung ergänzt, dass auch ein ALTER TABLE ... MOVE und ein ALTER INDEX ... VISIBLE/INVISIBLE die Änderung des Costings bewirken. Er schreibt weiter: "I think one of the key things is that some DDL will cause Oracle to flush and reload some of the dictionary cache information for the table - including the number of blocks, which can be derived from the table segment header. The problem is identifying which DDL. [,,,] I think there must be some detail about how Oracle thinks that DDL may cause important statistics to change, and therefore modifies the dictionary cache entries - but whether the apparent inconsistencies are deliberate or accidental I can't yet decide."