共享池---sql缓存

实验一
说明："sysdba>"为dba用户。 "huang>"为shcema huang.

sysdba>select kglnaobj,kglhdadr,kglhdpar,kglobhd0,kglobhd6 from  x$kglob where kglnaob

no rows selected

huang>select * from t where id=100;

ID
----------
       100
  
sysdba>select kglnaobj,kglhdadr,kglhdpar,kglobhd0,kglobhd6 from  x$kglob where kglnaobj='select * from t where id=100';


KGLNAOBJ KGLHDADR KGLHDPAR KGLOBHD0 KGLOBHD6
---------------------------------------- -------- -------- -------- --------
select * from t where id=100 4EE1117C 49B7985C 4EE039F0 48D534E4
select * from t where id=100 49B7985C 49B7985C 4EDEB1E0 00

sysdba>alter system flush shared_pool;

System altered.

sysdba>select kglnaobj,kglhdadr,kglhdpar,kglobhd0,kglobhd6 from  x$kglob where kglnaobj='select * from t where id=100';

KGLNAOBJ KGLHDADR KGLHDPAR KGLOBHD0 KGLOBHD6
---------------------------------------- -------- -------- -------- --------
select * from t where id=100 4EE1117C 49B7985C 00    00
select * from t where id=100 49B7985C 49B7985C 4EDEB1E0 00

huang>select * from dual;

D
-
X

sysdba>select kglnaobj,kglhdadr,kglhdpar,kglobhd0,kglobhd6 from  x$kglob where kglnaobj='select * from t where id=100';

KGLNAOBJ KGLHDADR KGLHDPAR KGLOBHD0 KGLOBHD6
---------------------------------------- -------- -------- -------- --------
select * from t where id=100 4EE1117C 49B7985C 00    00
select * from t where id=100 49B7985C 49B7985C 4EDEB1E0 00

sysdba>alter system flush shared_pool;

System altered.

sysdba>select kglnaobj,kglhdadr,kglhdpar,kglobhd0,kglobhd6 from  x$kglob where kglnaobj='select * from t where id=100';

no rows selected

结论：当一个sql被执行一次时，DBA用户使用刷新共享池后就把缓存在共享池中的SQL刷出了内存（前提条件是执行sql的会话在执行完这个sql后必须执行一下别的命令，我估计是因为游标没有关闭的引起的吧）


实验二

sysdba>select kglnaobj,kglhdadr,kglhdpar,kglobhd0,kglobhd6 from  x$kglob where kglnaobj='select * from t where id=100';

no rows selected

huang>select * from t where id=100;

ID
----------
       100

huang>select * from t where id=100;

ID
----------
       100

huang>select * from dual;

D
-
X

sysdba>select kglnaobj,kglhdadr,kglhdpar,kglobhd0,kglobhd6 from  x$kglob where kglnaobj='select * from t where id=100';

KGLNAOBJ KGLHDADR KGLHDPAR KGLOBHD0 KGLOBHD6
---------------------------------------- -------- -------- -------- --------
select * from t where id=100 4828E948 49BB86B4 4828E8D0 47E1A6FC
select * from t where id=100 49BB86B4 49BB86B4 4ED54990 00

sysdba>alter system flush shared_pool;

System altered.

sysdba>select kglnaobj,kglhdadr,kglhdpar,kglobhd0,kglobhd6 from  x$kglob where kglnaobj='select * from t where id=100';

no rows selected

总结：当一个sql被执行两次时，DBA用户使用刷新共享池后就把缓存在共享池中的SQL刷出了内存。


实验三

sysdba>select kglnaobj,kglhdadr,kglhdpar,kglobhd0,kglobhd6 from  x$kglob where kglnaobj='select * from t where id=100';

no rows selected

huang>select * from t where id=100;

ID
----------
       100

huang>select * from t where id=100;

ID
----------
       100

huang>select * from t where id=100;

ID
----------
       100

huang>select * from dual;

D
-
X

sysdba>select kglnaobj,kglhdadr,kglhdpar,kglobhd0,kglobhd6 from  x$kglob where kglnaobj='select * from t where id=100';

KGLNAOBJ KGLHDADR KGLHDPAR KGLOBHD0 KGLOBHD6
---------------------------------------- -------- -------- -------- --------
select * from t where id=100 49B71100 49BB86B4 4EEF9D9C 4B12F4E4
select * from t where id=100 49BB86B4 49BB86B4 4ED50B44 00

sysdba>alter system flush shared_pool;

System altered.

KGLNAOBJ KGLHDADR KGLHDPAR KGLOBHD0 KGLOBHD6
---------------------------------------- -------- -------- -------- --------
select * from t where id=100 49930714 49BB86B4 00    00
select * from t where id=100 49BB86B4 49BB86B4 45AF8194 00

总结：当一个sql执行3次以上的时候进行了软软解析，它的缓存地址存在在了pga里，从pga直接指向了堆6（堆6存放sql的执行计划）。 即使刷新共享池，sql执行计划还是在内存中。要想释放内存，可以杀掉对应会话，再刷新共享池。这也是ORA-04031的原因。