Re: Oracle 7.3.4 performance: First Query takes enormous amount of time

From: Rniemic <rniemic_at_aol.com>
Date: 8 Jun 1999 20:00:55 GMT
Message-ID: <19990608160055.26330.00001913@ng38.aol.com>

It sounds like the SGA is not making its way into physical memory until is is accessed (on some platforms you can use the PRE_PAGE_SGA=true to eliminate this but I don't think that it's available on NT). You can also use the the LOCK_SGA=true to keep the SGA from being swapped out, but I don't know if this is available on NT either. I would suggest that you pin all of your packages at the start of the database and that will not only "touch" the SGA into memory, but make them fast when accessed. The code below shows how to pin all of the packages.

This example shows how to pin as single PL/SQL object in memory using the DBMS_SHARED_POOL.KEEP procedure:

BEGIN
DBMS_SHARED_POOL.KEEP('PROCESS_DATE','P'); END; 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. For additional PL/SQL tips see chapter 10 of the book which focus exclusively on PL/SQL.

Rich Niemiec
Oracle Performance Tips and Techniques
Oracle Press
ISBN: 0078824346 Received on Tue Jun 08 1999 - 15:00:55 CDT