RE: Curious question about flushing the Pool

I think an excellent Oracle kernel enhancement would be to bias in the LRU scheme against SQL that uses literals, just like the buffer cache algorithm biases against blocks that are read via full-table scan. Think about it... What's the likelihood that a SQL statement that's filthy with literal values will ever be reused again in the future? Then why store it as if it will ever be shared (i.e., reused) in the future?

 
Cary Millsap
Hotsos Enterprises, Ltd.
cary.millsap_at_hotsos.com
http://www.hotsos.com

-----Original Message-----
Sent: Wednesday, April 24, 2002 8:58 PM
To: Multiple recipients of list ORACLE-L

• 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: Cary Millsap
  INET: cary.millsap_at_hotsos.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).