| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Curious question about flushing the Pool
Minor correction, cursor_sharing did work in versions under 8.1.7.3 (I
used it in 8.1.6) but there was a bug relating to very specific usage.
I never encountered it, I know you can look up the details of the bug
on Metalink.
Having said that, I used cursor_sharing=force instead of flushing the shared pool because it does almost entirely eliminate the "out of memory" error, while flushing, if you misset the timing, doesn't.
We had programmers who did not want to use bind variables (Java prepared statements) and so, for an OLTP system where they were looking up registration information, we ended up with each SQL statement, differing only by the constant value being looked up, in the shared pool.
I lost the fight to have the code fixed, and so turned on cursor_sharing. Worked like a charm
Rachel
--- Tim Gorman <Tim_at_SageLogix.com> wrote:
> ---- begin rant -----
> It's *ALWAYS* a good idea to try to understand the underlying causes,
> for
> any and every situation. Too often people attempt to attack new
> problems
> with the same approach that they used before (or heard some "guru"
> advise),
> in a different context, in a different environment, on a different
> stack of
> technology, across a different mix of versions, with differing
> requirements
> for business rules, performance, availability, and end-user
> expectations.
> This might imply that all prior knowledge and experience is
> worthless, but
> rather it should simply imply that everything is changing constantly
> and you
> have to understand *why* something works instead of simply
> remembering
> *what* works in order to act appropriately...
>
> Sometimes, we'll try to save time by skipping the "understand why"
> steps,
> and sometimes you get away with it, and other times you get bit.
> After all,
> we're only human. I like the quote by the British author and
> large-animal
> country veterinarian James Herriott -- "Veterinary practice
> (substitute
> "database administration") gives one ample opportunity to make a
> complete
> ass of oneself". I've proven this many times over... ;-)
> ---- end rant -----
>
> In the case of flushing the shared pool, it is a valid response to
> the
> problem of OLTP applications not utilizing "bind-variables" and
> bollixing up
> the Shared SQL Area. In this case, using ALTER SYSTEM FLUSH
> SHARED_POOL is
> very much analogous to using chemo-therapy to treat cancer. The cure
> is
> very nearly as debilitating as the disease, but it works.
>
> I've always seen the use of FLUSH SHARED_POOL as the last resort when
> the
> problem is entirely in the hands of the application, provided the
> Oracle
> version is 8.1.6 or less. CURSOR_SHARING was introduced in 8.1.6,
> but it
> didn't work until 8.1.7.3, I understand. To this day, I've not yet
> encountered that type of malicious application in a database of
> version
> 8.1.7 or above (yet!), so I've not used CURSOR_SHARING yet...
>
> Without the availability of the CURSOR_SHARING=FORCE functionality,
> the
> Shared SQL Area is simply at the mercy of the application. As I
> visualize
> it (and I could be very wrong!), there is little contention as long
> as the
> Shared SQL Area is *filling up*. Once it is *full*, however, is when
> contention starts. Once the Shared SQL Area has filled, it becomes
> necessary for the RDBMS must find an entry to age-out of the cache
> instead
> of just simply locating the next empty slot. So, frequent usage of
> the
> FLUSH SHARED_POOL command continually keeps the Shared SQL Area on a
> less-contentious "always filling" basis, rather than the
> very-contentious
> "gotta-pitch-one-to-make-room-for-another" basis. SQL is not being
> re-used,
> but it's not being re-used anyway -- using FLUSH SHARED_POOL has no
> impact
> on that. At least, that's my simple-minded way of looking at it...
>
> Anyway, if this is the problem they are facing, then a script to
> periodically (i.e. 5 mins? 30 mins? 60 mins?) FLUSH SHARED_POOL may
> be the
> only way to survive. However, if there is another alternative, then
> it
> might be worthwhile to attempt to talk them off the precipice...
>
> Comments? Corrections? Rants?
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, April 24, 2002 5:53 PM
>
>
> > I see a couple of folks who want to
> > know how to flush the pool or are looking
> > for a script to do it automatically.
> >
> > Shouldn't we be asking what is causing
> > the behavior that got us to this quandry
> > in the first place ?
> >
> > Just a stupid question .. I know !
> >
> > Peace !
> >
> > Mike
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Johnson, Michael
> > INET: Michael.Johnson_at_oln-afmc.af.mil
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing
> Lists
> >
> --------------------------------------------------------------------
> > 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
> > also send the HELP command for other information (like
> subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Tim Gorman
> INET: Tim_at_SageLogix.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> 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
> also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: wisernet100_at_yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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 also send the HELP command for other information (like subscribing).Received on Thu Apr 25 2002 - 07:58:27 CDT
 |
 |