Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Curious question about flushing the Pool

RE: Curious question about flushing the Pool

From: Seefelt, Beth <Beth.Seefelt_at_TetleyUSA.com>
Date: Thu, 25 Apr 2002 07:43:24 -0800
Message-ID: <F001.0044F60B.20020425074324@fatcity.com>

Wow, I think that's a brilliant idea. It would be a huge benefit to me, where all of our code is VB, and the developers refuse to use bind variables. My sql area is .5 GB and is 95% garbage.

-----Original Message-----
Sent: Thursday, April 25, 2002 9:03 AM
To: Multiple recipients of list ORACLE-L

okay, who do you still know inside Oracle who can push this enhancement? sounds eminently reasonable to me!

Rachel
--- Cary Millsap <cary.millsap_at_hotsos.com> wrote:
> 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).



Do You Yahoo!?
Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Seefelt, Beth
  INET: Beth.Seefelt_at_TetleyUSA.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 - 10:43:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US