dbms_shared_pool
--------------
默认情况下dbms_shared_pool包是不在系统中的
需要运行$ORACLE_HOME/rdbms/admin/dbmspool.sql进行创建
整个包有4个过程,如下
需要运行$ORACLE_HOME/rdbms/admin/dbmspool.sql进行创建
整个包有4个过程,如下
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 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
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 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.aborted_request_threshold
设定报错阀值,该值在5000 - 2147483647之间,
只要load的对象大小大于该值而shared pool中没有满足要求的空闲空间时,
不会从lru中flush对象出去,直接以一个ORA-4031错误结束。
只要load的对象大小大于该值而shared pool中没有满足要求的空闲空间时,
不会从lru中flush对象出去,直接以一个ORA-4031错误结束。
---------------
dbms_shared_pool.sizes
需要设置set serveroutput on
列出当前share_pool中大于给定参数k的对象
如我的测试系统中只有一个大小为772k的java对象大于500k
列出当前share_pool中大于给定参数k的对象
如我的测试系统中只有一个大小为772k的java对象大于500k
SQL> exec dbms_shared_pool.sizes(500);
SIZE(K) KEPT NAME
------- ------ ----------------------------------------------------
772 YES SYS.oracle/gss/util/NLSLocale (JAVA CLASS)
SIZE(K) KEPT NAME
------- ------ ----------------------------------------------------
772 YES SYS.oracle/gss/util/NLSLocale (JAVA CLASS)
PL/SQL procedure successfully completed.
-----------------
dbms_shared_pool.keep
dbms_shared_pool.unkeep
dbms_shared_pool.unkeep
将对象pin入shared_pool,而不进入LRU 机制
被keep的对象可以是数据库对象,也可以是sql
unkeep为反操作
被keep的对象可以是数据库对象,也可以是sql
unkeep为反操作
类型代号关系如下
C cursor
JC java class
JD java shared data
JR java resource
JS java source
P procedure
Q sequence
R trigger
T type
C cursor
JC java class
JD java shared data
JR java resource
JS java source
P procedure
Q sequence
R trigger
T type
如将procedure AN.p_a keep到share_pool中可以使用如下方法
exec dbms_shared_pool.keep('AN.P_A','p');
exec dbms_shared_pool.keep('AN.P_A','p');
keep sql需要知道sql的addr和hash_value
SQL> select * from dual;
D
-
X
-
X
SQL> select address,hash_value,sql_text from v$sqlarea where sql_text='select * from dual';
ADDRESS HASH_VALUE SQL_TEXT
-------- ---------- ---------------------
689667FC 942515969 select * from dual
-------- ---------- ---------------------
689667FC 942515969 select * from dual
keep sql命令:
exec dbms_shared_pool.keep('689667FC,942515969','W');
exec dbms_shared_pool.keep('689667FC,942515969','W');
已经被keep早share_pool中的对象和sql可以在v$db_object_cache中查询
字段kept为yes就是已经被keep的对象
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13177610/viewspace-690178/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13177610/viewspace-690178/