ANALYZE und DBMS_STATS

Um mich endlich mal dauerhaft daran zu erinnern, was die Unterschiede in der Statistikerhebung über ANALYZE TABLE ... COMPUTE STATISTICS und DBMS_STATS.GATHER_TABLE_STATS sind, hier mal wieder ein Test. Zunächst der Hinweis der Oracle-Doku (hier 11.1):

Do not use the COMPUTE and ESTIMATE clauses of ANALYZE to collect optimizer statistics. These clauses are supported for backward compatibility. Instead, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. The cost-based optimizer, which depends upon statistics, will eventually use only statistics that have been collected by DBMS_STATS.

Das könnte man natürlich auch gleich als Hinweis darauf deuten, dass ich mir diesen Test schenken kann, aber jetzt habe ich damit angefangen und werde es auch zu einem Ende bringen...

-- Anlage zweier einfacher und identischer Tabellen
create table test_analyze
as
select rownum id
     , mod(rownum, 10) col1
     , lpad('*', 20, '*') pad
  from dual
connect by level <= 1000;

create table test_dbms_stats
as
select rownum id
     , mod(rownum, 10) col1
     , lpad('*', 20, '*') pad
  from dual
connect by level <= 1000;

analyze table test_analyze compute statistics;
-- Tabelle wurde analysiert.
-- Abgelaufen: 00:00:00.18 

exec dbms_stats.gather_table_stats(user, 'TEST_DBMS_STATS')
-- PL/SQL-Prozedur erfolgreich abgeschlossen.
-- Abgelaufen: 00:00:02.04 

select table_name
     , num_rows
     , blocks
     , avg_space
     , avg_row_len
     , avg_cached_blocks
     , avg_cache_hit_ratio
     , last_analyzed
     , global_stats
  from user_tab_statistics
 where table_name in ('TEST_DBMS_STATS', 'TEST_ANALYZE');

TABLE_NAME         NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO LAST_ANA GLO
---------------- ---------- ---------- ---------- ----------- ----------------- ------------------- -------- ---
TEST_ANALYZE           1000          8       1488          31                                       02.06.11 NO
TEST_DBMS_STATS        1000          8          0          28                                       02.06.11 YES

select table_name
     , column_name
     , global_stats
     , avg_col_len
  from user_tab_cols
 where table_name in ('TEST_DBMS_STATS', 'TEST_ANALYZE')
 order by column_name, table_name

TABLE_NAME                     COLUMN_NAME                    GLO AVG_COL_LEN
------------------------------ ------------------------------ --- -----------
TEST_ANALYZE                   COL1                           NO            2
TEST_DBMS_STATS                COL1                           YES           3
TEST_ANALYZE                   ID                             NO            3
TEST_DBMS_STATS                ID                             YES           4
TEST_ANALYZE                   PAD                            NO           20
TEST_DBMS_STATS                PAD                            YES          21

Demnach liegen die Unterschiede darin, dass:

• ANALYZE die AVG_SPACE-Angabe liefert
• beide Versionen unterschiedliche GLOBAL_STATS-Werte enthalten
• die AVG_ROW_LEN bei DBMS_STATS immer um 1 größer ist als bei ANALYZE
• ANALYZE schneller ein Ergebnis liefert

Nach diesem relativ harmlosen Test habe ich dann auch noch bei Google nachgefragt und zunächst bei AskTom einen deutlich detaillierteren Test zum gleichen Thema gefunden, der auch noch zeigt, dass ANALYZE neben der AVG_SPACE-Angabe auch noch EMPTY_BLOCKS und CHAIN_CNT füllt: "dbms_stats won't gather things not used by the CBO such as chain_cnt/avg_space in particular". Außerdem weist Tom Kyte auf den unterschiedlichen Umgang mit partitionierten Tabellen hin:

ANALYZE calculates global statistics for partitioned tables and indexes instead of gathering them directly. This can lead to inaccuracies for some statistics, such as the number of distinct values.  DBMS_Stats won't do that.

Darüber hinaus liefert Jonathan Lewis eine einleuchtende Erklärung dafür, warum DBMS_STATS mehr Zeit benötigt: ANALYZE tut einfach deutlich weniger, während DBMS_STATS mit jedem neuen Release weitere Aufgaben bekommt. In Cost Based Oracle (Cap 12, S. 322f.) erklärt der Herr Lewis auch noch den Unterschied der Länge in AVG_ROW_LEN: DBMS_STATS enthält ein (oder mehrere) zusätzliche Length Byte(s).
Interessant ist auch noch die Sammlung von Einzelbeobachtungen, die man auf Yong Huangs unscheinbarer aber durchaus lesenswerter Web-Seite findet. Demnach gilt:

• die MOS-Dokumente 237293.1 und 237537.1 zeigen die Probleme von ANALYZE bei der Statistikerfassung für partitionierte Tabellen
• ANALYZE benötigt weniger Platz für Sortierungen im temporären Tablespace (was vermutlich zu Jonathan Lewis Aussage passt, dass DBMS_STATS mehr zu tun hat)
• die LAST_ANALYZED-Angabe passt für ANALYZE zum Startzeitpunkt der Analyse, während sie für DBMS_STATS den Endzeitpunkt liefert (das könnte ich gelegentlich noch mal prüfen)
• beide Kommandos führen ein implizites COMMIT durch. Dieser Punkt war mir dieser Tage auch schon einmal schmerzhaft bewusst geworden, als mein Versuch, die Statistikerfassung einer ETL-Operation zu verbessern, die vorhandene Transaktionslogik torpedierte (was mir immerhin vor Produktivsetzung klar wurde). Einleuchtend ist das Verhalten natürlich schon, da Statistiken ja nicht Privatsache einer Session sind.

P.S.: nach der Bedeutung der GLOBAL_STATS-Spalte in USER_TABLES (oder inzwischen USER_TAB_STATISTICS) hatte ich vor mehreren Jahren im C.D.O.-Forum gefragt und darauf eine Antwort von Christian Antognini bekommen, die mir jetzt auch deutlich klarer ist als damals...