Sample Size für Statistikerfassung im SQL Server

Erin Stellato erläutert im SQLPerformance.com-Blog die Rolle der Sample Size für die Statistikerfassung im SQL Server. Interessant ist dabei vor allem, dass die Erfassung mit einem Sample ab einem gewissen Prozentsatz länger dauert als die Erfassung über einen FULLSCAN, wobei dieser Prozentsatz von Tabellengröße und verfügbaren Ressourcen abhängt.

Oracle-Funktionalitäten im SQL Server nachbilden

Bei der Arbeit mit dem SQL Server ist mir dieser Tage wieder einmal klar geworden, was für eine tolle Datenbank der Oracle Server doch ist... Hier eine kurze Liste mit Oracle-Funktionalitäten, die ich mir zuletzt im SQL Server 2008 mit mehr oder minder plausiblen Verfahren nachbauen musste:

• Autonome Transaktionen: brauchte ich, um Nachrichten in eine Log-Tabelle zu schreiben und dafür zu sorgen, dass diese Informationen ein Rollback der umgebenden Transaktion überstehen. Im SQL Server kann man zu diesem Zweck einen loopback linked server einrichten. Dazu liefert Dmitry Tolpeko eine instruktive Anleitung.
• External Table mit Preprozessor: hätte ich gerne verwendet, um die Dateien eines OS-Directories aufzulisten, wie ich es gelegentlich hier skizziert habe. Im SQL Server scheint mir die plausibelste Alternative die Verwendung einer c#-Assembly und einer CLR table valued function zu sein, wie sie Diana Moldovan beschreibt.
• Sequences: im SQL Server 2012 gibt es Sequences, aber in älteren Versionen muss man sie durch eine (Identity-)Spalte in einer Hilfstabelle emulieren, wie Rohit Khare zeigt. Beim parallelen Zugriff auf diese handgefertigten Sequenzwerte kommt man allerdings - je nach Vorgehen bei der Implementierung - recht schnell zu deadlocks.
• Robuster csv-Import: wieder ein Fall für External Tables - und natürlich hat der SQL Server auch ein paar bewährte Verfahren zur Integration von csv-Daten (BCP, BULK INSERT und diverse DTS/SSIS-Mechanismen). Im Fall relativ einfacher Definitionen sind die SQL Server Varianten völlig ausreichend (und im ersten Moment zugänglicher als die External Tables), aber bei etwas komplexeren Fällen fehlen mir ein paar nützliche Features (z.B. OPTIONALLY ENCLOSED BY).

Ich will natürlich nicht ausschließen, dass es für einige dieser Punkte noch geeignetere Lösungen gegeben hätte, aber für meine Zwecke sind die workarounds erst mal ausreichend.

METHOD_OPT für DBMS_STATS

Maria Colgan erläutert dieser Tage im CBO-Entwicklungs-Blog die Funktion des METHOD_OPT-Parameters. Der Artikel enthält nicht unbedingt Neues, stellt aber eine schöne Zusammenfassung dar. Grundsätzlich bestimmt der METHOD_OPT-Parameter:

• die Erstellung von Basisstatistiken für Spalten
• die Erstellung von Histogrammen
• die Erstellung von Extended Statistics

Die Syntax des Parameters zerfällt in zwei Teile:

Teil 1: bestimmt die Spalten, für die Statistiken erfasst werden
FOR ALL [INDEXED|HIDDEN] COLUMNS
Teil 2: bestimmt die Histogramm-Erstellung
SIZE [SIZE_CLAUSE]

Für Teil 1 gilt, dass weder FOR ALL INDEXED COLUMNS noch FOR ALL HIDDEN COLUMNS wirklich sinnvoll sind, denn Statistiken werden natürlcih nicht nur für indizierte oder virtuelle Spalten benötigt, sondern für alle Spalten, die der CBO betrachten muss. Für eine Spalte, die nicht in der Statistikerfassung berücksichtigt wird, wird nur die column length bestimmt (um die row length bestimmen zu können), jedoch nicht dauerhaft gespeichert.

Für Teil 2 gibt es folgende SIZE_CLAUSE Varianten:

• AUTO: Oracle bestimmt die erforderlichen Histogramme auf der Basis von COL_USAGE$.
• (integer value) 1-254: explizite Angabe der Anzahl der Buckets für die Histogramm-Erstellung. Sind Histogramme gewünscht, kann man immer auf 254 gehen, da nur den benötigte Anzahl verwendet wird.
• REPEAT: Histogramme werden nur für Spalten angelegt, die bereits Histogramme hatten. Die Einstellung ist nicht empfohlen, da sie auch die Bucket-Anzahl aus der vorherigen Erstellung übernimmt.
• SKEWONLY: erzeugt Histogramme nur für Spalten mit deutlich uneinheitlicher Datenverteilung.

Um die Histogrammerstellung auf eine einzelne Spalte CUST_ID zu beschränken, ist folgende Parameter-Angabe zu verwenden:

FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 CUST_ID

(wobei ich die Reihenfolge der Angaben nicht unbedingt intuitiv finde.) Um extended statistics anzulegen, verwendet man:

FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 (PROD_ID, CUST_ID)

Abschließend schlägt Frau Colgan in ihrem Artikel noch vor, die METHOD_OPT-Angabe über DBMS_STATS.SET_TABLE_PREFS festzulegen. 

Statistische Funktionen in Oracle

Jonathan Gennick hat dieser Tage eine sehr interessante Serie mit einführenden Artikeln zu diversen statistischen Standard-Funktionen begonnen:

• AVG: mit Erläuterungen zum Durchschnitt (also dem arithmetischen Mittel), zum gewichteten Durchschnitt, zur Rolle von NULL-Werten und zur Frage, warum der Median oft eine sinnvollere Angabe wäre, aber seltener verwendet wird (die kurze Antwort darauf lautet: "SQL has a history of poor support for computing the median.")
• STDDEV: eine umfangreiche Erklärung der Standardabweichung und ihrer Rolle im Qualitätsmanagement (six sigma, obwohl der Begriff selbst im Artikel nicht erscheint).
• MEDIAN: zur Bedeutung des Median und seiner Berechnung.

Alle Artikel enthalten neben der Erläuterung der Funktionen recht umfangreiche SQL-Beispiele. Meine Erfahrung ist, dass man nie einen Statistiker zur Hand hat, wenn man einen braucht - und daher ist es nützlich, wenn man sich selbst zumindest mit den Grundlagen dieser (schwarzen oder zumindest tiefgrauen) Kunst vertraut macht.

Details zur AUTO_SAMPLE_SIZE in 11g

Hong Su erläutert im Blog der CBO Entwickler das Vorgehen bei der Statistikerfassung mit AUTO_SAMPLE_SIZE in 11g. Insbesondere erwähnt er folgende Punkte:

• in 11g führt AUTO_SAMPLE_SIZE automatisch zu einem Full Table Scan, der die Anzahl, den Minimal- und den Maximalwert zu jedem Attribut ermittelt. Vor 11g wurden im gleichen Fall Queries mit Sampling durchgeführt, wobei das Sample sukzessive vergrößert wurde, sofern das Ergebnis bestimmten (auf internen Metriken basierenden) Anforderungen entsprach.
• im Rahmen des FTS wird ein Hash-basierter Algorithmus verwendet, um die NDV-Angabe zu bestimmen. Vor 11g wurde dazu ein entsprechendes COUNT(DISTINCT ...) in der sample-basierten Ermittlungs-Query verwendet. Das hash-basierte Verfahren liefert eine Genauigkeit von nahezu 100%.
• Auch weitere statistische Werte werden im Rahmen des FTS mit dem gleichen Verfahren und hoher Genauigkeit bestimmt (number of NULLs, AVG column length).
• Die Erfassung von Histogrammen und Index-Statistiken erfolgt weiterhin über Sampling, allerdings wird die Anzahl der Sample-Versuche gegenüber dem alten Verfahren deutlich reduziert. Für die Index-Statistiken werden dabei die NDV-Werte der entsprechenden Columns (im Fall einspaltiger Indizes) bzw. ColumnGroups (i.e. extended statistics; für mehrspaltige Indizes) übernommen.

Im Fall der Histogramme sind allerdings die Samples bekanntlich erschütternd klein (5500 Sätze), was im Zusammenspiel mit der Annahme, dass ein im Histogramm unbekannter Wert die halbe Auftrittswahrscheinlichkeit hat wie der am seltensten im Histogramm erscheinende Wert, zu unschönen Ergebnissen führen kann. Da dieser Satz doch mal wieder etwas sperrig geworden ist, hier noch das zugehörige Beispiel (das ich in ähnlicher Form sicher schon mal hier irgendwo eingebaut hatte):

-- 11.2.0.1
-- eine Tabelle mit 1M rows, davon genau 1 Satz mit col1 = 0
-- für alle anderen Sätze ist col1 = 1
create table t
as
select case when mod(rownum, 1000000) = 500000 then 0 else 1 end col1
  from dual
connect by level <= 1000000;

-- Statistikerfassung mit AUTO_SAMPLE_SIZE
begin
dbms_stats.gather_table_stats(
    user
  , 'T'
  , estimate_percent => dbms_stats.auto_sample_size
  , method_opt => 'for all columns size auto'
);
end;
/

select column_name
     , num_distinct
     , num_buckets
     , sample_size
  from dba_tab_cols
 where table_name = 'T';

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE
------------------------------ ------------ ----------- -----------
COL1                                      2           1        5525

select column_name
     , endpoint_number
     , endpoint_value
  from dba_histograms
 where table_name = 'T';

COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ --------------- --------------
COL1                                      5525              1

explain plan for
select *
  from t
 where col1 = 1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   999K|  2929K|   436   (3)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    |   999K|  2929K|   436   (3)| 00:00:06 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL1"=1)

explain plan for
select *
  from t
 where col1 = 0;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500K|  1464K|   436   (3)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    |   500K|  1464K|   436   (3)| 00:00:06 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL1"=0)

explain plan for
select *
  from t
 where col1 = 42;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500K|  1464K|   436   (3)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    |   500K|  1464K|   436   (3)| 00:00:06 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL1"=42)

Hier ist der NUM_DISTINCT-Wert korrekt mit 2 angegeben, aber da der CBO im Histogramm nur einen Wert (nämlich 1) findet, nimmt er an, dass jeder unbekannte Wert (die seltene 0 genau wie die abwegige 42) halb so oft erscheint wie der seltenste (und in diesem Fall einzige) Wert - also: 999K/2 = 500K.

In diesem extremen Fall liefert erst estimate_percent => 100 mit Sicherheit korrekte Angaben (da jedes Sample Gefahr läuft, den seltenen Wert zu verpassen):

begin
dbms_stats.gather_table_stats(
    user
  , 'T'
  , estimate_percent => 100
  , method_opt => 'for all columns size auto'
);
end;
/

select column_name
     , num_distinct
     , num_buckets
     , sample_size
  from dba_tab_cols
 where table_name = 'T';

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE
------------------------------ ------------ ----------- -----------
COL1                                      2           2     1000000

select column_name
     , endpoint_number
     , endpoint_value
  from dba_histograms
 where table_name = 'T';

COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ --------------- --------------
COL1                                         1              0
COL1                                   1000000              1

explain plan for
select *
  from t
 where col1 = 0;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     3 |   436   (3)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     3 |   436   (3)| 00:00:06 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL1"=0)

Dynamic Sampling wäre in diesem Fall natürlich auch eine Alternative.

OLTP compression und row migration

Angeregt durch Jonathan Lewis' Artikelserie zum Thema hat Alberto Dell'Era sich mit der Frage beschäftigt, ob aufgrund eines Updates migrierte rows im Fall von OLTP compression im Zielblock wieder komprimiert werden. Die Antwort darauf lautet: ja - und wird im Artikel anhand von Block-Dumps belegt. Offenbar wird row migration in diesem Fall tatsächlich wie ein Insert in einen neuen Block behandelt (was inhaltlich ja auch plausibel ist).

Materialized Views zur Implementierung von table constraints

Toon Koppelaars zeigt, wie man Materialized Views mit der Option REFRESH FAST ON COMMIT angelegen kann, um table constraints - also multi-row constraints - zu unterstützen. Die Idee dabei ist sehr einfach (und nicht neu - erstmals ist sie mir vor ziemlich vielen Jahren bei Tom Kyte begegnet - aber durchaus wirksam): man legt eine MV an, die niemals Zeilen enthalten sollte - also z.B.:

create materialized view ...
refresh fast on commit
as
select 'wrong' as text
  from dual
 where exists ... -- hier folgt die eigentliche Constraint-Logik 

Auf der zur MV gehörigen Tabelle wird dann ein Check-Constraint mit der Bedingung 1 = 0 ergänzt, der folglich einen Fehler wirft, so bald das Commit für die Basistabelle eine Aktualisierung der MV initiiert. Leider sind die Voraussetzungen für REFRESH FAST ON COMMIT recht restriktiv - meine letzten Notizen zum Thema (mit ein paar weiterführenden Links) finden sich hier -, was die Einsatzmöglichkeiten beschränken kann. Außerdem ist das FAST Refresh eines jener Oracle-Features, bei denen die Angabe FAST Anlass zur Sorge gibt - tatsächlich ist es inkrementell, aber nicht unbedingt schnell (d.h. schneller als ein complete refresh). Aber so lange Oracle (und andere RDBMS) keine echten SQL Assertions zur Unterstützung von table constraints anbietet, sind solche MVs ein brauchbarer Workaround.

Ignorierte Hints

Der Titel ist ein Oxymoron, denn Hints werden bekanntlich nicht ignoriert, da sie kein gut gemeinter Vorschlag, sondern eine Direktive für den Optimizer sind: wenn sie bei der Erstellung eines Plans relevant sind, muss ihnen gefolgt werden. Allerdings gibt's natürlich doch ein paar Fälle, in denen sich Hints nicht so auswirken, wie man erwarten würde und Timur Akhmadeev hat sich die Mühe gemacht, diese Fälle in tabellarischer Form zusammenzufassen. Dort finden sich dann auch diverse Links zu den entsprechenden Artikeln der Herren Lewis und Geist und auf die Dokumentation.

Ergänzend hier auch noch mal der Hinweis, dass auch mehr oder minder beliebige Kommentare (z.B. einzelne Buchstaben, Satzzeichen) zur Invalidierung folgender Hints führen können.

Hinweise zu Hints

Tanel Poder hat mal wieder einen extrem interessanten Artikel veröffentlicht: diesmal zu den nicht offiziell dokumentierten Views V$SQL_FEATURE und V$SQL_FEATURE_HIERARCHIE, zu denen bisher nur spärliche Hinweise (unter anderem) von Julian Dyke und Yong Huang zu finden waren. Außerdem liefert er ein paar Analyse-Scripts, mit denen sich diesen beiden Views und V$SQL_HINT einige interessante Informationen entnehmen lassen:

• V$SQL_HINT
• in diesem Fall beschränkt sich das zugehörige Script hint auf eine case-insensitive Suche über den Namen des Hints und eine Spaltenauswahl. Zu den enthaltenen Informationen gehören die Angaben des Releases, in dem der Hint zum ersten Mal erschien (VERSION), und des Releases, in dem der Hint in den "stored outline" Code aufgenommen wurde (VERSION_OUTLINE).
• V$SQL_FEATURE, V$SQL_FEATURE_HIERARCHIE
• das Script hinth (Hint Hierarchie) verknüpft die beiden Views und liefert damit Informationen zur Feature-Hierarchy und zeigt, in welchen Kontext ein Hint gehört (Execution oder Compilation; darunter dann CBO, RBO, QUERY_REWRITE, TRANSFORMATION etc.)
• das Script sqlfh (SQL feature hierarchy) liefert eine Auflösung diverser Abkürzungen wie CBQT (cost-based query transformation) oder CVM (complex view merging), die man auch aus dem CBO Trace kennt (Event 10053)

Die direkten Links auf die Scripts des Herrn Poder spare ich mir, da ich mir bei Bedarf ähnliche Queries basteln würde - und denen würde ich dann Namen geben, mit denen ich mehr anfangen kann, als mit den Poder'schen Kürzeln.

Als kleine Ergänzung noch ein Link zu einem älteren Artikel von Jonathan Lewis mit einer kurzen Taxonomie undokumentierter Hints - die man in der Regel natürlich mit noch mehr Vorsicht behandeln sollte als die dokumentierten Varianten (mit denen man bekanntlich auch schon vorsichtig sein sollte).