Re: Curious question about flushing the Pool
That its not as easy as just using embedded sql. That kind of duhveloper should quit immediately and get a job in sales or damagement, since they aren't interested in doing anything properly. That kind likes to see the glitter on the screen and cares little about what goes on behind the scenes. Jared On Thursday 25 April 2002 14:08, Seefelt, Beth wrote: Greg, That its not as easy as just using embedded sql. I don't agree, it takes just a little bit more thought and a couple extra lines of code per call, but I lost that battle... -Original Message- Sent: Thursday, April 25, 2002 4:40 PM To: Multiple recipients of list ORACLE-L Beth, What reasons do your programmers give for not wanting to use bind variables? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 25, 2002 8:43 AM 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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).
RE: Curious question about flushing the Pool
Oh, you have no idea. I could show you code that would make your hair stand on end. The manager of that department believes in working in a complete vacuum, and there is so much custom and undocumented code here, the management above him is terrified of ticking him off. It used to frustrate me, but after a few years now, its just kind of comical to sit back and watch the whole situation. -Original Message- Sent: Friday, April 26, 2002 9:18 AM To: [EMAIL PROTECTED]; Seefelt, Beth That its not as easy as just using embedded sql. That kind of duhveloper should quit immediately and get a job in sales or damagement, since they aren't interested in doing anything properly. That kind likes to see the glitter on the screen and cares little about what goes on behind the scenes. Jared On Thursday 25 April 2002 14:08, Seefelt, Beth wrote: Greg, That its not as easy as just using embedded sql. I don't agree, it takes just a little bit more thought and a couple extra lines of code per call, but I lost that battle... -Original Message- Sent: Thursday, April 25, 2002 4:40 PM To: Multiple recipients of list ORACLE-L Beth, What reasons do your programmers give for not wanting to use bind variables? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 25, 2002 8:43 AM 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. -- 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).
Re: Curious question about flushing the Pool
Oh, but I *do* have an idea. I've worked with a few of those people in the past. Jared On Friday 26 April 2002 06:30, Seefelt, Beth wrote: Oh, you have no idea. I could show you code that would make your hair stand on end. The manager of that department believes in working in a complete vacuum, and there is so much custom and undocumented code here, the management above him is terrified of ticking him off. It used to frustrate me, but after a few years now, its just kind of comical to sit back and watch the whole situation. -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Friday, April 26, 2002 9:18 AM To: [EMAIL PROTECTED]; Seefelt, Beth Subject: Re: Curious question about flushing the Pool That its not as easy as just using embedded sql. That kind of duhveloper should quit immediately and get a job in sales or damagement, since they aren't interested in doing anything properly. That kind likes to see the glitter on the screen and cares little about what goes on behind the scenes. Jared On Thursday 25 April 2002 14:08, Seefelt, Beth wrote: Greg, That its not as easy as just using embedded sql. I don't agree, it takes just a little bit more thought and a couple extra lines of code per call, but I lost that battle... -Original Message- Sent: Thursday, April 25, 2002 4:40 PM To: Multiple recipients of list ORACLE-L Beth, What reasons do your programmers give for not wanting to use bind variables? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 25, 2002 8:43 AM 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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).
Re: Curious question about flushing the Pool
Tim, I've had opportunity to tune the database in an attempt to fix a poorly ( very ) designed application. This app may well benefit from CURSOR_SHARING=FORCE, but that would negate the use of histograms on one table that makes good use of them. That parameter may also have an effect on the optimizer_index_caching and optimizer_index_cost_adj parms as well, though I'm not sure about that one, and I'm too lazy to go look for it right now. It will definitely negate histogram usage though. Jared PS. This is absolutely the worst designed app I have ever had the misfortune to work with. I seem to draw these somehow. On Wednesday 24 April 2002 18:58, Tim Gorman wrote: 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). --
Re: Curious question about flushing the Pool
I'll second that suggestion! To extend the analogy, how about a recycle pool for them? (So they don't fragment or otherwise clutter up the keep pool.) How about adaptive auto-pin in the keep pool based on execution frequency? (Never mind... Probably not really necessary. I'd settle for the LRU bias.) Don Granaman [OraSaurus] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 11:03 PM 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman 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).
Re: Curious question about flushing the Pool
Comments? Corrections? Rants? I'll go for rant... I've had long and painful experience with a few pathological applications. One in particular contained hundreds of sets of common SQL statements - varying only in the literal values. Many were executed hundreds of thousands or even millions of times every day. (e.g. What could have been a few hundred distinct statements with bind variables was instead tens or hundreds of millions of distinct statements in the course of a day.) None of these applications ran on anything later than Oracle 8.1.7.1 - some on 7.3.x. On the vast majority of the 8i systems, there was some bug/quirk that prevented CURSOR_SHARING=FORCE. (For example, one such bug/quirk was that if the value for the first variable in a bind list was a null, the application process puked and either hung or died. I don't now remember the specific color, chunksize, ORA-x, or bug number though.) The typical symptom was a gradual degradation of overall performance as the shared pool filled up. When it slowed to a crawl, the only remedy was to flush the shared pool - which resulted temporarily in a much more dramatic performance hit. (Would you like that adhesive tape pulled off your hairy chest a little at a time or in one quick heart-rending yank?) Then everything ran fine until we came full-circle again on the roller-coaster ride. We created a scheduled job to flush the shared pool and tuned the frequency with which it ran! This continued for at least 2 1/2 years (until I left). I hear that it is still the main performance issue with this particular ultra-critical multi-million dollar system (loaded E10K, large EMC Sym, ...). I showed the outsourcing consultants that designed and wrote this atrocity the wait analysis - which they adamantly insisted was NOT caused by their code. (Tuning is the DBA's responsibility - and our management bought it). My most convincing argument was routinely dumping a join of v$SQLAREA and v$SQLTEXT sorted by SQL_TEXT into a file. Actually seeing 60,000+ copies of the same statement differing only in literal values, followed by 23,000 of another, etc. eventually convinced management that it might actually be a problem. (This isn't an argument against using the wait interface, but in this case it was unnecessary - the problem was quite obvious). Just as I fled the company, one of the people who took over this system came up with a simple solution (encouraged by Oracle support and several very popular tuning books) - he raised the shared pool size to almost a gigabyte - in spite of my admonitions. Oddly enough, that made it MUCH worse! ;-) In this case, the flushing solution was just barely survival. And, obviously, it did nothing to avoid the wildly excessive parsing. In other words, I agree - flushing the shared pool is curing symptoms, not the disease. Don Granaman [cranky old OraSaurus with enough war stories to publish a multi-volume set] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 8:58 PM [... snipped for brevity ...] 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,
RE: Curious question about flushing the Pool
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--
Re: Curious question about flushing the Pool
Minor correction, cursor_sharing did work in versions under 8.1.7.3 (I used it in 8.1.6) but there was a bug relating to very specific usage. I never encountered it, I know you can look up the details of the bug on Metalink. Having said that, I used cursor_sharing=force instead of flushing the shared pool because it does almost entirely eliminate the out of memory error, while flushing, if you misset the timing, doesn't. We had programmers who did not want to use bind variables (Java prepared statements) and so, for an OLTP system where they were looking up registration information, we ended up with each SQL statement, differing only by the constant value being looked up, in the shared pool. I lost the fight to have the code fixed, and so turned on cursor_sharing. Worked like a charm Rachel --- Tim Gorman [EMAIL PROTECTED] wrote: 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
RE: Curious question about flushing the Pool
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
RE: Curious question about flushing the Pool
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--
RE: Curious question about flushing the Pool
I lost the fight to have the code fixed, and so turned on cursor_sharing. Worked like a charm Been there, done that... but now I feel better knowing that a tenacious goddess of the DBA battlefields also lost this fight. :-) It's a tough fight when embedded SQL is scattered all over the place and development is more concerned with coding the next feature set on Internet time. Apart from the tons of embedded SQL, we have a dynamic SQL generator function in our code which creates SQL with literals based on states or values in webpage check boxes, radio buttons, dropdownlists, text boxes, etc. Since this is a central, oft-used part of the app I'm hoping to get duhvelopment to use bind variables in it but it's going to be an uphill battle. Just the normal frustration in the on-going DBA/Duhveloper battle. Sigh... Steve Orr Bozeman, MONTANA! -Original Message- Sent: Thursday, April 25, 2002 6:58 AM To: Multiple recipients of list ORACLE-L Importance: High Minor correction, cursor_sharing did work in versions under 8.1.7.3 (I used it in 8.1.6) but there was a bug relating to very specific usage. I never encountered it, I know you can look up the details of the bug on Metalink. Having said that, I used cursor_sharing=force instead of flushing the shared pool because it does almost entirely eliminate the out of memory error, while flushing, if you misset the timing, doesn't. We had programmers who did not want to use bind variables (Java prepared statements) and so, for an OLTP system where they were looking up registration information, we ended up with each SQL statement, differing only by the constant value being looked up, in the shared pool. I lost the fight to have the code fixed, and so turned on cursor_sharing. Worked like a charm Rachel -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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).
Re: Curious question about flushing the Pool
It sounds a cute idea at first sight (pardon the mixed
metaphor) - but then what do you do about the situation
where you deliberately have a handful of versions of
the 'same' SQL which are identified by the presence of
a literal string; or the SQL that you build with one
literal and many binds because that one literal is
required to make sure that Oracle uses a histogram
on one column ?
Then there's the problem of literals which are
textually constant but variable valued such as:
sys_context('hr_hierarchy','manager');
Marginal proliferation of 'semi-literate' joke SQL
can be a good thing. Maybe yet another undocumented
hint /*+ bypass_cache_flush */ would be sufficient.
One (trivial ?) thing I'd like to see is SQL normalised
before it goes into the shared pool. Oracle 9i does it
with stored_outlines - why not with all SQL ?
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 25 April 2002 05:00
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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
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).
Re: Curious question about flushing the Pool
So have you tried dropping your SGA to 275MB so that the stuff that is useful can be found quicker and latches are held for a shorter time ? Also consider looking at cursor_sharing - it's a band-aid but it can work well in extremis. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 25 April 2002 16:47 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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).
RE: Curious question about flushing the Pool
Some truisms I've found after losing the fight at several different locations: 1) If the programming staff was there before you got there, you will lose every fight 2) If the programming staff was hired after you got there, but has a prior working history with management, you will lose every fight 3) If what you ask for means going back and recoding existing working code, you will lose every fight 4) If you haven't convinced the development staff that it really is essential that you are part of the design process, you will lose every fight 5) if the code has been provided by a 3rd party vendor, you will lose every fight 6) No is a complete sentence. But it should be backed up with the reasons why doing whatever it is that they want you to will adversely impact the application. They don't care what it does to the database, but if it hurts the visible app, they will listen 7) DOCUMENT EVERYTHING. Every time you lose a fight, send a note to your home email address and to your manager explaining why you feel this is a bad idea. Do not send it to your office email address, it's too easy for those files to get lost (anyone old enough to remember the missing 18 minutes of tape?) --- Orr, Steve [EMAIL PROTECTED] wrote: I lost the fight to have the code fixed, and so turned on cursor_sharing. Worked like a charm Been there, done that... but now I feel better knowing that a tenacious goddess of the DBA battlefields also lost this fight. :-) It's a tough fight when embedded SQL is scattered all over the place and development is more concerned with coding the next feature set on Internet time. Apart from the tons of embedded SQL, we have a dynamic SQL generator function in our code which creates SQL with literals based on states or values in webpage check boxes, radio buttons, dropdownlists, text boxes, etc. Since this is a central, oft-used part of the app I'm hoping to get duhvelopment to use bind variables in it but it's going to be an uphill battle. Just the normal frustration in the on-going DBA/Duhveloper battle. Sigh... Steve Orr Bozeman, MONTANA! -Original Message- Sent: Thursday, April 25, 2002 6:58 AM To: Multiple recipients of list ORACLE-L Importance: High Minor correction, cursor_sharing did work in versions under 8.1.7.3 (I used it in 8.1.6) but there was a bug relating to very specific usage. I never encountered it, I know you can look up the details of the bug on Metalink. Having said that, I used cursor_sharing=force instead of flushing the shared pool because it does almost entirely eliminate the out of memory error, while flushing, if you misset the timing, doesn't. We had programmers who did not want to use bind variables (Java prepared statements) and so, for an OLTP system where they were looking up registration information, we ended up with each SQL statement, differing only by the constant value being looked up, in the shared pool. I lost the fight to have the code fixed, and so turned on cursor_sharing. Worked like a charm Rachel -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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).
Re: Curious question about flushing the Pool
Beth, What reasons do your programmers give for not wanting to use bind variables? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 25, 2002 8:43 AM 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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).
RE: Curious question about flushing the Pool
Greg, That its not as easy as just using embedded sql. I don't agree, it takes just a little bit more thought and a couple extra lines of code per call, but I lost that battle... -Original Message- Sent: Thursday, April 25, 2002 4:40 PM To: Multiple recipients of list ORACLE-L Beth, What reasons do your programmers give for not wanting to use bind variables? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 25, 2002 8:43 AM 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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).
RE: Curious question about flushing the Pool
Rachel, Shall we crown you the Jerry Quarry of Oracle DBAs? -Original Message- Carmichael Sent: Thursday, April 25, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Some truisms I've found after losing the fight at several different locations: 1) If the programming staff was there before you got there, you will lose every fight 2) If the programming staff was hired after you got there, but has a prior working history with management, you will lose every fight 3) If what you ask for means going back and recoding existing working code, you will lose every fight 4) If you haven't convinced the development staff that it really is essential that you are part of the design process, you will lose every fight 5) if the code has been provided by a 3rd party vendor, you will lose every fight 6) No is a complete sentence. But it should be backed up with the reasons why doing whatever it is that they want you to will adversely impact the application. They don't care what it does to the database, but if it hurts the visible app, they will listen 7) DOCUMENT EVERYTHING. Every time you lose a fight, send a note to your home email address and to your manager explaining why you feel this is a bad idea. Do not send it to your office email address, it's too easy for those files to get lost (anyone old enough to remember the missing 18 minutes of tape?) --- Orr, Steve [EMAIL PROTECTED] wrote: I lost the fight to have the code fixed, and so turned on cursor_sharing. Worked like a charm Been there, done that... but now I feel better knowing that a tenacious goddess of the DBA battlefields also lost this fight. :-) It's a tough fight when embedded SQL is scattered all over the place and development is more concerned with coding the next feature set on Internet time. Apart from the tons of embedded SQL, we have a dynamic SQL generator function in our code which creates SQL with literals based on states or values in webpage check boxes, radio buttons, dropdownlists, text boxes, etc. Since this is a central, oft-used part of the app I'm hoping to get duhvelopment to use bind variables in it but it's going to be an uphill battle. Just the normal frustration in the on-going DBA/Duhveloper battle. Sigh... Steve Orr Bozeman, MONTANA! -Original Message- Sent: Thursday, April 25, 2002 6:58 AM To: Multiple recipients of list ORACLE-L Importance: High Minor correction, cursor_sharing did work in versions under 8.1.7.3 (I used it in 8.1.6) but there was a bug relating to very specific usage. I never encountered it, I know you can look up the details of the bug on Metalink. Having said that, I used cursor_sharing=force instead of flushing the shared pool because it does almost entirely eliminate the out of memory error, while flushing, if you misset the timing, doesn't. We had programmers who did not want to use bind variables (Java prepared statements) and so, for an OLTP system where they were looking up registration information, we ended up with each SQL statement, differing only by the constant value being looked up, in the shared pool. I lost the fight to have the code fixed, and so turned on cursor_sharing. Worked like a charm Rachel -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing
RE: Curious question about flushing the Pool
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Rachel Carmichael Sent: Thursday, April 25, 2002 1:09 PM To: Multiple recipients of list ORACLE-L Subject: RE: Curious question about flushing the Pool 4) If you haven't convinced the development staff that it really is essential that you are part of the design process, you will lose every fight Been dealing with that lately, but not necessarily losing. Management issue with the left hand not talking to the right hand and apps suddenly appearing and someone emailing you asking what's wrong with the database (performance is bad) and no one has ever even heard of the application. Oh yeah, we developed a web app and rolled it out this weekend. We decided to do some authentication and validation against so and so database.. Well, thanks for telling us. But the thing is, someone in IT management knew, and someone in the DBA group had to know for the priv's to be there. Big time communication problems within the IT group, and with external groups. But things *are* changing ;-) And they are starting to see the value of getting us involved early on in the process. Larry G. Elkins [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins 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).
RE: Curious question about flushing the Pool
Hey Dan! I've seen Jerry Quarry fight, he's done better in his fights than I have in some of mine! did you ever do the startup tests we talked about? Rachel --- Daniel W. Fink [EMAIL PROTECTED] wrote: Rachel, Shall we crown you the Jerry Quarry of Oracle DBAs? -Original Message- Carmichael Sent: Thursday, April 25, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Some truisms I've found after losing the fight at several different locations: 1) If the programming staff was there before you got there, you will lose every fight 2) If the programming staff was hired after you got there, but has a prior working history with management, you will lose every fight 3) If what you ask for means going back and recoding existing working code, you will lose every fight 4) If you haven't convinced the development staff that it really is essential that you are part of the design process, you will lose every fight 5) if the code has been provided by a 3rd party vendor, you will lose every fight 6) No is a complete sentence. But it should be backed up with the reasons why doing whatever it is that they want you to will adversely impact the application. They don't care what it does to the database, but if it hurts the visible app, they will listen 7) DOCUMENT EVERYTHING. Every time you lose a fight, send a note to your home email address and to your manager explaining why you feel this is a bad idea. Do not send it to your office email address, it's too easy for those files to get lost (anyone old enough to remember the missing 18 minutes of tape?) --- Orr, Steve [EMAIL PROTECTED] wrote: I lost the fight to have the code fixed, and so turned on cursor_sharing. Worked like a charm Been there, done that... but now I feel better knowing that a tenacious goddess of the DBA battlefields also lost this fight. :-) It's a tough fight when embedded SQL is scattered all over the place and development is more concerned with coding the next feature set on Internet time. Apart from the tons of embedded SQL, we have a dynamic SQL generator function in our code which creates SQL with literals based on states or values in webpage check boxes, radio buttons, dropdownlists, text boxes, etc. Since this is a central, oft-used part of the app I'm hoping to get duhvelopment to use bind variables in it but it's going to be an uphill battle. Just the normal frustration in the on-going DBA/Duhveloper battle. Sigh... Steve Orr Bozeman, MONTANA! -Original Message- Sent: Thursday, April 25, 2002 6:58 AM To: Multiple recipients of list ORACLE-L Importance: High Minor correction, cursor_sharing did work in versions under 8.1.7.3 (I used it in 8.1.6) but there was a bug relating to very specific usage. I never encountered it, I know you can look up the details of the bug on Metalink. Having said that, I used cursor_sharing=force instead of flushing the shared pool because it does almost entirely eliminate the out of memory error, while flushing, if you misset the timing, doesn't. We had programmers who did not want to use bind variables (Java prepared statements) and so, for an OLTP system where they were looking up registration information, we ended up with each SQL statement, differing only by the constant value being looked up, in the shared pool. I lost the fight to have the code fixed, and so turned on cursor_sharing. Worked like a charm Rachel -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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
Re: Curious question about flushing the Pool
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).
RE: Curious question about flushing the Pool
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
