alter system flush shared_pool究竟做了些什么事情

我们的数据库的sga比较小，存储过程又多又长，老是出现oracle不能分配多少字节的内存这个错。有人告诉我可以用这个语句来优化shared pool.我查了一下sql reference,说是可以把sga里边所有的shared pool里边的东西都删除。但是那些仍旧在执行的sql语句不会被清除。这样会不会把那些pin在sga里边的包，function也一起删除？
如何知道那些包已经被pin了？

==========================================================================================

 

alter system flush shared_pool的作用

The FLUSH SHARED POOL clause lets you clear all data from the shared pool in the system global area (SGA). The shared pool stores Cached data dictionary information and Shared SQL and PL/SQL areas for SQL statements, stored procedures, function, packages, and triggers.
This statement does not clear shared SQL and PL/SQL areas for items that are currently being executed. You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.

实验步骤如下：
----------------------------------------------------------------------------------------
1.查看shared_pool中碎片
SQL> select count(*) from x$ksmsp;
COUNT(*)
----------
     7260
2.使用一个以前未曾使用过的查询，来让share pool分配内存，增加share pool中的chunk碎片
SQL> select count(*) from user_tables;
COUNT(*)
----------
      667
3.再次查询shared_pool中的碎片
SQL> select count(*) from x$ksmsp;
COUNT(*)
----------
     7515
每个buckets的碎片数量>2000就认为是不太好的一个情况，可能会引起share pool latch争用！
4.使用alter system flush shared_pool命令，并再次查询shared_pool中的碎片
SQL> alter system flush shared_pool;
系统已更改。
SQL> select count(*) from x$ksmsp;
COUNT(*)
----------
     7194
-----------------------------------------------------------------------------------------------------------------------
总结：执行这个语句的结果是将缓存在library cache和data dictionary cache 中的sql,pl/sql和数据字典定义都从共享池中清除了
在负载很重的生产库里执行flush shared_pool无异于自杀...慎用！

类似的还有aleter system flush buffer_cache;