Cardinality-Schätzungen für Remote-Zugriffe

Dieser Tage ist mir folgendes Phänomen begegnet, auf das ich mir noch keinen ganz klaren Reim machen kann: Ausgangspunkt war eine etwas unglücklich eingerichtete Reporting-Lösung, die grundsätzlich auf ein Star-Schema in einer Datenbank A (10.2.0.4) zugreift, aber zusätzlich auf die Daten einer Dimensionstabelle in einer Datenbank B (11.1.0.7) angewiesen ist, die über DB-Link (und Synonyme) angesprochen wird. Vor einiger Zeit ergaben sich für die Queries dieser Applikation Performance-Probleme, die zum Teil mit falschen Statistiken in Datenbank A zusammenhingen, aber z.T. auch auf fehlerhaften Einschätzungen der Cardinalities für die Dimensionstabelle in der Datenbank B beruhten. Meine erste Vermutung war, dass die falsche Cardinality durch die Korrelation von Spalteninhalten hervorgerufen wurde (in diesem Fall Produktgruppen und Lieferanten, die eben in vielen Fällen nicht unabhängig voneinander sind), weshalb ich extended statistics (und Histogramme) für die fragliche Spaltenkombination anlegte. Dadurch wurden die Cardinality-Schätzungen beim Zugriff in Datenbank B besser (wenn auch nicht völlig akkurat) und auch beim Remote-Zugriff von Datenbank A aus auf die einzelne Tabelle erhielt man die gleichen Angaben:

select /*+ full(t) */ count(*)
  from dim_table t
 where t.col1 = 421220
   AND t.col2 = 20

  COUNT(*)
----------
      2841

-------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|                 |     1 |     9 | 60397   (1)|
|   1 |  SORT AGGREGATE        |                 |     1 |     9 |            |
|*  2 |   TABLE ACCESS FULL    | DIM_TABLE       |   147 |  1323 | 60397   (1)|
-------------------------------------------------------------------------------

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

   2 - filter("A1"."COL1"=421220 AND "A1"."COL2"=20)

Note
-----
   - fully remote statement

Für das "fully remote statement" erwartet der CBO demnach 147 Sätze, was zwar von den tatsächlichen 2841 rows recht weit entfernt ist, aber immerhin mehr als die 1 (oder 2), die hier ohne die extended statistics erschien.

Für die komplexeren Queries der Applikation blieb es aber trotzdem bei einer deutlichen Unterschätzung der Cardinality für den Zugriff auf DIM_TABLE. Dazu hier ein minimales Beispiel:

select count(*)
  from fact_table f
     , dim_table d
 where f.col0 = d.col0
   and d.col1 = 421220
   AND d.col2 = 20
   and f.col3 = '01.12.2011'

  COUNT(*)
----------
     13562

----------------------------------------------------------------------------------------
| Id  | Operation                | Name                   | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                        |     1 |    54 |     8   (0)|
|   1 |  SORT AGGREGATE          |                        |     1 |    54 |            |
|   2 |   NESTED LOOPS           |                        |    24 |  1296 |     8   (0)|
|   3 |    REMOTE                | DIM_TABLE              |     2 |    78 |     4   (0)|
|   4 |    PARTITION RANGE SINGLE|                        |    12 |   180 |     2   (0)|
|*  5 |     INDEX RANGE SCAN     | IDX_FACT_TABLE         |    12 |   180 |     2   (0)|
----------------------------------------------------------------------------------------

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

   5 - access("F"."COL0"="A"."COL0" AND "F"."COL3"=TO_DATE(' 2011-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "COL0","COL1","COL2" FROM "DIM_TABLE" "A" WHERE "COL1"=421220 AND "COL2"=20
       (accessing 'DBL_XXX' )

Statt der Cardinality-Schätzung 147, die sich bei der einzelnen Ausführung des Remote SQL ergibt, erscheint hier eine 2, was im gegebenen Fall wahrscheinlich kein Problem ist, da der NL hier angemessen erscheint - aber manchmal einen geeigneteren HASH JOIN verhindern könnte. Meine Vermutung ist, dass die Extended Statistics nur beim "fully remote statement" herangezogen werden können, während das Join-Statement auf die Statistiken der Einzelspalten zurückgreift - möglicherweise, weil es in Datenbank A ausgeführt wird, also in 10.2.0.4, wo extended statistics noch gar nicht bekannt sind. Zur Prüfung dieser Annahme ein Versuch mit Ausführung in Datenbank B - erzwungen durch den DRIVING_SITE-Hint, zu dem Jonathan Lewis gelegentlich ein paar Hinweise gegeben hat.

select /*+ DRIVING_SITE(a) */ count(*)
  from fact_table f
     , dim_table d
 where f.col0 = d.col0
   and d.col1 = 421220
   AND d.col2 = 20
   and f.col3 = '01.12.2011'

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                         | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE       |                              |     1 |    38 |   395   (1)|
|   1 |  SORT AGGREGATE               |                              |     1 |    38 |            |
|   2 |   NESTED LOOPS                |                              |  1703 | 64714 |   395   (1)|
|   3 |    TABLE ACCESS BY INDEX ROWID| DIM_TABLE                    |   147 |  2352 |   100   (0)|
|*  4 |     INDEX RANGE SCAN          | IDX_DIM_TABLE                |   147 |       |     3   (0)|
|   5 |    REMOTE                     | FACT_TABLE                   |    12 |   264 |     2   (0)|
---------------------------------------------------------------------------------------------------

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

   4 - access("A1"."COL2"=20 AND "A1"."COL1"=421220)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   5 - SELECT "COL0","COL3" FROM "XXX"."FACT_TABLE" "A2" WHERE "COL3"=TO_DATE(' 2011-12-01
       00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "COL0"=:1 (accessing '!' )

Damit ist man dann wieder bei der 147, was meine Vermutung zu bestätigen scheint. Und damit ist das Problem in diesem Fall vermutlich nicht einmal durch einen Umzug der DIM_TABLE in Datenbank A lösbar, sondern erfordert eine grundsätzlichere Behandlung (oder einen kompletten Umzug der Applikation nach B).