Performance by Example: SpeedUp Datentransfer via DB-Links

Greift man von einer Oracle Datenbank mittels Datenbank Link auf eine andere Oracle Datenbank zu, so gelten die gleichen Optimierungsmöglichkeiten wie bei anderen Client/Server Anwendungen. In diesem Artikel nutze ich als Client Tool, das den DB-Link nutzt SQLplus. Das gleiche gilt aber beispielsweise auch für PLSQL Packages oder beliebige andere Datenbank Clients, die DB-Links nutzen. Lediglich die Konfiguration der Recordanzahl beim Fetch der Daten ist jeweils etwas anders zu implementieren.

SDU Size

Die SDU (Session Data Unit) Size gibt an, in welchen MAXIMALEN Paketeinheiten Oracle die Daten bei der Übertragung vorbereiten soll. Natürlich wird diese Paketeinheit auf der Netzwerkebene in mehrere/viele Netzwerkpakete aufgeteilt – diese werden aber sofort hintereinander versendet, ohne auf einzelne Bestätigungsmeldungen zu warten. In der Oracle Dokumentation findet man die verschiedenen Möglichkeiten, wo und wie man die SDU konfigurieren kann. Die flexibelste Möglichkeit ist am Connect-String selbst.

Mit Oracle 19c kann die SDU maximal 2MB groß konfiguriert werden, hier ein Beispiel Connect-String:

SourceDB=
(DESCRIPTION= 
    (SDU=2097152) 
    (ADDRESS=(PROTOCOL=tcp)(HOST=source-server)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=SourceDB))
) 

TCP I/O Buffer Space

Damit diese Einstellung beim Client und beim Server keine Netzwerkpufferüberlauf erzeugen, muss man sicherstellen, dass diese groß genug konfiguriert sind. Bei Linux findet man die Einstellungen typischerweise im /etc/sysctl.conf.

$ grep net.core /etc/sysctl.conf

# oracle-database-preinstall-19c setting for net.core.rmem_default is 262144
net.core.rmem_default = 262144
# oracle-database-preinstall-19c setting for net.core.rmem_max is 4194304
net.core.rmem_max = 4194304
# oracle-database-preinstall-19c setting for net.core.wmem_default is 262144
net.core.wmem_default = 262144
# oracle-database-preinstall-19c setting for net.core.wmem_max is 1048576
net.core.wmem_max = 1048576

Wie man hier sieht, ist die Einstellung für net.core.wmem_max mit 1MB zu gering. Die empfohlenen MINDESTEINSTELLUNGEN sind wie folgt:

net.core.rmem_max = 4194304
net.core.wmem_max = 4194304

Laufen mehrere parallele Sessions (zb wenn man Parallel Query über den DB-Link nutzt), sollte man die Einstellung entsprechend größer wählen. Ein Wert von 16MB ist dann durchaus sinnvoll.

Alternativ kann man dies auch in den Connect-Strings einbauen:

SourceDB=
(DESCRIPTION= 
    (SDU=2097152) 
    (ADDRESS=(PROTOCOL=tcp)(HOST=source-server)(PORT=1521)
        (SEND_BUF_SIZE=2097152)(RECV_BUF_SIZE=2097152))
    (CONNECT_DATA=(SERVICE_NAME=SourceDB))
) 

Weitere Informationen zum TCP I/O Buffer findet man ebenfalls in der Oracle Dokumentation.

Fetch Size Optimierung am Beispiel von SQL*Plus Sessions

Ab hier sind die Einstellungen vom jeweiligen Client abhängig. Es geht dabei aber immer um das gleiche: Konfiguration der optimalen Fetch-Size für die Datenübertragung. Diese sollte so groß sein, dass die SDU entsprechend gut genutzt wird, aber nicht zu groß, dass Oracle mehrere zusammenhängende Pakete benötigt. In meinem Blog Performance by Example: Reporting und Analyse Tools und die FetchSize habe ich ein Beispiel für PowerBI (nutzt ODP.NET) für die Einstellung der Fetch Size gezeigt. Auch bei Oracle findet man vergleichbare Artikel – beispielsweise Tune fetch size to optimize large query performance in Oracle Database Service for Azure, wo Beispiele auch für JDBC und OCI (Oracle Call Interface) zu finden sind.

In SQL*Plus kann man die Fetch Size mit dem Parameter ARRAYSIZE steuern. Der Default ist dabei 15. In der Dokumentation zu SQL*Plus findet man die möglichen Limits. Diese liegen bei 5000 für die ARRAYSIZE. Nur was ist jetzt der optimale Wert, wenn man auf die Daten zugreifen möchte? Um diesen zu ermitteln, gibt es eine einfache Formel:

ARRAYSIZE = abrunden ( SDU / avg_record_lenght )

Die SDU Size haben wir ja schon behandelt, aber wie kommt man zur avg_record_length? Am einfachsten findet man diese, wenn man alle Spalten einer Tabelle übertragen möchte. In USER_TABLES findet man in der Spalte AVG_COL_LEN genau diesen Wert. Greift man aber mit komplexeren Statements (mit Joins und/oder Views) zu, so gibt es keinen einfachen Weg mehr, die typische Länge eines einzelnen Datensatzes zu ermitteln. Nehmen wir aber als Beispiel an, dass für eine bestimmte Tabelle in USER_TABLES.AVG_COL_LEN den Wert von 1230 aufweist und wir die SDU Size auf 2MB gesetzt haben:

ARRAYSIZE = 2097152 / 1230 = 1705 ... abgerundet also ca. 1700

Bei vielen Abfragen und Tabellen wird der Datensatz aber kürzer wie 1230 Bytes sein. Daraus ergibt sich, dass die ARRAYSIZE im Schnitt problemlos auf einen Wert im Bereich von 1.000 und 5.000 gesetzt werden kann. In dem Beispiel bleiben wir aber beim errechneten Wert:

SET ARRAYSIZE 1700

Damit wird der Zugriff auf die Daten der Tabelle via DB-Link abhängig von der Latenz und der Bandbreiten zwischen den Datenbanken deutlich schneller. Damit der Bildschirmoutput von SQL*Plus die Werte nicht zu stark beeinfluss, kann man diesen wie folgt ausschalten:

SET AUTOTRACE TRACEONLY STAT
SET TIMING ON

Jetzt das Statement ausführen – beispielsweise:

SELECT *
  FROM MYTAB@DBLINK
 WHERE Status = 7;

Ohne die Optimierung mit SDU Size, TCP I/O Buffers und SQL*Plus ARRAYSIZE wird die Übertragung deutlich länger benötigen. Liegt die remote Datenbank beispielsweise in einer Cloud – egal in welcher – sind hier riesige Faktoren an Performance erreichbar.