Freitag, Juni 10, 2011

DBMS_STATS und Cursor Invalidation

Gestern hatte ich sowohl im SQL Server als auch in Oracle mit dem Effekt zu tun, dass Zugriffspläne mit extrem ungünstigen NL-Operationen erzeugt wurden, weil die Column-Statistiken behaupteten, dass ein Wert, nach dem eine Tabelle gefiltert werden sollte, in der entsprechenden Tabelle nicht vorkäme. Im Oracle-Fall ergab die Analyse folgende Zusammenhänge:
  • es existierte eine Query mit extremer Laufzeit
  • im Execution Plan finden sich sehr viele NESTED LOOPS, obwohl Massendaten verarbeitet werden.
  • Ausgangspunkt für die NLs ist offenbar die Kardianlität einer Menge, die auf 1 geschätzt wurde, obwohl tatsächlich 1.500.000 Sätze vorlagen. Da für einen NL jeder Ergebnissatz in der äußeren Tabelle einen Zugriff auf die innere Tabelle hervorruft, war das natürlich eine ziemlich gravierende Fehlannahme
  • das Filterkriterium für die auf 1 geschätzte Tabelle war TYP = 61
  • in USER_TABLES waren als NUM_ROWS für die fragliche Tabelle ca. 2.000.000 angegeben, tatsächlich enthielt die Tabelle aber über 15.000.000 Sätze
  • in USER_TAB_COLS wurde für die TYP-Spalte unter NUM_DISTINCT eine 1 angegeben - und der LOW_VALUE (= HIGH_VALUE) war 67; tatsächlich gab es aber 9 Typen in der Tabelle und jeder davon erschien über 1.500.000 mal
  • ich erinnere mich, dass in Cost Based Oracle erläutert wird, wie die Cardinality-Schätzungen jenseits der LOW- und HIGH_VALUE Grenzen langsam absinken. Wenn es aber nur einen distinkten Wert gibt, dann sackt die Schätzung extrem schnell ab:
create table test_1_value
as
select 42 col1
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 'TEST_1_VALUE')

select *
  from TEST_1_VALUE
 where COL1 = 42;

--------------------------------------------------
| Id  | Operation         | Name         | Rows  |
--------------------------------------------------
|   0 | SELECT STATEMENT  |              |  1000K|
|*  1 |  TABLE ACCESS FULL| TEST_1_VALUE |  1000K|
--------------------------------------------------

select *
  from TEST_1_VALUE
 where COL1 = 43;

--------------------------------------------------
| Id  | Operation         | Name         | Rows  |
--------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |
|*  1 |  TABLE ACCESS FULL| TEST_1_VALUE |     1 |
--------------------------------------------------

select *
  from TEST_1_VALUE
 where COL1 = 41;

--------------------------------------------------
| Id  | Operation         | Name         | Rows  |
--------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |
|*  1 |  TABLE ACCESS FULL| TEST_1_VALUE |     1 |
--------------------------------------------------

Demnach können in einem solchen Fall, in dem vermutlich die Statistiken nach einer initialen Füllung nicht mehr aktualisiert werden, extreme Fehleinschätzungen auftreten.

Aber das alles passt noch nicht zum Titel dieses Blog-Eintrags. Nach der Aktualisierung der Statistiken wurde die Query vom OWB erneut ausgeführt - und ich stellte mit Schrecken fest, dass die Aktualisierung zwar meine Testquery auf einen sinnvollen Zugriff geführt hatte, die OWB-Query aber immer noch den absurden NL-Plan verwendete. Verantwortlich dafür war offenbar mein DBMS_STATS-Aufruf, der mit den default-Einstellungen arbeitete - so dass der Parameter no_invalidate auf den Standard-Wert gesetzt blieb. Diesen Parameter erläutert die Doku folgendermaßen:
Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.
Wenn man diesen Angaben weiter folgt, stößt man irgendwann auf eine Erläuterung der Rolling Cursor Invalidations with DBMS_STATS in Oracle10g [ID 557661.1], die darauf hinauslaufen, dass ein Cursor nicht direkt invalidiert wird, sondern erst mit einer Verzögerung von 5 Stunden (18000 sec.). In einem OLTP-System mag das auch ganz sinnvoll sein, aber in einem DWH passt es leider gar nicht. Weitere Details zum Verhalten findet man auch hier und hier.

Keine Kommentare:

Kommentar veröffentlichen