Optimieren von Oracle gespeicherten Prozeduren

Question

Vor kurzem wurde ich mit der Optimierung einiger bestehender Oracle Stored Procedures beauftragt. Jede dieser gespeicherten Prozeduren fragt die Datenbank ab und generiert eine XML-Dateiausgabe. Insbesondere eine dieser Prozeduren brauchte etwa 20 Minuten, um ihre Ausführung zu beenden. Bei näherer Betrachtung stellte ich fest, dass es mehrere verschachtelte Schleifen und unnötige Abfragen gab. Anstatt zum Beispiel eine

SELECT * from Employee e, Department d WHERE e.DEPT_ID = d.ID
--write data from query to XML

es war mehr wie

FOR emp_rec in ( SELECT * from employee )
LOOP
   SELECT * from Department WHERE id = emp_rec.DEPT_ID;
   --write data from query to XML
END LOOP;

Die Änderung all dieser Fälle so, dass sie eher wie die erste Option aussehen, beschleunigte die Verfahren immens. Meine Frage ist: Warum? Warum ist eine Verknüpfung in der Select-Abfrage schneller als das manuelle Kombinieren der Tabellen? Was sind die zugrunde liegenden Prozesse?

Answer 1

Schauen wir uns an, wie die ursprüngliche Version wahrscheinlich bearbeitet wird.

FOR emp_rec in ( SELECT * from employee )
LOOP
   SELECT * from Department WHERE id = emp_rec.DEPT_ID;
   --write data from query to XML
END LOOP;

Die Schleifenabfrage führt wahrscheinlich einen vollständigen Tabellenscan über employee . Dann wird für jede zurückgegebene Zeile die innere Abfrage ausgeführt. Unter der Annahme, dass id ist der Primärschlüssel von department Bei jeder Ausführung der Abfrage wird wahrscheinlich eine eindeutige Suche unter Verwendung des Primärschlüsselindexes durchgeführt.

Klingt toll, oder? Eindeutige Indexabfragen sind in der Regel der schnellste Weg, um eine einzelne Zeile zu erhalten (außer bei expliziten Abfragen nach ROWID). Aber denken Sie darüber nach, was dies bei mehreren Iterationen der Schleife bewirkt. Vermutlich gehört jeder Mitarbeiter zu einer Abteilung; jede Abteilung hat Mitarbeiter; und die meisten oder alle Abteilungen haben mehrere Mitarbeiter.

Bei mehreren Iterationen der Schleife wiederholen Sie also genau dieselbe Arbeit für die innere Abfrage mehrmals. Ja, die Datenblöcke können im Cache gespeichert sein, so dass Sie die physischen Lesevorgänge nicht wiederholen müssen, aber der Zugriff auf die Daten im Cache verursacht einen gewissen CPU-Overhead, der sehr groß werden kann, wenn immer wieder auf dieselben Blöcke zugegriffen wird.

Außerdem werden Sie wahrscheinlich jede Zeile in department mindestens einmal, wahrscheinlich sogar mehr als einmal. Da jeder einzelne Block in der Tabelle gelesen werden muss, spart man nicht wirklich Arbeit, indem man einen Index nachschlägt, sondern man fügt zusätzliche Arbeit hinzu.

Wenn Sie die Schleife als eine einzige Abfrage umschreiben, kann der Optimierer dies berücksichtigen. Eine Option, die ihm zur Verfügung steht, wäre eine verschachtelte Schleifenverbindung, die durch employee was im Wesentlichen dasselbe wäre wie die explizite Schleife in PL/SQL (ohne die von Mark erwähnte Kontextumschaltung). Angesichts der Beziehungen zwischen den beiden Tabellen und des Fehlens eines Filterprädikats kann der Optimierer jedoch feststellen, dass es effizienter ist, einfach beide Tabellen vollständig zu durchsuchen und einen Merge- oder Hash-Join durchzuführen. Dies führt tatsächlich zu weniger physischen IOs (unter der Annahme eines sauberen Cache zu Beginn jeder Ausführung) und viel weniger logischen IOs.

Answer 2

Die "zugrunde liegenden Prozesse" erfordern eine ausführliche Antwort. Ich überlasse es Tom Kyte um diese Frage zu beantworten ;)