How to Pin a Cursor in the Shared Pool using DBMS_SHARED_POOL.KEEP (文档 ID 726780.1)

In this Document

Goal

Fix

References

---

APPLIES TO:

Oracle Server - Enterprise Edition - Version 8.0.3.0 and later
Oracle Server - Personal Edition - Version 8.0.3.0 and later
Oracle Server - Standard Edition - Version 8.0.3.0 and later
Information in this document applies to any platform.

GOAL

This document shows how to pin a single cursor in the shared pool.

FIX

For various reasons, primarily the re-loading of a cursor due to aging out, it may be necessary to pin an individual cursor in the shared pool.  Thus, prevent the reload, avoid  re-parsing  and the regeneration of the access path for a query etc.
 


A cursor can be pinned using the address and the hash_value of the cursor which can be found in the V$SQL view. To  identify the cursor a select can be used similar to:

select address,hash_value 
from v$sql
where sql_text like '%&IDENTIFIABLE_SQL_TEXT%';

Example output:

ADDRESS HASH_VALUE 
-------- ---------- 
310E6C4C 2745775928

Then the address and hash value to KEEP the cursor in the shared pool as follows:

exec DBMS_SHARED_POOL.KEEP( '310E6C4C,2745775928' , 'C' );

Where:

• 310E6C4C is the ADDRESS from the select in question
• 2745775928 is the HASH_VALUE from the select in question
• 'C' identifier can be any any character except 'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or 'T' or 't'.

So, by way of a worked example, say I have a SQL statement:

SELECT 'Example SQL' FROM dual;

I would need to find something identifiable about this SQL to search on in order to find the address and hash_value.
In this case I have chosen the "Example SQL" phrase , but in your SQL you will likely need to use something else.

Once I have decided some identifier, I issue the following:

SELECT SUBSTR(sql_text,1,30) Text,address,hash_value
FROM v$sql
WHERE sql_text LIKE '%Example SQL%'
/

TEXT                           ADDRESS          HASH_VALUE KEPT_VERSIONS
------------------------------ ---------------- ---------- -------------
SELECT 'Example SQL' FROM dual 0000000391908750 1869363365             0
SELECT SUBSTR(sql_text,1,30) T 000000038B5DBA70  504353775             0

We are interested in HASH_VALUE '1869363365'.
(HASH_VALUE 504353775 is the select from v$sql which is returned as it also contains the string.)
Note that the KEPT_VERSIONS 'flag' is 0 at this point.
If we 'KEEP' the cursor using:

exec DBMS_SHARED_POOL.KEEP('0000000391908750,1869363365','C');

PL/SQL procedure successfully completed.

Then the KEPT_VERSIONS is set to '65' indicating it is being kept:

select substr(sql_text,1,30) Text,address,hash_value,KEPT_VERSIONS
from v$sql
where sql_text like '%Example SQL%'
/

TEXT                           ADDRESS          HASH_VALUE KEPT_VERSIONS
------------------------------ ---------------- ---------- -------------
SELECT 'Example SQL' FROM dual 0000000391908750 1869363365            65
SELECT SUBSTR(sql_text,1,30) T 000000038B5DBA70  504353775             0

If we UNKEEP the Cursor using:

exec DBMS_SHARED_POOL.UNKEEP('0000000391908750,1869363365','C');

PL/SQL procedure successfully completed.

Then the KEPT_VERSIONS is restored:

TEXT                           ADDRESS          HASH_VALUE KEPT_VERSIONS
------------------------------ ---------------- ---------- -------------
SELECT 'Example SQL' FROM dual 0000000391908750 1869363365             0
SELECT SUBSTR(sql_text,1,30) T 000000038B5DBA70  504353775             0

 You can also see the kept status in v$db_object_cache:

 
SELECT substr( name,1,30) NAME, type, kept
 FROM v$db_object_cache
 WHERE  name like '%Example SQL%'
 /

NAME                                   TYPE                                                  KEP
-------------------------------------- ----------------------------------------------------- ---
SELECT 'Example SQL' FROM dual CURSOR                                                        YES
SELECT 'Example SQL' FROM dual CURSOR                                                        YES

For details of the package, see:

Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)
Part Number E25788-04
Chapter  132 DBMS_SHARED_POOL
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_shared_pool.htm#ARPLS055

In order to pin multiple statements in the shared pool, perhaps based upon the number of times a cursor is executed, See:

Document 130699.1 How to Reduce LIBRARY CACHE LATCH Contention with Pin Cursor Procedure

REFERENCES

相关内容

产品

• Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)
• Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Personal Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)
• Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Standard Edition > Generic RDBMS > Database Level Performance Issues (not SQL Tuning)

关键字

CACHE; INVALIDATION; LIBRARY CACHE; SHARED POOL; V$SQL