{"id":92,"date":"2013-04-30T21:51:26","date_gmt":"2013-04-30T19:51:26","guid":{"rendered":"http:\/\/blog.sqlora.com\/de\/?p=92"},"modified":"2013-07-15T13:51:27","modified_gmt":"2013-07-15T11:51:27","slug":"subquery-unnesting-wenn-es-mal-nicht-funktioniert","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/de\/subquery-unnesting-wenn-es-mal-nicht-funktioniert\/","title":{"rendered":"Subquery Unnesting – wenn es mal nicht funktioniert"},"content":{"rendered":"

Eigentlich leistet der CBO bei den „NOT IN“ Subqueries meistens einen guten Job und generiert effektive Ausf\u00fchrungspl\u00e4ne.
\nEs 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.<\/p>\n

Es ging um ein „INSERT AS SELECT“ DML-Statement, das von einem Tag auf den anderen statt wenige Minuten viele Stunden lief.
\nSchauen wir es uns am folgenden Beispiel an:<\/p>\n

\r\nCREATE TABLE t1 \r\n( c1 NUMBER\r\n, c2 NUMBER\r\n, c3 NUMBER\r\n, other_infos VARCHAR2(100) );\r\n\r\nCREATE TABLE t2 (c1 NUMBER, c2 NUMBER, c3 NUMBER);\r\n\r\nINSERT INTO t1 (c1, c2, c3, other_infos)\r\nSELECT c1, c2, c3, min(other_infos) \r\nFROM (\r\nSELECT TRUNC(SYS.DBMS_RANDOM.VALUE(1,25000)) c1\r\n,      TRUNC(SYS.DBMS_RANDOM.VALUE(1,1000)) c2\r\n,      TRUNC(SYS.DBMS_RANDOM.VALUE(1,10)) c3\r\n,      TRUNC(SYS.DBMS_RANDOM.VALUE(0,100)) other_infos\r\nFROM   DUAL\r\nCONNECT BY  LEVEL <= 6000000\r\n) \r\nGROUP BY c1, c2, c3;\r\n\r\nINSERT INTO t2 (c1, c2, c3)\r\nSELECT TRUNC(SYS.DBMS_RANDOM.VALUE(1,25000))\r\n,      TRUNC(SYS.DBMS_RANDOM.VALUE(1,1000))\r\n,      TRUNC(SYS.DBMS_RANDOM.VALUE(1,10))\r\nFROM   DUAL\r\nCONNECT BY  LEVEL <= 500000;\r\n\r\nBEGIN\r\n  DBMS_STATS.GATHER_TABLE_STATS('SQLORA','T1');\r\n  DBMS_STATS.GATHER_TABLE_STATS('SQLORA','T2');\r\nEND;  \r\n\/\r\n<\/pre>\n
Listing 1<\/p>\n
Achten Sie auf die Zeile 9: das INSERT-Statement sorgt daf\u00fcr, das die Kombination C1, C2 und C3 in der T1 eindeutig ist. Die Tabellenstruktur, Datenmenge und Datenverteilung entspricht in etwa der Realit\u00e4t. Das Beispiel ist aber noch nicht komplett. Ich lasse bewusst noch ein St\u00fcck weg, um zu zeigen, wie die problematische Abfrage sich jetzt verh\u00e4lt. Bei der SELECT-Abfrage geht es darum, Datens\u00e4tze aus T1 zu selektieren, die nicht in T2 vorkommen. Dies wird durch eine NOT IN Subquery erreicht.<\/p>\n
\r\nSQL> EXPLAIN PLAN FOR\r\n  2  SELECT t1.*\r\n  3  FROM   t1\r\n  4  WHERE  (t1.c1, t1.c2, t1.c3)\r\n  5         NOT IN (SELECT t2.c1, t2.c2, t2.c3\r\n  6                 FROM   t2);\r\n\r\nExplained.\r\n\r\nSQL>\r\nSQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'BASIC'));\r\n\r\nPLAN_TABLE_OUTPUT\r\n-------------------------------------------------------------------\r\nPlan hash value: 895956251\r\n\r\n-----------------------------------\r\n| Id  | Operation          | Name |\r\n-----------------------------------\r\n|   0 | SELECT STATEMENT   |      |\r\n|   1 |  FILTER            |      |\r\n|   2 |   TABLE ACCESS FULL| T1   |\r\n|   3 |   TABLE ACCESS FULL| T2   |\r\n-----------------------------------\r\n\r\n10 rows selected.\r\n<\/pre>\n
Listing 2<\/p>\n
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\u00fcr jeden Datensatz aus der Tabelle T1 ausgef\u00fchrt wird. Eine Optimierung greift bei FILTER-Operation zu, sodass die Unterabfrage einmal pro Gruppe der Datens\u00e4tze aus T1 mit eindeutigem Join-Kriterium ausgef\u00fchrt 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\u00fcllen der T1 bewusst eindeutig gemacht.<\/p>\n
Damit Unnesting bei NOT IN funktioniert, sollte man in den Versionen vor 11g daf\u00fcr 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<\/a> gut beschrieben, wir betrachten sie hier nicht weiter.<\/p>\n
Wir schlie\u00dfen die NULLs f\u00fcr die T1 aus, indem wir einen Primary Key Constraint anlegen und f\u00fcr die T2 f\u00fcgen wir die entsprechende WHERE-Bedingung direkt in die Abfrage ein.<\/p>\n
\r\nALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (c1, c2, c3);\r\n\r\nSQL> EXPLAIN PLAN FOR\r\n  2  SELECT t1.*\r\n  3  FROM   t1\r\n  4  WHERE  (t1.c1, t1.c2, t1.c3)\r\n  5         NOT IN (SELECT t2.c1, t2.c2, t2.c3\r\n  6                 FROM   t2\r\n  7                 WHERE  t2.c1 IS NOT NULL\r\n  8                 AND    t2.c2 IS NOT NULL\r\n  9                 AND    t2.c3 IS NOT NULL);\r\n\r\nExplained.\r\n\r\nSQL>\r\nSQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'BASIC'));\r\n\r\nPLAN_TABLE_OUTPUT\r\n-------------------------------------------------------------------\r\nPlan hash value: 629543484\r\n\r\n-------------------------------------\r\n| Id  | Operation            | Name |\r\n-------------------------------------\r\n|   0 | SELECT STATEMENT     |      |\r\n|   1 |  HASH JOIN RIGHT ANTI|      |\r\n|   2 |   TABLE ACCESS FULL  | T2   |\r\n|   3 |   TABLE ACCESS FULL  | T1   |\r\n-------------------------------------\r\n\r\n10 rows selected.\r\n<\/pre>\n
Listing 3<\/p>\n
Und nun ist das Beispiel komplett. Das war der Stand der Dinge vor dem Auftreten der Performance-Probleme. <\/p>\n
Performance-Einbruch<\/strong><\/p>\n
Am Tag X stand im Ausf\u00fchrungsplan eine FILTER-Operation, wie im Listing 2. Die Laufzeiten schossen in die H\u00f6he und der Prozess wurde immer h\u00e4ndisch 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\u00e4ndern und der Fix darf auch z\u00fcgig produktiv gestellt werden. Also eine leichte Aufgabe?<\/p>\n
In der Entwicklungs- und Test-DB ist der Ausf\u00fchrungsplan noch in Ordnung. Also mit Stored Outlines versuchen? Hat nichts gebracht.<\/p>\n
Die Voraussetzungen f\u00fcr einen Anti-Join sind ja nach wie vor da. Soll man mit einem Hint dem CBO nachhelfen? Etwa so:<\/p>\n
\r\nSELECT t1.*\r\nFROM   t1\r\nWHERE  (t1.c1, t1.c2, t1.c3)\r\n        NOT IN (SELECT \/*+ HASH_AJ *\/ t2.c1, t2.c2, t2.c3\r\n                FROM   t2\r\n                WHERE  t2.c1 IS NOT NULL\r\n                AND    t2.c2 IS NOT NULL\r\n                AND    t2.c3 IS NOT NULL);\r\n<\/pre>\n
Listing 4<\/p>\n
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\u00fchrungsplan von dem SQL immer entsprechend ge\u00e4ndert hat. Nur die produktive DB war immer noch gegen alle Hints v\u00f6llig resistent!<\/p>\n
Um zu verstehen, was da abgeht, blieb nichts anderes, als CBO es selber erkl\u00e4ren zu lassen – \u00fcber trace event 10053.<\/p>\n
\r\nSQL> SELECT VALUE FROM V$PARAMETER WHERE UPPER(NAME)='USER_DUMP_DEST';\r\n\r\nVALUE\r\n----------------------------------------------------------------------\r\n\/u00\/app\/oracle\/admin\/LLBE10\/udump\r\n\r\nSQL>\r\nSQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='MY_NOT_IN_TRACE';\r\n\r\nSession altered.\r\n\r\nSQL>\r\nSQL> ALTER SESSION SET EVENTS \r\n        '10053 trace name context forever, level 2';\r\n\r\nSession altered.\r\n\r\nSQL>\r\nSQL> EXPLAIN PLAN FOR\r\n  2    SELECT t1.*\r\n  3    FROM   t1\r\n  4    WHERE  (t1.c1, t1.c2, t1.c3)\r\n  5           NOT IN (SELECT t2.c1, t2.c2, t2.c3\r\n  6                   FROM   t2\r\n  7                   WHERE  t2.c1 IS NOT NULL\r\n  8                   AND    t2.c2 IS NOT NULL\r\n  9                   AND    t2.c3 IS NOT NULL);\r\n\r\nExplained.\r\n\r\nSQL>\r\nSQL> ALTER SESSION SET EVENTS '10053 trace name context off';\r\n\r\nSession altered.\r\n<\/pre>\n
Listing 5<\/p>\n
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.<\/p>\n
\r\n*****************************\r\nCost-Based Subquery Unnesting\r\n*****************************\r\nSU: No subqueries to consider in query block SEL$2 (#2).\r\nSU: Considering subquery unnesting in query block SEL$1 (#1)\r\nSU: Performing unnesting that does not require costing.\r\nSU: Considering subquery unnest on SEL$1 (#1).\r\nSU:   Checking validity of unnesting subquery SEL$2 (#2)\r\nSU:   Passed validity checks.\r\nSU:   Transform an NOT IN subquery to an anti-join. \r\n<\/pre>\n
Listing 6: Test-System – alles gut<\/p>\n
\r\n*****************************\r\nCost-Based Subquery Unnesting\r\n*****************************\r\nSU: No subqueries to consider in query block SEL$2 (#2).\r\nSU: Considering subquery unnesting in query block SEL$1 (#1)\r\nSU: Performing unnesting that does not require costing.\r\nSU: Considering subquery unnest on SEL$1 (#1).\r\nSU:   Checking validity of unnesting subquery SEL$2 (#2)\r\nSU:     SU bypassed: No correlation to immediate outer subquery.\r\nSU:   Validity checks failed.\r\n*******************************\r\n<\/pre>\n
Listing 7: Produktion – Unnesting findet nicht statt<\/p>\n
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\u00fcr unm\u00f6glich gehalten hat. Warum? Ich habe nach dieser Erkenntnis  noch einmal die Tabellenstruktur, Primary-Key auf T1 (enabled), etc. \u00fcberpr\u00fcft. Alles gleich… Nun war zumindest schon mal klar, warum die Hints und die Outlines ignoriert wurden: der CBO hielt die Transformation, bei der sie \u00fcberhaupt etwas bewirken konnten, f\u00fcr unm\u00f6glich.<\/p>\n
Es klingt so alles nach einem Bug. Es w\u00e4re 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 \u00e4ndern, 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:<\/p>\n
\r\nSQL> EXPLAIN PLAN FOR\r\n  2    SELECT t1.*\r\n  3    FROM t1 LEFT JOIN t2 \r\n           ON (t1.c1 = t2.c1 AND t1.c2 = t2.c2 AND t1.c3 = t2.c3)\r\n  4    WHERE t2.c1 IS NULL;\r\n\r\nExplained.\r\n\r\nSQL>\r\nSQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'BASIC'));\r\n\r\nPLAN_TABLE_OUTPUT\r\n---------------------------------------------------------------------\r\nPlan hash value: 196153611\r\n\r\n---------------------------------------\r\n| Id  | Operation              | Name |\r\n---------------------------------------\r\n|   0 | SELECT STATEMENT       |      |\r\n|   1 |  FILTER                |      |\r\n|   2 |   HASH JOIN RIGHT OUTER|      |\r\n|   3 |    TABLE ACCESS FULL   | T2   |\r\n|   4 |    TABLE ACCESS FULL   | T1   |\r\n---------------------------------------\r\n<\/pre>\n
Listing 8<\/p>\n
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\u00e4lt.<\/p>\n
\r\nSQL> INSERT INTO t3\r\n  2  SELECT t1.*\r\n  3  FROM   t1\r\n  4  WHERE  (t1.c1, t1.c2, t1.c3)\r\n  5         NOT IN (SELECT t2.c1, t2.c2, t2.c3\r\n  6                 FROM   t2\r\n  7                 WHERE  t2.c1 IS NOT NULL\r\n  8                 AND    t2.c2 IS NOT NULL\r\n  9                 AND    t2.c3 IS NOT NULL);\r\n\r\n5907679 rows created.\r\n\r\nSQL>\r\nSQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));\r\nSQL_ID  13fhw1c4kbkcc, child number 0\r\n-------------------------------------\r\nINSERT INTO t3 SELECT t1.* FROM   t1 WHERE  (t1.c1, t1.c2, t1.c3)        NOT IN (SELECT t2.c1, t2.c2, t2.c3\r\n        FROM   t2                WHERE  t2.c1 IS NOT NULL                AND    t2.c2 IS NOT NULL\r\nAND    t2.c3 IS NOT NULL)\r\n\r\nPlan hash value: 629543484\r\n\r\n---------------------------------------------------------------------------------------------------------------------------\r\n| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |\r\n---------------------------------------------------------------------------------------------------------------------------\r\n|*  1 |  HASH JOIN RIGHT ANTI|      |      1 |   5925K|   5907K|00:01:20.13 |   17154 |  17135 |    14M|  2366K|   20M (0)|\r\n|*  2 |   TABLE ACCESS FULL  | T2   |      1 |    498K|    500K|00:00:00.50 |    1182 |   1174 |       |       |          |\r\n|   3 |   TABLE ACCESS FULL  | T1   |      1 |   5925K|   5920K|00:00:23.13 |   15972 |  15961 |       |       |          |\r\n---------------------------------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   1 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3")\r\n   2 - filter(("T2"."C1" IS NOT NULL AND "T2"."C2" IS NOT NULL AND "T2"."C3" IS NOT NULL))\r\n\r\n\r\n22 rows selected.\r\n\r\nSQL> truncate table t3 reuse storage;\r\n\r\nTable truncated.\r\n\r\nSQL>\r\nSQL> INSERT INTO t3\r\n  2    SELECT t1.*\r\n  3    FROM t1 LEFT JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2=t2.c2 AND t1.c3=t2.c3)\r\n  4    WHERE t2.c1 IS NULL;\r\n\r\n5907679 rows created.\r\n\r\nSQL>\r\nSQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));\r\nSQL_ID  a0m9d3nr8d75d, child number 0\r\n-------------------------------------\r\nINSERT 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\r\nIS NULL\r\n\r\nPlan hash value: 196153611\r\n\r\n-----------------------------------------------------------------------------------------------------------------------------\r\n| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |\r\n-----------------------------------------------------------------------------------------------------------------------------\r\n|*  1 |  FILTER                |      |      1 |        |   5907K|00:02:01.28 |   17154 |  17135 |    |  |          |\r\n|*  2 |   HASH JOIN RIGHT OUTER|      |      1 |      1 |   5920K|00:01:35.56 |   17154 |  17135 |    14M|  2366K|   20M (0)|\r\n|   3 |    TABLE ACCESS FULL   | T2   |      1 |    498K|    500K|00:00:00.50 |    1182 |   1174 |    |  |          |\r\n|   4 |    TABLE ACCESS FULL   | T1   |      1 |   5925K|   5920K|00:00:23.30 |   15972 |  15961 |    |  |          |\r\n-----------------------------------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   1 - filter("T2"."C1" IS NULL)\r\n   2 - access("T1"."C3"="T2"."C3" AND "T1"."C2"="T2"."C2" AND "T1"."C1"="T2"."C1")\r\n\r\n22 rows selected.\r\n<\/pre>\n
Listing 9<\/p>\n","protected":false},"excerpt":{"rendered":"
Eigentlich leistet der CBO bei den „NOT IN“ Subqueries meistens einen guten Job und generiert effektive Ausf\u00fchrungspl\u00e4ne. 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.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11,3,4,22],"tags":[16,14,12,6,38,13,23,15],"class_list":["post-92","post","type-post","status-publish","format-standard","hentry","category-cbo","category-oracle","category-sql","category-trivadis","tag-16","tag-anti-join","tag-hash_aj","tag-hint","tag-sql","tag-subquery","tag-trivadiscontent","tag-unnesting"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/92","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/comments?post=92"}],"version-history":[{"count":57,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/92\/revisions"}],"predecessor-version":[{"id":183,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/posts\/92\/revisions\/183"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/media?parent=92"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/categories?post=92"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/de\/wp-json\/wp\/v2\/tags?post=92"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}