Oracle RDBMS : Flushing a Single SQL Statement out of the Object Library Cache
来源:互联网 发布:.net文件管理系统源码 编辑:程序博客网 时间:2024/05/18 01:36
It is well known that the entire shared pool can be flushed with a simple ALTER SYSTEM
statement.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;System altered.
What if the execution plan of a single SQL statement has to be invalidated or flushed out of the shared pool so the subsequent query execution forces a hard parse on that SQL statement. Oracle 11g introduced a new procedure calledPURGE
in the DBMS_SHARED_POOL
package to flush a specific object such as a cursor, package, sequence, trigger, .. out of the object library cache.
The syntax for the PURGE
procedure is shown below.
procedure PURGE ( name VARCHAR2, flag CHAR DEFAULT 'P', heaps NUMBER DEFAULT 1)
Explanation for each of the arguments is documented in detail in $ORACLE_HOME/rdbms/admin/dbmspool.sql file.
If a single SQL statement has to be flushed out of the object library cache, the first step is to find the address of the handle and the hash value of the cursor that has to go away.Name of the object [to be purged] is the concatenation of the ADDRESS
andHASH_VALUE
columns from the V$SQLAREA
view. Here is an example:
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';ADDRESS HASH_VALUE---------------- ----------000000085FD77CF0 808321886SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');PL/SQL procedure successfully completed.SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';no rows selectedNote to Oracle 10g R2 Customers
The enhanced DBMS_SHARED_POOL
package with the PURGE
procedure is included in the 10.2.0.4 patchset release.
10.2.0.2 and 10.2.0.3 customers can download and install RDBMS patch 5614566 to get access to these enhancements inDBMS_SHARED_POOL
package.
- Oracle RDBMS : Flushing a Single SQL Statement out of the Object Library Cache
- Oracle SQL with multiple versions in the library cache
- oracle Library Cache Object loaded into SGA
- understanding the Oracle Library Cache Pin WaitEvent
- Dropping multiple SQL Server objects with a single DROP statement
- 053-16 Which statement most accurately describes the implementation of a SQL Access Advisor recommen
- 16 Which statement most accurately describes the implementation of a SQL Access Advisor recommendati
- a bolt out of the blue 晴天霹雳
- a very good guide to makethe Most Out of the SQL Server 2005 Performance Dashboard
- [Javascript] A function works like 'print_r()' in PHP to print out the details of an object for JS debugging
- SQL Error:library routine called out of sequence.
- HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK
- Object-Relational Mapping - Taking the Horror Out of Data Access
- Which statement is true about loading data using the conventional path of SQL*Loader? A.Redo is not
- Determining the Size of a Class Object
- Determining the Size of a Class Object
- Determining the Size of a Class Object
- Determining the Size of a Class Object
- [转]Rolling cURL: PHP并发最佳实践
- 软件架构设计的三个维度 之 面向服务
- 判断富文本是否为空
- 如何使用Spring的HibernateTemplate分页
- 使用ComponentArt Grid的服务器端模板
- Oracle RDBMS : Flushing a Single SQL Statement out of the Object Library Cache
- 通用findMax方法
- ComponentArt Grid用法二 大量数据
- hadoop+zookeeper+hbase安装、配置及应用实例
- log4j.properties 完整版
- 关于DELPHI中出现'BOF或EOF'中有一个是“真”,或者当前的记录已被删除,
- 在Android中实现全屏显示的方法
- ComponentArt Grid用法一 客户端运行模式
- 在windows下压缩的文件,在linux下解压会乱码的解决方案