Statistics_Level

Ein - zumindest für mich - überraschendes Ergebnis einiger Tests, bei denen ich mir über die Abarbeitung von HASH JOINS und die Rolle der PGA-Dimensionierung Klarheit verschaffen wollte, ist der extreme Effekt des Statistics-Levels auf die Perfomance. Dazu folgende Resultate:

-- Anlage von Testtabellen 
SQL> create table test1
  2  as
  3  select rownum col1
  4       , trunc(rownum/10) col2
  5       , trunc(rownum/100) col3
  6       , trunc(rownum/1000) col4
  7       , trunc(rownum/10000) col5
  8       , trunc(rownum/100000) col6
  9    from dual
 10  connect by level <= 1000000;

Tabelle wurde erstellt.

SQL> create table test2
  2  as
  3  select rownum col1
  4       , trunc(rownum/10) col2
  5       , trunc(rownum/100) col3
  6       , trunc(rownum/1000) col4
  7       , trunc(rownum/10000) col5
  8       , trunc(rownum/100000) col6
  9    from dual
 10  connect by level <= 1000000;

Tabelle wurde erstellt.

SQL> sho parameter statistics_level

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
statistics_level                     string      TYPICAL

SQL> select count(*)
  2    from test1, test2
  3   where test1.col1 = test2.col1;

  COUNT(*)
----------
   1000000

Abgelaufen: 00:00:01.48

SQL> select count(*) from test1;

  COUNT(*)
----------
   1000000

Abgelaufen: 00:00:00.07

So weit alles ganz harmlos. Aber jetzt ändere ich das STATISTICS_LEVEL:

SQL> alter system set statistics_level=all;

System wurde geändert.

SQL> select count(*)
  2    from test1, test2
  3   where test1.col1 = test2.col1;

  COUNT(*)
----------
   1000000

Abgelaufen: 00:00:13.05
SQL> select count(*) from test1;

  COUNT(*)
----------
   1000000

Abgelaufen: 00:00:02.47

Für den einfachen FTS erhöht sich die Laufzeit von 0,07 sec auf 2,47 sec. Für den Join (der als HASH JOIN als optimal execution komplett im Speicher durchgeführt werden kann) steigt die Laufzeit von 1,48 sec auf 13,05 sec (und diese Werte bestätigen sich bei wiederholter Ausführung). Offenbar ist der negative Effekt des erhöhten statistics_level auf die Performance weit größer als der Einfluß etwa eines 10046er Traces.

Nachtrag: der Hinweis darauf, dass man das Statistics_Level nur mit Bedacht auf 'ALL' setzen sollte, findet sich z.B. bei Tanel Poder und Chris Antognini.