Samstag, Oktober 03, 2015

Estimate_Percent und Histogramme

Dass die Sample-Größe bei der Statistikerfassung via dbms_stats ein schwieriges Thema ist, habe ich wohl schon gelegentlich erwähnt - bzw. die Artikel anderer Autoren nacherzählt, die darüber geschrieben haben. Und ein besonders heikler Teilbereich dieses Themas sind die Histogramme. Und wahrscheinlich gehe ich auf den neuen Scratchpad-Artikel von Jonathan Lewis hier vor allem deshalb noch einmal intensiver ein, weil der Herr Lewis im zugehörigen OTN-Thread meine Einschätzung des gegebenen Falls bestätigt hat - und lobende Erwähnungen von Jonathan Lewis heben meine Stimmung ganz beträchtlich.

Im Artikel geht es um Folgendes: für eine relativ große Tabelle werden täglich neue Statistiken auf Basis einer Sample-Größe von einem Prozent erstellt. In der Folge ergeben sich für eine auf die Tabelle zugreifende einfache Query zwei Pläne: ein effektiver Plan mit Full Table Scan und ein ineffektiver Plan mit Index Range Scan, bei dem die Cardinality der einschränkenden Bedingungen offenbar massiv unterschätzt wird. Im Fall des Index Range Scans erfolgt dabei ein Access über eine Id-Spalte (mit order_id = 0, was ja oft ein häufig erscheinender Sonderfall ist) und eine anschließende Filterung über zwei weitere Spalten (bucket_type = 'P' und sec_id > 0). Auffällig ist dabei noch, dass der Filter Step nur noch eine geringe Reduzierung der Cardinality mit sich bringt, was darauf hin deutet, dass zumindest für bucket_type ein Histogram existiert, dass dem Optimizer mitteilt, dass diese Bedingung nicht selektiv ist. Um das Verhalten zu überprüfen, wird ein Test erstellt, der ein Datenmuster erzeugt, dass zu solchen Effekten führen könnte. Entscheidend ist dabei, dass für die order_id eine massive Ungleichverteilung definiert wird: 5% der Werte enthalten den Wert 0 und sind am Ende der Tabelle geclustert. Anschließend werden die Statistiken einmal mit estimate_percent => 1 und einmal mit mit der default auto_sample_size erzeugt. Wie im OTN-Fall ergeben sich zwei Pläne: bei Verwendung des expliziten Prozentwertes ergibt sich ein ein Index Range Scan und bei Verwendung von auto_sample_size ein Full Table Scan. Ursache ist, dass der Optimizer im Fall des 1% samples nicht erkannte, dass hier ein Histogramm nützlich sein könnte und daher eine Gleichverteilung annahm - und damit die Cardinality für den prädominaten Fall natürlich unterschätzt; und das, obwohl das 1% sample für die Histogrammerstellung eine größere Anzahl von Datensätzen überprüft als die auto_sample_size - nämlich 10000 gegenüber 5500 (diesen unerfreulich niedrigen und nicht anpassbaren Standardwert habe ich hier schon häufiger erwähnt). Warum die beiden Verfahren zu unterschiedlichen Einschätzung kommen, kann auch Jonathan Lewis auf Anhieb nicht erklären: zwar gibt es kleinere Unterschiede zwischen den sql traces der beiden Strategien, aber die erklären das unterschiedliche Verhalten hinsichtlich der Histogramme nicht - und auch die Dokumentation schweigt dazu. Aber entscheidend ist an dieser Stelle zunächst, dass das 1% sample die Gefahr mit sich bringt, data skew für stark geclusterte Werte zu übersehen, was die Instabilität der Pläne in der OTN-Frage erklärt.

Keine Kommentare:

Kommentar veröffentlichen