使用DBMS_SHARED_POOL包将对象固定到共享池

来源：这里教程网时间：2026-03-03 16:37:59 作者：

-****************************************** -- 使用DBMS_SHARED_POOL包将对象固定到共享池 --****************************************** DBMS_SHARED_POOL包提供存储过程来将PL/SQL对象或SQL游标固定到Oracle 共享池。一旦这些对象固定之后，将不再参与aged out,而是常驻内存，即便是使用alter system flush shared_pool也不会将对象清除出共享池。对于一些大值对象装载进共享池时容易引发两种类型的问题： ORA-04031 errors 由于没有足够的内存引发该类似的错误为大值对像寻找可用的空间而引发系统性能下降将大值对象在实例启动时装载进共享池可以避免上述问题。对于已经固定在内存中的包，在关闭数据库之前，该对象会被一直保留，不会清除或失效。需要访问DBMS_SHARED_POOL这个包的任何用户都必须由SYS授予执行权限。如果在SYS模式中创建的包并在不同的模式中运行示例代码，则首先必须给运行示例(即TEST)的用户授予EXECUTE_CATALOG_ROLE 角色且在DBMS_SHARED_POOL上给TEST以EXECUTE权限，然后需要在SYS.DBMS_SHARED_POOL.KEEP中完全地限定这个包，因为dbmspool.sql 脚本并不为这个包创建公有同义词。一、安装(DBMS_SHARED_POOL缺省并没有随系统安装) 要使用这个过程，首先必须运行DBMSPOOL.SQL脚本。在启动DBMSPOOL.SQL脚本后，PRVTPOOL.PLB脚本将自动执行。这些脚本不能使用CATPROC.SQL来运行。 1.查看版本信息 SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production 2.以sys帐户安装DBMS_SHARED_POOL包 SQL> show user; USER is "SYS" SQL> @?/rdbms/admin/dbmspool.sql Package created. Grant succeeded. View created. Package body created. 3.查看包包含的存储过程 SQL> desc dbms_shared_pool PROCEDURE ABORTED_REQUEST_THRESHOLD Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- THRESHOLD_SIZE NUMBER IN PROCEDURE KEEP Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN FLAG CHAR IN DEFAULT PROCEDURE PURGE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN FLAG CHAR IN DEFAULT HEAPS NUMBER IN DEFAULT PROCEDURE SIZES Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- MINSIZE NUMBER IN PROCEDURE UNKEEP Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN FLAG CHAR IN DEFAULT 二、DBMS_SHARED_POOL包的使用 1.DBMS_SHARED_POOL.KEEP 存储过程该过程用于将对象固定到共享池 PROCEDURE DBMS_SHARED_POOL.KEEP (name IN VARCHAR2 ,flag IN CHAR DEFAULT 'P'); Flag标志 Description ---------- -------------- C cursor JC java class JD java shared data JR java resource JS java source P Package, procedure, or function name Q sequence R trigger T type Any other character Cursor specified by address and hash value e.g. exec sys.dbms_shared_pool.keep('SYS.STANDARD'); exec sys.dbms_shared_pool.keep('scott.tri_test','T') 2.DBMS_SHARED_POOL.UNKEEP 存储过程从过程的描述即可以知道，该过程用于将对象从清出保留池 e.g. exec sys.dbms_shared_pool.unkeep('SYS.STANDARD','P') 3.DBMS_SHARED_POOL.SIZES 存储过程该过程显示在共享池中超过指定值大小的对象，包括游标以及匿名的PL/SQL块。(指定值的大小的单位为kbytes) PROCEDURE DBMS_SHARED_POOL.SIZES (minsize IN NUMBER); e.g. execute sys.dbms_shared_pool.sizes(70); 4.ABORTED_REQUEST_THRESHOLD存储过程该过程可以设定一个阙值尺寸，当该阙值被设定后，一个大于该设定值的对象被装载到共享池时，在共享池没有足够的空间，且设置了Oracle动态清空未固定在内存的对象，可以避免该类事件的发生。但是将收到一个错误ORA-4031，而不会清空共享池为该对象腾出空间。该值在5000 - 2147483647之间，该阙值的设定可以避免由于共享池空间压力而导致的系统性能下降，但同时导致了ORA-4031错误的机率。DBA也可以根据ORA-4031 错误来将特定的大值对象固定了保留池。 PROCEDURE DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD (threshold_size IN NUMBER); execute SYS.DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD(50000); 三、将对象自动固定到保留池方案将对象固定到保留池的最佳时间是Oracle实例首次启动之后，因此此时共享池空闲空间较多，且几乎没有内存碎片。下面创建一张表以及一个存储过程用于来实现实例自动启动后将大值对象固定到保留池 1.首先创建一张表，用于保存需要pin到保留池的对象 CREATE TABLE keep_objects (obj_schema VARCHAR2(30) NOT NULL , obj_name VARCHAR2(30) NOT NULL , CONSTRAINT ko_PK PRIMARY KEY (obj_schema, obj_name) ) TABLESPACE USERS STORAGE (INITIAL 2 NEXT 2 PCTINCREASE 0); 2.创建存储过程用于将对象pin到保留池 CREATE OR REPLACE PROCEDURE object_keeper --Procedure to pin objects into the shared pool --using DBMS_SHARED_POOL.KEEP procedure. All --objects found in the keep_objects table will be KEEPed. --For best results, procedure should be created in the SYS schema. --Author: John Beresniewicz, Savant Corp --Created: 09/18/97 -- Compilation Requirements: --注意权限问题 --SELECT on SYS.DBA_OBJECTS || EXECUTE on SYS.DBMS_SHARED_POOL || --Execution Requirements: --Some SYS objects may get ORA-1031 unless the procedure is run by SYS IS CURSOR keep_objects_cur IS SELECT do.owner || '.' || do.object_name OBJECT ,decode(do.object_type, 'PACKAGE' , 'P', 'PROCEDURE' ,'P', 'FUNCTION' ,'P', 'TRIGGER' ,'R', NULL) TYPE FROM keep_objects ko, dba_objects do WHERE upper(ko.obj_schema) = do.owner AND upper(ko.obj_name) = do.object_name AND do.object_type IN ('PACKAGE', 'PROCEDURE', 'FUNCTION', 'TRIGGER'); BEGIN FOR ko_rec IN keep_objects_cur LOOP BEGIN sys.dbms_shared_pool.keep(ko_rec.object, ko_rec.type); dbms_output.put_line('KEPT: ' || ko_rec.object); EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM); dbms_output.put_line('KEEP FAIL: ' || ko_rec.object || ' ' || ko_rec.type); END; END LOOP; END object_keeper; / 3.创建触发器用于实例启动后将对象pin到保留池(提示，先应当寻找需要pin住的对象且将其插入到表keep_objects中) CREATE OR REPLACE TRIGGER tr_object_keeper AFTER startup ON DATABASE BEGIN sys.object_keeper; END; / 四、使频繁的大值对象常驻共享池 1.首先寻找需要常驻共享池的对象 SELECT * FROM v$db_object_cache WHERE sharable_mem > 10000 /*此参数为占住内存的大小，可自行设定大小*/ AND (TYPE='PACKAGE' OR TYPE='PACKAGE BODY' OR TYPE='FUNCTION' OR TYPE='PROCEDURE') AND kept='NO'; 2.将对象常驻内存使用包dbms_shared_pool.keep将这些对象常驻内存,尽可能在实例启动后实施操作,因为此时内存比较空闲，不会因为内存不足导致aged out。 EXECUTE dbms_shared_pool.keep('package_name'); 3.将SQL语句常驻内存对于单独的SQL语句，且被经常使用，同样可以将其常驻内存。此时，需要得到SQL语句的hash值，我们可以通过$sqlarea里的address和hash_value列获得 SQL> select count(*) from all_objects; COUNT(1) -------- 40793 SQL> select address,hash_value,sql_text from v$sqlarea where sql_text='select count(*) from all_objects'; ADDRESS HASH_VALUE SQL_TEXT -------- --------------- ---------------------------------------- 2D33FF58 789896629 select count(*) from all_objects SQL> exec sys.dbms_shared_pool.keep('2D33FF58,789896629','C'); PL/SQL procedure successfully completed. 如果我们要取消固定到内存的话，则调用DBMS_SHARED_POOL.UNKEEP即可，该过程的参数与KEEP相同。 4.清空share pool的命令(如果在使用包keep对象没有可用空间时，可以flush shared_pool) ALTER SYSTEM FLUSH SHARED_POOL --此操作不会清除常驻内存的对象 5.查看当前已经常驻内存的对象 select * from v$db_object_cache where kept='YES' 6.寻找较大匿名的PL/SQL 块将其分割为小的PL/SQL块，以提高共享池的利用率 SELECT sql_text FROM v$sqlarea WHERE command_type=47 AND LENGTH(sql_text)>500; 五、下列标准的系统包建议将其pin到保留池通常下列两种情形将对象固定在保留池 1.频繁使用的包应 -->这些对象固定在SGA中将大大提高性能 2.一些Oracle的标准包 -->避免过多的硬解析 DBMS_ALERT DBMS_DESCRIBE DBMS_DDL DBMS_LOCK DBMS_OUTPUT DBMS_PIPE DBMS_SESSION DBMS_SHARED_POOL DBMS_STANDARD DBMS_UTILITY STANDARD 六、实战演练 1.以sys as sysdba帐户安装DBMS_SHARED_POOL包 2.创建用户并授予权限 CREATE USER tester IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE PROCEDURE, EXECUTE_CATALOG_ROLE TO tester; GRANT EXECUTE ON DBMS_SHARED_POOL TO tester; 3.以tester身份创建过程 sys@ORCL> conn tester/password Connected. tester@ORCL> CREATE OR REPLACE PROCEDURE p1 AS 2 BEGIN 3 NULL; 4 END p1; 5 / Procedure created. tester@ORCL> BEGIN 2 SYS.DBMS_SHARED_POOL.KEEP('P1','P'); 3 END; 4 / PL/SQL procedure successfully completed. 4.以sys身份查询当前pin住的对象 sys@ORCL> set linesize 180 sys@ORCL> col owner format a20 sys@ORCL> col name format a40 sys@ORCL> col type format a15 sys@ORCL> col namespace format a30 sys@ORCL> select owner,name,type,namespace from v$db_object_cache 2 where kept='YES' and type!='INVALID TYPE' and owner='TESTER'; OWNER NAME TYPE NAMESPACE -------------------- ---------------------------------------- --------------- ------------------------------ TESTER P1 PROCEDURE TABLE/PROCEDURE 5.使用alter system flush shared_pool清空共享池，从下面的查询中可知，被pin住的对像并没有被aged out。 sys@ORCL> alter system flush shared_pool; System altered. sys@ORCL> select owner,name,type,namespace from v$db_object_cache 2 where kept='YES' and type!='INVALID TYPE' and owner='TESTER'; OWNER NAME TYPE NAMESPACE -------------------- ---------------------------------------- --------------- ------------------------------ TESTER P1 PROCEDURE TABLE/PROCEDURE 6.使用DBMS_SHARED_POOL.SIZES显示超出指定大小的对象 sys@ORCL> execute sys.dbms_shared_pool.sizes(70) SIZE(K) KEPT NAME ------- ------ --------------------------------------------------------------- 429 YES SYS.STANDARD (PACKAGE) 388 SYS.DBMS_RCVMAN (PACKAGE BODY) 258 SYS.DBMS_BACKUP_RESTORE (PACKAGE) 239 SYS.DBMS_RCVMAN (PACKAGE) 149 YES SYS.DBMS_SQL (PACKAGE) 95 SYS.DBMS_BACKUP_RESTORE (PACKAGE BODY) PL/SQL procedure successfully completed. 7.使用DBMS_SHARED_POOL.UNKEEP存储过程将对象aged out. sys@ORCL> exec sys.dbms_shared_pool.unkeep('TESTER.P1','P') PL/SQL procedure successfully completed. sys@ORCL> select owner,name,type,namespace from v$db_object_cache 2 where kept='YES' and type!='INVALID TYPE' and owner='TESTER'; no rows selected 8.查询当前library cache中pin住的对象 set linesize 180 col owner format a20 col name format a30 col type format a15 col namespace format a30 sys@ORCL> select owner,name,type,namespace from v$db_object_cache where kept='YES' and type!='INVALID TYPE'; OWNER NAME TYPE NAMESPACE -------------------- ------------------------------ --------------- ------------------------------ SYS STANDARD PACKAGE TABLE/PROCEDURE SYS IND_STATS$ TABLE TABLE/PROCEDURE SYS CON$ TABLE TABLE/PROCEDURE SYS CLU$ TABLE TABLE/PROCEDURE SYS I_OBJ#_INTCOL# INDEX INDEX SYS C_TS# CLUSTER CLUSTER SYS HISTGRM$ TABLE TABLE/PROCEDURE SYS HIST_HEAD$ TABLE TABLE/PROCEDURE SYS C_FILE#_BLOCK# CLUSTER CLUSTER 9.清除tester用户及其数据 sys@ORCL> drop user tester cascade;

编辑推荐：

返回列表

使用DBMS_SHARED_POOL包将对象固定到共享池

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推