FTS Kosten

Noch einmal zu den Kosten von Full Table Scans: im cbo Buch des Herrn Lewis findet man auf S. 12 eine Tabelle, in der ein adjusted dbf_mbrc abhängig vom Parameter db_file_multiblock_read_count berechnet wird.

I/O costing
Der adjusted dbf_mbrc ist der Wert, der vom cbo - bei Verwendung von I/O-costing - für die Kostenberechnung von Multiblock-Zugriffen verwendet wird, während der db_file_multiblock_read_count die Anzahl der Blocks angibt, die bei Leseoperationen tatsächlich gelesen werden (sollen): aufgrund von extent-Grenzen und bereits im Cache vorliegenden Blocks bleibt die read-size tatsächlich häufig unterhalb des definierten Werts.

Hier noch mal ein Beispiel, das dem des cbo-Buchs erschreckend ähnlich ist (auf 11.1.0.7 und mit einem non-ASSM-Tablespace):

drop table test_fts;

create table test_fts pctused 1 pctfree 90 tablespace test_ts
as
select rownum rn
     , lpad('*', 1000, '*') padding
  from dual
connect by level <= 10000;

exec dbms_stats.gather_table_stats(user, 'test_fts', estimate_percent=> 100)

-- der Hint sorgt dafür, dass kein cpu costing erfolgt
select /*+ opt_param('_optimizer_cost_model','io') */ count(*)
  from TEST_FTS;

  COUNT(*)
----------
     10000

1 Zeile wurde ausgewählt.


Ausführungsplan
----------------------------------------------------------
Plan hash value: 2190879977

-------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost  |
-------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   246 |
|   1 |  SORT AGGREGATE    |          |     1 |       |
|   2 |   TABLE ACCESS FULL| TEST_FTS | 10000 |   246 |
-------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)

Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10006  consistent gets
      10000  physical reads
          0  redo size
        342  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

alter session set db_file_multiblock_read_count = 4;
--> cost: 2397 
alter session set db_file_multiblock_read_count = 8;
--> cost: 1519
alter session set db_file_multiblock_read_count = 16;
--> cost: 963
alter session set db_file_multiblock_read_count = 32;
--> cost: 611
alter session set db_file_multiblock_read_count = 64;
--> cost: 388
alter session set db_file_multiblock_read_count = 128;
--> cost: 246

select 4 mbrc, round(10006/2397, 2) adjusted_dbf_mbrc from dual
union all
select 8, round(10006/1519, 2) from dual
union all
select 16, round(10006/963, 2) from dual
union all
select 32, round(10006/611, 2) from dual
union all
select 64, round(10006/388, 2) from dual
union all
select 128, round(10006/246, 2) from dual;

MBRC ADJUSTED_DBF_MBRC
---- -----------------
   4              4,17
   8              6,59
  16             10,39
  32             16,38
  64             25,79
 128             40,67

Die Zahlen sind nahezu identisch mit den Ergebnissen bei Jonathan Lewis. Die marginalen Abweichungen mögen sich daraus ergeben, dass meine Bestimmung der HWM fragwürdig sein könnte.

cpu costing (workload statistics)
Zum Verhalten bei Verwendung von Workload-Statistiken hatte ich gelegentlich auch einen Test durchgeführt, der ebenfalls auf den Ergebnissen des cbo Buchs basiert.