首页 » ORACLE » DBMS_SHARED_POOL包 创建与使用

DBMS_SHARED_POOL包 创建与使用

DBMS_SHARED_POOL 是从10.2开始提供的包,可以访问shared_pool中的对象,
功能检查大于批定大小的对象,加入/移除shared_pool对象
下面看我的例子

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SYS>desc dbms_shared_pool;                                                                                                                                                       
ERROR:
ORA-04043: object dbms_shared_pool does not exist


SYS>select object_name,object_id,object_type from dba_objects where object_name='DBMS_SHARED_POOL';                                                                              

no rows selected

SYS>exit                                                                                                                                                                         
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

[oracle@ora10g admin]$ vi dbmspool.sql 

rem
rem $Header: dbmspool.sql 15-jun-99.08:54:18 mjungerm Exp $
rem
Rem  Copyright (c) 1991, 1996, 1997, 1998, 1999 by Oracle Corporation
Rem    NAME
Rem      dbmspool.sql - dbms_shared_pool utility package.
Rem    DESCRIPTION
Rem      This package allows you to display the sizes of objects in the
Rem      shared pool, and mark them for keeping or unkeeping in order to
Rem      reduce memory fragmentation.
Rem    RETURNS
Rem
Rem    NOTES
Rem    MODIFIED   (MM/DD/YY)
Rem     mjungerm   06/15/99 -  add java shared data object type
Rem     ansriniv   04/13/98 -  keep functionality for types
Rem     gdoherty   06/05/97 -  invoke prvtpool
Rem     ansriniv   03/26/97 -  provide keep/unkeep for sequences
Rem     asurpur    04/09/96 -  Dictionary Protection Implementation
Rem     bhirano    12/23/94 -  merge changes from branch 1.3.720.1
Rem     adowning   02/23/94 -  split into public/private files
Rem     ajasuja    01/06/94 -  merge changes from branch 1.1.312.1
Rem     rkooi      04/20/93 -  change psdkeep to psdkep
Rem     ajasuja    11/05/93 -  handle UNIX addresses
Rem     rkooi      12/08/92 -  Creation

create or replace package dbms_shared_pool is
  ------------
  --  OVERVIEW
  --
  --  This package provides access to the shared pool.  This is the
  --  shared memory area where cursors and PL/SQL objects are stored.

。。。
从上面可以看出在10201中就有了这个包,但对比10204中包的注释发现10204版本中又进行了更新,比如下面
rem
rem $Header: dbmspool.sql 24-jan-2007.10:50:18 desinha Exp $
rem
Rem Copyright (c) 1991, 2006, Oracle. All rights reserved.
Rem    NAME
Rem      dbmspool.sql - dbms_shared_pool utility package.
Rem    DESCRIPTION
Rem      This package allows you to display the sizes of objects in the
Rem      shared pool, and mark them for keeping or unkeeping in order to
Rem      reduce memory fragmentation.
Rem    RETURNS
Rem
Rem    NOTES
Rem    MODIFIED   (MM/DD/YY)
Rem     desinha    08/29/06  - Add purge
Rem     desinha    01/24/07  - Backport desinha_purge from main
Rem     mjungerm   06/15/99 -  add java shared data object type
Rem     ansriniv   04/13/98 -  keep functionality for types
Rem     gdoherty   06/05/97 -  invoke prvtpool
Rem     ansriniv   03/26/97 -  provide keep/unkeep for sequences
Rem     asurpur    04/09/96 -  Dictionary Protection Implementation
Rem     bhirano    12/23/94 -  merge changes from branch 1.3.720.1
Rem     adowning   02/23/94 -  split into public/private files
Rem     ajasuja    01/06/94 -  merge changes from branch 1.1.312.1
Rem     rkooi      04/20/93 -  change psdkeep to psdkep
Rem     ajasuja    11/05/93 -  handle UNIX addresses
Rem     rkooi      12/08/92 -  Creation

并且在
dbmspool.sql 的脚本最后又调用了 prvtpool.plb

[oracle@ora10g admin]$ vi prvtpool.plb 

create or replace view dba_keepsizes (totsize, owner, name) as
  select trunc((sum(parsed_size)+sum(code_size))/1024),
         owner, name
  from dba_object_size
  where type in ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY','TRIGGER',
                 'JAVA SOURCE','JAVA CLASS','JAVA RESOURCE','JAVA DATA')
  group by owner, name;
create or replace package body dbms_shared_pool wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
。。。

包体在这创建,用wrap加密过

--创建DBMS_SHARED_POOL包

SYS>@?/rdbms/admin/dbmspool.sql                                                                                                                                                  
Package created.
Grant succeeded.
View created.
Package body created.

SYS>select object_name,object_id,object_type from dba_objects where object_name='DBMS_SHARED_POOL';                                                                              

OBJECT_NAME                     OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------------
DBMS_SHARED_POOL                     9807 PACKAGE
DBMS_SHARED_POOL                     9809 PACKAGE BODY

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

 
 This article explains and illustrates how the PURGE procedure in the DBMS_SHARED_POOL package can be used to flush a specific object, such as a cursor, out of the Object Library Cache.
 
 用dbms_shared_pool.purge 可以flush 一个指定的对象从shared_pool中,而不需求 alter system flush shared_pool,刷整个共享池,如果是一个正忙的数据库这样会突然灾难性的负担,比如是一个sql的执行计划有问题,就可以只刷出批定的cursor从library cache中.

for example:
 
SQL> conn anbob/anbob
Connected.
 SQL> select object_id,object_name from allobj where object_id<10;

 OBJECT_ID OBJECT_NAME
---------- ----------------------------------------------------------------
         9 I_FILE#_BLOCK#
         7 I_TS#
         6 C_TS#
         5 CLU$
         8 C_FILE#_BLOCK#
         2 C_OBJ#
         4 TAB$

7 rows selected.

SQL> select object_id,object_name from allobj where object_id<100;

 OBJECT_ID OBJECT_NAME
---------- ----------------------------------------------------------------------
        17 FILE$
        13 UET$
         9 I_FILE#_BLOCK#
        41 I_FILE1
        48 I_CON1
        38 I_OBJ3
         7 I_TS#
        53 I_CDEF4
		...
 SQL> conn / as sysdba
Connected.
SQL> col sql_text for a60
SQL> select sql_text,address,hash_value from v$sqlarea where sql_text like 'select object_id,object_name from allobj %';

SQL_TEXT                                                     ADDRESS          HASH_VALUE
------------------------------------------------------------ ---------------- ----------
select object_id,object_name from allobj where object_id<100 00000000C3A459F8  749150589
select object_id,object_name from allobj where object_id<10  00000000CBCE5E38 1381360950

SQL> exec dbms_shared_pool.purge ('00000000CBCE5E38,1381360950','C');

PL/SQL procedure successfully completed.

SQL> select sql_text,address,hash_value from v$sqlarea where sql_text like 'select object_id,object_name from allobj %';

SQL_TEXT                                                     ADDRESS          HASH_VALUE
------------------------------------------------------------ ---------------- ----------
select object_id,object_name from allobj where object_id<100 00000000C3A459F8  749150589
select object_id,object_name from allobj where object_id<10  00000000CBCE5E38 1381360950


SQL> oradebug setmypid
Statement processed.
SQL> oradebug  event 5614566 trace name context forever
Statement processed.
SQL> select sql_text,address,hash_value from v$sqlarea where sql_text like 'select object_id,object_name from allobj %';

SQL_TEXT                                                     ADDRESS          HASH_VALUE
------------------------------------------------------------ ---------------- ----------
select object_id,object_name from allobj where object_id<100 00000000C3A459F8  749150589
select object_id,object_name from allobj where object_id<10  00000000CBCE5E38 1381360950

SQL> exec dbms_shared_pool.purge ('00000000CBCE5E38,1381360950','C');

PL/SQL procedure successfully completed.

SQL> select sql_text,address,hash_value from v$sqlarea where sql_text like 'select object_id,object_name from allobj %';

SQL_TEXT                                                     ADDRESS          HASH_VALUE
------------------------------------------------------------ ---------------- ----------
select object_id,object_name from allobj where object_id<100 00000000C3A459F8  749150589

SQL> 

note:
如果没有启作用,这是10204的bug,Bug 5614566.,在11.1已修复,在10204中需要设置event 5614566 使用purge

关于dbms_shared_pool.sizes 列出大于批定大小(单位kb)shared_pool中的对象
set serveroutput on size unl
exec dbms_shared_pool.sizes(3000);
个人猜想应该是列出v$sqlarea中SHARABLE_MEM 的cursor,和dba_keepsizes view中的对象。

其它方法看官方解释http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_shpool.htm
打赏

对不起,这篇文章暂时关闭评论。