Costing und manuelle Optimierung

Nachdem ich beim Versuch, diesem Eintrag einen passenderen Titel zu geben, bei epischen Beschreibungen nach Art von Kevin Closson gelandet bin, hab ich's aufgegeben und stattdessen eine kurze Überschrift mit geringer Aussagekraft gewählt ...

Worum es geht: gestern habe ich eine Query geschrieben, die die Sätze einer sehr kleinen Korrekturtabelle (3 rows) mit den entsprechenden Inhalten einer großen (partitionierten) Faktentabelle (700M rows) verbinden sollte. Auf der großen Tabelle existierte ein Index, in dem die Join-Spalte an zweiter Position erschien, wobei die führende Spalte ein Datum mit relativ wenigen distinkten Werten war. Meine Erwartung war, dass der CBO einen Plan liefern würde, der die kleine Tabelle über FTS lesen und auf die große Tabelle über NESTED LOOPS und INDEX SKIP SCAN zugreifen würde - aber das war nicht der Fall. Stattdessen ergab sich folgender Plan:

----------------------------------------------------------------
| Id  | Operation             | Name                   | Rows  |
----------------------------------------------------------------
|   0 | SELECT STATEMENT      |                        |     1 |
|   1 |  HASH UNIQUE          |                        |     1 |
|*  2 |   HASH JOIN           |                        |     1 |
|   3 |    TABLE ACCESS FULL  | KORREKTUR              |     3 |
|   4 |    PARTITION RANGE ALL|                        |   708M|
|   5 |     TABLE ACCESS FULL | FAKTEN                 |   708M|
----------------------------------------------------------------

Dass der FTS auf die große Faktentabelle eine ziemlich dumme Idee wäre, schien mir ziemlich offensichtlich, und deshalb habe ich mit Hints den von mir favorisierten Plan erzwungen - und stellte dabei fest, dass die Kosten des per Hint erzwungenen Plans bei 5674 lagen: gegenüber 1717K für den Plan, den sich der CBO ausgedacht hatte. Heute habe ich den Fall noch mal auf den folgenden kleineren Test (mit 11.1.0.7) reduziert:

drop table t_outer;
create table t_outer
as
select rownum id
  from dual
connect by level <= 5;

drop table t_inner;
create table t_inner
as
select trunc(rownum/10000) col1
     , rownum id
     , mod(rownum, 10) col2
     , lpad('*', 50, '*') padding
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 't_outer')
exec dbms_stats.gather_table_stats(user, 't_inner')

create index t_inner_idx on t_inner(col1, id, col2);

set autot trace
-- ohne Hints
select count(*)
  from t_inner i
     , t_outer o
 where i.id = o.id;

-- mit Hints
select /*+ use_nl(o i) index_ss(i t_inner_idx) */
       count(*)
  from t_inner i
     , t_outer o
 where i.id = o.id;

Das Ergebnis ist hier ein HASH JOIN mit einem INDEX FAST FULL SCAN zum Zugriff auf die große Tabelle T_INNER - immerhin kein FTS - aber immer noch ein Plan, dessen Kosten höher sind als die des durch die Hints herbeigeführten NL mit INDEX SKIP SCAN (845 zu 518):

-- ohne Hints
--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |     8 |   845   (2)| 00:00:11 |
|   1 |  SORT AGGREGATE        |             |     1 |     8 |            |          |
|*  2 |   HASH JOIN            |             |     5 |    40 |   845   (2)| 00:00:11 |
|   3 |    TABLE ACCESS FULL   | T_OUTER     |     5 |    15 |     3   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| T_INNER_IDX |  1000K|  4882K|   836   (2)| 00:00:11 |
--------------------------------------------------------------------------------------

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

   2 - access("I"."ID"="O"."ID")


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

-- mit Hints
-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |     1 |     8 |   518   (0)| 00:00:07 |
|   1 |  SORT AGGREGATE     |             |     1 |     8 |            |          |
|   2 |   NESTED LOOPS      |             |     5 |    40 |   518   (0)| 00:00:07 |
|   3 |    TABLE ACCESS FULL| T_OUTER     |     5 |    15 |     3   (0)| 00:00:01 |
|*  4 |    INDEX SKIP SCAN  | T_INNER_IDX |     1 |     5 |   103   (0)| 00:00:02 |
-----------------------------------------------------------------------------------

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

   4 - access("I"."ID"="O"."ID")
       filter("I"."ID"="O"."ID")


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

Dabei zeigen die consistent gets, dass die cost-Angabe durchaus mit der tatsächlich auszuführenden Arbeit korreliert. Die Frage ist (mal wieder): warum zieht der CBO den günstigeren NL-Plan nicht in Betracht? Ein Blick ins 10053er Trace zeigt nur, dass dort der INDEX SKIP SCAN für den Fall des NL-Zugriffs nicht in Betracht gezogen wird:

Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:  T_OUTER[O]#0  T_INNER[I]#1

***************
Now joining: T_INNER[I]#1
***************
NL Join
  Outer table: Card: 5.00  Cost: 3.00  Resp: 3.00  Degree: 1  Bytes: 3
Access path analysis for T_INNER
  Inner table: T_INNER  Alias: I
  Access Path: TableScan
    NL Join:  Cost: 12806.18  Resp: 12806.18  Degree: 1
      Cost_io: 12734.00  Cost_cpu: 1184736916
      Resp_io: 12734.00  Resp_cpu: 1184736916
  Access Path: index (index (FFS))
    Index: T_INNER_IDX
    resc_io: 825.40  resc_cpu: 141691906
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Inner table: T_INNER  Alias: I
  Access Path: index (FFS)
    NL Join:  Cost: 4173.16  Resp: 4173.16  Degree: 1
      Cost_io: 4130.00  Cost_cpu: 708488767
      Resp_io: 4130.00  Resp_cpu: 708488767
kkofmx: index filter:"I"."ID"="O"."ID"

  Access Path: index (FullScan)
    Index: T_INNER_IDX
    resc_io: 3048.00  resc_cpu: 221706149
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
 ***** Logdef predicate Adjustment ****** 
 Final IO cst 0.00 , CPU cst 0.00
 ***** End Logdef Adjustment ****** 
    NL Join : Cost: 15310.54  Resp: 15310.54  Degree: 1
      Cost_io: 15243.00  Cost_cpu: 1108559981
      Resp_io: 15243.00  Resp_cpu: 1108559981

  Best NL cost: 4173.16
          resc: 4173.16  resc_io: 4130.00  resc_cpu: 708488767
          resp: 4173.16  resp_io: 4130.00  resc_cpu: 708488767

Einen guten Grund für das Verhalten des CBO habe ich bisher noch nicht gefunden.

Nachtrag 16.06.2012: Hier noch ein Link auf einen Thread im OTN-Forum Database - General, den ich zum Thema begonnen habe, und in dem Nikolay Savvinov auf einige Bugs mit SKIP SCANs hinweist. Relevant scheint dabei vor allem der Hinweis auf die identischen ACCESS und FILTER Prädikate im günstigen Plan zu sein.