Florenz

Von Fiesole aus gesehen.

Technical Papers des cbo Teams

Das Team der cbo-Entwickler hat ein paar Links zu technischen Papers bereitgestellt, die sicher alle eine genauere Lektüre verdienen.

block_sample für gather_table_stats

Dass man die Sample-Größe für die dbms_stats-Prozeduren seit Release 11 in aller Regel nicht mehr anpassen muss, da die AUTO_SAMPLE_SIZE sehr gute Resultate liefert, hat Greg Rahn schon vor einigen Jahren festgestellt:

Overall the 11g DBMS_STATS has been enhanced to gather stats in less time, but in my opinion the significant enhancement is to AUTO_SAMPLE_SIZE which yields near 100% sample accuracy in 10% sample time.

Trotzdem hatte ich mir zuletzt Gedanken darüber gemacht, ob man mit der Option block_sample die Laufzeit der Statistik-Erstellung signifikant reduzieren kann. Die Dokumentation erklärt diese Option folgendermaßen:

Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.

Ein kleiner Test zeigt, dass die Option tatsächlich einen deutlichen Einfluss auf die Leseoperationen der Statistikerfassung hat:

-- Anlage einer relativ großen Testtabelle
drop table big_t;

create table big_t
as
with
base_data
as
(
select rownum id1
  from dual
connect by level <= 1000000
)
,
mult
as
(
select rownum id2
  from dual
connect by level <= 30
)
select rownum id
     , 'aaa' col2
  from mult
     , base_data;

-- Statistikerhebung
exec dbms_stats.gather_table_stats(user, 'BIG_T')
 
exec dbms_stats.gather_table_stats(user, 'BIG_T', estimate_percent=>1)

exec dbms_stats.gather_table_stats(user, 'BIG_T', estimate_percent=>1, block_sample=>TRUE)

Die Ergebnisse für die angesprochenen Versionen sehen folgendermaßen aus:

Version	Parameter	Laufzeit
10.2.0.4	ohne	33.86 sec.
10.2.0.4	estimate_percent: 1	12.31 sec.
10.2.0.4	estimate_percent: 1; block_sampling	1.01 sec.
11.1.0.7	ohne	13.07 sec.
11.1.0.7	estimate_percent: 1	2.40 sec.
11.1.0.7	estimate_percent: 1; block_sampling	1.78 sec.

Dazu noch ein paar Kommentare:

• die deutlichen Laufzeitunterschiede ergeben sich aus der unterschiedlichen I/O-Performance der beiden Testsysteme (sehr langsame Platten für 10.2.0.4)
• ein 10046er Trace zeigt, dass die zugehörigen Queries mit der Klausel SAMPLE bzw. SAMPLE BLOCK ausgeführt werden.
• mit SAMPLE BLOCK sinkt die Anzahl der erforderlichen LIOs und PIOs in beiden Versionen; Für die Queries mit SAMPLE werden alle Tabellenblocks gelesen, während SAMPLE BLOCK tatsächlich nur auf einen gewissen Prozentsatz liest, der ungefähr dem geforderten Wert entspricht (ermittelt mit AUTOTRACE in 11.1.0.7):
• FTS ohne Sampling: 30812  consistent gets
• FTS mit sample(1): 30812  consistent gets
• FTS mit sample block (1): 327  consistent gets
• FTS mit sample block (10): 3399  consistent gets

Für die Erstellung von Statistiken für sehr große Tabellen könnte die block_sample-Option demnach auch in Versionen >= 11 immer noch interessant sein, da sie die erforderlichen Leseoperationen dramatisch reduzieren kann. Das gilt aber natürlich nur dann, wenn sichergestellt ist, dass die Blocks ähnliche Verteilungsmuster besitzen wie die gesamte Tabelle. Die Qualität der mit Hilfe von Sampling erzeugten Statistiken wäre aber ohnehin noch mal ein anderes Thema.

Dynamic Sampling

Heute wollte ich einer Query einen dynamic_sampling Hint mitgeben und fand bei Jonathan Lewis günstigerweise den Hinweis, dass man diesen Hint nicht pro Tabelle setzen muss, sondern auf Query-Ebene definieren kann.

Eine Liste mit einer Beschreibung der Sampling-Levels (und weitere Informationen zum Thema) findet man bei Maria Colgan.

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

Tabellentransfer mit dbms_datapump und DB-Link

Laurent Schneider hat vor kurzem eine kleine Demonstration dazu geliefert, wie man einzelne Tabellen mit Hilfe von dbms_datapump transferieren kann, und auf diese Weise auch die abhängigen Elemente (Indizes, Constraints) befördert, die ein CTAS über DB-Link verlieren würde.

Beim Durchspielen des Beispiels gab's bei mir noch folgende Effekte:

• von 10.2.0.4 nach 11.1.0.7 funktionierte der Transfer problemlos: da der Transfer-Job asynchron läuft, wird die Tabelle zwar sofort angelegt, aber der Datentransfer benötigt anschließend noch einige Zeit, so dass ein SELECT COUNT(*) erst einmal 0 Sätze liefert.
• der umgekehrte Weg (von 11.1.0.7 nach 10.2.0.4) lieferte keinen Fehler, aber die Tabelle kam nicht an. Möglicherweise ist da noch ein Kompatiblitätsproblem im Spiel.

Auf jeden Fall ein interessantes Feature, das Transfer-Tasks deutlich handhabbarer machen könnte.

Dynamic Sampling für stark veränderliche Daten

Randolf Geist erläutert in seinem Blog, in welchen Fällen dynamic sampling für "highly volatile data" (z.B. in einer global temporary table) geeigneter ist als eine grundsätzliche Erhebung der Statistiken, und vor allem unter welchen Umständen dynamic sampling nicht ohne Weiteres weiterhilft:

However, there is still a potential problem even when resorting to Dynamic Sampling. If the cursors based on Dynamic Sampling get shared between sessions then they won't be re-optimized even if a GTT in one session is completely different from the one of the session that created the shared cursor previously.

Eine mögliche Lösung solcher Probleme liefert die Verwendung von Row Level Security (RLS) durch Ergänzung redundanter Prädikate, die dafür sorgen, dass Queries sich textuell unterscheinden (ergänzt wird 1=1; 2=2 oder dergleichen).

Darüber hinaus werden auch noch die Effekte von Adaptive Cursor Sharing und Cardinality Feedback diskutiert und - wie beim Herrn Geist üblich - anhand zahlreicher ausführlicher Testfälle überprüft.

SSAS 2008 R2 Performance-Guide

Der SSAS-Performance Guide für Version 2008 R2 ist seit einigen Tagen verfügbar. Chris Webb fasst die Bedeutung des Guides prägnant zusammen: "if you are serious about SSAS you need to read it".

Hier noch eine Sammlung von Punkten, die mir erinnerungswürdig erscheinen (ohne jeden Anspruch auf Vollständigkeit):

• Performance-Vorteile geeigneter Attribute Relationships (S. 11)
• "Cross products between levels in the hierarchy do not need to go through the key attribute. This saves CPU time during queries."
• "Aggregations built on attributes can be reused for queries on related attributes. This saves resources during processing and for queries."
• "Auto-Exist can more efficiently eliminate attribute combinations that do not exist in the data."
• Flexible vs. Rigid Relationships (S.12)
• "When a change is detected during process in a flexible relationship, all indexes for partitions referencing the affected dimension (including the indexes for attribute that are not affected) must be invalidated. This is an expensive operation and may cause Process Update operations to take a very long time. Indexes invalidated by changes in flexible relationships must be rebuilt after a Process Update operation with a Process Index on the affected partitions; this adds even more time to cube processing."
• Reference Dimensions (S. 14)
• können materialisiert werden
• Probleme beim ProcessUpdate: "if you run a process update on the intermediate dimension, any changes in the relationships between the outer dimension and the reference will not be reflected in the cube. Instead, the original relationship between the outer dimension and the measure group is retained"
• Large Dimensions (S. 18)
• "If a dimension contains attributes where the total size of the string values (this includes translations) exceeds 4 GB, you will get an error during processing." Für Denali soll das Limit beseitigt werden.
• Partition Slicing (S. 20)
• Auto Slice: "when Analysis Services reads the data during processing, it keeps track of the minimum and maximum attribute DataID reads. These values are used to set the slice when the indexes are built on the partition." + "auto slice typically works best if the data contained in the partition maps to a single attribute value. When that is the case, the maximum and minimum DataID contained in the slice will be equal and the slice will work efficiently."
• Partition Sizing (S. 22)
• "partition size alone does not have a substantial impact on query speeds"
• sollte sich an administrativen Gesichtspunkten orientieren
• die BIDS-Warnung vor Partitionen mit mehr als 20M rows ist bedeutungslos
• Relational Data Source Design (S.26ff.)
• "Use a Star Schema for Best Performance"
• "Consider Moving Calculations to the Relational Engine"
• "Use Views"
•  Query Processor Cache (S. 35)
• Query Context, Session, Context, Global Context: "Even though a query references all three contexts, it will typically use the cache of a single context. This means that on a per-query basis, the query processor must select which cache to use. The query processor always attempts to use the broadly applicable cache depending on whether or not it detects the presence of calculations at a narrower context. If the query processor encounters calculations created at query time, it always uses the query context, even if a query also references calculations from the global context (there is an exception to this –
  queries with query calculated members of the form Aggregate() do share the session cache)."
• offenbar gibt es sehr viele Fälle, in denen der Global Cache nicht verwendet werden kann (MDX functions that are locale-dependent; "use of cell security; functions such as UserName, StrToSet, StrToMember, and StrToTuple; or LookupCube functions in the MDX script or in the dimension or cell security definition disable the global cache. That is, just one expression that uses any of these functions or features disables global caching for the entire cube."; visual totals; "Queries that use the subselect syntax (SELECT FROM SELECT) or are based on a session subcube (CREATE SUBCUBE)"; Arbitrary shapes); es bietet sich an, mit dem SQL Profiler zu prüfen, ob Standard-Excel-Zugriffe den Global Cache verwenden können, oder ob einer der Problemfälle dagegen spricht.
• Data Retrieval (S.38)
• Storage Engine (SE) sucht nach Daten in folgender Reihenfolge:
• SE Cache
• Aggregationen (I/O from disk oder OS-Cache)
• Partitionsdaten (I/O from disk oder OS-Cache)
• "storage engine cache is also known as the data cache registry because it is composed of the dimension and measure group caches that are the same structurally"
• vorhandene Inhalte können aggregiert oder gesplittet werden
• bei Memory-Engpässen wird der Cache geleert
• Aggressive Data Scanning (S. 39): SSAS führt manchmal recht massive Prefetch-Operationen durch (in der Annahme, dass der Anwender die Daten schon noch brauchen wird). Deaktivieren kann man das Prefetching mit "Disable Prefetch = 1" im Connection-String.
• Subspace Computation (S.40ff.): erläutert die Unterschiede zwischen Cell-by-cell evaluation und subspace computation. 
•  Cell-by-cell evaluation: "The 10 cells for [2005, All Products] are each evaluated in turn. For each, the previous year is located, and then the sales value is obtained and then added to the sales for the current year." (bei sparse data wird viel unnötige Arbeit geleistet; außerdem werden Zugriffe unnötig wiederholt)
• "In subspace computation, the engine works its way down an execution tree determining what spaces need to be filled."
• Expensive vs. Inexpensive Query Plans (S. 42): ein Plan ist expensive, wenn er Cell-by-cell evaluation enthält: IIf, CASE, and IF functions können expensive query plans hervorrufen. Eine Liste mit Gründen für eine Cell-by-cell evaluation folgt aus Seite 49ff.
• Optimizing MDX (S. 45ff.): liefert diverse Hinweise zur Query-Optimierung (Cache-Effekte ausschalten; SQL Profiler nutzen; Unterscheidung zwischen SE und FE Events; binäre Vereinfachung von Queries etc.) und auch einige wichtige Links. Hier nur die Punkte, diemir halbwegs neu waren:
• Avoid Assigning Nonnull Values to Otherwise Empty Cells (S. 50): leuchtet ein, denn SSAS "is very efficient at using sparsity of the data to improve performance. Adding calculations with nonempty values replacing empty values does not allow Analysis Services to eliminate these rows."
• Sparse/Dense Considerations with “expr1 * expr2” Expressions (S. 51): die Expression mit der höheren Anzahl von NULLs sollte vorne stehen: entspricht dem Vorgehen bei Nested Loops Joins, wo ebenfalls die kleinere Menge die Schleife treiben sollte.
• IIF-Optimierung mit den Hints EAGER und STRICT (S. 53).
• NON_EMPTY_BEHAVIOR: in 2008 in vielen Fällen nicht mehr nützlich (= wird oft ignoriert), und manchmal sogar ein Problem ("Eliminate it from the MDX script and add it back after performance testing demonstrates improvement")
•  Aggregations (S. 60ff.)
•  unnötige Aggregationen schädigen den Cache: "adding unnecessary aggregations can worsen query performance because the rare hits move the aggregation into the file cache at the cost of moving something else out"; außerdem: "there is a direct correlation between the number of aggregations and the duration for the Analysis Services storage engine to parse them"
•  Aggregations-Zugriffe werden im Profiler durch das "Get Data From Aggregation event" ausgewiesen
• Scale-Out (S. 69): Verteilung auf mehrere Server; z.B. zur Vermeidung des Blockierens durch langlaufende Queries.
• Tuning Processing Performance (S. 70ff.)
• Dimension Processing
• ProcessUpdate (S. 76) 
• erfordert Neuaufbau von flexible aggregations: "Note that ProcessUpdate drops invalid aggregations and indexes, requiring you to take action to rebuild the aggregations in order to maintain query performance. However, flexible aggregations are dropped only if a change is detected."
• kann sehr teuer sein: "When ProcessUpdate runs, it must walk through the partitions that depend on the dimension. For each partition, all indexes and aggregation must be checked to see whether they require updating. On a cube with many partitions, indexes, and aggregates, this can take a very long time. Because this dependency walk is expensive, ProcessUpdate is often the most expensive of all processing operations on a well-tuned system, dwarfing even large partition processing commands."
• ProcessAdd (S. 77): 
• "typically runs much faster than ProcessUpdate."
• mit Default Konfiguration gibt's Fehlermeldungen duplicate key beim Processing ("caused by the addition of non-key properties that already exist in the dimension": Anpassung der Fehlerkonfiguration erforderlich)
• Tuning
• Reduzierung der Dimensionsattribute
• Verzicht auf Bitmap Indexes
• ByTable Processing: liefert notwendigerweise duplicate key Fehler
• Splitting Processing Index and Process Data (S.84): wird immer noch empfohlen