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
打赏
微信扫一扫，打赏作者吧～