17. April 2008

Mengenoperationen vs. Einzelzugriffe

English title: Singe row vs. bulk operations

Heute starten wir mal mit einer ganz einfachen Anforderung: Wir möchten eine Tabellenzeile aufsummieren. Dazu habe ich mal vier Ansätze vorbereitet ... die findet Ihr im Folgenden (und es soll keiner sagen, manche Ansätze wären total unrealistisch und keiner würde so programmieren - alles schon gesehen). Die Tabelle MYSALES ist ein Subset (bei mir mit 90.000 Zeilen) der Tabelle SH.SALES aus den Oracle Sample Schemas - kann man sich einfach mit einem CTAS und der SAMPLE-Klausel erzeugen ...
Today we'll start with a very simple requirement: We want to sum up a table column. I've prepared four approaches for this ... and I've seen examples for every introduced approach. Be careful with saying "nobody writes code like this" - the world is big. The table MYSALES is a subset (90.000 rows) of the table SH.SALES in the Oracle Sample Schemas. You can create it easily with a CTAS and the SAMPLE clause ...
Lasst das Skript einfach mal laufen und schaut euch die Unterschiede an ...
Just start the script and have a look at the differences ...
set serveroutput on
set timing on

/* 1. Ansatz */

declare
  v_sum number := 0.0;
  v_val number;
begin
  /*
   * ROWID-Zugriffe, da ROWID der schnellste denkbare Zugriff
   * auf eine Tabellenzeile ist
   */
  for i in (select rowid from mysales) loop
    select amount_sold into v_val from mysales where rowid=i.rowid;
    v_sum := v_sum + v_val;
  end loop;
  dbms_output.put_line(v_sum);
end;
/

19662136,24   

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.55

/* 2. Ansatz */

declare
  v_sum number := 0.0;
begin
  for i in (select amount_sold from mysales) loop
    v_sum := v_sum + i.amount_sold;
  end loop;
  dbms_output.put_line(v_sum);
end;
/

19662136,24  

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31

/* 3. Ansatz */

declare
  type numlist_t is table of number index by binary_integer;
  v_val numlist_t;

  v_sum number := 0.0;
begin
  select amount_sold bulk collect into v_val from mysales;
  for i in 1..v_val.count loop
    v_sum := v_sum + v_val(i);
  end loop;
  dbms_output.put_line(v_sum);
end;
/

19662136,24   

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17

/* 4. Ansatz */

select sum(amount_sold) from mysales
/

SUM(AMOUNT_SOLD)
----------------
     19662136,24  

1 row selected

Elapsed: 00:00:00.04
Man sieht, dass der erste Ansatz der langsamste ist. Um das Argument der zusätzlichen Indexzugriffe von vorneherein auszuschließen, habe ich den Zugriff über die ROWID gemacht. Wenn die Schleife also durchläuft, wird auf jede Tabellenzeile direkt und ohne Umwege zugegriffen. Trotzdem hat er 12 Sekunden gebraucht: Die Datenbank musste das SQL ...
It's obvious that the first approach is the slowest one. To bypass the dicussion about index access I used ROWID's to access the individual table rows. Therefore the loop accesses every table row directly and without using an index or any other additional I/O. Nevertheless, it took 12 seconds: The database had to execute the SQL query ...
select amount_sold from mysales where rowid = :1
... ja auch 90.000 mal ausführen. Auch wenn man ROWID's und Bindevariablen nutzt, braucht das immer noch seine Zeit. Schauen wir uns die zweite Variante an:
... 90.000 times. Although ROWID's and bind variables were used this took some time. Let's have a look at the second approach:
Hier haben wir schon eine Mengenoperation, lediglich das SUM machen wir noch mit PL/SQL. Und da nur noch ein SQL-Kommando ausgeführt wird, geht das auch schon wesentlich schneller: Nur noch eine Drittelsekunde. Zwar wurde in diesem Fall nur noch eine SQL-Abfrage ausgeführt, allerdings wurden immer noch 90.000 Tabellenzeilen in den PL/SQL Block "gefetcht" - da es eine "ordinäre" PL/SQL Schleife ist, bedeutet dies nochmals 90.000 "Round-Trips" von SQL nach PL/SQL. Diese Roundtrips sind einzeln betrachtet zwar nicht teuer, aber schauen wir mal auf Variante 3:
Here we have a mass operation, just the summing-up is done within PL/SQL. And since the SQL query is executed only once this is very much faster than the first approach. But although the SQL Query was executed only once the PL/SQL loop had to fetch all the 90.000 rows. And the "ordinary" PL/SQL loop did this row-by-row, so we had 90.000 context switches between SQL and PL/SQL. A single context switch is not very expensive, but if you consider it 90.000 times ... let's look at approach 3:
... in welcher die PL/SQL Loop durch eine BULK COLLECT-Anweisung ersetzt wurde. Es werden immer noch 90.000 Zeilen von SQL nach PL/SQL übertragen, aber mit nur noch einem Round-Trip zwischen SQL und PL/SQL. Durch die lange Zeit, die Variante 1 verbraucht hat, geht dieser Effekt fast unter ... aber es ist fast Faktor 2 (0,17 Sekunden vs. 0,31 Sekunden). Das dies kein zufälliger Effekt ist, lässt sich nachweisen, wenn man die Varianten 2 und 3 mal gegen die Originaltabelle SH.SALES laufen lässt ...
... which used the BULK COLLECT clause. So the 90.000 rows are still being fetched from SQL to PL/SQL but this requires only one roundtrip. Due to the long elapsed time of approach 1 the difference between approaches 2 and 3 seems to be not significant - but it is nearly factor 2 (0,17 vs. 0,31 seconds). Testing both approaches with the bigger "original table" SH.SALES reveals that this difference is not by coincidence:
SQL> start script2.sql
Variante 2: 98205831,21

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.88

Variante 3: 98205831,21

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.09
Der Bulk Collect macht diese Operation also um 40% schneller. Was kann noch besser sein?
As seen: Using the bulk collect clause leads to a 40% faster operation - what could be better ...?
Ganz klar: Für diese Aufgabe(!) ganz auf PL/SQL verzichten Denn eine Summierung (und so hätten wir es alle von vorneherein gemacht) kann direkt im SQL ausgeführt werden. Das ist mit 0.04 Sekunden (MYSALES) bzw. 0,36 Sekunden (auf der Originaltabelle) die schnellste Variante.
For this requirement this is easy: Do it within pure SQL! PL/SQL is not needed for just summing up a column - and most of us wouldn't have used it. So the "pure" SQL approach is with 0,04 seconds also the fastest one. The operation on the "original table" SH.SALES took 0,36 seconds which is also faster than the other approaches.
Was kann man also daraus ableiten? Zunächst ist es eine Bestätigung des alten, bekannten Grundsatzes "80% des Tuning-Erfolges lassen sich durch Anwendungstuning erzielen". Meint also: Bevor man bei langsam laufenden PL/SQL-Blöcken ins Systemtuning (Datenbankparameter, I/O Tuning, etc) einsteigt, sollte man stets prüfen, ob der Code seine Aufgabe optimal erfüllt - und dabei kann man die vier hier vorgestellten Varianten quasi rückqärts abarbeiten:
  1. Lässt sich die Aufgabe mit reinem SQL erledigen?
    Filtern, Sortieren, Gruppieren niemals mit PL/SQL programmieren, sondern stets durch SQL-Abfragen sicherstellen. Auch anspruchsvollere Aggregate wie gleitende Durchschnitte oder das "Aufsummieren" von Tabellenzeilen benötigen keinen PL/SQL-Code; dazu gibt es analytische Funktionen (einfach mal in die SQL Reference sehen). In PL/SQL sollte nur dann kodiert werden, wenn es wirklich nötig ist.
  2. Wenn PL/SQL programmiert wird: Niemals Einzelsatzzugriffe programmieren, wo eine Mengenoperation das gleiche erreichen kann..
    Insbesondere beim "Schachteln" von Cursorn sollte man aufpassen, ob man nicht gerade dabei ist, Einzelsatzzugriffe zu programmieren (kann man die gleiche Aufgabe nicht mit einem SQL und Subqueries erledigen). Selbst if-then-else-Konstrukte können mit purem SQL und dem CASE-Konstrukt erledigt werden.
  3. Bulk Collect bzw. Bulk DML nutzen, wo möglich.
    SQL und PL/SQL sind zwar "nahe Verwandte" aber doch getrennte Umgebungen. Wenn Werte aus einer SQL-Abfrage in eine PL/SQL-Variable überführt werden, findet ein sog. Context-Switch (oder "Roundtrip") zwischen SQL und PL/SQL statt. Der kostet (für sich betrachtet) nicht viel ... wird das in einer Schleife allerdings häufiger gemacht, so fällt es ins Gewicht. Bulk Collects übernehmen eine ganze Reihe von Werten auf einmal(!) in ein PL/SQL Array - das ist wesentlich schneller.
So what does this mean for practical development? Firstly this is the reassurance of the "old principle" that 80% of tuning success can be achieved by tuning the application. Expressed otherwise: Check the application code before tuning database parameters, I/O or similar - the effects is in most cases much better. And when doing this one can work reversely through the here introduced four approaches:
  1. Can the requirement be solved by using pure SQL?
    Filtering, ordering, grouping does not require PL/SQL coding - SQL is pretty good in doing this. Ambitious query requirements like moving averages, ratio-to-reports or moving sums can also be solved with pure SQL with analytical functions (just have a look into the SQL reference). Again: PL/SQL is not needed for this - use PL/SQL only when it is really necessary.
  2. In PL/SQL programs: Avoid accessing your tables row by row when you're indeed doing a mass operation. Be aware of this when nesting cursors (can't this be done with a single SQL and nested subqueries?). Remember that SQL even allows to code if-then-else blocks using the CASE constructs.
  3. Use bulk Collect or Bulk DML is possible.
    Though SQL and PL/SQL are "close relatives" they are separated environments. A context switch or roundtrip between SQL and PL/SQL is not very expensive, but if its done very often it gets significant. Coding an ordinary loop with "n" iterations means "n" context switches - using the bulk clauses means only one context switch - this leads to better performance in most cases.
Bulk-Operationen sind übrigens auch bei DML wesentlich schneller, wie das folgende Beispiel zeigt: 100.000 zufällige Werte sollen in eine Tabelle eingefügt werden:
BTW: Bulk operations are also faster when doing DML - as the following example illustrates: 100.000 random values are to be inserted in a table:
drop table random_values
/

create table random_values (col number)
/

declare
  v_val number;
begin
  for i in 1..100000 loop
    v_val := dbms_random.value(0,1000);
    insert into random_values values (v_val);
  end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.55

truncate table random_values
/

Tabelle mit TRUNCATE geleert.

Elapsed: 00:00:00.48

declare
  type numlist_t is table of number index by binary_integer;
  v_val numlist_t;
begin
  for i in 1..100000 loop
    v_val (i) := dbms_random.value(0,1000);
  end loop;
  forall i in 1..v_val.count 
    insert into random_values values (v_val(i));
end;
/

PL/SQL-Prozedur erfolgreich abgeschlossen.

Elapsed: 00:00:00.71
Fazit: Wir wussten es immer: Eine Datenbank kann ihre Fähigkeiten nur ausnutzen, wenn man sie lässt - das bedeutet, dass man der Datenbank als Entwickler die Ausführungspläne nicht vorschreibt (Einzelsatz!), sondern mengenorientiert arbeitet.
The database can do its job best only if the developer allows it. If application developers determine access paths in their application code the database optimizer cannot do much to improve performance. Its best practice to push mass operations completely down to the database.

Keine Kommentare:

Beliebte Postings