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 <[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). __________________________________________________ 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: [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: Seefelt, Beth 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).
