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

--******************************************

-- 使用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

八、 快捷参考

有关性能优化请参考

Oracle 硬解析与软解析

共享池的调整与优化(Shared pool Tuning)

Buffer cache 的调整与优化(一)

Oracle 表缓存(caching table)的使用

有关ORACLE体系结构请参考

Oracle 表空间与数据文件

Oracle 密码文件

Oracle 参数文件

Oracle 联机重做日志文件(ONLINE LOG FILE)

Oracle 控制文件(CONTROLFILE)

Oracle 归档日志

Oracle 回滚(ROLLBACK)和撤销(UNDO)

Oracle 数据库实例启动关闭过程

Oracle 10g SGA 的自动化管理

Oracle 实例和Oracle数据库(Oracle体系结构)

有关闪回特性请参考

Oracle 闪回特性(FLASHBACK DATABASE)

Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)

Oracle 闪回特性(Flashback Query、Flashback Table)

Oracle 闪回特性(Flashback Version、Flashback Transaction)

有关基于用户管理的备份和备份恢复的概念请参考

Oracle 冷备份

Oracle 热备份

Oracle 备份恢复概念

Oracle 实例恢复

Oracle 基于用户管理恢复的处理(详细描述了介质恢复及其处理)

SYSTEM 表空间管理及备份恢复

SYSAUX表空间管理及恢复

有关RMAN的备份恢复与管理请参考

RMAN 概述及其体系结构

RMAN 配置、监控与管理

RMAN 备份详解

RMAN 还原与恢复

RMAN catalog 的创建和使用

基于catalog 创建RMAN存储脚本

基于catalog 的RMAN 备份与恢复

使用RMAN迁移文件系统数据库到ASM

RMAN 备份路径困惑(使用plus archivelog时)

有关ORACLE故障请参考

ORA-32004 的错误处理

ORA-01658 错误

CRS-0215 错误处理

ORA-00119，ORA-00132 错误处理

又一例SPFILE设置错误导致数据库无法启动

对参数FAST_START_MTTR_TARGET = 0 的误解及设定

SPFILE 错误导致数据库无法启动(ORA-01565)

有关ASM请参考

创建ASM实例及ASM数据库

ASM 磁盘、目录的管理

使用 ASMCMD 工具管理ASM目录及文件

有关SQL/PLSQL请参考

SQLPlus 常用命令

替代变量与SQL*Plus环境设置

使用Uniread实现SQLplus翻页功能

SQL 基础-->SELECT 查询

SQL 基础--> NEW_VALUE 的使用

SQL 基础--> 集合运算(UNION 与UNION ALL)

SQL 基础--> 常用函数

SQL 基础--> 视图(CREATE VIEW)

SQL 基础--> 创建和管理表

SQL 基础--> 多表查询

SQL 基础--> 过滤和排序

SQL 基础--> 子查询

SQL 基础--> 分组与分组函数

SQL 基础--> 层次化查询(START BY ... CONNECT BY PRIOR)

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

PL/SQL --> 游标

PL/SQL --> 异常处理(Exception)

PL/SQL --> 语言基础

PL/SQL --> 流程控制

PL/SQL --> PL/SQL记录

PL/SQL --> 包的创建与管理

PL/SQL --> 隐式游标(SQL%FOUND)

PL/SQL --> 包重载、初始化

PL/SQL --> DBMS_DDL包的使用

PL/SQL --> DML 触发器

PL/SQL --> INSTEAD OF 触发器

PL/SQL --> 存储过程

PL/SQL --> 函数

PL/SQL --> 动态SQL

PL/SQL --> 动态SQL的常见错误

有关ORACLE其它特性

Oracle 常用目录结构(10g)

使用OEM,SQL*Plus,iSQL*Plus 管理Oracle实例

日志记录模式(LOGGING 、FORCE LOGGING 、NOLOGGING)

表段、索引段上的LOGGING与NOLOGGING

Oralce OMF 功能详解

Oracle 用户、对象权限、系统权限

Oracle 角色、配置文件

Oracle 分区表

Oracle 外部表

使用外部表管理Oracle 告警日志(ALAERT_$SID.LOG)

簇表及簇表管理(Index clustered tables)

数据泵 EXPDP 导出工具的使用

数据泵 IMPDP 导入工具的使用

导入导出 Oracle 分区表数据

SQL*Loader使用方法

启用用户进程跟踪

配置非默认端口的动态服务注册

配置ORACLE 客户端连接到数据库

system sys，sysoper sysdba 的区别

ORACLE_SID、DB_NAME、INSTANCE_NAME、DB_DOMIAN、GLOBAL_NAME

Oracle 补丁全集 (Oracle 9i 10g 11g Path)

Oracle 10.2.0.1 升级到 10.2.0.4

Oracle 彻底 kill session