31. Oktober 2008

Einzelne Cursor invalidieren mit DBMS_SHARED_POOL

English title: Purging Cursors from the shared pool with DBMS_SHARED_POOL

Die Tage bekam ich die Frage, ob und wenn ja, wie man denn einen Cursor im Shared Pool invalidieren könne. Es wurde also 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 gecacht. Wenn die gleiche SQL-Abfrage nun nochmals ankommt, wird der gecache Ausführungsplan wiederverwendet. Im Zusammenhang mit Bind Variablen hatten wir das Thema auch schonmal ...
The last days I was asked the question how to invalidate one particular cursor in the shared pool. So there was a SQL query executed, it was (of course) parsed, an execution plan was generated and all this is now cached in the shared pool. When this query comes again the cached information is being used. There was a posting about this regarding the discussion of bind variables.
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, einen SQL-Kommentar in die Abfrage einzubauen - dann ist es ein neuer SQL-Text und die Abfrage passt nicht mehr zur gecachten - sie ist dann tatsächlich "neu".
Sometimes there is the situation where you just don't want the database to use that cache. Now - the easiest way is then to add some comment to the SQL query - the SQL text changes, so you have a new query.
Und wenn man an die Abfrage nicht herankommt? Tja ... dann könnte man ...
  • ... den Shared Pool komplett leeren (alter system flush shared_pool) ...
    ... was aber alle Inhalte löscht, also auch den Buffer Cache.
  • ... die Tabelle ändern (bspw. einen Kommentar hinzufügen) ...
    ... würde aber alle Cursor invalidieren, die mit der Tabelle arbeiten.
But you can't always change the query; if you don't have access to the applications' code you can't change any SQL ... So you might ...
  • ... flush the shared pool completely (alter session flush shared_pool)...
    ... but this elimiates all cached information - including the buffer cache.
  • ... change the table definition (add a table comment) - this invalidates the cursor ...
    ... but it invalidates all other cursors selecting this table also.
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. Getestet habe ich bislang nur auf Oracle11g.
The goal is just to eliminate one particular cursor. And in Oracle11g this is possible with DBMS_SHARED_POOL (Metalink note 457309.1 states that it is also possible with 10.2.0.4 but I tested only on 11g).
Wenn DBMS_SHARED_POOL nicht vorhanden ist, muss es mit dem Skript $ORACLE_HOME/rdbms/admin/dbmspool.sql eingespielt werden.
If DBMS_SHARED_POOL is not present in your database you have to run the script $ORACLE_HOME/rdbms/admin/dbmspool.sql.
Zuerst also ein SQL absetzen - mit dem Kommentar finden wir es später leichter wieder ...
First we issue a SQL query ... the comment makes it easier to find again ...
SQL> select /* SQL 1 !!!*/ sal from scott.emp where empno=7839;

       SAL
----------
      5000
Dann den Cursor in der View V$SQLAREA heraussuchen ...
Then look it up in V$SQLAREA...
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 DBMS_SHARED_POOL.PURGE aufrufen ... Ein Cursor wird durch seine ADDRESS und HASH_VALUE identifiziert.
Now we call DBMS_SHARED_POOL.PURGE - to purge a cursor we identify it by concatenating its ADDRESS and HASH_VALUE.
begin 
  dbms_shared_pool.purge('4158E358 2329752635', 'C');
end;
/

PL/SQL procedure successfully completed.
Nochmal in der V$SQLAREA nachgucken ...
Check in V$SQLAREA...
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 weg ... führen wir das Original-SQL nochmals aus, dann wird es neu geparst.
It's gone. We now want to check further and execute the original query again.
SQL> select /* SQL 1 !!!*/ sal from scott.emp where empno=7839;

       SAL
----------
      5000
Und ein drittes Mal in der V$SQLAREA nachgucken ...
And look into V$SQLAREA - the third time.
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 Invalidation. Mit DBMS_SHARED_POOL kann man aber noch mehr machen. Die SIZES-Prozedur zeigt bspw. an, welche Objekte sich im Shared Pool befinden. Der folgende Aufruf listet alle Objekte, die mehr als 500 Kilobyte im Shared Pool belegen.
Now we have one invalidation - the SQL query was parsed like a new query - the cursor object was infact purged from the shared pool. But DBMS_SHARED_POOL can do more: the SIZES procedure shows the objects currently residing in the shared pool. The following call lists all objects greater than 500 kilobytes.
SQL> set serveroutput on size 2000000
SQL> exec dbms_shared_pool.sizes(500);
SIZE(K) KEPT   NAME
------- ------ ----------------------------------------------
2870 YES    XDB.XDNsht+pGJQ9jgQESYGWQVVg==(XDB)
1717 YES    XDB.XDbD/PLZ01TcHgNAgAIIegtw==(XDB)
1274        XDB.XDNsht+plHQ9jgQESYGWQVVg==(XDB)
1210        XDB.XDNsht+o5NQ9jgQESYGWQVVg==(XDB)
835        XDB.XDNsht+o3tQ9jgQESYGWQVVg==(XDB)
650 YES    SYS.oracle/i18n/text/OraMapTable(JAVA CLASS)
531 YES    SYSMAN.MGMT_JOB_ENGINE        (PACKAGE BODY)

PL/SQL-Prozedur erfolgreich abgeschlossen.
Die Ausgabe via DBMS_OUTPUT ist zwar etwas unübersichtlich (eine Table Function wäre mir lieber gewesen) ... aber immerhin ...
The output via DBMS_OUTPUT is a little bit cumbersome (a table function would fit better, IMHO) - but its better than nothing ...

Kommentare:

Milen Kulev hat gesagt…

Hallo Carsten,

dbms_shared_pool.sizes benutzt intern
V$DB_OBJECT_CACHE view ;)
D.h. Du kannst immer eine eigfenbe SQL Abfrage schareiben, die genau das macht (bezüglich die Formatierung der Ausgabe),
was du willst.

Beispiel:
Bei Absetzen des SQL Statements
exec dbms_shared_pool.sizes(5000);

fürht Oracle im Hintergrund folgende SQL Abfrage aus:

SELECT TO_CHAR(SHARABLE_MEM/1024, '999999') SZ, DECODE(KEPT_VERSIONS, 0, ' ', RPAD('YES(' || TO_CHAR(KEPT_VERSIONS) || ')', 6)) KEEPED, RAWTOHEX(ADDRESS) || ',' || TO_CHAR(HASH_VALUE) NAME, SUBSTR(SQL_TEXT,1,354) EXTRA, 1 ISCURSOR FROM V$SQLAREA WHERE SHARABLE_MEM > :B1 *1024 UNION SELECT TO_CHAR(SHARABLE_MEM/1024, '999999') SZ, DECODE(KEPT, 'YES', 'YES ', ' ') KEEPED, OWNER || '.' || NAME || LPAD(' ', 29 - (LENGTH(OWNER) + LENGTH(NAME))) || '(' || TYPE || ')' NAME, NULL EXTRA, 0 ISCURSOR FROM V$DB_OBJECT_CACHE V WHERE SHARABLE_MEM > :B1 *1024 ORDER BY 1 DESC

:B1 = 5000 (kb).
Das Problem ist, dass es KEIN KEPT='YES'
generiert wird. Das bedeuted, dass Du mittels
"exec dbms_shared_pool.sizes(5000);"
einfach falsche Ergebnisse kriegst ;)

Version : 10.2.0.4 unter Suse10 SP1.

Gruss. Milen

Dirk Fleischmann hat gesagt…

Hallo Carsten,
funktioniert auch unter 10.2.0.4.

muss aber per "alter session set events '5614566 trace name context forever';" freigeschaltet werden.

Beliebte Postings