Optimizer Statistics Präsentation bei Open World 2011

Das Team der cbo Entwickler (bzw. Maria Colgan) hat bei der Oracle Open World 2011 offenbar eine sehr schöne Präsentation zur Erstellung und Rolle der Statistiken gehalten, und die zugehörigen Folien ins Netz gestellt. Hier eine kurze Liste mit Punkten, die mir erinnerungswürdig erscheinen:

• dbms_stats
• üblicherweise sollte man nur die ersten Parameter der GATHER_%_STATS-Prozeduren benötigen (Schema Name, Table Name, Partition Name)
• Weitere Parameter:
• Inkrementelle Statistiken: "Ability to accurate generate global statistics from partition level statistics"
• Concurrent Statistics Gathering: "Ability to gather statistics on multiple objects concurrently under a GATHER_SCHEMA_STATS command"
• Default-Werte der Prozeduren können auf verschiedenen Ebenen gesetzt werden
• Sample Size: in 11g sollte man immer die default AUTO_SAMPLE_SIZE verwenden, da sie die Performance eines 10% Samples mit der Genauigkeit eines 100% Samples verbindet (dazu hatte Greg Rahn vor längerer Zeit gebloggt)
•  Index-Statistiken
• "Index statistics are automatically gathered during creation and maintained by GATHER_TABLE_STATS"
• Histogramme
• "Default create histogram on any column that has been used in the WHERE clause or GROUP BY of a statement AND has a data skew" ("Relies on column usage information gathered at compilation time and stored in SYS.COL_USAGE$")
• Cardinality-Berechnung:
• für popular values ("Popular value means values that are the endpoint for two or more buckets") -> (Anzahl Endpoint Buckets/Gesamtanzahl Buckets)/Anzahl rows in der Tabelle
• für non popular values ("Non-popular value means values that are the endpoint for only one bucket or are not an endpoint at all") -> DENSITY X * Anzahl rows in der Tabelle ("Density from 10.2.0.4 is calculated on the fly based on histogram information and is not the value show in USER_HISTOGRAMS")
• Probleme:
• Bind Peeking (in 11.2 durch Adaptive Cursor Sharing kein Problem mehr)
• Nearly popular values ("Nearly popular value means the value is classified as
  non-popular but the density calculation is not accurate for them"): werden unterschätzt; behebbar durch dynamic sampling hint im Statement)
• Extended Statistics
• für column groups: zur Darstellung von Korrelationen
• für expressions (virtual columns)
• automatische Erzeugung mit Hilfe von dbms_stats.seed_col_usage + Monitoring von Workload + dbms_stats.create_extended_stats
• Performance der Statistik-Erstellung durch Parallelisierung
• Parallelisierung für ein Objekt über Degree-Parameter ("Default is to use parallel degree specified on object"
• paralleler Zugriff auf mehrere Objekte: gesteuert über Parameter CONCURRENT
• Kombination beider Optionen möglich
• Inkrementelle Erstellung globaler Statistken
• "Typically gathering statistics after a bulk loading data into one partition would causes a full scan of all partitions to gather global table statistics" -> "Extremely time consuming"
• Inkremental sorgt dafür, dass nur Partitionen gelsen werden, deren Daten geändert wurden
• keine Statistiken für
• Volatile Tables: Tabellen mit stark veränderlichen Daten (Queue-Tabellen)
• GTT
• Intermediate work tables: ETL-Hilfstabellen