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.