True - but then you're up for a definition of what
constitutes filthy?
A query like
"select to_char(x,'...'), substr(y,1,3),instr(..)"
(ie insert any appropriate Oracle function that could
have static numeric/character arguments)
and suddenly its "filthy"...Still, I'd like something
more dramatic like
(First run)
"ORA-12345: Your SQL contains literals - could be a
bad idea"
(On cache reload)
"ORA-12346: Look I've told you already - fix that SQL"
:-)
Connor
- 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).
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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:13:22 CDT