Subquery Unnesting – wenn es mal nicht funktioniert

Eigentlich leistet der CBO bei den “NOT IN” Subqueries meistens einen guten Job und generiert effektive Ausführungspläne.
Es sei denn, irgendwas geht schief. Neulich war ich in einer 10.2.0.5 Datenbank lange auf der Ursachenforschung und nur die CBO Trace-Dateien (Event 10053) haben mich letztendlich auf die richtige Spur gebracht. Aber eins nach dem anderen.

Es ging um ein “INSERT AS SELECT” DML-Statement, das von einem Tag auf den anderen statt wenige Minuten viele Stunden lief.
Schauen wir es uns am folgenden Beispiel an:

CREATE TABLE t1 
( c1 NUMBER
, c2 NUMBER
, c3 NUMBER
, other_infos VARCHAR2(100) );

CREATE TABLE t2 (c1 NUMBER, c2 NUMBER, c3 NUMBER);

INSERT INTO t1 (c1, c2, c3, other_infos)
SELECT c1, c2, c3, min(other_infos) 
FROM (
SELECT TRUNC(SYS.DBMS_RANDOM.VALUE(1,25000)) c1
,      TRUNC(SYS.DBMS_RANDOM.VALUE(1,1000)) c2
,      TRUNC(SYS.DBMS_RANDOM.VALUE(1,10)) c3
,      TRUNC(SYS.DBMS_RANDOM.VALUE(0,100)) other_infos
FROM   DUAL
CONNECT BY  LEVEL <= 6000000
) 
GROUP BY c1, c2, c3;

INSERT INTO t2 (c1, c2, c3)
SELECT TRUNC(SYS.DBMS_RANDOM.VALUE(1,25000))
,      TRUNC(SYS.DBMS_RANDOM.VALUE(1,1000))
,      TRUNC(SYS.DBMS_RANDOM.VALUE(1,10))
FROM   DUAL
CONNECT BY  LEVEL <= 500000;

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('SQLORA','T1');
  DBMS_STATS.GATHER_TABLE_STATS('SQLORA','T2');
END;  
/

Listing 1

Achten Sie auf die Zeile 9: das INSERT-Statement sorgt dafür, das die Kombination C1, C2 und C3 in der T1 eindeutig ist. Die Tabellenstruktur, Datenmenge und Datenverteilung entspricht in etwa der Realität. Das Beispiel ist aber noch nicht komplett. Ich lasse bewusst noch ein Stück weg, um zu zeigen, wie die problematische Abfrage sich jetzt verhält. Bei der SELECT-Abfrage geht es darum, Datensätze aus T1 zu selektieren, die nicht in T2 vorkommen. Dies wird durch eine NOT IN Subquery erreicht.

SQL> EXPLAIN PLAN FOR
  2  SELECT t1.*
  3  FROM   t1
  4  WHERE  (t1.c1, t1.c2, t1.c3)
  5         NOT IN (SELECT t2.c1, t2.c2, t2.c3
  6                 FROM   t2);

Explained.

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'BASIC'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
Plan hash value: 895956251

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  FILTER            |      |
|   2 |   TABLE ACCESS FULL| T1   |
|   3 |   TABLE ACCESS FULL| T2   |
-----------------------------------

10 rows selected.

Listing 2

In dieser Konstellation kann hier noch kein Unnesting stattfinden. Die Methode FILTER, die der Optimizer anwendet, funktioniert im Grunde wie Nested Loops. Das heisst, dass die Unterabfrage für jeden Datensatz aus der Tabelle T1 ausgeführt wird. Eine Optimierung greift bei FILTER-Operation zu, sodass die Unterabfrage einmal pro Gruppe der Datensätze aus T1 mit eindeutigem Join-Kriterium ausgeführt wird, also einmal pro Kombination von den Spalten C1, C2 und C3. Dies bringt in unserem Fall allerdings gar nichts, denn die Kombination C1, C2, C3 wurde beim Befüllen der T1 bewusst eindeutig gemacht.

Damit Unnesting bei NOT IN funktioniert, sollte man in den Versionen vor 11g dafür sorgen, dass auf beiden Seiten keine NULLs vorkommen. Ab 11g erfordern die sogennaten Null-Aware Anti-Joins dies nicht mehr. Diese Art von Joins sind hier gut beschrieben, wir betrachten sie hier nicht weiter.

Wir schließen die NULLs für die T1 aus, indem wir einen Primary Key Constraint anlegen und für die T2 fügen wir die entsprechende WHERE-Bedingung direkt in die Abfrage ein.

ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (c1, c2, c3);

SQL> EXPLAIN PLAN FOR
  2  SELECT t1.*
  3  FROM   t1
  4  WHERE  (t1.c1, t1.c2, t1.c3)
  5         NOT IN (SELECT t2.c1, t2.c2, t2.c3
  6                 FROM   t2
  7                 WHERE  t2.c1 IS NOT NULL
  8                 AND    t2.c2 IS NOT NULL
  9                 AND    t2.c3 IS NOT NULL);

Explained.

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'BASIC'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
Plan hash value: 629543484

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  HASH JOIN RIGHT ANTI|      |
|   2 |   TABLE ACCESS FULL  | T2   |
|   3 |   TABLE ACCESS FULL  | T1   |
-------------------------------------

10 rows selected.

Listing 3

Und nun ist das Beispiel komplett. Das war der Stand der Dinge vor dem Auftreten der Performance-Probleme.

Performance-Einbruch

Am Tag X stand im Ausführungsplan eine FILTER-Operation, wie im Listing 2. Die Laufzeiten schossen in die Höhe und der Prozess wurde immer händisch nach mehreren Stunden abgebrochen. Ich habe am Anfang nicht so viel Zeit in die Ursachenforschung investiert: die Datenbank stand kurz vor einer Migration, das Problem sollte schnell gefixt sein. Als Entwickler durfte ich den Quellcode verändern und der Fix darf auch zügig produktiv gestellt werden. Also eine leichte Aufgabe?

In der Entwicklungs- und Test-DB ist der Ausführungsplan noch in Ordnung. Also mit Stored Outlines versuchen? Hat nichts gebracht.

Die Voraussetzungen für einen Anti-Join sind ja nach wie vor da. Soll man mit einem Hint dem CBO nachhelfen? Etwa so:

SELECT t1.*
FROM   t1
WHERE  (t1.c1, t1.c2, t1.c3)
        NOT IN (SELECT /*+ HASH_AJ */ t2.c1, t2.c2, t2.c3
                FROM   t2
                WHERE  t2.c1 IS NOT NULL
                AND    t2.c2 IS NOT NULL
                AND    t2.c3 IS NOT NULL);

Listing 4

Bringt auch nichts. Ich konnte in der Test-DB auch MERGE_AJ oder NL_AJ als Hints nehmen mit dem Ergebnis, dass sich der Ausführungsplan von dem SQL immer entsprechend geändert hat. Nur die produktive DB war immer noch gegen alle Hints völlig resistent!

Um zu verstehen, was da abgeht, blieb nichts anderes, als CBO es selber erklären zu lassen – über trace event 10053.

SQL> SELECT VALUE FROM V$PARAMETER WHERE UPPER(NAME)='USER_DUMP_DEST';

VALUE
----------------------------------------------------------------------
/u00/app/oracle/admin/LLBE10/udump

SQL>
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='MY_NOT_IN_TRACE';

Session altered.

SQL>
SQL> ALTER SESSION SET EVENTS 
        '10053 trace name context forever, level 2';

Session altered.

SQL>
SQL> EXPLAIN PLAN FOR
  2    SELECT t1.*
  3    FROM   t1
  4    WHERE  (t1.c1, t1.c2, t1.c3)
  5           NOT IN (SELECT t2.c1, t2.c2, t2.c3
  6                   FROM   t2
  7                   WHERE  t2.c1 IS NOT NULL
  8                   AND    t2.c2 IS NOT NULL
  9                   AND    t2.c3 IS NOT NULL);

Explained.

SQL>
SQL> ALTER SESSION SET EVENTS '10053 trace name context off';

Session altered.

Listing 5

Dann suchen wir in dem in Zeile 5 selektierten Verzeichnis nach der Trace-Datei, die den Identifier “MY_NOT_IN_TRACE” im Dateinamen hat. So bin ich in der Test- und Produktions-DB vorgegangen und beide Dateien verglichen.

*****************************
Cost-Based Subquery Unnesting
*****************************
SU: No subqueries to consider in query block SEL$2 (#2).
SU: Considering subquery unnesting in query block SEL$1 (#1)
SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on SEL$1 (#1).
SU:   Checking validity of unnesting subquery SEL$2 (#2)
SU:   Passed validity checks.
SU:   Transform an NOT IN subquery to an anti-join. 

Listing 6: Test-System – alles gut

*****************************
Cost-Based Subquery Unnesting
*****************************
SU: No subqueries to consider in query block SEL$2 (#2).
SU: Considering subquery unnesting in query block SEL$1 (#1)
SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on SEL$1 (#1).
SU:   Checking validity of unnesting subquery SEL$2 (#2)
SU:     SU bypassed: No correlation to immediate outer subquery.
SU:   Validity checks failed.
*******************************

Listing 7: Produktion – Unnesting findet nicht statt

Man sieht, dass er im ersten Fall (Test) die NOT IN Subquery in einen Anti-Join umschreiben konnte und im zweiten Fall (Produktion) dies für unmöglich gehalten hat. Warum? Ich habe nach dieser Erkenntnis noch einmal die Tabellenstruktur, Primary-Key auf T1 (enabled), etc. überprüft. Alles gleich… Nun war zumindest schon mal klar, warum die Hints und die Outlines ignoriert wurden: der CBO hielt die Transformation, bei der sie überhaupt etwas bewirken konnten, für unmöglich.

Es klingt so alles nach einem Bug. Es wäre auch vielleicht der richtige Zeitpunkt, Oracle Support einzuschalten… In meiner Projektsituation habe ich mich dagegen entschieden, denn die DB sollte in wenigen Wochen auf 11.2 migriert werden, der Workaround muss dringend her und die Freiheit, den Code zu ändern, ist auch da. Der CBO macht seine Arbeit nicht? Dann mach wir es selber! Wir wollten, dass der Optimizer unsere NOT IN Subquery in einen Hash Anti-Join umwandelt. Dann schreiben wir den Hash Anti-Join selber:

SQL> EXPLAIN PLAN FOR
  2    SELECT t1.*
  3    FROM t1 LEFT JOIN t2 
           ON (t1.c1 = t2.c1 AND t1.c2 = t2.c2 AND t1.c3 = t2.c3)
  4    WHERE t2.c1 IS NULL;

Explained.

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'BASIC'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 196153611

---------------------------------------
| Id  | Operation              | Name |
---------------------------------------
|   0 | SELECT STATEMENT       |      |
|   1 |  FILTER                |      |
|   2 |   HASH JOIN RIGHT OUTER|      |
|   3 |    TABLE ACCESS FULL   | T2   |
|   4 |    TABLE ACCESS FULL   | T1   |
---------------------------------------

Listing 8

Die Operation FILTER hier darf man nicht mit der von Listing 2 verwechseln. Es geht hier um das Filtern von Ergebnissen von Outer Join, der bereits stattgefunden hat, um daraus ein Anti-Join zu machen. Aus dem Listing 9 ist ersichtlich, dass der Overhead sich in Grenzen hält.

SQL> INSERT INTO t3
  2  SELECT t1.*
  3  FROM   t1
  4  WHERE  (t1.c1, t1.c2, t1.c3)
  5         NOT IN (SELECT t2.c1, t2.c2, t2.c3
  6                 FROM   t2
  7                 WHERE  t2.c1 IS NOT NULL
  8                 AND    t2.c2 IS NOT NULL
  9                 AND    t2.c3 IS NOT NULL);

5907679 rows created.

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));
SQL_ID  13fhw1c4kbkcc, child number 0
-------------------------------------
INSERT INTO t3 SELECT t1.* FROM   t1 WHERE  (t1.c1, t1.c2, t1.c3)        NOT IN (SELECT t2.c1, t2.c2, t2.c3
        FROM   t2                WHERE  t2.c1 IS NOT NULL                AND    t2.c2 IS NOT NULL
AND    t2.c3 IS NOT NULL)

Plan hash value: 629543484

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN RIGHT ANTI|      |      1 |   5925K|   5907K|00:01:20.13 |   17154 |  17135 |    14M|  2366K|   20M (0)|
|*  2 |   TABLE ACCESS FULL  | T2   |      1 |    498K|    500K|00:00:00.50 |    1182 |   1174 |       |       |          |
|   3 |   TABLE ACCESS FULL  | T1   |      1 |   5925K|   5920K|00:00:23.13 |   15972 |  15961 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3")
   2 - filter(("T2"."C1" IS NOT NULL AND "T2"."C2" IS NOT NULL AND "T2"."C3" IS NOT NULL))


22 rows selected.

SQL> truncate table t3 reuse storage;

Table truncated.

SQL>
SQL> INSERT INTO t3
  2    SELECT t1.*
  3    FROM t1 LEFT JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2=t2.c2 AND t1.c3=t2.c3)
  4    WHERE t2.c1 IS NULL;

5907679 rows created.

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));
SQL_ID  a0m9d3nr8d75d, child number 0
-------------------------------------
INSERT INTO t3   SELECT t1.*   FROM t1 LEFT JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2=t2.c2 AND t1.c3=t2.c3)   WHERE t2.c1
IS NULL

Plan hash value: 196153611

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|*  1 |  FILTER                |      |      1 |        |   5907K|00:02:01.28 |   17154 |  17135 |    |  |          |
|*  2 |   HASH JOIN RIGHT OUTER|      |      1 |      1 |   5920K|00:01:35.56 |   17154 |  17135 |    14M|  2366K|   20M (0)|
|   3 |    TABLE ACCESS FULL   | T2   |      1 |    498K|    500K|00:00:00.50 |    1182 |   1174 |    |  |          |
|   4 |    TABLE ACCESS FULL   | T1   |      1 |   5925K|   5920K|00:00:23.30 |   15972 |  15961 |    |  |          |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T2"."C1" IS NULL)
   2 - access("T1"."C3"="T2"."C3" AND "T1"."C2"="T2"."C2" AND "T1"."C1"="T2"."C1")

22 rows selected.

Listing 9

Hinterlasse eine Antwort

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *


*

Du kannst folgende HTML-Tags benutzen: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>