如何利用PIN将应用的SQL钉在共享池中

数据库版本:9.2.0.4
操作系统:SOLARIS 8

应用过程中总是出现ORA-04031错误，采用该方法后运行2个月没有再次出现过该错误。

可以提交一个JOB来自动运行这个过程，将存储过程先钉在缓冲区中。

autopin.sql：

@?/rdbms/admin/dbmspool.sql

create or replace procedure AUTO_PIN is
  address                            varchar2(20);      --sql_address
  hashvalue                          varchar2(20);      --sql_hashvalue
 
  SumMemory                          number;            --SQL缓冲区总空间
  PinMemory                          number;            --钉在SQL缓冲区的空间
 
  cursor cSele_SQL is                                   --查询执行过100次的SQL
    select t.ADDRESS,t.HASH_VALUE
      from v$sql t
     where t.KEPT_VERSIONS=0
       and t.EXECUTIONS>100
     order by t.EXECUTIONS desc;
begin
  select nvl(sum(t.SHARABLE_MEM),0)                     --查询钉住的SQL所占空间
    into PinMemory
    from v$sql t
   where t.KEPT_VERSIONS<>0;
  
  select sum(t.SHARABLE_MEM)                            --查询总空间
    into SumMemory
    from v$sql t;
  
  if PinMemory/SumMemory<0.7 then                       --小于70%
    open cSele_SQL;
    fetch cSele_SQL into address,hashvalue;
   
    while cSele_SQL%found loop
--      dbms_output.put_line(address||','||hashvalue);
     
      sys.dbms_shared_pool.keep(address||','||hashvalue,'C');    --钉SQL
     
      fetch cSele_SQL into address,hashvalue;
    end loop;
    close cSele_SQL;
  else                                                  --大于70%
    execute immediate 'alter system flush shared_pool';          --刷新SHARED_POOL
  end if;
end AUTO_PIN;
/