How to Use pin to pin the SQL of the application in the Shared Pool

Database Version: 9.2.0.4
Operating System: Solaris 8

There is always a ORA-04031 error during the application, and this error does not occur again after running for 2 months.

You can submit a job to automatically run this process and pin the stored procedure to the buffer.

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; -- total space of the SQL Buffer
Pinmemory number; -- the space in the SQL Buffer.

Cursor csele_ SQL is -- query the SQL statements that have been executed for 100 times
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) -- queries the space occupied by pinned SQL statements.
Into pinmemory
From v $ SQL t
Where T. kept_versions <> 0;

Select sum (T. sharable_mem) -- query the total space
Into summemory
From v $ SQL T;

If pinmemory/summemory <0.7 then -- less than 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'); -- dingsql

Fetch csele_ SQL into address, hashvalue;
End loop;
Close csele_ SQL;
Else -- over 70%
Execute immediate 'alter system flush shared_pool '; -- refresh shared_pool
End if;
End auto_pin;
/