Message-ID: <F001.0044F1F1.20020425050327@fatcity.com>
Date: Thu, 25 Apr 2002 05:03:27 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: Rachel Carmichael <wisernet100@yahoo.com>
Subject: RE: Curious question about flushing the Pool
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 8bit

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

Rachel
--- Cary Millsap <cary.millsap@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@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@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@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@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@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@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@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@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@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@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).