White Paper zur Statistik-Erzeugung

Maria Colgan verweist im Blog der CBO-Entwickler auf das neue Whitepaper Best Practices for Gathering Optimizer Statistics, aus dem ich hier ein paar Punkte aufliste, die mir wesentlich erscheinen:

• in der Regel ist der automatische Statistik-Erfassungs-Job, der die DBMS_STATS-Prozedur GATHER_DATABASE_STATS_JOB_PROC verwendet, mit den Default-Einstellungen ausreichend.
• in relevanten Fällen können die Defaults mit Hilfe von DBMS_STATS.SET_*_PREF überschrieben werden.
• für den Parameter ESTIMATE_PERCENT wird der Default-Wert AUTO_SAMPLE_SIZE vorgeschlagen. In 10g wurde bei Werten mit extrem ungleicher Verteilung (skew) oft ein zu kleines Sample verwendet, aber in 11g ist dieses Problem behoben (hinsichtlich der Details des Verfahrens wird auf ein weitere White-Paper verwiesen).
• METHOD_OPT ist der umstrittenste Parameter in den GATHER_*_STATS-Prozeduren und dient der Erzeugung von Histogrammen.
• In 10g sorgte das einmalige Bind-Peeking dafür, dass der erste Bindewert den Plan bestimmte - und deshalb konnten Histogramme zu einem besonders ungünstigen   (weil hochgradig speziellen) Plan führen. Deshalb wird für 10g empfohlen entweder das Bind-Peeking oder die Histogramm-Erstellung zu deaktivieren (abhängig von der Häufigkeit der Verwendung von Bindevariablen). 
• In 11g wurde das adaptive cursor sharing eingeführt, das potentiell mehrere Pläne für eine Query mit Bindewerten verfügbar macht. Dadurch sind Histogramme kein Problem mehr.
• erläutert wird auch der Umgang mit Attribut-Werten die durch die Histogramme als popular  bestimmt werden, wiel sie mehrere Buckets umfassen. In diesem Fall wird die cardinality über die Formel ((anzahl_der buckets_die_den_wert_enthalten/anzahl_aller_buckets) * anzahl_saetze) bestimmt). Für Werte, die der Endpoint nur eines oder keines Buckets sind, wird stattdessen die Formel: (density) * (anzahl_saetze) verwendet (wobei die density on the fly bestimmt wird. Problematisch sind dabei nearly popular values, die nahezu zwei Buckets umfassen, denn für sie gilt ebenfalls die density-basierte Formel. Die einzige Möglichkeit, nearly popular values zu erfassen, ist dynamic sampling.
• grundsätzlich wird für 11g der default-Wert für method_opt empfohlen. Explizit abgeraten wird von der Verwendung von "for all columns size 254" abgeraten, da dies zur (kostspieligen) Erzeugung überflüssiger Histogramme führt.
• Pending Statistics sollten verwendet werden, um die Effekte von Änderungen an den Defaults der GATHER%STATS-Prozeduren vor der Produktivsetzung zu überprüfen.
• die automatische Statistikerfassung kann bei Bedarf angepasst oder auch deaktiviert werden.
• Für unveränderte Daten sollte keine Statistikerfassung erfolgen.
• Für ETL-Operationen kann die Statistikerfassung als Teil der Operation definiert werden (wobei auf das implizite Commit der Statistikerfassung zu achten ist).
• Ein Problem für Tabellen mit stark veränderlichen Daten sind "out-of_range" Werte (oberhalb des Maximal- oder unterhalb des Minimal-Wertes), da der cbo jenseits des bekannten Wertebereichs stark sinkende cardinalities annimmt. Für partitionierte Tabellen kann man die Statistiken einer existierenden Partition über die Prozedur DBMS_STATS.COPY_TABLE_STATS auf eine neue Partition übertragen, wobei die Werte des Partitioning Keys passend adaptiert werden. Für nicht partitionierte Tabellen kann man die Grenzwerte mit Hilfe von DBMS_STATS.SET_COLUMN_STATS manuell setzen.
• Zur Beschleunigung der Statistikerfassung dient Parellelisierung, die als inter object oder intra object parallelism möglich ist: im ersten Fall werden gleichzeitig Statistiken für mehrere Objekte erzeugt (was allerdings erst seit 11.2.0.2 unterstützt wird), im zweiten Fall erfolgt der Zugriff auf ein Objekt parallel (gesteuert über den DEGREE-Parameter der GATHER%STATS-Prozeduren). Beide Verfahren können kombiniert werden.
• Für partitionierte Tabellen kann eine inkrementelle Statistikerfassung erfolgen (Details dazu haben zuletzt Randolf Geist und Doug Burns in ihren Blogs geliefert). Interessant ist dabei vor allem die Bestimmung globaler Werte über eine Synopse.
• Keine (automatische) Statistkerfassung wird in folgenden Fällen empfohlen, für die man man repräsentative Statistiken locken oder dynamic sampling verwenden sollte:
• Tabellen mit stark veränderlichen Daten
• Global Temporary Tables (GTT)
• ETL-Zwischentabellen (die einmalig gefüllt, gelesen und wieder geleert werden)
• erfasst werden sollten außerdem dictionary statistics (regelmäßig), fixed table statistics und system statistics (jeweils einmalig).

Alles nicht besonders überraschend aber sehr kompakt und plausibel zusammengefasst. Detailprobleme zu den einzelnen Punkten findet man bei den üblichen Verdächtigen (Lewis, Geist, Antognini etc.), auf die ich hier regelmäßig verweise (und verlinke).

Außerdem findet sich bei Frau Colgan noch mal der Hinweis auf das vor kurzem veröffentlichte Whitepaper Understanding Optimizer Statistics.