Fünf wichtige Optimizer Hints

Jonathan Lewis hat in seinem Blog eine Zusammenfassung der wichtigsten Punkte seiner DOAG-Präsentation Five Hints for Optimising SQL veröffentlicht (was ich sehr erfreulich finde, da ich den Vortrag natürlich nicht gehört habe, so wie alle Vorträge, die in den letzten zehn Jahren bei nationalen und internationalen Konferenzen gehalten wurden). Dabei beginnt er mit dem üblichen Hinweis, dass Hints normalerweise im Produktivsystem zu vermeiden und als Ultima Ratio zu betrachten sind. Und dass von den 332 Hints, die in 12.1.0.2 zur Verfügung stehen, nur eine Handvoll tatsächlich eingesetzt werden sollten, wenn es sich nicht vermeiden lässt. Aus dieser Menge nimmt er dann noch einmal fünf Hints heraus, die er als relativ nützliche Hilfsmittel ansieht, um den Optimizer bei seiner Entscheidungsfindung zu unterstützen. Zu diesen fünf Hints liefert er dann jeweils eine längere Erklärung, die im Rahmen eines komplexeren Beispiels erscheint. Ich versuche, hier hier die initiale knappe Definition mit den folgenden Erklärungen zusammenzuführen. Wie übliche werde ich dabei zweifellos zentrale Punkte unterschlagen...

• Merge / no_merge — "Whether to use complex view merging". Der Hint kann aber auch verwendet werden, um einzelne Abschnitte einer komplexen Abfrage voneinander zu isolieren. Dadurch kann man einerseits den Suchraum des Optimizers reduzieren und andererseits manchmal auch verhindern, dass der Optimizer in einem bestimmten Bereich der Query eine ungeeignete Strategie wählt. Im Beispiel wird neben den no_merge Hint noch ein cardinality Hint gestellt, der zwar nicht vollständig dokumentiert ist, aber dazu verwendet werden kann, die cardinality eines Query Blocks anzugeben. Ein Einsatzgebiet des Hint-Paars ist auch der Wechsel zwischen den Möglichkeiten "join then aggregate" und "aggregate then join".
• Push_pred / no_push_pred — "What to do with join predicates to non-merged views". Mit Hilfe dieser Hints kann man festlegen, ob eine Join-Bedingung in eine (nicht im Rahmen der Transformation in einen Join umgewandelte) Subquery propagiert werden soll. Das kann sinnvoll sein, wenn sich dadurch eine effektivere Zugriffsstrategie ergibt (etwa durch einen indizierten Zugriff).
• Unnest / no_unnest — "Whether or not to unnest subqueries". Normalerweise wandelt der Optimizer Subqueries gerne in Joins um (Semi-Join für exists; Anti-Join für not exists). Da eine Subquery in solchen Fällen oft besser zu verstehen ist, kann ein Hint an dieser Stelle dafür sorgen, dass der Optimizer auch dann die Join-Variante wählt, wenn das (fehleranfällige) Costing nicht für diese Strategie sprechen würde. Im Rahmen des Beispiels weist der Autor darauf hin, dass das Costing in seinem Beispiel (ohne Unnest) tatsächlich die Anzahl der Ausführungen einer Subquery berücksichtigt (Anzahl Departments), was eine jüngere Errungenschaft des CBOs ist.
• Push_subq / no_push_subq — "When to handle a subquery that has not been unnested". Über diesen Hint kann man beeinflussen, wann eine Subquery ausgeführt wird - traditionell wurde sie erst gegen Ende der Verarbeitung berücksichtigt. Das Pushing einer Subquery bedeutet, sie im Plan nach unten zu schieben, also früher auszuführen.
• Driving_site — "Where to execute a distributed query". Dieser Hint hat keine NO-Variante, weil er nur angibt, auf welcher Seite einer verteilten Query die Ausführung koordiniert wird und welche Seite als remote fungiert. Der Hint fünktioniert übrigens nicht für CTAS und Insert as Select Queries, was nicht unbedingt nachvollziehbar ist. In solchen Fällen kann die Verwendung einer pipelined table function sinnvoll sein.

Mal wieder eine sehr nützliche Zusammenfassung, die ich in Zukunft vermutlich häufiger als Referenz verwenden werde.

Nutzlose und weniger nutzlose METHOD_OPT-Angaben

Da ich hier zuletzt fast nur noch Links kommentiert habe, zur Abwechslung noch mal ein bisschen was Praktisches. Im OTN-Forum wurde heute die Frage gestellt, wieso dbms_stats.gather_table_stats auf den nicht dokumentierten Parameter-Wert "FOR ALL INDEXES" nicht mit einem Fehler reagiert. Meine Antwort darauf lautet: keine Ahnung, aber er ist noch gefährlicher als "FOR ALL INDEXED COLUMNS":

drop table t;
create table t
as
select rownum id
     , mod(rownum, 2) col1
     , mod(rownum, 5) col2
     , mod(rownum, 10) col3
  from dual
 connect by level <= 10000;  
 
create index t_idx1 on t(id);

exec dbms_stats.delete_table_stats(user, 't')
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'FOR ALL INDEXES FOR ALL INDEXED COLUMNS')

select column_name, num_distinct, last_analyzed, histogram from user_tab_cols where table_name = 'T' order by 1;

COLUMN_NAME                    NUM_DISTINCT LAST_ANA HISTOGRAM
------------------------------ ------------ -------- ---------------
COL1                                                 NONE
COL2                                                 NONE
COL3                                                 NONE
ID                                    10000 01.12.15 HEIGHT BALANCED

--> create column statistics (and histograms) just for indexed columns

exec dbms_stats.delete_table_stats(user, 't')
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'FOR ALL INDEXES')

select column_name, num_distinct, last_analyzed, histogram from user_tab_cols where table_name = 'T' order by 1;

COLUMN_NAME                    NUM_DISTINCT LAST_ANA HISTOGRAM
------------------------------ ------------ -------- ---------------
COL1                                                 NONE
COL2                                                 NONE
COL3                                                 NONE
ID                                                   NONE

--> creates no column statistics

exec dbms_stats.delete_table_stats(user, 't')
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'FOR ALL COLUMNS')

select column_name, num_distinct, last_analyzed, histogram from user_tab_cols where table_name = 'T' order by 1;

COLUMN_NAME                    NUM_DISTINCT LAST_ANA HISTOGRAM
------------------------------ ------------ -------- ---------------
COL1                                      2 01.12.15 FREQUENCY
COL2                                      5 01.12.15 FREQUENCY
COL3                                     10 01.12.15 FREQUENCY
ID                                    10000 01.12.15 HEIGHT BALANCED

--> creates column statistics (and histograms) for all columns

exec dbms_stats.delete_table_stats(user, 't')
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS COL3 SIZE 254')

select column_name, num_distinct, last_analyzed, histogram from user_tab_cols where table_name = 'T' order by 1;

COLUMN_NAME                    NUM_DISTINCT LAST_ANA HISTOGRAM
------------------------------ ------------ -------- ---------------
COL1                                      2 01.12.15 NONE
COL2                                      5 01.12.15 NONE
COL3                                     10 01.12.15 FREQUENCY
ID                                    10000 01.12.15 NONE

--> creates column statistics for all columns and a histogram for COL3

Warum gefährlicher als "FOR ALL INDEXED COLUMNS"? Weil man damit tatsächlich gar keine Spalten-Statistiken erhält, so dass der Optimizer bei der Bestimmung der Cardinalities für alle Spalten auf Schätzungen zurückgehen muss. Ganz ohne Statistiken hätte man da noch dynamic sampling (und dadurch brauchbare Cardinalities), aber wenn Tabellen-Statistiken vorliegen, geht der Optimizer davon aus, dass er auch mit den Angaben zu den Spalten etwas anfangen kann:

SQL> select count(*) from t where col2 = 1;

  COUNT(*)
----------
      2000

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   100 |  1300 |     9   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("COL2"=1)

SQL> exec dbms_stats.delete_table_stats(user, 't')

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> select count(*) from t where col2 = 1;

  COUNT(*)
----------
      2000

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  2000 | 26000 |     9   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("COL2"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)

Korrigierte Histogramm-Statistiken im SQL Server anlegen

Nachdem ich viele Jahre lang Tom Kytes Mantra wiederholt habe, dass alle RDBMS unterschiedlich sind und man die Unterschiede kennen muss, um sinnvoll mit den Systemen umzugehen, behaupte ich in jüngerer Vergangenheit immer häufiger, dass die relationalen Datenbanken im Prinzip alle ziemlich ähnlich sind und sich in mancher Hinsicht immer ähnlicher werden. In jedem Fall bin ich immer wieder froh, wenn ich neue Gemeinsamkeiten feststelle, so etwa auch diese hier: im immer wieder lesenswerten SQL Performance.com Blog erläutert Dan Holmes anhand eines umfangreichen Beispiels, wie man mit Hilfe der (nicht supporteten) Option STATS_STREAM des UPDATE STATISTICS Kommandos Optimizer-Statistiken exportieren und importieren kann, um auf diese Weise ein passenderes Histogram einer ungleichen Datenverteilung zu erstellen, als das durch die WITH SAMPLE Option von UPDATE STATISTICS erzeugte. Im Oracle-Universum ist diese Strategie nicht unbekannt (und wird dort sogar offiziell unterstützt) - ein entsprechendes Beispiel liefert (wie üblich) Jonathan Lewis.

Ein griffiges Beispiel für Lateral Inline Views

Auf den Lateral Join, der in 12c eingeführt wurde, habe ich hier schon vor geraumer Zeit hingewiesen - aber was damals fehlte, war ein griffiges Beispiel dafür, was man damit eigentlich anstellen kann. Das hat nun Oren Nakdimon in Teil 7 seiner überaus lesenswerten Serie Write Less with More ergänzt. In seinem Beispiel wird eine Projekt-Tabelle mit einer last_days_to_show_in_reports Spalte vorgestellt, in der für die einzelnen Einträge unterschiedliche Zeiträume angegeben sind (als Anzahl von Tagen). Will man an diese Tabelle eine der last_days_to_show_in_reports Angabe entsprechende Anzahl von Datensätzen joinen, so könnte man vor 12c einen Join mit einer Generator-Query und einer geeigneten ">=" Bedingung definieren (oder alternativ eine table function mit Collection Unnesting, was für mich aber deutlich komplizierter aussieht). In 12c lässt sich dazu in der FROM Klausel eine sehr übersichtliche korrelierte Subquery einsetzen, die nach dem Schlüsselwort LATERAL erscheint. Na gut: ich gebe zu, dass das bei wiederholtem Lesen auch auch nicht selbsterklärend klingt - aber wenn man sich das Code-Beispiel im Artikel ansieht, wird der Fall deutlich klarer.

Ein Wait Interface für Postgres

Ein schöner Hinweis im Artikel Wait interface in PostgreSQL, dessen Autor ich leider nicht ohne weitere Recherche namentlich benennen kann: für postgres 9.4 gibt es eine von Ildus Kurbangaliev entwickelte Erweiterung pg_stat_wait, die möglicherweise in 9.6 Teil des Standards werden wird (aktuell aber noch ein Recompile von postgres und eine Einbindung via shared_preload_libraries erfordert). Nach der Installation stehen diverse Funktionen und Views zur Verfügung, die Informationen zu Events unterschiedlicher Typen bereitstellen (LWLocks, Storage, Locks, Network, Latch, CPU). Zu den Wait Events werden Angaben zur Häufigkeit des Auftretens und zur Dauer der Wartesituationen erfasst. Dabei ist die Erfassung Sample-basiert (wodurch sich nur ein geringer Overhead ergibt), aber man kann auch ein explizites Tracing der Session aktivieren (pg_start_trace), das ein lückenlose Erfassung der Waits ermöglicht. Insgesamt eine Ergänzung, die meine Begeisterung für die Analyse von Performance-Fragestellungen in postgres deutlich erhöhen würde, und die aus meiner Sicht unbedingt in den Standard gehört.

Wait Event Analyse mit Flame Graphs

Luca Canali hat im Databases at CERN Blog wieder einmal eine detaillierte Analyse vorgestellt, in der er Flame Graphs mit Extended Stack Profiling verbindet und damit die Aussagen des Oracle Wait Interfaces näher beleuchtet. Der eigentliche Anlass der Untersuchung ist dabei die Beobachtung des Auftretens von Fällen, in denen die DB time größer ist als die Summe von CPU time und Wait time, was a) nicht vorkommen sollte und b) mit dem Einsatz moderner Storage zu tun haben kann. Eine Nacherzählung der umfangreichen Analyseschritte erscheint mir eher witzlos - mir genügt hier der Vermerk, dass das verwendete Instrumentarium sehr genaue Aussagen über das Systemverhalten erlaubt. Wenn ich gelegentlich wieder mal mit komplexen und OS-nahen Problemen zu tun habe, sollte ich mich hier bedienen. Nützlich ist dabei auch, dass der Herr Canali seine älteren Artikel zu ähnlichen Themen und die verwendeten Tools verlinkt hat.

Falsche Ergebnisse in 12c mit ROWSETS

Gerne würde ich an dieser Stelle erklären, was ein ROWSET (außerhalb von Java) in Oracle 12c eigentlich ist, aber dazu findet man nicht allzu viele Erklärungen. Aber zumindest kann man offenbar mit einiger Sicherheit sagen, dass es sich nicht um ein besonders ausgereiftes neues Feature handelt...

In einem aktuellen Scratchpad-Artikel beschreibt Jonathan Lewis einen Fall, in dem die Auswahl unterschiedlicher Arraysize-Angaben dazu führt, dass die gleiche Query (auf der gleichen Datenbasis) bei mehrfacher Ausführung eine unterschiedliche Anzahl von Ergebniszeilen zurückliefert. Nun sollten Queries bei unveränderter Datenbasis grundsätzlich die gleichen Ergebnisse liefern - und die Arrayssize, die nur bestimmt, wie viele Datensätze im Rahmen einer Fetch-Operation an den Client geschickt werden, sollte ganz gewiß keine Ergebnisänderung hervorrufen. Die Ursache der unterschiedlichen Ergebnisse zeigt dbms_xplan.display_cursor mit aktivierter Anzeige der Projection. In dieser findet sich in runden Klammern eine Angabe (rowset=200). Zur Bedeutung des Features sagt der Herr Lewis nur: "This is reporting a feature new in 12c (and not to be confused with Oracle Java Rowsets) that should improve the performance of some queries." Und viel mehr habe ich dazu auch an anderer Stelle dazu nicht gefunden. Der Hinweis auf die mögliche Rolle der Rowset verdankte sich dabei übrigens Stefan Koehler, der vor einigen Wochen in den Kreis der Oak Table aufgenommen wurde - was sicherlich eine sehr plausible Ergänzung dieser Tafelrunde darstellt.

Basierend auf dem Scratchpad-Artikel (und weiteren bekannten Bugs) hat inzwischen Mike Dietrich die offizielle Empfehlung ausgesprochen, vorläufig auf die Verwendung von rowsets zu verzichten.

Nachtrag 17.11.2015: Mike Dietrich hat inzwischen in einem weiteren Artikel zusätzliche Details zum Thema geliefert. Einerseits nennt er die Ursache des Problems: "When a hash join operation receives rowsets from its right input but then produces one row at a time as output. This explains why one of the bugs had as potential workaround hash_join_enabled=false (and please don't use this as a w/a!!!)." Zusätzlich liefert er neben der globalen Deaktivierung des Features noch zwei weitere Workarounds: das Einspielen des zugehörigen Bug-Fixes (der aber zum Zeitpunkt der Veröffentlichung des Artikels noch nicht verfügbar war) und die Verwendung eines speziellen Events im spfile, das die rowset Verwendung nur im angesprochenen Problemfall deaktiviert.

Clustering Factor in RAC-Systemen

Da ich seit einigen Monaten wieder verstärkt mit RAC-Datenbanken (und überhaupt mit Oracle) zu tun habe, ist der folgende Hinweis von Jonathan Lewis für mich recht interessant: in einem RAC-System besitzt jede Instanz im Rahmen des ASSM-Speichermanagements ihren eigenen level 1 (L1) bitmap Block und schreibt neue Datensätze insofern auch in ihr zugeordnete Datenblöcke. Wenn konkurrierende Inserts über mehrere Instanzen hinweg erfolgen, landen die neusten Einträge daher in vielen unterschiedlichen Blocks, denn neben der Anzahl der Instanzen spielt dabei auch die Strategie von ASSM eine Rolle, Inserts auch in einer einzelnen Instanz auf 16 unterschiedliche Blocks zu verteilen. Der Clustering Factor eines Index würde unter diesen Umständen in nahezu jedem Fall extrem schlecht aussehen, wenn es nicht die Möglichkeit gäbe, mit Hilfe des Parameters table_cached_blocks dafür zu sorgen, dass Oracle ein gewisses Erinnerungsvermögen zeigt, wenn bestimmt wird, wie stark die Daten in der Tabelle im Hinblick auf einen Index geordnet sind. Ursprünglich wurde hier nur gezählt, wie oft sich die data block Adresse ändert, wenn man den sortiereten rowid-Verweisen der Index-Struktur folgt - was in Abwesenheit von RAC und ASSM immer noch eine plausible Strategie ist. Ausgehend von diesen Überlegungen schlägt der Herr Lewis als Ausgangswert für table_cached_blocks in einem RAC-System 16 * Anzahl Instanzen vor - und das ist angesichts dieser Überlegungen nachvollziehbar.