Bizarre Plandarstellung mit dbms_xplan.display_cursor

Vor einigen Tagen ist mir aufgefallen, dass in einer Datenbank (11.2.0.3), an deren Wartung ich seit kurzem beteiligt bin, für eine harmlose Nagios-Check-Query mit dbms_xplan.display_cursor höchst merkwürdige Pläne generiert wurden, die die einzelnen Schritte des Ausführungsplans in schier endloser Folge wiederholten. Ein Blick in v$sql_plan zeigte, dass hier tatsächlich extrem viele Plan-Duplikate vorlagen, aus v$sql_shared_cursor war zu ersehen, dass die Begründung der neuen Cursor in der Regel mit OPTIMIZER_MISMATCH oder PX_MISMATCH angegeben war, und aus v$sql ging hervor, dass die meisten dieser Cursor mit dem Flag IS_OBSOLETE=Y gekennzeichnet waren, das die Dokumentation folgendermaßen erläutert: "Indicates whether the cursor has become obsolete (Y) or not (N). This can happen if the number of child cursors is too large."

Ein Blick auf den Ausführungsplan der Query zeigte, dass hier tatsächlich Parallelisierung beim Zugriff auf Dictionary-Objekte im Spiel war, aber das erklärt aus meiner Sicht noch nicht die Häufigkeit der Wiederholungen. Aber immerhin war schnell festzustellen, dass Timur Akhmadeev das Problem der bizarren Plandarstellung bereits 2012 beschrieben hat:

• V$SQL.IS_OBSOLETE: erwähnt die fehlerhafte Plandarstellung und weist darauf hin, dass in solchen Fällen immer höchstens ein "aktiver" Repräsentant pro child_number vorliegt (mit IS_OBSOLETE = 'N') und zusätzlich viele inaktive (IS_OBSOLETE = 'Y').
• Obsolete cursors: reproduziert das Verhalten mit einem einfachen Testfall: "After the point of 100 child cursors per parent, Oracle builds a new parent (V$SQL.ADDRESS holds its address) cursor, marking old parent and child cursors as obsolete. V$SQLAREA handles the situation well, but V$SQL doesn’t and that’s clearly a bug. The threshold point of when to build a new parent cursor is 100 by default and is controlled with a new hidden parameter _cursor_obsolete_threshold."

Damit habe ich die Erklärung für das Anzeigeproblem. Jetzt fehlt mir nur noch eine Erklärung für die Erzeugung so vieler Cursor durch einen regelmäßig durchgeführten Nagios-Check mit einer relativ einfachen statischen SQL-Query.

P.S.: mein nächster Schritt wird die Überarbeitung der Check-Query, die mir ganz grundsätzlich noch nicht so recht gefällt.

P.P.S.: Stefan Koehler hat mich daran erinnert, dass Christian Antognini auch einen Artikel zum Thema (und insbesondere zum Parameter _cursor_obsolete_threshold) geschrieben hat, was mir vermutlich auch schon mal bekannt gewesen ist - zumindest habe ich den Artikel hier verlinkt.

Indizierung für LIKE-Operationen in postgres

Ein interessanter Hinweis von Daniel Westermann im DBI Services Blog: durch die Verwendung der pg_trgm extension ist es in postgres möglich, (GIN oder GiST) Indizes zur Unterstüzung von Like-Einschränkungen mit führendem (oder auch im Vergleichsstring enthaltenen) Platzhalter(n) zu erstellen. Laut Dokumentation gilt:

The pg_trgm module provides GiST and GIN index operator classes that allow you to create an index over a text column for the purpose of very fast similarity searches. These index types support the above-described similarity operators, and additionally support trigram-based index searches for LIKE, ILIKE, ~ and ~* queries. (These indexes do not support equality nor simple comparison operators, so you may need a regular B-tree index too.)

Und zur Erinnerung noch mal die - an gleicher Stelle aufgeführte - Klassifikation für GIN und GiST Indizes: "As a rule of thumb, a GIN index is faster to search than a GiST index, but slower to build or update; so GIN is better suited for static data and GiST for often-updated data." Ich muss mal anfangen, diese Features häufiger einzusetzen, damit ich sie endlich meinem aktiven Wissensbestand hinzufügen kann - anstatt beim Lesen immer nur zu denken: stimmt, das gibt es ja auch noch...

Schlechtere Performance durch semi_to_inner-Transformation in 12c

Jonathan Lewis zeigt in seinem aktuellen Artikel 12c Downgrade einen Fall, in dem der Optimizer in 12c einen deutlich weniger effizienten Plan auswählt als in 11.2.0.4, weil er die Query besser versteht und erkennt, dass darin eine Transformation eines Semi-Joins zu einem Inner-Join möglich ist, was grundsätzlich eine sinnvolle Strategie sein sollte. Allerdings profitiert der semi-join nested loop von der gleichen Optimierung, die auch für das Caching der Ergebnisse skalarer Subqueries verwendet wird - aber nicht für Inner-Joins. Daher wird der Zugriff in 12c ineffektiver: insbesondere, wenn die aus der Driving-Table gelesenen Daten ungeordnet sind, da sich dann das Caching besonders positiv auswirkt.

Performance-Probleme beim Zugriff auf DBA_FREE_SPACE

Das Phänomen ist offenbar relativ bekannt, war mir aber bisher nicht begegnet (oder in Erinnerung geblieben): ein Icinga-Test zur Bestimmung des Füllgrads eines Tablespaces erreichte zuletzt Laufzeiten von über einer Minute, was zu Timeouts und Icinga-Fehlern führte. Schnell zu bestimmen war, dass das eigentliche Problem im Zugriff auf DBA_FREE_SPACE lag. Aber warum reagierte diese Dictionary-View so träge? Zur Prüfung habe ich zunächst einen Blick in die Definition in DBA_VIEWS geworfen, aber den hätte ich mir sparen können, denn ein Plan mit rowsource statistics zeigte die gleichen Informationen - und lieferte darüber hinaus die entscheidenden Details. Hier ein entsprechendes Beispiel, das ich mit 12.1.0.2 erstellt habe, und das sich in diesem Release noch einmal interessanter verhält als in 11.2, wo mir das Phänomen ursprünglich begegnet war:

select /*+ gather_plan_statistics */
       tablespace_name
     , count(*)
  from dba_free_space
 group by tablespace_name

TABLESPACE_NAME                  COUNT(*)
------------------------------ ----------
SYSAUX                                  4
USERS                                 915
SYSTEM                                  1

Abgelaufen: 00:00:16.49

Plan hash value: 2867613348

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |      1 |        |      3 |00:00:17.80 |     828K|    373K|       |       |          |
|   1 |  HASH GROUP BY               |                  |      1 |      2 |      3 |00:00:17.80 |     828K|    373K|  1452K|  1452K|  760K (0)|
|   2 |   VIEW                       | DBA_FREE_SPACE   |      1 |     68 |    920 |00:00:00.03 |     828K|    373K|       |       |          |
|   3 |    UNION-ALL                 |                  |      1 |        |    920 |00:00:00.03 |     828K|    373K|       |       |          |
|   4 |     NESTED LOOPS             |                  |      1 |      1 |      0 |00:00:00.01 |       6 |      2 |       |       |          |
|   5 |      NESTED LOOPS            |                  |      1 |      1 |      0 |00:00:00.01 |       6 |      2 |       |       |          |
|   6 |       INDEX FULL SCAN        | I_FILE2          |      1 |      3 |      3 |00:00:00.01 |       1 |      1 |       |       |          |
|*  7 |       TABLE ACCESS CLUSTER   | FET$             |      3 |      1 |      0 |00:00:00.01 |       5 |      1 |       |       |          |
|*  8 |        INDEX UNIQUE SCAN     | I_TS#            |      3 |      1 |      3 |00:00:00.01 |       2 |      0 |       |       |          |
|*  9 |      TABLE ACCESS CLUSTER    | TS$              |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 10 |       INDEX UNIQUE SCAN      | I_TS#            |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  11 |     NESTED LOOPS             |                  |      1 |      3 |     16 |00:00:00.01 |      19 |      6 |       |       |          |
|  12 |      NESTED LOOPS            |                  |      1 |      3 |     16 |00:00:00.01 |      15 |      6 |       |       |          |
|* 13 |       TABLE ACCESS FULL      | TS$              |      1 |      2 |      3 |00:00:00.01 |       6 |      2 |       |       |          |
|* 14 |       FIXED TABLE FIXED INDEX| X$KTFBFE (ind:1) |      3 |      2 |     16 |00:00:00.01 |       9 |      4 |       |       |          |
|* 15 |      INDEX UNIQUE SCAN       | I_FILE2          |     16 |      1 |     16 |00:00:00.01 |       4 |      0 |       |       |          |
|  16 |     NESTED LOOPS             |                  |      1 |     63 |    904 |00:00:00.17 |     828K|    373K|       |       |          |
|  17 |      NESTED LOOPS            |                  |      1 |    188 |    904 |00:00:00.17 |     828K|    373K|       |       |          |
|* 18 |       HASH JOIN              |                  |      1 |     20 |    102 |00:00:00.01 |      10 |      1 |  1483K|  1483K|  695K (0)|
|* 19 |        TABLE ACCESS FULL     | TS$              |      1 |      2 |      3 |00:00:00.01 |       6 |      0 |       |       |          |
|* 20 |        TABLE ACCESS FULL     | RECYCLEBIN$      |      1 |     24 |    102 |00:00:00.01 |       4 |      1 |       |       |          |
|* 21 |       FIXED TABLE FULL       | X$KTFBUE         |    102 |      9 |    904 |00:00:17.54 |     828K|    373K|       |       |          |
|* 22 |      INDEX UNIQUE SCAN       | I_FILE2          |    904 |      1 |    904 |00:00:00.01 |       4 |      1 |       |       |          |
|  23 |     NESTED LOOPS             |                  |      1 |      1 |      0 |00:00:00.01 |     302 |      2 |       |       |          |
|  24 |      NESTED LOOPS            |                  |      1 |      1 |      0 |00:00:00.01 |     302 |      2 |       |       |          |
|  25 |       MERGE JOIN CARTESIAN   |                  |      1 |     73 |    306 |00:00:00.01 |       5 |      0 |       |       |          |
|  26 |        INDEX FULL SCAN       | I_FILE2          |      1 |      3 |      3 |00:00:00.01 |       1 |      0 |       |       |          |
|  27 |        BUFFER SORT           |                  |      3 |     24 |    306 |00:00:00.01 |       4 |      0 |  6144 |  6144 | 6144  (0)|
|* 28 |         TABLE ACCESS FULL    | RECYCLEBIN$      |      1 |     24 |    102 |00:00:00.01 |       4 |      0 |       |       |          |
|  29 |       TABLE ACCESS CLUSTER   | UET$             |    306 |      1 |      0 |00:00:00.01 |     297 |      2 |       |       |          |
|* 30 |        INDEX UNIQUE SCAN     | I_FILE#_BLOCK#   |    306 |      1 |     91 |00:00:00.01 |     206 |      2 |       |       |          |
|* 31 |      TABLE ACCESS CLUSTER    | TS$              |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 32 |       INDEX UNIQUE SCAN      | I_TS#            |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------

   7 - filter("F"."FILE#"="FI"."RELFILE#")
   8 - access("F"."TS#"="FI"."TS#")
   9 - filter("TS"."BITMAPPED"=0)
  10 - access("TS"."TS#"="F"."TS#")
  13 - filter(("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$")))
  14 - filter(("TS"."TS#"="KTFBFETSN" AND INTERNAL_FUNCTION("CON_ID")))
  15 - access("KTFBFETSN"="FI"."TS#" AND "KTFBFEFNO"="FI"."RELFILE#")
  18 - access("TS"."TS#"="RB"."TS#")
  19 - filter(("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$")))
  20 - filter(("RB"."TS#" IS NOT NULL AND "RB"."FILE#" IS NOT NULL AND "RB"."BLOCK#" IS NOT NULL))
  21 - filter((INTERNAL_FUNCTION("CON_ID") AND "KTFBUESEGBNO"="RB"."BLOCK#" AND "KTFBUESEGFNO"="RB"."FILE#" AND
              "KTFBUESEGTSN"="RB"."TS#"))
  22 - access("RB"."TS#"="FI"."TS#" AND "KTFBUEFNO"="FI"."RELFILE#")
  28 - filter(("RB"."TS#" IS NOT NULL AND "RB"."FILE#" IS NOT NULL AND "RB"."BLOCK#" IS NOT NULL))
  30 - access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")
       filter(("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#"))
  31 - filter("TS"."BITMAPPED"=0)
  32 - access("TS"."TS#"="U"."TS#")

Note
-----
   - this is an adaptive plan

Auffällig sind mehrere Punkte:

• 16 Sekunden sind ziemlich viel für die Ermittlung dieses Ergebnisses.
• die Laufzeit entfällt fast komplett auf eine der vier über UNION ALL verknüpften Teilabfragen.
• in der langsamen Teilabfrage erfolgt ein Zugriff auf RECYCLEBIN$.
• es handelt sich um einen adaptiven Plan.

Im 11.2er System habe ich durch einen Blick in den Recyclebin festgestellt, dass dort tatsächlich mehr als 1500 Varianten einer Hilfstabelle lagen, die regelmäßig gelöscht wurde, ohne dass ein Purge des Objekts erfolgte. Nach Durchführung einer Purge-Operation für die Objekt-Inkarnationen (die leider nicht komplett automatisiert erfolgen kann, weil ein PURGE TABLE [object_name] nur die älteste Inkarnation löscht) läuft die Query wieder in weniger als einer Sekunde und das Problem ist gelöst (zumal die Test-Logik angepasst wurde, die für das Löschen der Tabelle verantwortlich war).

In 12.1.0.2 ist die Lage allerdings eine andere: hier hat der Optimizer nach wenigen Ausführungen genügend Material gesammelt, um die geeignetere Version des adaptiven Plans zu verwenden:

Plan hash value: 2991530507

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |      1 |        |      3 |00:00:00.19 |    8462 |   3669 |       |       |          |
|   1 |  HASH GROUP BY               |                  |      1 |      2 |      3 |00:00:00.19 |    8462 |   3669 |  1452K|  1452K|  740K (0)|
|   2 |   VIEW                       | DBA_FREE_SPACE   |      1 |    922 |    920 |00:00:00.04 |    8462 |   3669 |       |       |          |
|   3 |    UNION-ALL                 |                  |      1 |        |    920 |00:00:00.04 |    8462 |   3669 |       |       |          |
|   4 |     NESTED LOOPS             |                  |      1 |      1 |      0 |00:00:00.01 |       6 |      1 |       |       |          |
|   5 |      NESTED LOOPS            |                  |      1 |      1 |      0 |00:00:00.01 |       6 |      1 |       |       |          |
|   6 |       INDEX FULL SCAN        | I_FILE2          |      1 |      3 |      3 |00:00:00.01 |       1 |      0 |       |       |          |
|*  7 |       TABLE ACCESS CLUSTER   | FET$             |      3 |      1 |      0 |00:00:00.01 |       5 |      1 |       |       |          |
|*  8 |        INDEX UNIQUE SCAN     | I_TS#            |      3 |      1 |      3 |00:00:00.01 |       2 |      0 |       |       |          |
|*  9 |      TABLE ACCESS CLUSTER    | TS$              |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 10 |       INDEX UNIQUE SCAN      | I_TS#            |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  11 |     NESTED LOOPS             |                  |      1 |     16 |     16 |00:00:00.01 |      19 |      7 |       |       |          |
|  12 |      NESTED LOOPS            |                  |      1 |     16 |     16 |00:00:00.01 |      15 |      7 |       |       |          |
|* 13 |       TABLE ACCESS FULL      | TS$              |      1 |      2 |      3 |00:00:00.01 |       6 |      1 |       |       |          |
|* 14 |       FIXED TABLE FIXED INDEX| X$KTFBFE (ind:1) |      3 |     10 |     16 |00:00:00.01 |       9 |      6 |       |       |          |
|* 15 |      INDEX UNIQUE SCAN       | I_FILE2          |     16 |      1 |     16 |00:00:00.01 |       4 |      0 |       |       |          |
|  16 |     NESTED LOOPS             |                  |      1 |    904 |    904 |00:00:08.75 |    8135 |   3658 |       |       |          |
|* 17 |      HASH JOIN               |                  |      1 |    904 |    904 |00:00:08.74 |    8131 |   3656 |  1087K|  1087K| 1288K (0)|
|* 18 |       HASH JOIN              |                  |      1 |    102 |    102 |00:00:00.01 |      10 |      0 |  1483K|  1483K|  913K (0)|
|* 19 |        TABLE ACCESS FULL     | TS$              |      1 |      2 |      3 |00:00:00.01 |       6 |      0 |       |       |          |
|* 20 |        TABLE ACCESS FULL     | RECYCLEBIN$      |      1 |    102 |    102 |00:00:00.01 |       4 |      0 |       |       |          |
|* 21 |       FIXED TABLE FULL       | X$KTFBUE         |      1 |   1000 |   7548 |00:00:00.13 |    8121 |   3656 |       |       |          |
|* 22 |      INDEX UNIQUE SCAN       | I_FILE2          |    904 |      1 |    904 |00:00:00.01 |       4 |      2 |       |       |          |
|  23 |     NESTED LOOPS             |                  |      1 |      1 |      0 |00:00:00.01 |     302 |      3 |       |       |          |
|  24 |      NESTED LOOPS            |                  |      1 |      1 |      0 |00:00:00.01 |     302 |      3 |       |       |          |
|  25 |       MERGE JOIN CARTESIAN   |                  |      1 |    306 |    306 |00:00:00.01 |       5 |      1 |       |       |          |
|  26 |        INDEX FULL SCAN       | I_FILE2          |      1 |      3 |      3 |00:00:00.01 |       1 |      0 |       |       |          |
|  27 |        BUFFER SORT           |                  |      3 |    102 |    306 |00:00:00.01 |       4 |      1 |  6144 |  6144 | 6144  (0)|
|* 28 |         TABLE ACCESS FULL    | RECYCLEBIN$      |      1 |    102 |    102 |00:00:00.01 |       4 |      1 |       |       |          |
|  29 |       TABLE ACCESS CLUSTER   | UET$             |    306 |      1 |      0 |00:00:00.01 |     297 |      2 |       |       |          |
|* 30 |        INDEX UNIQUE SCAN     | I_FILE#_BLOCK#   |    306 |      1 |     91 |00:00:00.01 |     206 |      2 |       |       |          |
|* 31 |      TABLE ACCESS CLUSTER    | TS$              |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 32 |       INDEX UNIQUE SCAN      | I_TS#            |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------

   7 - filter("F"."FILE#"="FI"."RELFILE#")
   8 - access("F"."TS#"="FI"."TS#")
   9 - filter("TS"."BITMAPPED"=0)
  10 - access("TS"."TS#"="F"."TS#")
  13 - filter(("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$")))
  14 - filter(("TS"."TS#"="KTFBFETSN" AND INTERNAL_FUNCTION("CON_ID")))
  15 - access("KTFBFETSN"="FI"."TS#" AND "KTFBFEFNO"="FI"."RELFILE#")
  17 - access("KTFBUESEGTSN"="RB"."TS#" AND "KTFBUESEGFNO"="RB"."FILE#" AND "KTFBUESEGBNO"="RB"."BLOCK#")
  18 - access("TS"."TS#"="RB"."TS#")
  19 - filter(("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$")))
  20 - filter(("RB"."TS#" IS NOT NULL AND "RB"."FILE#" IS NOT NULL AND "RB"."BLOCK#" IS NOT NULL))
  21 - filter(("CON_ID"=0 OR "CON_ID"=3))
  22 - access("RB"."TS#"="FI"."TS#" AND "KTFBUEFNO"="FI"."RELFILE#")
  28 - filter(("RB"."TS#" IS NOT NULL AND "RB"."FILE#" IS NOT NULL AND "RB"."BLOCK#" IS NOT NULL))
  30 - access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")
       filter(("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#"))
  31 - filter("TS"."BITMAPPED"=0)
  32 - access("TS"."TS#"="U"."TS#")

Note
-----
   - statistics feedback used for this statement
   - this is an adaptive plan

In step 17 wird der NESTED LOOPS Join durch einen HASH JOIN ersetzt, so dass der kostspielige Zugriff auf X$KTFBUE nur einmalig erfolgt (außerdem sieht man die durch das statistics feedback hervorgerufenen Cardinality-Korrekturen). Mit dem adaptiven Plan ist das Problem also nach kurzer Zeit keines mehr. Für ältere Releases bleibt aber festzuhalten, dass ein mit vielen Objekten gefüllter Recyclebin negative Auswirkungen auf die Query-Performance für Abfragen gegen DBA_FREE_SPACE hat. Das Problem ist, wie schon erwähnt, nicht unbekannt - nachdem ich mein Ergebnis hatte, habe ich bei Martin Widlake einen Artikel gleichen Inhalts gefunden (und es gibt weitere Artikel ähnlichen Inhalts). Weniger bekannt dürfte aber sein, dass die Schwierigkeit in 12c für alle praktischen Fragen als behoben betrachtet werden kann.

OS-Analyse für Logical I/O-Operationen

Fritz Hoogland, dessen Blog eine der besten Quellen zu I/O-Fragen in Oracle ist, beschäftigt sich in seinem aktuellen Artikel mit der Performance von Logical-I/O-Zugriffen. Ausgangspunkt ist dabei, dass der Kernel aktueller Redhat-Versionen die Möglichkeit bietet, via systemtap die Performance einzelner OS-Routinen zu überprüfen. Aufgrund entsprechender Aussagen des Oracle Supports und eigenen Beobachtungen kann der Autor Consistent Reads der C-Funktion kcbgtcr() zuordnen, während Current reads wohl durch kcbgcur() abgebildet sind. Aufbauend auf diesen Voraussetzungen werden im Artikel diverse OS-Skripte und ihre Ergebnisse vorgestellt, aus denen hervorgeht, dass die Ausführung von kcbgtcr in der Regel sehr wenig Zeit benötigt (1-2 Mikrosekunden), aber in einzelnen Fällen dramatisch langsamer ist (um den Faktor 100K). Mit Hilfe eines (nicht unbedingt minimal invasiven) systemtab-Skripts kann der Herr Hoogland die Ursache der Abweichungen bestimmen: in den langsamen Fällen ruft ein logischer Blockzugriff einen physikalischen Lesezugriff hervor, weil der Block erst in den Cache gelesen werden muss. Vom Ergebnis her ist das nicht unbedingt eine große Überraschung, aber das - hier nur schemenhaft wiedergegebene - Analysevorgehen ist außerordentlich eindrucksvoll. Ich gehe davon aus, dass sich mit ähnlichen Verfahren sehr umfassende Einblicke in die interne Arbeitsweise des Oracle Servers erreichen lassen.

Neue SQL Plan Operationen und Hints in 12c

Sayan Malakshinov hat sich die Mühe gemacht, eine kommentierte Link-Liste zu den in 12c eingeführten SQL PLAN OPERATIONS und Hints zusammenzustellen. Das Ergebnis sieht ausgesprochen vielversprechend aus.

Insert-Sortierung und Index-Größe

Mal wieder ein Titel, der den Eindruck erweckt, dass vielleicht eine größere Präzisierung möglich gewesen wäre - aber da Richard Foote dem Artikel, den ich hier wiedergebe, den Titel Why A Brand New Index Might Benefit From An Immediate Coalesce (One Slip) gegeben hat, fühle ich mich nicht dazu verpflichtet, länger darüber nachzudenken. Worum es geht, ist Folgendes: der Herr Foote zeigt in seinem Blog, dass ein Index auf einer via Insert as Select befüllten Tabelle deutlich größer ist als erforderlich. Die Reihenfolge im Testfall ist dabei:

• Anlage einer Testtabelle mit Index (non-unique) und 1M rows.
• der Index umfasst 2226 leaf blocks.
• Sicherung von 30% der Daten (also 300K rows) in einer Hilfstabelle und Durchführung eines truncate auf der Quelltabelle.
• der Index enthält jetzt keine Einträge.
• Insert der in der Hilfstabelle gesicherten Datensätze in die Ursprungstabelle.
• der Index umfasst 1112 leaf blocks. Also recht genau 50% der initialen Größe.

Die Frage ist somit: warum ist der Index nicht auf 30% der Ursprungsgröße geschrumpft? Ein Blick auf die Index-Struktur zeigt, dass sehr viele Blocks jetzt nur zu 50% gefüllt sind und nicht mehr zu 90%. Ursache sind 50-50-block splits in der Index-Struktur, die sich ergeben, weil die Daten in der Hilfstabelle nicht sortiert vorlagen, sondern (mehr oder minder) willkürlich in freien Blocks untergebracht wurden: der Transfer dieser Sätze in die Ursprungstabelle fügt also nicht nur am Ende der Indexstruktur neue Einträge ein (was zu 90-10-block splits führen würde), sondern ergänzt Einträge mitten in den bereits angelegten Strukturen, was zur 50-50-Teilung der Index-Blocks führt. Um eine kompakte Index-Struktur zu erhalten, wäre eine Sortierung beim Insert as Select notwendig - oder die Verwendung eines Append-Hints, der eine Vorsortierung der eizufügenden Sätze durchführt. Dabei gilt natürlich, dass ein extrem kompakter Index nur unter bestimmten Umständen sinnvoll ist: entweder, wenn die Tabelle statisch ist (also überhaupt keine DML-Operationen erfolgen), oder wenn nur am Ende des Index eingefügt wird und somit in der Folge nur Splits vom Typ 90-10 erfolgen.

I/O-Performance in Datenbanken und auf OS-Seite

Auf Oracle-L hat Stefan Koehler heute einen interessanten Link zum Thema "Kommunikation zwischen DBAs und Storage-Administratoren" untergebracht: im referenzierten Artikel erläutert Bart Sjerps, dass beide Gruppen oft von unterschiedlichen Dingen sprechen, wenn sie über I/O-Performance diskutieren: während die Storage-Admins die reine Service time vor Augen haben - also die Zeit, die die Storage selbst benötigt, um einen einzelnen I/O request zu beantworten (= die von iostat ausgegebene Spalte svctm) -, interessieren sich die DBAs eher für die Average Wait time - also die Wartezeit in der Host I/O queue, die sich als Produkt der iostat-Angaben avgqu-sz (= Average queue size) und await (= Average wait in der queue) ergibt; denn das ist dann auch die Zahl, die in den internen Datenbankstatistiken erscheint (sofern das RDBMS eine entsprechende Instrumentierung besitzt). Dabei sind die Werte der Storage-Admins dann natürlich immer niedriger als die der DBAs und diese Abweichungen können dann Ursache für Missverständnisse sein.