如何避免已经pin住的object被FLUSH 出来

显示全部楼层 · 2007-9-26 18:42:10

ALTER SYSTEM FLUSH SHARED_POOL;
这个会把共享池中的object全部给FLUSH 出来，怎么避免已经pin住的object被FLUSH 出来

千问 · 2007-9-26 18:42:10

pin住的object能给flush出来么？
那还要pin做什么?

千问 · 2007-9-26 18:42:10

pin住的object是不会被flush的..
可以使用dbms_shared_pool.keep去pin住对象..

千问 · 2007-9-26 18:42:10

FLUSH SHARED_POOL Clause
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.

千问 · 2007-9-26 18:42:10

说明pin住的语句是不会被flush的。

千问 · 2007-9-26 18:42:10

SQL> select count(*) from dba_objects;
COUNT(*)
----------
11513
SQL> select ADDRESS,HASH_VALUE from v$sqlarea
2 where sql_text='select count(*) from dba_objects';
ADDRESSHASH_VALUE
-------- ----------
230FCC9C 2935929963

千问 · 2007-9-26 18:42:10

SQL> exec sys.dbms_shared_pool.keep('230FCC9C,2935929963','C');
PL/SQL procedure successfully completed.
SQL> select ADDRESS,HASH_VALUE from v$sqlarea
2 where sql_text='select count(*) from dba_objects';
ADDRESSHASH_VALUE
-------- ----------
230FCC9C 2935929963

千问 · 2007-9-26 18:42:10

为什么keep语句，在flush后，在共享池中不存在：
SQL>alter system flush shared_pool;
System altered.
SQL> selectsql_text from v$sqlarea
2where ADDRESS='230FCC9C';
no rows selected
SQL> selectsql_text from v$sqlarea
2 where HASH_VALUE='2935929963';
no rows selected

千问 · 2007-9-26 18:42:10

昨天的实验得出dbms_shared_pool.keep并不能pin住对象在shared——pool中。
继续昨天的试验：
打开两个sqlplus
其中之一运行：
SQL> select count(*) from t;
COUNT(*)
----------
3931967

千问 · 2007-9-26 18:42:10

在另外sqlplus中执行：
SQL> select address,hash_value from v$sqlarea
2where sql_text='select count(*) from t';
ADDRESSHASH_VALUE
-------- ----------
23330464 2763161912
SQL> alter system flush shared_pool;
System altered.
SQL> select address,hash_value from v$sqlarea
2where sql_text='select count(*) from t';
ADDRESSHASH_VALUE
-------- ----------
23330464 2763161912
这是在 select count(*) from t;执行过程中，执行的。发现在语句执行过程中，执行 alter system flush shared_pool;并不能被flush。即语句在执行过程中是被pin住的。