共享池的优化～！ 之模拟一个库缓存等待

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