Re: When to use packages?

"NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message news:40ff3ccc.53941333_at_news.acedsl.com...
> Our application(s) are very dynamic in terms of being 'always in
> development'. Packages sometimes become invalid, b/c they depend on
> objects that might've changed (e.g. column added, view rebuilt). When
> people try to recompile them, while they're being executed by multiple
> clients (app servers) the database stalls for a minute or two, since a
> bunch of sessions wait for library cache pin, and in certain
> circumstances we have to kill sessions, restart app server
> connections, or even the database, since a timed out session (on the
> app end), gets stuck holding/waiging on a 'library cache lock' (I
> might be inprecise here, since this all happens very fast, and my
> priority is usually not to diagnose but bring the db back to life)
>
> Aside from criticism for modifying production database would you
> advise to
> a) not recompile the package, and let the database validate/recompile
> the procedures that are being executed
> b) avoid using packages unless they're necessary for given code (I
> understand, packages do have some advantages, however, only remotedly
> aware about some of them)
> c) .. any other suggestion
>
> Thanks
>
> P.S.
> I have been fighting to have 'releases' of our software, as opposed to
> ongoing changes. It's been a long fight, and it probably won't be over
> as soon as I want it to. However, at least any changes are now 'taboo'
> when application becomes critical.

Your problem IS doing development in production. Bad practice. You should properly plan your migrations from a test environment to a production environment. These should be during planned maint. periods. Also the packages should be grouped into logical units so you don't have that much cross dependencies. What you are trying to do is make a change in code while the code is being used. What the heck do you expect? Remove the fact that it is a database and think of it as C or C++ code. Do you recompile it and change the object code while the system is running. Jim Received on Wed Jul 21 2004 - 23:40:01 CDT