ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用dbms_shared_pool包将对象pin到内存中

使用dbms_shared_pool包将对象pin到内存中

原创 Linux操作系统 作者:尛样儿 时间:2011-03-02 16:54:52 0 删除 编辑

oracle@ythdc:/home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 27 17:18:20 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

1.执行dbmspool脚本创建dbms_shared_pool包,默认不存在。
SQL> @?/rdbms/admin/dbmspool

Package created.


Grant succeeded.


View created.


Package body created.

2.执行权限授予test用户。
SQL> grant execute on dbms_shared_pool to test;

Grant succeeded.

SQL> connect test/test
Connected.

3.创建一个测试存储过程。
SQL> create or replace procedure test.t111
as
t date;
begin
select sysdate into t from dual;
dbms_output.put_line(t);
end t111;
/  2    3    4    5    6    7    8

Procedure created.

4.将t111存储过程pin到内存中。
SQL> exec sys.dbms_shared_pool.keep('t111');

PL/SQL procedure successfully completed.

5.查看是否pin成功。
SQL> COL OWNER FORMAT A10;
SQL> COL NAME FORMAT A30;
SQL> select OWNER,NAME,TYPE,KEPT from V$db_Object_Cache where name='T111';

OWNER      NAME                           TYPE                         KEP
---------- ------------------------------ ---------------------------- ---
TEST       T111                           PROCEDURE                    YES

6.查看sys.dbms_shared_pool定义。
SQL> desc sys.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


FLAG的值如下:
Value     Kind of Object to keep
-----        ----------------------
P          package/procedure/function(默认)
Q          sequence
R          trigger
T          type
JS         java source
JC         java class
JR         java resource
JD         java shared data
C          cursor

        dbms_shared_pool.purge的使用参考文章:
http://space.itpub.net/471666/viewspace-616339

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23135684/viewspace-688279/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle数据库管理员,Oracle数据库系统构架员;2012年7月出版《构建最高可用Oracle数据库系统:Oracle 11gR2 RAC管理、维护与性能优化》一书;Oracle 10g OCM。

注册时间:2010-01-05

  • 博文量
    483
  • 访问量
    5419339