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 <[EMAIL PROTECTED]> 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. > [EMAIL PROTECTED] > 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" > <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
