Lineare Regression mit Oracle-Funktionen

Carsten Czarski zeigt in seinem Blog, was für schöne Bordmittel der Oracle Server im Bereich der Statistik hat.

10046er Trace-Files parsen

Kyle Hailey stellt ein (von Clive Bostock erstelltes) Perl-Script vor, mit dessen Hilfe man 10046er Trace-Files parsen und daraus zusammenfassende Summaries und Histogramme erzeugen kann.

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.

Hints richtig setzen

In schöner Koinzidenz zu meinem harmlosen (und unvorteilhaft benannten) View-Hint-Eintrag hat Jonathan Lewis einen Artikel über das korrekte Setzen von Hints und die Verwendung von benamten Query-Blocks geschrieben.

Hints für eine Query auf eine View

Im OTN Forum wurde heute die beliebte Frage gestellt, warum der cbo für eine Query keinen Index-Zugriff wählt - und das trotz eines Index-Hints. Wie Jonathan Lewis regelmäßig erläutert, sind Hints ja eigentlich keine Hints (soll heissen: Hinweise), sondern klare Direktiven: der cbo muss sich an sie halten, sofern er nach Abschluss seiner internen Optimierungs-Umformungen einen Zugriff auswählt, in dem sie berücksichtigt werden können. Im gegebenen Fall wurde der Hint allerdings ignoriert, weil er sich auf eine Tabelle bezog, die Query selbst aber eine darauf aufbauende View ansprach. Dazu ein Beispiel (das ich in ähnlicher Form auch im Thread untergebracht hatte):

create table test
as
select * from dba_objects;

create index test_idx on test(OWNER);

-- beim Zugriff auf die Basistabelle wird der Hint berücksichtigt
select /*+ index (test test_idx) */ * from test where owner like '%SYS%';
 
------------------------------------------------
| Id  | Operation                   | Name     |
------------------------------------------------
|   0 | SELECT STATEMENT            |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |
|*  2 |   INDEX FULL SCAN           | TEST_IDX |
------------------------------------------------
 
create view v_test as select * from test;

-- der Hint spricht die Basistabelle an - und wird ignoriert, weil
-- hier die View abgefragt wird
select /*+ index (test test_idx) */ * from v_test where owner like '%SYS%';
 
----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| TEST |
----------------------------------

-- wenn der Hint die View anspricht, wird er berücksichtigt
select /*+ index (v_test test_idx) */ * from v_test where owner like '%SYS%';

------------------------------------------------
| Id  | Operation                   | Name     |
------------------------------------------------
|   0 | SELECT STATEMENT            |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |
|*  2 |   INDEX FULL SCAN           | TEST_IDX |
------------------------------------------------

Dass der Index-Zugriff im Test-Beispiel (ähnlich wie im OTN-Thread) wahrscheinlich eher weniger sinnvoll ist, steht auf einem anderen Blatt.

Serverparametrisierung MSMDSRV.ini

Bei Durchsicht des Analysis Services Operations Guide (Abschnitt 2.3.2, S. 14 f.) ist mir klar geworden, dass mir die Memory-Parameter in msmdsrv.ini regelmäßig entfallen. Daher an dieser Stelle ein paar kurze Definitionen:

• LowMemoryLimit: der Arbeitsspeicher (in % oder Byte), den der SSAS nicht wieder freigibt, nachdem er ihn einmal bekommen hat. Dieser Speicher wird per default nicht beim Systemstart allokiert.
• Counter:MSOLAP:Memory\LowMemoryLimit
• PreAllocate: sorgt dafür, dass schon beim Systemstart Speicher für den SSAS allokiert wird (was dann interessant sein kann, wenn der SSAS sich einen Server mit anderen Applikationen teilen muss). Für Windows Server 2008 ist diese Einstellung nicht mehr besonders relevant.
• TotalMemoryLimit: der Schwellwert (Arbeitsspeicher in % oder Byte), ab dessen Überschreitung der SSAS damit beginnt, Speicher wieder freizugeben. Die Angabe ist keine Obergrenze für den Speicherverbrauch des Prozesses.
• Counter: MSOLAP:Memory\TotalMemoryLimit
• HardMemoryLimit (verfügbar ab SSAS 2008): eine aggressivere Variante zu TotalMemoryLimit, die Sessions beendet, wenn der Schwellwert überschritten wird.
• LimitSystemFileCacheMB: beschränkt die Größe des OS-Caches, damit dieser nicht mit dem SSAS-Prozess kollidiert.
• OLAP\Process\AggregationMemoryLimitMin (bzw. %Max): bestimmt die Größe des zum Aufbau von Indizes und Aggregationen verwendeten Arbeitsspeichers. Der Min-Wert gilt pro Partition: "if you start five concurrent partition processing jobs with AggregationMemoryMin = 10, an estimated 50 percent (5 x 10%) of reserved memory is allocated for processing". Deshalb kann es in großen Systemen, die über sehr viel Arbeitsspeicher verfügen, sinnvoll sein, den Min-Wert zu reduzieren (weniger % oder sogar eine fixe Byte-Angabe), um eine höhere Parallelisierung zu ermöglichen.

Möglicherweise werde ich diese Liste gelegentlich erweitern.

Analysis Services Operations Guide

Ich beschwere mich gerne und regelmäßig darüber, dass es in der SSAS-Welt nur sehr wenige brauchbare Informationsquellen zu komplexeren Fragestellungen gibt. Neben dem Performance Guide gab es in diesem Bereich lange Zeit recht wenig. Anscheinend hat sich die Lage nun aber deutlich verbessert, da der Analysis Services 2008R2 Operations Guide veröffentlicht wurde, dessen Liste der Contributors and Technical Reviewers so ziemlich jeden enthält, der in Sachen SSAS Rang und Namen hat.

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...