HASH JOIN und NOT EXISTS

Zwei entsprechende Threads im OTN-Forum haben Jonathan Lewis dazu veranlasst innerhalb von zwei Wochen zwei Artikel mit dem identischen Titel Not Exists zu veröffentlichen. Da der Herr Lewis in der Regel über ein sehr zuverlässiges Gedächtnis verfügt, nehme ich an, dass er das mit Absicht so gemacht hat - und dass es keinen unique constraint auf seinen Artikelüberschriften gibt... Hier ein paar Notizen zu den Artikeln:

• https://jonathanlewis.wordpress.com/2015/04/13/not-exists/: beschäftigt sich mit der Auswirkung eines no_unnest Hints in der korrelierten NOT EXISTS Subquery. Durch den Hint wird aus dem HASH JOIN eine Filter-Operation, bei der für jeden Ergebnis-Satz aus der ersten Tabelle eine Lookup-Query ausgeführt wird. Die Effektivität dieses Vorgehens hängt davon ab, ob das Scalar Subquery Caching die Anzahl der Lookups reduzieren kann. Mit passenden Indizes (und geeigneten NOT NULL Constraints) lässt sich die Effektivität des Cachings erhöhen.
• https://jonathanlewis.wordpress.com/2015/04/26/not-exists-2/: untersucht einen weiteren Fall, in dem der no_unnest Hint anscheinend eine positive Wirkung auf die Laufzeit eine NOT EXISTS Query hatte (jedenfalls laut Aussage des Fragestellers im Thread), bei der ein Self-Join Datensätze suchte, bei denen ein Spaltenwert Übereinstimmungen und ein anderer Spaltenwert Abweichungen lieferte (also: where w1.x = w2.x and w1.y <> w2.y). Dazu liefert der Herr Lewis ein recht detailliertes Modell, bei dem er die Anzahl der übereinstimmenden bzw. abweichenden Werte unterschiedlich definiert, um die Effektivität von HASH JOIN bzw. Filter-Query in Abhängigkeit von diesen Voraussetzungen zu bestimmen. Das Ergebnis lautet: 
• wenn es in der Build Table "lange Hash-Chains" gibt (also viele identische Werte bzw. Hash-Ergebnisse für die Join-Spalte aus der ersten Tabelle), dann erhöht das (erwartungsgemäß) den Aufwand für Probe-Operation für ein Element der zweiten Tabelle.
• wenn sich die "langen Hash-Chains" aus einer ungleichmäßigen Verteilung ergeben (Stichwort: skew), dann kann das dazu führen, dass der Optimizer nicht erkennt, dass der HASH JOIN nicht die beste Lösung für das vorliegende Problem ist.

In beiden Fällen dienen die zugehörigen OTN-Threads als Anlass für die Untersuchungen, aber wie üblich fehlen in den Threads ab einem bestimmten Punkt die Informationen die erforderlich wären, um die exakten Zusammenhänge der ursprünglichen Probleme zu bestimmen. 

Function Based Indexes und CURSOR_SHARING

Randolf Geist erläutert in seinem Blog einige interessante Details zum Zusammenspiel der Cursor-Sharing-Optionen Force und Similar (deprecated) - bei denen die verwendeten Literale durch Bindewerte ersetzt werden - mit funktionsbasierten Indizes (= Function Based Indexes; aka FBI). Grundsätzlich gilt dabei, dass FBIs durch die genannten Cursor-Sharing-Varianten unwirksam werden. Aber zumindest für den Bereich der Releases 10.2.0.4 bis 11.2.0.3 gibt es bestimmte Sonderfälle mit ergänzender Built-in-Logik, die die FBI-Verwendung der grundsätzlichen Einschränkung zum Trotz erlauben. Dabei ergibt sich eine Abweichung zwischen dem durch dbms_xplan.display_cursor angezeigten Query-Text, der die Bindewerte anzeigt, und der Prädikat-Sektion, in der teilweise noch die Literalwerte auftreten: in diesen Fällen ist die FBI-Nutzung möglich. Allerdings ergibt sich dieses Verhalten nur, wenn ein passender FBI existiert: fehlt er, so zeigt sich keine Abweichung zwischen Query-Text und Prädikat-Sektion. Darüber hinaus beschränkt sich die Optimierung auf bestimmte Expressions - in den meisten Fällen ergibt sich das Standardverhalten, das Jonathan Lewis in einem älteren Artikel erläutert, auf den mehrfach verwiesen wird, und der FBI wird nicht verwendet. Weiterhin ist die Optimierung (wenn es denn überhaupt eine gewesen ist) ab 11.2.0.4 nicht mehr wirksam.

Postgres FDW-Zugriff auf Oracle

Ein kleines Syntaxbeispiel zur Definition eines Foreign Data Wrappers (FDW) in postgres. Ein paar grundsätzlichere Erläuterungen zum Thema hatte ich hier vor längerer Zeit notiert, dabei aber nur FDW-Definitionen mit Zugriff von einer postgres-Datenbank auf eine andere postgres-Datenbank untergebracht. Hier folgt eine kurze Zusammenfassung der Schritte, die nötig waren, um einen postgres-FDW-Zugriff auf eine Oracle-Datenbank einzurichten:

1. Download der zugehörigen Komponenten, die hier zu finden sind. Der Download enthält die Kontrolldatei (oracle_fdw.control) und zwei SQL-Dateien für den Ordner share\extension, sowie die erforderliche dll (oracle_fdw.dll) für den lib-Ordner.

2. Anlage der Extension in der vorgesehenen postgres-Datenbank

create extension oracle_fdw;

3. Anlage der FDW-Definitionen

drop server if exists test_oracle cascade;
create server test_oracle 
foreign data wrapper oracle_fdw 
options (
    dbserver '//xxx.xxx.xxx.xxx:1521/yyy.yyy.yyy' (also eine Host-Naming-Verbindung mit ip und DB-Namen)
);

create user mapping for postgres
server test_oracle
options (
    user 'test'
  , password 'test'
);

create foreign table t_oracle (
    id integer
  , col1 text
)
server test_oracle options (schema 'TEST', table 'T');

select * from t_oracle limit 10;
+----+----------------------------------------------------+
| id |                        col1                        |
+----+----------------------------------------------------+
|  1 | ************************************************** |
|  2 | ************************************************** |
|  3 | ************************************************** |
|  4 | ************************************************** |
|  5 | ************************************************** |
|  6 | ************************************************** |
|  7 | ************************************************** |
|  8 | ************************************************** |
|  9 | ************************************************** |
| 10 | ************************************************** |
+----+----------------------------------------------------+
(10 Zeilen)

Ein vollständigeres Beispiel hätte wohl auch noch die Tabellenanlage auf Oracle-Seite enthalten, aber ich verbürge mich dafür, dass das Query-Resultat der Tabellendefinition im Quellsystem ähnelt. Im Prinzip ist eine solche Einbindung von Oracle-Daten ist postgres also sehr einfach.

Table Functions (Steven Feuerstein)

Wenn ich an PL/SQL denke, ist meine erste Assoziation dazu der Name Steven Feuerstein. Nun denke ich nicht furchtbar oft on PL/SQL, aber wenn der Herr Feuerstein über ein Thema schreibt, dem ich mit einer gewissen Regelmäßigkeit begegne, dann ist das allemal eine Verlinkung und Zusammenfassung der zugehörigen Artikel wert:

• Table Functions: Introduction and Exploration, Part 1: skizziert die vorgesehene Struktur der Artikelserie und erläutert zunächst das Grundkonzept von table functions: der table operator wandelt eine collection in ein relationales Dataset um, so dass man darauf via Select zugreifen kann. Diese Datasets können als Grundlage in (parametrisierten) Views verwendet werden und mit anderen Tabellen/Datasets gejoint werden, man kann die Ergebnisse gruppieren, sortieren etc. In älteren Releases konnten nur nested tables und varrays vom table operator transformiert werden, aber seit 12.1 bieten auch integer-indexed associative arrays eine verwendbare Grundlage. Der Herr Feuerstein erwähnt die einschlägigen Artikel zum Thema von Adrian Billington und Tim Hall, auf die ich hier auch schon häufiger verwiesen habe. Es folgt eine Liste möglicher use cases (inklusive diverser Beispiele dazu):
• Session-spezifische Daten mit Tabellendaten mergen, um die Vorteile Set-orientierter SQL-Operationen zu erhalten.
• Programmatische Erzeugung spezieller Datasets für die Ausgabe.
• Parametrisierte Views.
• Parallelisierte Selects auf der Basis von pipelined table functions.
• Verringerte PGA-Nutzung durch pipelined table functions, da die zugehörigen Collections nicht komplett im Speicher gehalten werden müssen, sondern sukzessive aufgebaut und weiterverarbeitet werden können.
• Table Functions: Returning complex (non-scalar) datasets, part 2: geht über die im ersten Artikel vorgestellten Beispiel mit (skalaren) Einzelwerten hinaus und zeigt den Umgang mit rows, die mehrere unterschiedliche Attribute enthalten. Zunächst zeigt er dabei, wie man es besser nicht machen sollte (z.B. eine Table mit VARCHAR2(4000), deren Ergebnisse über String-Operationen zerlegt werden). Leider steht die plausiblere Lösung, einen PL/SQL record type als Datentyp einer nested table zu verwenden, nicht zur Verfügung, weil SQL an dieser Stelle etwas kleinlich ist. Stattdessen muss man einen object type verwenden, was syntaktisch kaum einen Unterschied macht.
• Table Functions, Part 3a: table functions as parameterized views in the PL/SQL Challenge website: liefert - wie der Name schon andeutet - ein Beispiel für die Verwendung einer table function als parameterized view.
• Table Functions, Part 3b: implementing table functions for PL/SQL Challenge reports: mit einem umfangreichen table function Beispiel.
• Table Functions, Part 4: Streaming table functions: erklärt, wie man table functions in ETL-Operationen zur Abbildung komplexerer Transformationen einsetzen kann. Das Beispiel ist recht umfangreich, endet aber, ehe die pipelined table functions erreicht sind.

Die folgenden Artikel versuche ich hier zu ergänzen, sobald sie folgen.

Truncate Table Cascade

Eine nützliche Ergänzung in 12c, die mir bisher entgangen war, ist das TRUNCATE CASCADE, mit dessen Hilfe man eine Parent-Tabelle entleeren kann und zugleich alle Datensätze in via FK verbundenen Tabellen löscht. Das funktionierte bislang nur mit DELETE - und auf DELETE verzichte ich gerne, wo immer das möglich ist. Dazu ein kleines Beispiel:

drop table child;
drop table parent;

create table parent(
    parent_id number primary key
);

insert into parent(parent_id)
select rownum 
  from dual
connect by level <= 10000;  

create table child(
    id number primary key
  , parent_id number
  , constraint fk_parent_id foreign key(parent_id) references parent(parent_id) on delete cascade
);

insert into child(id, parent_id)
select rownum 
     , rownum
  from dual
connect by level <= 10000;  

insert into child(id, parent_id)
values(-1, null);

create index child_fk_idx on child(parent_id);

Ausgehend von diesem Versuchsaufbau lassen sich die Unterschiede in Performance und Verhalten leicht überprüfen:

delete from parent cascade;

--> 10000 Zeilen gelöscht.
--> Abgelaufen: 00:00:01.54

select * from child where id < 0;

        ID  PARENT_ID
---------- ----------
        -1

rollback;

truncate table parent cascade;

--> Tabelle mit TRUNCATE geleert
--> Abgelaufen: 00:00:00.07

select * from child where id < 0;

Es wurden keine Zeilen ausgewählt

Ursprünglich wollte ich hier noch diverse v$sesstat-Angaben unterbringen, aber das unterschiedliche Verhalten hinsichtlich des verwaisten Satzes mit id -1 in der child-Tabelle macht ziemlich deutlich, was hier passiert:

• das DELETE CASCADE löscht alle Sätze aus parent und alle via FK verknüpften Sätze aus child. Das dauert für 10000 Datensätze auf meiner Workstation bereits 1,5 sec, lässt aber den Orphan-Datensatz aus child (id = -1) stehen.
• das TRUNCATE CASCADE leert parent und child komplett, was deutlich schneller durchgeführt werden kann als das DELETE, aber den Orphan-Satz nicht stehen lässt. Es handelt sich also um ein TRUNCATE auf beiden Ebenen - nicht um ein TRUNCATE für parent und ein DELETE aller Sätze ohne FK-Beziehung in der abhängigen Tabelle.

Wenn man die Dokumentation liest, ehe man das Verhalten testet, ist das Ergebnis nicht besonders erstaunlich:

If you specify CASCADE, then Oracle Database truncates all child tables that reference table with an enabled ON DELETE CASCADE referential constraint. This is a recursive operation that will truncate all child tables, granchild tables, and so on, using the specified options.

Ich kann mir durchaus Fälle vorstellen, in den dieses Verhalten genau das ist, was man haben möchte - aber auch solche, in denen es nicht den Erwartungen der Anwender entspricht. Auf jeden Fall sollte man sich einprägen, dass DELETE CASCADE und TRUNCATE CASCADE unterschiedliche Ergebnisse hervorrufen.

Concurrent UNION ALL (Randolf Geist)

Wie bereits gelegentlich erwähnt hat sich im Release 12c im Bereich der Parallel Execution allerlei getan - und Randolf Geist hat sich umfassend zu den meisten neuen Features geäußert. Eine wichtige Neuerung ist das Concurrent UNION ALL, also die Möglichkeit, mehrere über UNION ALL verbundene Teil-Operationen parallel zu verarbeiten. Wie üblich sind die Ausführungen des Herrn Geist ausgesprochen detailliert - und wie üblich werde ich ihnen in meiner Zusammenfassung nicht gerecht. Daher seien die folgenden Punkte wieder einmal nur als Link-Sammlung zu verstehen, auf die gelegentlich zurückzukommen wäre:

• 12c Parallel Execution New Features: Concurrent UNION ALL - Part 1: vor 12c führte Oracle niemals mehrere getrennte Branches (also etwa über UNION ALL verknüpfte Blöcke) eines Execution Plans gleichzeitig aus: die Parallelisierung betraf immer nur Einzeloperationen. Mit 12c hat sich das geändert. Der Artikel nennt die zugehörigen kontrollierenden Hints (PQ_CONCURRENT_UNION, NO_PQ_CONCURRENT_UNION) und erläutert einen Fall, in dem das Feature sehr nützlich sein könnte, nämlich die parallele Verarbeitungen von Operationen, bei denen via DB-Link auf Remote-Quellen zugegriffen wird (was vor 12c eine Do-it-yourself-Parallelisierung erforderte). Der Artikel untersucht weiterhin den Parallelisierungsgrad der zugehörigen Operationen und ergänzt umfangreiche Tests. Im Plan sind parallele UNION ALL Elemente über einen PX SELECTOR Eintrag dargestellt.
• 12c Parallel Execution New Features: Concurrent UNION ALL - Part 2: zeigt, dass das Feature automatisch aktiv wird, wenn man parallele und serielle Branches innerhalb eines UNION ALL mischt (dann erscheint für die seriellen Branches der PX SELECTOR und für die parallelen z.B. ein PX BLOCK ITERATOR). Es folgt eine umfassende Analyse des Laufzeitverhaltens (die ich hier nicht wiedergebe).
• 12c Parallel Execution New Features: Concurrent UNION ALL - Part 3: erläutert die in Teil 1 skizzierte Optimierung der parallelen Verarbeitung verteilter Operationen genauer. Auch dazu gibt es wieder einen umfassenden Versuchsaufbau und auch dazu schreibe ich an dieser Stelle nicht mehr.

Wie gesagt: der Eintrag dient eher als Link-Sammlung, denn der Kommentierung. Um sinnvollere Kommentare zu ergänzen, müsste ich die Details besser verstanden haben - aber aktuell ist das Thema für mich ausschließlich von akademischem Interesse.

Exadata und In-Memory

Obwohl ich bisher weder mit Exadata noch mit der IN-Memory-Option ernsthaft gearbeitet habe, halte ich beide Möglichkeiten für hochinteressant - und das gilt folglich auch für die von Maria Colgan gesammelten 10 Reasons to run Database In-Memory on Exadata. Die könnte ich jetzt nacherzählen, spare mir das aber, weil mir dazu insbesondere auf Exadata-Seite das Sachwissen zur Ergänzung erhellender Kommentare fehlt. Daher bleibt hier nur der Faden liegen, dem ich möglicherweise eines Tages nachgehen könnte.

Postgres-Performance seit 7.4

Da ich mir ziemlich sicher bin, dass ich diese Beiträge irgendwann wieder suchen werden, hinterlege ich an dieser Stelle die Links auf Thomas Vondras interessante Artikel zur Entwicklung der Performance von postgres seit Version 7.4:

• Performance since PostgreSQL 7.4 / pgbench: mit dem schönen Fazit "The newer the PostgreSQL release, the better the performance. Yay!" Darüber hinaus gibt es noch folgende Einsichten:
• "some releases make a huge improvement"
• "Older releases perform much better with more conservative settings"
• "Newer hardware matters"
• Performance since PostgreSQL 7.4 / TPC-DS: behandelt das Verhalten mit analytischen (soll heißen: großen) Datenvolumina und liefert folgende Einsichten:
• "Loading is much faster than on 8.0 - about 2x as fast. Most of the speedup happened in 8.1 / 8.2, and we're keeping about the same performance since then."
• "The query speedup is even better - PostgreSQL 9.4 is about 7x faster than 8.0. Again, most of the speedup happened in 8.1/8.2, but there are signifincant improvements in the following releases too."
• Performance since PostgreSQL 7.4 / fulltext: weist insbesondere auf die Beschleunigung von GIN zwischen 9.3 und 9.4 hin. Die Details spare ich aus (bis zu dem Tag, an dem ich mich endlich mal mit GIN und GIST beschäftige).

Alle Artikel beinhalten dabei sehr umfangreiche und gut dokumentierte Tests mit ansprechender Visualisierung der Ergebnisse.

Query Optimierung mit ORDER BY?

Franck Pachot liefert in seinem Blog ein interessantes Beispiel, in dem eine simple Query ohne jede Einschränkung, die eine kleine Tabelle via Full Table Scan einliest, eine deutliche Reduzierung der Consistent Gets und auch der Laufzeit erfährt, wenn man ein ORDER BY ergänzt:

• select lpad(x,2000,x) from DEMO;
  
• 683 consistent gets
• 20163693 bytes sent via SQL*Net to client
• 668 SQL*Net roundtrips to/from client
• select lpad(x,2000,x) from DEMO order by x;
• 35 consistent gets
• 118324 bytes sent via SQL*Net to client
• 668 SQL*Net roundtrips to/from client

Das klingt erst mal überraschend, hat aber zwei einleuchtende Ursachen:

• die sqlnet compression, die Jonathan Lewis gelegentlich erläutert, hat, was ich an anderer Stelle nacherzählt habe, und die sich dergestalt auswirkt, dass wiederholte Elemente dedupliziert werden können - und das funktioniert mit den sortierten Werten im Beispiel deutlich besser, weil die Spalte x in der Demo-Tabelle nur die Werte 0 und 1 in abwechselnder Folge enthält.
• die Wirkung der ARRAYSIZE, die auf den sqlplus-Standard 15 gesetzt ist (was an 10000/668 abzusehen ist). Jeder Roundtrip führt dabei zu einem neuerlichen Blockzugriff (also 35 + 668 * 1 = 683), wobei der Block natürlich permanent im Speicher gehalten wird. Im Fall der Query mit Sortierung werden die Daten initial in die PGA gelesen und dort sortiert. Der Zugriff auf das Ergebnis erfordert dann keine lesekonsistenten Zugriffe mehr.

Obwohl mir der Zusammenhang grundsätzlich klar war, muss ich gestehen, dass ich zunächst eine Weile nachdenken (und ausprobieren musste), ehe ich die Details an die richtige Stelle einordnen konnte.