Using a Procedure to KEEP Cursors Executed> 10 times (文档 ID 130699.1)
来源:互联网 发布:sql%20server%202008 编辑:程序博客网 时间:2024/06/05 11:19
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 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;
/
- 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
- Using a Procedure to KEEP Cursors Executed> 10 times (文档 ID 130699.1)
- How to Pin a Cursor in the Shared Pool using DBMS_SHARED_POOL.KEEP (文档 ID 726780.1)
- How to Convert 10g Single-Instance database to 10g RAC using Manual Conversion procedure (文档 ID 7474
- Enhancement to obsolete parent cursors if VERSION_COUNT exceeds a threshold (文档 ID 10187168.8)
- How to call an Oracle Stored Procedure that returns one or more REF CURSORS, using ADO from C++ How
- How to Convert 10g Single-Instance database to 10g RAC using Manual Conversion procedure [ID 747457.
- How to Perform A Miscellaneous Transaction Using An API (文档 ID 2078693.1)
- How to disable the scheduler using SCHEDULER_DISABLED attribute in 10g (文档 ID 1491941.1)
- How to disable the scheduler using SCHEDULER_DISABLED attribute in 10g (文档 ID 1491941.1)
- Monitoring Open Cursors & Troubleshooting ORA-1000 Errors (文档 ID 76684.1)
- What a heart to keep?
- Using RenderTexture to render one sprite multiple times
- How to Convert 10g Single-Instance database to 10g RAC using Manual Conversion procedure
- How to Convert 10g Single-Instance database to 10g RAC using Manual Conversion procedure
- How to Recreate a Controlfile (文档 ID 735106.1)
- How To Generate A Wallet Containing A Self Signed Certificate Using ORAPKI [ID 560982.1]
- How to Enable SQL_TRACE for Another Session or in MTS Using Oradebug(文档 ID 1058210.6)
- I begin to keep a daily
- [Leetcode] Partition List (Java)
- iOS push
- 怎么复制DataTable过滤部分数据到另一个DataTable
- 最近新发现的歌谱排版软件Lilypond
- HBase中的RIT机制(Region in Transcation)
- Using a Procedure to KEEP Cursors Executed> 10 times (文档 ID 130699.1)
- xendesktop 7 How to Make StoreFront the Default Page within IIS
- 广州传智播客—自学.Net怎么样?
- C语言union(联合体 共用体)
- Android OTA 升级之一:编译升级包
- OpenCV对感兴趣区域(ROI)的操作
- android平台下使用点九PNG技术
- 赊购货税款暂欠会计分录处理
- thoughtworks面试总结