News
DBA-Tipp: Augen zu und durch? IGNORE_ROW_ON_ DUPKEY_INDEX Hint für INSERT Statements im Test

Die Information ist ein schnelllebiges Gut. Jeden Tag werden wir mit hunderten Informationen zugemüllt. Deshalb sind wir bestrebt uns auf das Wesentliche zu konzentrieren und nur substantiell nachhaltige Informationen bereitzustellen.

Icon Unternehmen

Sicherlich jeder Entwickler und der eine oder andere DBA werden regelmäßig mit der Herausforderung konfrontiert, Massendaten in eine Tabelle mit Primary- oder Unique-Key-Constraints einzufügen und dabei auf entsprechende Constraint-Violations zu stoßen, weil einzufügende Daten zum Teil bereits enthalten waren.

In älteren Datenbankversionen behalf man sich in diesem Fall gern mit einer PL/SQL-Routine plus Exception Handler oder SQL-Statements, die Dopplungen vorab ausschlossen. Und seit Oracle 10g kann dem Problem alternativ auch mit einem MERGE-Statement begegnet werden. MERGE gibt es zwar schon seit Oracle 9i, dort mussten aber noch sowohl WHEN MATCHED als auch WHEN NOT MATCHED behandelt werden, was für unseren Anwendungsfall nicht umsetzbar ist. Mit Oracle Version 11.2.0.1 wurde nun eine weitere Alternative, der  IGNORE_ROW_ON_DUPKEY_INDEX-Hint eingeführt. Laut Oracles New Features Guide wird der Hint empfohlen mit der Begründung: "This hint improves performance and ease-of-programming when implementing an online application upgrade script using edition-based redefinition."
Wir schauen uns in diesem DBA-Tipp einmal an, wie es um die Performance im Vergleich zu anderen Lösungsvarianten bestellt ist.

Testszenario

Für die Performancemessungen wird jeweils die SH.SALES-Tabelle aus den offiziellen Oracle Samples herangezogen, da sie mit ca. 870.000 Zeilen ausreichend viele Daten enthält. Zusätzlich dazu legen wir uns eine Kopie dieser SALES-Tabelle an, in die später die INSERTs erfolgen werden. Dazu übertragen wir ca. 5% der Zeilen der SALES-Tabelle, die später dann für die entsprechenden Constraint-Violations sorgen werden. Zudem legen wir natürlich noch einen Primary-Key-Constraint auf der SALES_COPY-Tabelle an.

CREATE TABLE sales_copy AS
SELECT * FROM sales sample (5);

ALTER TABLE sales_copy ADD CONSTRAINT pk_sales PRIMARY KEY(prod_id, cust_id, time_id, channel_id, promo_id);

Vor jedem Test wird natürlich ein „alter system flush buffer_cache“ ausgeführt, um möglichst vergleichbare Rahmenbedingungen zu schaffen. Auf dessen Darstellung wird in den Testbeschreibungen der Einfachheit halber aber verzichtet.

Nach jeder Ausführung ermitteln wir mit folgendem Statement ausgewählte Sessionstatistiken, die wir am Ende miteinander vergleichen wollen.

SELECT n.name,
  s.value
FROM v$mystat s,
  v$statname n
WHERE s.statistic#=n.statistic#
AND name IN ('db block gets','redo size','physical reads', 'sorts (memory)','sorts (disk)','sorts (rows)')
ORDER BY n.name;

Test 1 – Violations durch PL/SQL mit Exception Handler verhindern

PL/SQL bietet uns die Möglichkeit, Exceptions kontrolliert abzufangen und zu behandeln. Eine Constraint-Violation führt also damit nicht zum Abbruch des gesamten Statements. Um die PL/SQL-Variante möglichst performant umzusetzen, greifen wir natürlich sowohl für das SELECT als auch das INSERT auf BULK-Operationen zurück:

DECLARE
type salesrowtype
IS
  TABLE OF sales%rowtype;
  salescollection salesrowtype;
  dml_errors EXCEPTION;
  pragma exception_init(dml_errors, -24381);
BEGIN
  SELECT * bulk collect INTO salescollection FROM sales;
  forall i IN salescollection.first .. salescollection.last SAVE exceptions
  INSERT
  INTO sales_copy
    (
      prod_id,
      cust_id,
      time_id,
      channel_id,
      promo_id,
      quantity_sold,
      amount_sold
    )
    VALUES
    (
      salescollection(i).prod_id,
      salescollection(i).cust_id,
      salescollection(i).time_id,
      salescollection(i).channel_id,
      salescollection(i).promo_id,
      salescollection(i).quantity_sold,
      salescollection(i).amount_sold
    );
EXCEPTION
WHEN dml_errors THEN
  NULL;
END;
/

Laufzeit

02:27.24
db block gets27.621.202
physical reads12.205
redo size2.907.605.392

 

Test 2 – Violations per SQL-Subquery vermeiden

INSERT INTO sales_copy
SELECT *
FROM sales
WHERE (prod_id, cust_id, time_id, channel_id, promo_id) NOT IN
  ( SELECT prod_id, cust_id, time_id, channel_id, promo_id FROM sales_copy
  );

Laufzeit00:14.92
db block gets2.026.703
physical reads6.977
redo size317.183.016

 

Test 3 – Violations durch Merge vermeiden

MERGE INTO sales_copy dst USING
(SELECT prod_id,
  cust_id,
  time_id,
  channel_id,
  promo_id,
  quantity_sold,
  amount_sold
FROM sales
) src ON ( dst.prod_id=src.prod_id AND dst.cust_id=src.cust_id AND dst.time_id=src.time_id AND dst.channel_id=src.channel_id AND dst.promo_id=src.promo_id )
WHEN NOT matched THEN
  INSERT
    (
      prod_id,
      cust_id,
      time_id,
      channel_id,
      promo_id,
      quantity_sold,
      amount_sold
    )
    VALUES
    (
      src.prod_id,
      src.cust_id,
      src.time_id,
      src.channel_id,
      src.promo_id,
      src.quantity_sold,
      src.amount_sold
    );

Laufzeit00:11.92
db block gets2.285.383
physical reads16.739
redo size244.404.684

 

Test 4 – Violations durch Hint vermeiden

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(sales_copy pk_sales) */
INTO sales_copy
SELECT * FROM sales;

Laufzeit02:22.77
db block gets27.618.994
physical reads12.397
redo size2.907.486.308

 

Auswertung

Aus dem direkten Vergleich der vier Varianten lassen sich nun folgende Schlüsse ziehen:

 (1) PL /SQL(2) Subquery(3) Merge(4) Hint
Laufzeit02:27.2400:14.9200:11.9202:22.77
db block gets27.621.2022.026.7032.285.38327.618.994
physical reads12.2056.97716.73912.397
redo size2.907.605.392317.183.016244.404.6842.907.486.308
  • Die PL/SQL-Variante ist trotz Verwendung von BULK-Operationen die ressourcenhungrigste und langsamste. Der Grund dafür liegt vor allem darin, wie das BULK-Insert intern ausgeführt wird. Wenngleich durch die FORALL-Klausel die für die PL/SQL-Performance bekanntermaßen fatalen Context-Switches zwischen SQL- und PL/SQL-Engine weitgehend vermieden werden, werden am Ende in der SQL-Engine trotzdem Single-Row-Inserts in einer Schleife durchgeführt. In unserem Beispiel werden also ca. 870.000 INSERTS ausgeführt, von denen ca. 5% auf eine Exception laufen und jeweils ein Single-Row-Rollback auslösen.
  • Deutlich besser performt das SQL mit Subquery. Da hier bereits vorab alle Zeilen ausgefiltert werden, die eine Primary-Key-Violation verursachen würden, ist auch die Redomenge grundsätzlich kleiner als beim INSERT über PL/SQL-Block. Zudem werden keine Zeilen erst eingefügt, die ohnehin später wieder zurückgerollt werden müssen. Die überproportional hohe Einsparung hinsichtlich Zeit und Redomenge rührt aber daher, dass das INSERT hier tatsächlich in einer Operation (im Gegensatz zu 870.000 INSERTs im BULK) erfolgt und die Daten wesentlich dichter in Datenblöcken zusammengefasst eingefügt werden können.
     
  • Das MERGE-STATEMENT ist in unserem speziellen Testfall gegenüber der Subselect-Variante marginal schneller, da es hier FULL TABLE SCANs verwendet, während das  Subselect über NESTED LOOPS ANTI ausgeführt wird. Grundsätzlich kann man aber davon ausgehen, dass beide Varianten unter Umständen etwa die gleiche Performance zeigen werden.
     
  • Absolut enttäuschend hingegen ist die Ausführungsstatistik des Statements unter Nutzung des neuen Hints. Wenngleich das „easy-of-programming“ tatsächlich sichtbar wird, zeigen sich Performance und Ressourcenbedarf auf einem katastrophalen Niveau. Sowohl Laufzeit als auch Redomenge lassen den Verdacht zu, dass das Statement intern auf Single-Row-Inserts umgeschrieben wird. Das Statement verhält sich ebenso schlecht wie die PL/SQL-Lösung, die nicht ohne Grund gern als Negativbeispiel in dem einen oder anderen SQL-Tuning-Kurs verwendet wird.

Fazit

Aktuell ist trotz der Bequemlichkeit, die mit der Nutzung des IGNORE_ROW_ON_DUPKEY_INDEX Hints einhergeht, von seiner Verwendung abzuraten. Neben der außerordentlich schlechten Performance, die in dem vorstehenden Vergleichstest nachgewiesen werden konnte, kommen auch noch weitere Einschränkungen hinzu. So muss der im Hint angegebene Index auch tatsächlich existieren. Andernfalls wird der Hint nicht, wie üblich ignoriert, sondern ein Fehler geworfen. Außerdem können INSERTs, die den Hint verwenden, weder von Parallelisierung, noch von Direct Path oder NOLOGGING profitieren. Und zu allem Überfluss existiert in den Versionen Oracle 11.2.0.2 und 11.2.0.3 im Zusammenhang mit dieser Funktionalität der Bug 11865420, der zu Indexcorruption führen und durch Verletzung des Unique-Constraints logische Inkonsistenzen verursachen kann.
Hier muss Oracle also definitiv nachbessern.