Using a Procedure to KEEP Cursors Executed> 10 times (文档 ID 130699.1)

APPLIES TO:

Oracle Server - Enterprise Edition - Version 7.0.16.0 to 11.2.0.3 [Release 7.0 to 11.2]
Information in this document applies to any platform.

GOAL

The purpose of this article is to explain to you how to reduce LIBRARY CACHE LATCH contention. How to Reduce LIBRARY CACHE LATCH Contention with Pin Cursor Procedure.

FIX

If an Oracle database is used by applications which use LITERAL SQL

(select * from table where column = 'text')

as well as bind variables

(select * from table where column = :B1)

, there might be a big performance problem. The statements using LITERAL SQL may override central cursors that are used by statements using bind variables.

In extreme cases, this can appear to make the the database hang and you can may see symptoms such as waits for LATCH FREE wait events within the V$SESSION_WAIT view. After a while, the hang situation disappears. During this time several sessions want to execute the overwritten cursor and have to wait until the cursor is rebuilt.

The best way to solve this problem is to change the application so that only SQL statements with bind variables are used. But very often this is not possible. In that case there is one simple method to avoid the problem: Cursors can be pinned in the shared pool.

There is a procedure KEEP within the package DBMS_SHARED_POOL, which can be used for pinning objects in the shared pool. To identify the cursor for this procedure you must know the ADDRESS and the HASH_VALUE, which could be found in V$SQLAREA. There are details for how to do this in:

Document 152679.1 How to Pin SQL Statements in Memory Using DBMS_SHARED_POOL
Document 726780.1 How to Pin a Cursor in the Shared Pool using DBMS_SHARED_POOL.KEEP

You could also do this by creating a procedure to enable you to handle large numbers of cursors more easily.

The following procedure is an example for pinning all cursors, which are executed more than ten times. The best way is to run this procedure periodically, to pin those cursors too, which have not been cached during previous runs.

CREATE OR REPLACE PROCEDURE pincurs AS 
addr_plus_hash varchar2(100); 
cursor c1 is select rawtohex(address) addr,hash_value from v$sqlarea where executions > 10; 

BEGIN 
for C in C1 loop 
addr_plus_hash := c.addr||','||c.hash_value; 
DBMS_SHARED_POOL.KEEP (addr_plus_hash,'C'); 
end loop; 
END pincurs; 
/

 

Note: package DBMS_SHARED_POOL can be created by running ?\rdbms\admin\dbmspool.sql as SYS/Internal

 

相关内容

产品

• 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 - Enterprise Edition > RDBMS > Generic issues-rollback redo logs db creation

关键字

DBMS_SHARED_POOL