Problemstellung
Egal ob Datenbanken in der Cloud oder im (Remote-)Rechenzentrum laufen, kann es auf Grund von Netzwerklatenzen zu unerwarteten Laufzeiten kommen. In diesem Beispiel läuft die Datenbank in einer Cloud und die Client Software auf einem Bürorechner.
Reporting bzw Analyse Tools – in diesem Beispiel PowerBI – brauchen zum Ermitteln der Daten aus der Oracle Datenbank mehrere Sekunden, bevor die Aufbereitung/Verarbeitung der Daten erfolgen kann. Dies macht sich bei den Benutzern durch Verzögerungen – die berühmte Sanduhr – bemerkbar.
Im aktuellen Beispiel werden aus der Datenbank ca. 83.000 Datensätze abgeholt, die PowerBI für die Analyse und Auswertung benötigt. Es handelt sich dabei um eine Dimension (Lookup) Tabelle, die in PowerBI für die Auswahl der konkreten Informationen benötig wird, bevor die Analyse erfolgen kann. Jedes mal, wenn diese Dimensionsinformation aus der Datenbank abgeholt wird, muss der Benutzer 4-5 Sekunden warten.
Problemanalyse
83.000 Datensätze sind einerseits nicht wirklich viele Daten für eine Datenbank, andererseits stellen sie bei der Übertragung bereits eine nennenswerte Datengröße dar. Im ersten Schritt wurde mittels folgendem SELECT Statement verifiziert, wie lange der Zugriff auf die Daten in der Datenbank dauert:
SELECT COUNT(*)
FROM (SELECT non_idx_column FROM dim_table);
Das Statement war in wenigen Millisekunden fertig – an der Datenbank selbst kann es daher nicht liegen. Da sich PowerBI nur bedingt gut tracen lässt, nutzen wir SQLPlus am Client, um das Verhalten zu reproduzieren. Da SQLPLUS für den Bildschirmoutput relativ viel Zeit benötigt, müssen wir hier einen Trick anwenden. Wenn in SQLPlus „SET AUTOTRACE TRACEONLY“ einsgechaltet wird, werden die Daten zwar über das Netzwerk übertragen, aber nicht am Bildschirm ausgegeben. Zusätzlich erhalten wir noch einige Statistiken, die uns weiterhelfen können. Durch „SET TIMING ON“ bekommen wir die dafür benötigte Zeit.
SQL> SET TIMING ON
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT * FROM DIM_TABLE;
83480 rows selected.
Elapsed: 00:00:04.30
...
Die Laufzeit von ca. 4-5 Sekunden in PowerBI kann mit SQLPLUS und mit 4.3 Sekunden bestätigt werden. Die Tabelle ist mit 3 MB Gesamtgröße zwar nennenswert, aber in einem 1 GBit Netzwerk sollte die Übertragung lediglich um die 30 Millisekunden benötigen. Woher kommen die rund 4 Sekunden, die hier zu viel verbraucht werden?
Werfen wir einen Blick auf die restlichen Informationen vom AUTOTRACE, speziell die Statistiken am Ende:
...
Statistics
----------------------------------------------------------
...
5567 SQL*Net roundtrips to/from client
Für die Übertragung der 83.480 Datensätze wurde in Summe 5567 mal mit dem Datenbankserver gesprochen. Das bedeutet, dass jeweils zumindest ein Datenpaket vom Client zum Server und wieder zurück geschickt wurde. Da der Client und der Server nicht direkt miteinander verbunden sind, spielt hier die Netzwerklatenz (inkl. Verarbeitungszeiten für die Übertragung sowohl am Client als auch am Server) ebenfalls eine Rolle.
Was wäre, wenn MEHR Daten auf einmal übertragen werden würden?
Anzahl der Datensätze pro Fetch erhöhen.
In SQLPLUS gibt es dazu den Parameter ARRAYSIZE, dessen Default auf 15 steht. Versuchen wir es einmal mit 1000 (das von SQLPLUS unterstützte Maximum ist 5000 rows).
SQL> SELECT * FROM DIM_TABLE;
83480 rows selected.
Elapsed: 00:00:00.21
...
Statistics
----------------------------------------------------------
...
85 SQL*Net roundtrips to/from client
Wow, die Laufzeit hat sich von 4.3 Sekunden auf 0.21 Sekunden reduziert. Das ist ein Zwanzigstel! Jetzt müssen wir PowerBI dieses Kunststück ebenfalls beibringen. Manchmal ist Googeln eine gute Lösung. Wobei wir hier nicht ARRAYSIZE (SQLPLUS Bezeichnung) sondern FetchSize (die richtige Bezeichnung) als Stichwort verwenden.
Google Abfrage: PowerBI Oracle FetchSize
Gleich der erste Treffer ist: Connecting Microsoft Power BI Desktop to Oracle Autonomous Databases and On-premises Databases.
Dort ist folgende Information zu finden:
Typically, BI and ETL applications retrieve large data amounts from a source database for further processing. To speed up Oracle data retrieval via Power BI Desktop, the ODP.NET FetchSize can be increased from its default 128K value (131,072 bytes) to as large as int.MaxValue. The FetchSize determines the amount of data ODP.NET fetches into its internal cache upon each database round trip. It’s possible to improve performance by an order of magnitude by significantly increasing FetchSize when retrieving large result sets.
Kurzübersetzung: Um die Datenübertragung schneller zu machen, kann die FetchSize im ODP.NET vom Default von 128k entsprechend vergrößert werden.
Gleich darunter gibt es einen Hinweis, wie das geht:
Mittels Windows Registry Editor unter
HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ODP.NET\4.122.19.1 <– Versionsnummer von ODP.NET
einen Value String „FetchSize“ mit einem entsprechend größeren Wert – beispielsweise 4194304 (=4MB) – eintragen.
Umsetzung und Ergebnis im PowerBI
Nachdem der Parameter in der Registry gesetzt und PowerBI neu gestartet wurde (ein Reconnect hätte vermutlich auch gereicht), sind die Daten SOFORT da. Der Benutzer merkt keine Verzögerung mehr!
Weitere Optimierungsmöglichkeit
Oracle nutzt zur Datenübertragung – abhängig von der Oracle Version – meist 8k Netzwerkpakete. Das bedeutet für unser Beispiel, dass sehr wohl noch unnötig viele Netzwerk Acknowledges erfolgt sind (Übertragungsbestätigungen). Das kann reduziert werden, indem die SDU – Session Data Unit Size – für die Übertragung vergrößert wird. Für die Einstellung der SDU gibt es bei Oracle viele Möglichkeiten. Wir wollen uns auf die Client-seitigen Möglichkeiten beschränken:
Maximale SDU Size
- Oracle bis 11.2.0.2: 32767 Bytes
- Oracle ab 11.2.0.2: 65536 Bytes
- Oracle 19c: 2MB
Hinweis: Nutzen Sie maximal 65536 Bytes SDU Size, solange Sie nicht noch weitere Netzwerkeinstellungen vornehmen können. Wenn Sie zu große Werte nutzen, kann es zu einem Überlauf der SEND und RECIEVE Buffer am Client oder Server kommen. Das führt dazu, dass Datenpakete verworfen und die Daten erneut übertragen werden müssen – dadurch wird alles noch langsamer.
SQLNET.ORA am Client
DEFAULT_SDU_SIZE=65536
Connect String am Client (TNSNAMES.ORA)
MYDB =(DESCRIPTION=(SDU=65536)(ADDRESS=(PROTOCOL=tcp)(HOST=myserver)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MYDB)))
Zusammenfassung
Der Default für die Datenübertragung zwischen Client und Datenbank Server ist nicht für alle Anwendungsfälle passend. Speziell jene Anwendungen, die viele Daten übertragen müssen, benötigen sehr viele Netzwerkroundtrips – und hier kommt die Latenz zwischen Client und Server zu tragen. Das reduziert die Verarbeitungsgechwindigkeit merklich. Durch Reduktion der Anzahl der Roundtrips kann man den Einfluss der Netzwerklatenz deutlich reduzieren.