--******************************************
-- 使用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;
User dropped.
10.有关使用存储过程来实现自动pin住对象到library cache参考前面的讲解，此处不再演示
七、有关DBMS_SHARED_POOL，请参考
https://netfiles.uiuc.edu/jstrode/www/oraview/V$DB_OBJECT_CACHE.html
http://www.dba-oracle.com/art_proc.htm
http://docstore.mik.ua/orelly/oracle/bipack/ch12_02.htm
八、 快捷参考
有关性能优化请参考
共享池的调整与优化(Shared pool Tuning)
Oracle 表缓存(caching table)的使用
有关ORACLE体系结构请参考
Oracle 联机重做日志文件(ONLINE LOG FILE)
Oracle 回滚(ROLLBACK)和撤销(UNDO)
Oracle 实例和Oracle数据库(Oracle体系结构)
有关闪回特性请参考
Oracle 闪回特性(FLASHBACK DATABASE)
Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)
Oracle 闪回特性(Flashback Query、Flashback Table)
Oracle 闪回特性(Flashback Version、Flashback Transaction)
有关基于用户管理的备份和备份恢复的概念请参考
Oracle 基于用户管理恢复的处理(详细描述了介质恢复及其处理)
有关RMAN的备份恢复与管理请参考
RMAN 备份路径困惑(使用plus archivelog时)
有关ORACLE故障请参考
对参数FAST_START_MTTR_TARGET = 0 的误解及设定
SPFILE 错误导致数据库无法启动(ORA-01565)
有关ASM请参考
有关SQL/PLSQL请参考
SQL 基础-->SELECT 查询
SQL 基础--> 集合运算(UNION 与UNION ALL)
SQL 基础--> 多表查询
SQL 基础--> 层次化查询(START BY ... CONNECT BY PRIOR)
SQL 基础--> ROLLUP与CUBE运算符实现数据汇总
PL/SQL --> 异常处理(Exception)
PL/SQL --> 隐式游标(SQL%FOUND)
