shared_pool_size
1.库缓存--->语句文本,解析码和执行执行 lru算法管理
2.字典缓存--->包含表,定义栏和数据字典表的权限
注意: 过大的shared_pool_size设置可能是个有害的选择,由于管理的开销可能会折磨你的数据库
SQL> select namespace,gets,gethits,gethitratio from v$librarycache;
NAMESPACE GETS GETHITS GETHITRATIO
--------------- ---------- ---------- -----------
SQL AREA 1568 300 .191326531
TABLE/PROCEDURE 3149 1196 .379803112
BODY 38 13 .342105263
TRIGGER 2 0 0
INDEX 77 20 .25974026
CLUSTER 92 84 .913043478
OBJECT 0 0 1
PIPE 0 0 1
JAVA SOURCE 0 0 1
JAVA RESOURCE 0 0 1
JAVA DATA 0 0 1
OLTP系统,应该大于90%SQL> select NAMESPACE,GETS,GETHITS,GETHITRATIO from v$librarycache
2 where namespace='SQL AREA';
NAMESPACE GETS GETHITS GETHITRATIO
--------------- ---------- ---------- -----------
SQL AREA 38549 24904 .646034917
OLTP系统,应该大于90%
SQL> select namespace,pins,reloads,invalidations from v$librarycache
2 where namespace='SQL AREA';
NAMESPACE PINS RELOADS INVALIDATIONS
--------------- ---------- ---------- -------------
SQL AREA 247641 97 495
gets:查找名称空间对象的次数
pins:执行名称对象的次数
RELOADS:由于miss而重新解析的次数,理想为0
INVALIDATIONS: 运行期间,一个对象被修改,那么所有跟这个对象相关的执行计划都被标记为失效,再次使用需要重新解析
模拟一个库缓存等待
二 实例
SQL> create table t_p(a int);
Table created.
SQL> insert into t_p values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> create or replace procedure pin
2 as
3 b int;
4 begin
5 select count(*) into b from t_p;
6 dbms_lock.sleep(1000);------->1000秒以后执行,现在挂在这
7 end pin;
8 /
Procedure created.
SQL> exec pin
session 2:
SQL> select object_name ,status from dba_objects
2 where object_name='PIN';
OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
PIN
VALID--------------------------->可看出pin对象此时是有效的
sesseion 3:
SQL> drop table t_p;
Table dropped. ----------------------->删除pin对象所作用的表
session 2:
SQL> select object_name ,status from dba_objects
2 where object_name='PIN';
OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
PIN
INVALID-------------------------------------------------------------------------------------->此时可看出pin对象已经无效
SQL> select event,count(*) from v$session_wait group by event;
EVENT COUNT(*)
---------------------------------------------------------------- ----------
SQL*Net message from client 4
PL/SQL lock timer 1
jobq slave wait 2
rdbms ipc message 14
smon timer 1
pmon timer 1
SQL*Net message to client 1
Streams AQ: waiting for time management or cleanup tasks 10
Streams AQ: qmn coordinator idle wait 1
9 rows selected.
session 4:
exec pin
session 2:
SQL> select event,count(*) from v$session_wait group by event;
EVENT COUNT(*)
---------------------------------------------------------------- ----------
SQL*Net message from client 4
PL/SQL lock timer 1
library cache pin 1----------------->增加了一个库缓存等待
jobq slave wait 2
rdbms ipc message 14
smon timer 1
pmon timer 1
SQL*Net message to client 1
Streams AQ: waiting for time management or cleanup tasks 10
Streams AQ: qmn coordinator idle wait 1
10 rows selected.
进一步说明在业务高峰期,不要执行表ddl
杀会话,尽量通过sid---->spid ,kill -9 spid
select spid,pid from v$process
where addr=(
select paddr from v$session where sid=131);
SPID PID
------------ ----------
27813 16