Re: ORA-06508 and the Shared Pool sizing

Ah yes - the number of the counting :)

On 6/29/05, Paul Drake <bdbafh_at_gmail.com> wrote:
>
> On 6/29/05, Robyn <robyn.sands_at_gmail.com> wrote:
> > Hi Mark,
> >
> > Yes this does help. The shared pool appeared to be sized appropriately
> for
> > normal processing, and the second failure came just 20 minutes after the
> > shared pool increase with restart, so further increases in the shared
> pool
> > seemed like the wrong answer.
> >
> > My first exposure to this code was yesterday afternoon, but from what
> I've
> > seen so far, there are some larger packages with multiple dependancies.
> > I'll try the recompile with a shared pool flush when we get to test
> later
> > today, and check into the pinning option as well.
> >
> > thanks again ... Robyn
>
> Robyn,
>
> Depending upon the type of crap ... you might have to flush 3 times in a
> row.
> I'm not kidding.
>
> Paul
>
>
> > On 6/29/05, Powell, Mark D <mark.powell_at_eds.com> wrote:
> > >
> > > The ORA-06508 error means Oracle could not find enough contiguous
> memory
> > in the shared pool to load the referenced package into. The shared pool
> > becomes fragmented over time and even though Oracle can load packages
> into
> > multiple non-contiguous chunks of memory the rdbms still must find large
> > contiguous space for certain structures required by the code. You can
> > generally avoid this problem by doing the following:
> > >
> > > 1 - in your production environment do not make object changes where
> > dependencies exist from large stored procedures and/or packages except
> > during a maintenance window
> > >
> > > 2 - flush the pool and immediately recompile all invalidated code
> after
> > making a change
> > >
> > > 3 - limit the size of any package or procedure using two or three
> smaller
> > routines if necessary
> > >
> > > 4 - use dbms_keep to pin all large packages immediately after database
> > startup
> > >
> > > HTH -- Mark D Powell --
> > >
> > >
> > >
> > > ________________________________
> > From: oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org]
> > On Behalf Of Robyn
> > > Sent: Wednesday, June 29, 2005 12:08 PM
> > > To: oracle-l_at_freelists.org
> > > Subject: ORA-06508 and the Shared Pool sizing
> > >
> > >
> > >
> > > Hello everyone,
> > >
> > > We've run into a new issue and I'm curious as to how 'normal' this
> might
> > be. We have a new/upgraded application in test. User testing was
> scheduled
> > to start yesterday but one trigger was failing. This trigger calls a
> > procedure owned by another user and had worked in the past. It is an
> 'after
> > update' trigger and the procedure populates a status table, but resulted
> in
> > ORA-06508: PL/SQL: could not find program unit being called.
> > >
> > > The developers worked through all the stuff they knew to check
> > (permissions, full identificiation of the objects, etc) to no avail. The
> > procedure and trigger appeared to be valid, but compiling the trigger
> caused
> > the procedure to go invalid. Procedure could be recompiled but the
> trigger
> > still didn't work.
> > >
> > > I searched at Metalink and found several documents referrencing this
> error
> > for various Oracle applications, while the docs specific to the database
> all
> > mentioned the same stuff that had already been tried.
> > >
> > > The Oracle applications docs stated that the shared pool was too small
> and
> > needed to be increased. Seemed odd to me since the error mentioned not
> > finding a program unit, but these were the only docs that referenced
> > receiving just this error. In every other case, there was a related
> error
> > message was mentioned.
> > >
> > > So, I shutdown the database, increase the shared pool by 128M (or 33%)
> and
> > the procedure worked. However a later attempt to recompile again broke
> the
> > procedure. This time the database was restarted without an increase in
> the
> > shared pool and again, the procedure worked.
> > >
> > > We're going to test a few things later when the users get off the
> system,
> > but this seems really odd. The database is on HP-UX not WinDoze, so I
> > should not have to play the restart game to fix a problem. Has anyone
> seem
> > a similar problem? I am planning to open a TAR if it repeats but I can't
> > test again until tonight.
> > >
> > > TIA ... Robyn
> > >
> >
> >
>
>
> --
> #/etc/init.d/init.cssd stop
> # f=ma, divide by 1, convert to moles.
>

--
http://www.freelists.org/webpage/oracle-l