Re: Tuning Assistance

From: Rniemic <rniemic_at_aol.com>
Date: 29 Jun 1999 20:23:42 GMT
Message-ID: <19990629162342.08643.00006115@ng-fa1.aol.com>

Here is an excerpt from the book on pinning things ... you may also want to check your v$sgastat for free memory in the shared pool (in case it's lacking) and x$ksmsp for contiguous space free ... The pinning info... note after you pin everything ... you should still have free bytes (a fair amount depending on the app.) in the shared pool when you query v$sgastat...sorry for the formatting below...just time to cut and paste...

In the event that you cannot maintain a sufficient SHARED_POOL_SIZE, it may become important to keep the most important objects cached (pinned) in memory. The following example shows how to pin PL/SQL object statements in memory using the DBMS_SHARED_POOL.KEEP procedure. For additional PL/SQL tips see chapter 10 which focus exclusively on PL/SQL.
BEGIN
DBMS_SHARED_POOL.KEEP('PROCESS_DATE','P'); END; Tip: Pin PL/SQL objects into memory immediately upon starting the database to avoid insufficient memory errors later in the day. To accomplish this, use the DBMS_SHARED_POOL.KEEP procedure for PL/SQL object statements. Ensure that the STANDARD procedure is pinned soon after startup since it is so large.

(3)You may also pin all packages:

To pin all packages in the system, execute the following (from Oracle's Metalink):
declare
own varchar2(100);
nam varchar2(100);

cursor pkgs is      
	select   	owner, object_name  
	from   	dba_objects  
	where 	object_type = 'PACKAGE';  
begin  
	open pkgs;   
	loop   
    	fetch pkgs into own, nam;  
    	exit when pkgs%notfound;   
    	dbms_shared_pool.keep(own || '.' || nam, 'P');   
	end loop;   

end;    

Common "problem packages" that are shipped with Oracle (and should be 'kept') include 'STANDARD', 'DBMS_STANDARD', and 'DIUTIL'.

Tip: Use the DBMS_SHARED_POOL.KEEP procedure combined in PL/SQL to pin all packages when the database is started (if memory/shared pool permits) and avoid all errors involving loading packages in the future. See chapter 10 for additional PL/SQL and pinning tips.

Rich Niemiec
Oracle Performance Tips and Techniques, Oracle Press ISBN: 0078824346 Received on Tue Jun 29 1999 - 15:23:42 CDT