RE: Pinning packages

Rich

The only time I've seen SYS.STANDARD paged out is when (virtually) no PL/SQL was run against the database -- and even then the startup times were small.

Anyway, I was actualy referring to a series of related packages that totaled tens of MB in size. The original package was split because Oracle couldn't handle a package that size.

In no other case have i seen any noticable performance benefit to pinning packages in memory.

However I never tried pinning packages in memory to avoid the ORA-4031s.

Kevin

-----Original Message-----
From: Jesse, Rich [mailto:Rich.Jesse_at_qtiworld.com] Sent: Wednesday, July 23, 2003 5:19 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Pinning packages

I'm not sure I buy that lesson in this case. I have objects pinned at startup as a way to prevent ORA-4031s from happening and also for some performance gain, although that was an after-thought and the performance gains weren't measurable. Personal preference perhaps for an 8.1.6 (now 8.1.7.4) DB. I'd just as soon not see the 265KB SYS.STANDARD package bounce in and out of the shared pool. Maybe I'm showing my age?!?? ;)

As a matter of fact, it's been awhile since I've checked the kept procs...Doh! Need to add another trigger to the kept list that's been reloaded 300 times...

Rich

Rich Jesse                           System/Database Administrator
rjesse_at_qtiworld.com                  Quad/Tech Inc, Sussex, WI USA

> -----Original Message-----
> From: Kevin Toepke [mailto:ktoepke_at_rlcarriers.com]
> Sent: Wednesday, July 23, 2003 3:15 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Pinning packages
>
>
> There was once that I pinned a package. It was large and
> somewhat frequently
> used by customer service....in a database with VERY
> high-activity. It would
> take 5-10 seconds to load the first time. After that, run
> times would be in
> the 1-2 second range. About 20 times a day it was aged out.
> Customer service
> complained about run-times until I pinned the package in
> memory. then the
> complaints went away....
>
> The lession, don't do it unless there is a specific need.
>
> Kevin
>
> -----Original Message-----
> Sent: Wednesday, July 23, 2003 3:49 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi!
>
> I've never pinned anything unless I've had serious trouble in
> shared pool
> (fragmentation, ORA-4031's, latch contention etc..)
> And pinning has usually been one of the last resorts, because
> Oracle manages
> shared pool contents fine if your application is well
> written, you are using
> decent Oracle version and shared pool is sized reasonably...)
>
> Tanel.
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, July 23, 2003 10:19 PM
>
>
> > Hi
> >
> > My question is to all the 8i dba's.
> > Do you still pin packages in memory. ??? If you do what is
> the reason for
> it??
> > I am referring to both system packages and application packages.
> >
> > TIA
> > Yuval.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may