Re: Curious question about flushing the Pool

Tim,

I've had opportunity to tune the database in an attempt to fix a poorly ( very ) designed application. This app may well benefit from CURSOR_SHARING=FORCE, but that would negate the use of histograms on one table that makes good use of them.

That parameter may also have an effect on the optimizer_index_caching and optimizer_index_cost_adj parms as well, though I'm not sure about that one, and I'm too lazy to go look for it right now.

It will definitely negate histogram usage though.

Jared

PS. This is absolutely the worst designed app I have ever had the misfortune to work with. I seem to draw these somehow.

On Wednesday 24 April 2002 18:58, Tim Gorman 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: Jared Still
  INET: jkstill_at_cybcon.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).