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

I believe LOCK_SGA is available on NT.

HTH. Pete

Rniemic wrote:

> 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
--

Regards

Pete

Received on Tue Jun 08 1999 - 15:18:37 CDT