Oracle 12.2 VM

Dass Oracle 12.2 (endlich) zum download verfügbar ist, konnte man dieser Tage bereits in jedem Oracle-Blog lesen. Nett finde ich aber insbesondere, dass es dazu gleich auch eine Virtual Box Appliance gibt - worauf Jeff Smith hinweist. Da ich die Installation auf Dauer nicht so sagenhaft spannend finde, bin ich dafür durchaus dankbar.

Online Statistics Gathering in 12c

Maria Colgan hat in den letzten Wochen zwei Artikel zum Thema der Erfassung von Optimizer Statistiken bei der Objektanlage veröffentlicht:

• Online Statistics Gathering: seit Oracle 9 werden die Statistiken für Indizes im Rahmen der Anlage eines Index automatisch erfasst: da in diesem Zusammenhang ohnehin ein Full Scan der Daten und Sortierungen erforderlich sind, kann man die zusätzliche Erfassung der Statistiken problemlos in die Operation integrieren. Mit Oracle 12c wird diese Technik jetzt auch für Tabellen verwendet, wenn sie über direct path Operationen wie CTAS und INSERT append (dort allerdings nur, wenn vorher noch keine Daten in der Tabelle existierten) befüllt werden. Über den Parameter _optimizer_gather_stats_on_load kann man den Mechanismus deaktivieren.
• Histogram sample size and Online Statistics Gathering: darin weist die Autorin darauf hin, dass im Fall der automatischen Statistikerstellung im Rahmen der Ladeoperationen in den Spaltenstatistiken unter Notes ein Eintrag STATS_ON_LOAD erscheint. Führt man anschließend eine Statistikerfassung mit der Option GATHER AUTO durch, dann werden nur Histogramme erzeugt (NOTES = HISTOGRAM_ONLY) und diese mit dem üblichen traurig kleinen Sample von ca. 5500 not null Werten.

Sollte Frau Colgan hier noch weitere Artikel liefern, werde ich sie an dieser Stelle ergänzen.

Intra-block und inter-block chaining

Sayan Malakshinov erläutert in seinem Blog, wie intra-block chaining bei reinen insert Operationen zustande kommt und liefert dazu zunächst die Definitions-Grundlagen:

• laut Doku gilt, dass Datensätze einer Tabelle mit mehr als 255 Spalten, in denen Spalten jenseits Nr. 255 Werte ungleich NULL enthalten "are likely to be chained within the same block. This is called intra-block chaining."
•  intra-block chaining sollte keine Auswirkung auf die I/O performance haben (ist aber in den Session-Statistiken sichtbar).
• Oracle verwendet eine umgekehrte Reihenfolge (reverse order) beim Aufbau der row pieces: im Beispiel mit 300 Spalten wird daher ein piece mit den Spalten 46-300 erzeugt und eines mit den Spalten 1-45..
• NULL-Werte am Ende eines Datensatzes werden nicht physikalisch abgespeichert - das gilt aber nicht für row pieces.
• das intra-block chaining ergibt sich nur bei inserts: sind updates im Spiel, so wird das row piece in einen anderen Block verlagert und es ergibt sich inter-block chaining.

Neben den Beispielen im Artikel ist vor allem der Hinweis interessant, dass der Umgang mit Trailing Nulls zu recht bizarren Effekten führen kann: im Beispiel gelingt es dem Herrn Malakshinov mit einem Insert eines Datensatzes mit einem Wertes in Spalte 1 einer Tabelle mit 355 Spalten und drei folgenden updates auf die Spalten 300, 301 und 302 eine Aufteilung dieses Datensatzes in vier row pieces (mit inter-block chaining) hervorzurufen:

• das insert legt ein row piece an, bei dem die trailing nulls keine Bedeutung haben.
• das erste Update führt zur Teilung des pieces in zwei Teile: 1-45 und 46-300.
• das zweite Update teilt das größere Stück wiederum in zwei Teile 46 und 47-301.
• und das dritte Update wiederholt diese Operation mit dem Ergebnis 47 und 48-302.

Insgesamt ergeben sich somit zwei pieces mit nur einem Attribut. In einem solchen Szenario könnte chaining sehr schnell zu einem massiven Problem werden. Aber vielleicht sollte man einfach grundsätzlich von Tabellen mit mehr als 255 Spalten Abstand nehmen: sie bereiten wenig Freude.

Nachtrag 21.04.2017: in einem weiteren Artikel zeigt der Autor, dass mit 12.2 auch updates zu intra-block-chaining führen, was mir eine günstige Entwicklung zu sein scheint.

Nachtrag 22.06.2017: Jonathan Lewis spricht das Thema in seinem Scratchpad an und verweist auf die erwähnten Artikel.

Interval-Reference Partitionierung in 12c

Früher einmal habe ich meine Blog-Beiträge selbst erdacht und geschrieben - inzwischen gebe ich sie gerne in Auftrag oder lasse sie in Auftrag geben. So auch hier: in der letzten Woche war Markus Flechtner von Trivadis bei uns im Haus und hat uns in die dunklen Geheimnisse von Oracle 12 eingeweiht. Viele Fragen blieben nicht offen, aber einer meiner Kollegen hatte ein paar Detailfragen zum Thema Interval-Reference-Partitionierung und row movement. Ich hätte es wahrscheinlich bei der Antwort "das ist ein weites Feld" bewenden lassen und vielleicht noch ein paar haltlose Versprechungen gemacht, gelegentlich mal einen Blick darauf zu werfen. Nicht so der Herr Flechtner, der daraus gleich einen Artikel Interval-Reference-Partitionierung: Partition-Merge und Row-Movement gemacht hat. Darin finden sich unter anderem folgende Beobachtungen:

• zur Erinnerung: beim reference partitioning erbt eine child Tabelle die Partitionierungs-Charakteristiken der parent Tabelle. Und beim interval partitioning werden erforderliche Partitionen nach Bedarf auf Basis einer vorgegebenen Ranges-Größe (oder Intervall-Angabe) erzeugt.
• die für die child-Tabelle angelegten Partitionen korrespondieren exakt mit denen der parent-Tabelle: auch die generierten Namen der Partitionen sind identisch.
• ein Merge für Partitionen der parent-Tabelle wird automatisch an die child-Tabelle propagiert. Allerdings sind die Namen auf parent- und child-Ebene dann nicht mehr identisch.
• ein Merge auf child-Ebene ist nicht möglich.
• wie üblich führt die Merge-Operation zu einer Invalidierung der Indizes, sofern nicht die Klausel UPDATE INDEXES verwendet wird.
• um Verschiebungen von Datensätzen über Partitionsgrenzen zu erlauben, muss row movement aktiviert sein: dabei muss es erst auf child-, dann auf parent-Ebene aktiviert werden (bei umgekehrter Reihenfolge ergibt sich ein Fehler "ORA-14662: row movement cannot be enabled".

Viele weitere Fragen würden mir in diesem Zusammenhang auch nicht mehr einfallen (außer vielleicht, ob sich split partition entsprechend verhält, wovon ich erst einmal ausgehe). In jedem Fall ein herzlicher Dank an den Autor für die rasche Beantwortung dieser Fragen.

Nicht deterministische JDBC-Anmeldeprobleme mit Oracle 11.2.0.3

Im Lauf der Woche bin ich einem Problem beim Zugriff auf eine Oracle-Datenbank via JDBC begegnet, das eine umfangreichere Schilderung verdient hätte - wenn Uwe Küchler diese Schilderung nicht schon vor einigen Jahren in seinem Blog unter Berücksichtigung aller relevanten Details durchgeführt hätte. Das Problem lag darin, dass Anmeldungen in manchen Fällen problemlos erfolgten, dann aber wieder in Timeouts liefen, ohne dass sich dafür auf Netzwerkebene eine Erklärung finden ließ. Verantwortlich ist die Verwendung von Zufallszahlen beim in 11g eingesetzten Authentifizierungsverfahren - aber ich spare mir jede weitere Erklärung, da sich alles, was man dazu wissen muss, im verlinkten Artikel findet. Dafür noch mal mein Dank an den Autor.

Zur Semantik des USE_NL Hints

Ich erinnere mich, dass Jonathan Lewis diesen Punkt schon häufiger erwähnt hat, aber offenbar hatte er tatsächlich noch keinen Artikel dazu geschrieben, und dies jetzt nachgeholt: häufig sieht man in Oracle SQL-Queries Hints der folgenden Form:

use_nl(t1 t2)

Und es gibt wohl in der Tat viele Verwender, die davon ausgehen, dass man den Optimizer damit anweist, einen NESTED LOOPS Join zu verwenden, bei dem t1 unmittelbar mit t2 verknüpft wird, wobei t1 die driving table ist, also zuerst abgefragt wird. Das ist allerdings nicht der Fall. Tatsächlich ist der Hint nur eine Kuzform für:

use_nl(t1) use_nl(t2)

Der Hint sagt nichts darüber, in welcher Reihenfolge der Zugriff erfolgt - und wenn weitere Tabellen im Join beteiligt sind, kann es durchaus dazu kommen, dass t1 und t2 nur mittelbar miteinander verknüft werden. Um die Ausführungsreihenfolge zu bestimmen, benötigt man zumindest einen weiteren leading-Hint. Das Beispiel macht einmal mehr deutlich, dass die Verwendung von Hints deutlich komplzierter ist, als sie manchmal auszusehen scheint.

Redundante Prädikate zur SQL Optimierung im SQL Server

Da ich nicht so oft lobende Erwähnungen auf anderen Webseiten erhalte, will ich nicht darauf verzichten, diese hier zu verlinken: mein alter Freund und ehemaliger Kollege Andrej Kuklin hat im SDX Blog einen Artikel veröffentlicht, der sich damit beschäftigt, wie man Queries im SQL Server durch die Ergänzung eigentlich redundanter Prädikate optimieren kann. In seinem Beispiel läuft eine Query mit einem Inner Join schnell, so lange über eine gegebene Variable auf ein bestimmtes Datum eingeschränkt wird, wobei die entsprechende Spalte auch in der Join-Bedingung erscheint. Andrejs (und meine) Annahme ist, dass hier - so wie bei Oracle - ein Fall von transitive closure vorliegt: die auf der einen Seite angegebe Einschränkung wird dupliziert und auch auf die zweite Menge/Tabelle angewendet:

explain plan for
SELECT
    fp.*
   ,ft.TradeID
   ,ft.IsCompleted
   ,ft.Amount
FROM
    FactPosition fp
INNER JOIN FactTrade ft ON
      ft.DateID=fp.DateId
      AND ft.PositionID=fp.PositionId
WHERE
    fp.DateId=20151028;

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |  2309 |   101K|    26   (4)| 00:00:01 |
|*  1 |  HASH JOIN                   |                 |  2309 |   101K|    26   (4)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| FACTPOSITION    |  1000 | 24000 |     9   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | FACTPOSITION_PK |  1000 |       |     5   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| FACTTRADE       |  2000 | 42000 |    16   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | FACTTRADE_PK    |  2000 |       |     8   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   1 - access("FT"."DATEID"="FP"."DATEID" AND "FT"."POSITIONID"="FP"."POSITIONID")
   3 - access("FP"."DATEID"=20151028)
   5 - access("FT"."DATEID"=20151028)

Im Plan sieht man, dass die DateId-Einschränkung für beide Tabellen hergezogen werden kann (Step 3 und Step 5). Wird jedoch statt der Angabe der Variable (oder wie bei mir: eines Literals) eine Subquery verwendet ("WHERE fp.DateId=(SELECT MAX(DateId) FROM DimDate)"), dann kann der Optimizer diese Einschränkung im SQL Server und in Oracle nicht auf die zweite Tabelle anwenden und muss auf dieser einen full table scan durchführen, obwohl der Index-Zugriff deutlich schneller wäre. Um dem Optimizer die zusätzliche Information zu liefern, muss in diesem Fall die Subquery dupliziert werden (also: "WHERE fp.DateId=(SELECT MAX(DateId) FROM DimDate) AND ft.DateId=(SELECT MAX(DateId) FROM DimDate);"). Inhaltlich würde ich annehmen, dass der Optimizer eine solche Umformung auch selbständig ergänzen könnte, aber in den aktuellen Versionen von SQL Server und Oracle tut er das offenbar noch nicht. Interessant ist jedenfalls mal wieder zu sehen, wie ähnlich sich relationale Datenbanken in vielen Fällen verhalten.

Anders als meine Notiz hier zeichnet sich Andrejs Artikel übrigens durch die detaillierte Präsentation des Beispiels und der sich ergebenden SQL Server Pläne aus.

Maria Colgan antwortet bei AskTom

Ein vielversprechender Start ins Jahr 2017: Maria Colgan gehört jetzt neben Chris Saxon und Connor McDonald zum Team bei AskTom. Außerdem hat sie damit angefangen, in ihrem eigenen Blog zu schreiben. Da es vermutlich kaum jemanden gibt, der mehr über den Optimizer (oder ind In-Memory-Optionen) weiß als Frau Colgan, ist das eine günstige Entwicklung.