Einzelne Cursor invalidieren mit DBMS_SHARED_POOL
von Carsten Czarski und Ralf Durben, ORACLE Deutschland GmbH

Manchmal stellt sich dem Datenbankadministrator die Frage, ob und wenn ja, wie man denn einen Cursor im Shared Pool invalidieren könne. Diese Frage ergibt sich meistens aus folgendem Grund:

Es wurde eine SQL-Abfrage in der Datenbank ausgeführt. Diese wurde (natürlich) geparst, ein Ausführungsplan wurde erstellt und diese Daten sind nun im Shared Pool gecached. Wenn die gleiche SQL-Abfrage nun nochmals ausgeführt wird, wird der gecachte Ausführungsplan wiederverwendet. Nun tritt ab und zu die Situation auf (Tuning-Maßnahmen), dass man diesen Cursor eben nicht nutzen möchte. Obwohl die SQL-Abfrage "schon da" ist, möchte man, dass sie neu geparst, also ein neuer Ausführungsplan erstellt wird. Der erste (und einfachste Ansatz) wäre die Veränderung des Abfragecodes, zum Beispiel durch einen SQL-Kommentar. Dann ist es ein neuer SQL-Text und die Abfrage passt nicht mehr zur gecachten Version, ist dann also tatsächlich "neu". Nur ist das ändern des SQL-Codes nicht elegant und meistens nicht möglich, da von einer Anwendung kommend.

Es gibt bis Oracle Datenbank 10g einschließlich die folgenden Möglichkeiten zum Invalidieren von Cursor:

  • den Shared Pool komplett leeren (alter system flush shared_pool)
    das leert aber leider den kompletten Shared Pool
  • die Tabelle ändern (bspw. einen Kommentar hinzufügen)
    würde aber alle Cursor invalidieren, die mit der Tabelle arbeiten.
Eigentlich soll ja nur der einzelne Cursor aus der SGA elimiert werden. Und das geht in Oracle11g (und lt. Metalink Note 457309.1 auch in 10.2.0.4) mit dem PL/SQL-Paket DBMS_SHARED_POOL. Wenn DBMS_SHARED_POOL nicht vorhanden ist, muss es mit dem Skript $ORACLE_HOME/rdbms/admin/dbmspool.sql eingespielt werden.

Im folgenden sei die Nutzung an einem Beispiel gezeigt:

Zunächst wird ein SQL ausgeführt - mit dem Kommentar findet man es später leichter wieder. 
SQL> select /* SQL 1 !!!*/ sal from scott.emp where empno=7839;

       SAL
----------
      5000
Dann muss man den Cursor in der View V$SQLAREA heraussuchen... 
SQL> select address, hash_value, executions, invalidations, parse_calls, sql_text from v$sqlarea where sql_text like 'select /* SQL 1 !!!*/%';

ADDRESS  HASH_VALUE EXECUTIONS INVALIDATIONS PARSE_CALLS SQL_TEXT
-------- ---------- ---------- ------------- ----------- --------------------------------------------------
4158E358 2329752635          1             0           1 select /* SQL 1 !!!*/ sal from scott.emp where
                                                         empno=7839
Nun wird DBMS_SHARED_POOL.PURGE aufgerufen. Ein Cursor wird durch seine ADDRESS und HASH_VALUE identifiziert. 
begin 
  dbms_shared_pool.purge('4158E358 2329752635', 'C');
end;
/
PL/SQL procedure successfully completed.
Jetzt wird der Status des SQL in V$SQLAREA geprüft... 
SQL>  select address, hash_value, executions, invalidations, parse_calls, sql_text from v$sqlarea where sql_text like 'select /* SQL 1 !!!*/%';

no rows selected
Der Cursor ist also verschwunden. Nun wird das Original-SQL nochmals ausgeführt - es wird neu geparst. 
SQL> select /* SQL 1 !!!*/ sal from scott.emp where empno=7839;

       SAL
----------
      5000
Jetzt wird wieder der Status des SQL in V$SQLAREA geprüft... 
SQL> select address, hash_value, executions, invalidations, parse_calls, sql_text from v$sqlarea where sql_text like 'select /* SQL 1 !!!*/%';

ADDRESS  HASH_VALUE EXECUTIONS INVALIDATIONS PARSE_CALLS SQL_TEXT
-------- ---------- ---------- ------------- ----------- --------------------------------------------------
4158E358 2329752635          1             1           1 select /* SQL 1 !!!*/ sal from scott.emp where
                                                         empno=7839
Wir haben nun eine Invalidierung.

Zurück zur Community-Seite