Disy Tech-Blog

High Performance Spatial Computing mit Oracle 12c

High Performance Spatial Computing mit Oracle 12c

Teil II: Beispiel aus der Praxis

09.03.2016 | Eva-Maria Kramer

Dieser Artikel erschien zuerst im DOAG Magazin (Nr.6 | Dezember 2015).

Im ersten Teil dieses Beitrags hast du erfahren welche Grundlagen geschaffen werden mussten, um in unserem Kundenprojekt in kurzer Zeit heterogene Geometrie- und Sachdaten zu einem konsistenten Gesamtdatenbestand aufzubereiten. In diesem zweiten Teil zeigen wir dir an einem Beispiel, wie wir auch bei komplexen geometrischen Aufgaben mit Oracle 12c zum Ziel gekommen sind.

Im Rahmen des Projektes zeigte sich wieder eindrucksvoll, dass auch das beste Setup nichts nützt, wenn die einzelnen Abfragen unperformant geschrieben sind oder der Optimizer überfordert ist. Gerade die Verarbeitung von räumlichen Daten bietet hier einige Herausforderungen.

Im Folgenden ein Beispiel, mit welchen Strategien wir auch bei komplexen geometrischen Aufgaben mit Oracle 12c zum Ziel gekommen sind.

Isophonen

Das Ergebnis der Modellierung waren sogenannte Isophonen, d.h. Flächen (Geometrien) mit gleicher Lautstärke.

Isophonengeometrien als Flächen und Bänder
Isophonengeometrien als Flächen (oben) und Bänder (unten)

Isophonen können als Flächen oder als Bänder vorliegen. Die Flächen zeigen je Pegelklasse die Bereiche, in denen bestimmte Lautstärken erreicht sind (Mindestlautstärke). Die Bänder dagegen zeigen jeweils die betroffene Fläche eines Lautstärkeintervalls an. Im Projekt mussten beide Arten der Darstellung erzeugt werden.

Komplexe Isophonengeometrien
Komplexe Isophonengeometrien

Modellierte Isophonen zeichnen sich in der Regel durch extrem viele Stützpunkte aus, da die Geometrien häufig sehr groß und gleichzeitig sehr komplex sind. Viele Stützpunkte bedeuten mehr Speicherbedarf, vor allem jedoch längere Laufzeiten bei räumlichen Abfragen bzw. Operationen. Bei Bändern ist die Anzahl der Stützstellen durch die inneren Ringe nochmals deutlich größer als bei den Flächen.

Aus dem Projekt resultierten knapp 400.000 Isophonenflächen mit in Summe 95 Millionen Stützpunkten. Hieraus mussten fast 200 Millionen Isophonenbänder berechnet werden, die in Summe über 150 Millionen Stützpunkte aufwiesen.

Implementierung

Erster Ansatz: Kurz und knackig

Mit einem kurzen Statement konnten wir aus den Flächengeometrien Bändergeometrien generieren:

CREATE TABLE Isophonen_Baender AS
SELECT o.Sachdaten,
       o.Pegel,
       SDO_GEOM.SDO_DIFFERENCE (
           o.Geom,
           (SELECT SDO_AGGR_UNION (SDOAGGRTYPE (i.Geom, 0.05))
              FROM Isophonen_Flaechen i
             WHERE i.Sachdaten = o.Sachdaten
               AND i.Pegel > o.Pegel),
           0.05) Geom
  FROM Isophonen_Flaechen o;

Hier wurde von den Flächengeometrien alle im gleichen räumlichen Gebiet liegenden Flächen mit höheren Lärmwerten abgezogen. Sachdaten und Identifier blieben erhalten.

Für sehr kleine Datenmengen ist ein solches Statement vertretbar. Für größere Datenmengen ist dieser Ansatz nicht praktikabel.

Selbst wenn das Statement nach Wochen erfolgreich abgeschlossen worden wäre (und wir diese Zeit gehabt hätten), wären drei große Nachteile geblieben:

  1. Die gesamte Konfiguration der Redo-Logs muss auf lang laufende Abfragen ausgelegt sein, mit allen Nachteilen, die man hierdurch in Kauf nehmen muss.
  2. Im Falle eines Abbruchs (verlorene Connection, Stromausfall) müsste nach dem Redo das gesamte Statement nochmals von vorne verarbeitet werden.
  3. Mit solch einem Statement hat man keinerlei Anhaltspunkt, wie lange die Verarbeitung noch dauert.

Optimierung: Eins nach dem Anderen

DECLARE
BEGIN
  FOR c IN (SELECT Sachdaten, Pegel, Geom FROM Isophonen_Flaechen)
  LOOP
    INSERT INTO Isophonen_Baender (Sachdaten, Pegel, Geom)
      SELECT o.Sachdaten,
             o.Pegel,
             SDO_GEOM.SDO_DIFFERENCE (
                 o.Geom,
                 (SELECT SDO_AGGR_UNION (SDOAGGRTYPE (i.Geom, 0.05))
                    FROM Isophonen_Flaechen i
                   WHERE i.Sachdaten = o.Sachdaten
                     AND i.Pegel > o.Pegel),
                 0.05) Geom
        FROM Isophonen_Flaechen o
        WHERE c.Sachdaten = o.Sachdaten AND c.Pegel = o.Pegel;
    COMMIT WRITE NOWAIT;
  END LOOP;
END;
/

Die Berechnung erfolgte nicht mehr in einer einzigen Transaktion in der Datenbank. Vielmehr wurde in einer Schleife über alle Flächen iteriert. Jede berechnete Fläche wurde direkt in die Ergebnistabelle hinzugefügt.

Damit bot sich die Möglichkeit, dass im Falle eines Abbruchs das Statement jederzeit für alle noch nicht verarbeiteten Geometrien wieder aufgenommen werden konnte. Schon bearbeitete Datensätze blieben erhalten. Gleichzeitig war jederzeit transparent wie viele Datensätze bereits verarbeitet wurden und somit eine zeitliche Hochrechnung möglich.

Die technischen Probleme des ersten Ansatzes waren damit adressiert. Für den engen Zeitplan war die Laufzeit des Statements jedoch immer noch nicht hinreichend.

Lösung: Teile und herrsche

DECLARE
  v_band_geom  MDSYS.SDO_GEOMETRY;
BEGIN
  INSERT INTO Isophonen_Baender (Sachdaten, Pegel, Geom)
      SELECT Sachdaten, Pegel, Geom
        FROM Isophonen_Flaechen
       WHERE Pegel = 75;
  COMMIT WRITE NOWAIT;  

   FOR o IN (SELECT Sachdaten, Pegel, Geom, SDO_GEOM_MBR(Geom) Mbr
               FROM Isophonen_Flaechen
               WHERE Pegel != 75)
   LOOP
     v_band_geom := o.Geom;
     FOR i IN (SELECT Geom
                 FROM Isophonen_Flaechen 
                WHERE Sachdaten = o.Sachdaten
                  AND Pegel > o.Pegel 
                  AND SDO_RELATE(Geom, o.Mbr, 'mask=anyinteract')
                        = 'TRUE'
             ORDER BY Pegel desc)
     LOOP
       v_band_geom := SDO_GEOM.SDO_DIFFERENCE(v_band_geom,
                                              i.Geom,
                                              0.05);
     END LOOP;

     INSERT INTO Isophonen_Baender (Sachdaten, Pegel, Geom)
          VALUES (o.Sachdaten, o.Pegel, v_band_geom);
     COMMIT WRITE NOWAIT;
   END LOOP;
END;
/

In einem ersten Schritt wurden alle innersten Geometrien (d.h. die lautesten Flächen) direkt in die Ergebnistabelle für Bänder geschrieben. Hier gab es keinen Unterschied von Fläche zu Band. Anschließend wurden die restlichen Geometrien mit einer doppelten Schleife verarbeitet.

Der Optimizer von Oracle ist an dieser Stelle nicht in der Lage, den geometrischen Index für eine Vorselektion für die Funktion SDO_RELATE zu nutzen. Stattdessen musste eine Vorselektion über das vorberechnete minimal umgebende Rechteck (engl.: MBR) erfolgen, was ein Vielfaches an Rechenzeit einsparte.

An dieser Stelle ein Tipp: In Oracle 12c gibt es gegenüber 11g eine verbesserte und schnellere Implementierung dieser Funktion: SDO_GEOM_MBR statt SDO_GEOM.SDO_MBR.

Die Aufgabe der äußeren Schleife bestand darin, alle noch zu prozessierenden Flächen zu selektieren und das MBR zu erstellen. Die innere Schleife iterierte dann über alle Objekte, welche das MBR der äußeren Schleife berührten. Es wurden also nur die Geometrien rechenintensiv miteinander verschnitten, die sich gemäß der Vorselektion an Hand der räumlichen Lage auch potentiell überlagerten.

Neben den Vorteilen der ersten Optimierung war mit dieser Anweisung auch das Laufzeitproblem adressiert.

Fazit

Mit diesem Aufbau haben wir das Projekt sehr erfolgreich abgeschlossen. Durch die Kombination der stabilen Oracle 12c und ihrer enormen Verbesserungen der Performance für räumliche Abfragen gegenüber der Leistung von Oracle 11g, sowie dem gewählten Hardware Setup, waren wir in der Lage sämtliche Ergebnisse in time, quality and budget abzuliefern.

Die von Oracle bereitgestellten räumlichen Funktionen (Locator und Spatial Option) sind ausgereift und vollumfänglich. Auch die mit Oracle 12c teilweise komplett neu geschriebenen Funktionen sind inzwischen stabil. Bei einigen Funktionen muss sich der Anwender jedoch über ihre Besonderheiten oder ihren offiziellen Funktionsumfang im Klaren sein. Der Umgang der gleichen Funktion mit 2D- und 3D-Daten ist zwischen den Versionen zum Teil unterschiedlich.

Bei der Implementierung von Abfragen oder PL/SQL-Routinen lohnt es sich, immer wieder einen Blick in die Dokumentation oder das Changelog zu werfen. Mit 12c wurde zum Beispiel das bereits erwähnte SDO_GEOM.SDO_MBR durch SDO_GEOM_MBR ergänzt, wobei letzteres deutlich schneller ein Resultat liefert. Die neue Funktion SDO_POINTINPOLYGON ist in der Lage extrem schnell Punkte mit Polygonen zu korrelieren. Das absolute Highlight für die Berechnung war aber das SPATIAL_VECTOR_ACCELERATION-Flag. Die Geschwindigkeitsgewinne durch diese Funktion sind wirklich enorm.

Wenn der Projektzeitplan ein wenig lockerer gewesen wäre, hätte ich große Freude daran gehabt, die diversen Berechnungen vergleichsweise auch auf einer 11g auszuführen, um den Unterschied zwischen 11g und 12c im Gesamten zu sehen. Für meinen Teil möchte ich die Oracle 12c für Spatial-Processing-Projekte nicht mehr missen.

Die finalen Ergebnisse des Kundenprojektes und vor allem die Isophonen können Sie Online auf den Seiten des Eisenbahn-Bundesamtes finden.