Re: Package compilation performance

Jeremy wrote:
>
> In article <3EC8F9DA.88C88270_at_exxesolutions.com>,
> damorgan_at_exxesolutions.com says...
> > Jeremy wrote:
> >
> > > Has anybody noticed that, over time, pl/sql compilation time appears to
> > > increase? I don't mean just in regard to size but generally?
> > >
> > > Is there a way to 'clean things up' to restore pl/sql compilation speeds
> > > to what they were originally?
> > >
> > > Or am I imagining things!
> > >
> > > --
> > >
> > > jeremy
> >
> > It has not been my experience.
> >
> > If you want to pursue this I would suggest you supply the following:
> > 1. Oracle version and edition
> > 2. Server hardware and operating system
> > 3. Client hardware and operating system
> > 4. Time period, number of compilations, and size of compiled objects
> >
>
> Thanks Daniel. This is 8.1.6 Standard Edition on Solaris 2.6. Client is
> sqlplus 9.0.1.3.0 on WinXP Professional.
>
> I figured that perhaps there might be some fragmentation occurring over
> time (this database is 18 months old, contains about 76 application-
> specific pl/sql packages in total around 120,000 lines of code. Packages
> are always being altered and reompiled (it is a devt db)) - is this
> possible?
>
> thanks anyway
>
> --
>
> jeremy

It is conceivably possible that fragmentation might cause some performance problems but I would be putting them in the 'negligible' ball park. There's about 5 or so tables that plsql impacts in the dictionary (source$ plus some idl_... ones) so every once in a while you might find that and additional extent is required for these which would cause a 'blip'.

Another possibility is that amount of inter-dependencies you've got. Its common practice in my experience for people to compile both package and package body when only the body needs changing. That's a lot of unnecessary overhead.

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"