RE: Do programmers tune SQL?
Thanks to everyone that replied to this question. I feel that I have a MUCH more well-rounded view of how this works at other organizations, and where the pitfalls are. I am going to develop a document for the developers that outlines how I see this working. I will share it with the list when I complete it. Thanks again. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 01, 2002 7:48 PM To: Multiple recipients of list ORACLE-L There was one guy at a site I used to work for that was interesting. He wrote his own shell scripts and PL/SQL routines and did them well. He was open to learning and all that good stuff. However, he felt that it was the sys. admin's job to write the shell scripts and the DBA's to write the PL/SQL (not the SQL). Its like ya, you want me to write your Java and C code also? Just a funny mix -Original Message- Faroult Sent: Monday, April 01, 2002 7:18 AM To: Multiple recipients of list ORACLE-L Rachel Carmichael wrote: My last shop we had one programmer who not only expected me to tune his SQL, he expected me to WRITE his SQL for me. I got emails I need a query that returns this information from these tables. This guy seems to be trying a new tactic these days: DBA mailing lists. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Kimberly Smith 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: DENNIS WILLIAMS 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: Do programmers tune SQL?
Whether a programmer tunes SQL depends on their answer to another question, Does the programmer care about overall application performance? If they do and a significant part of the application involves a database then they need to get on a professional development track to become a Database Programmer just like other programmers may need to become Network Programmers, Real Time Embedded Systems Programmers, Internet Programmers, or, for those old Mainframe heads, Systems Programmers. If they don't want to be bothered with such stuff then they'll remain mere programmers and never progress to Developers, Software Engineers, Systems Analysts, System Architects, and other gods. IMHO :-) Programming for the database in not that much of a specialty and anyone who wants to be a pro should get some solid experience with SQL. If you just read Harrison's book then you may think that SQL tuning is overwhelming but it really doesn't have to be that way. If you were to distill down SQL tuning to the basics then you could create a programmers guide to SQL that would be simple, concise, (less than 10 pages) and easy to understand. I wouldn't expect every programmer to come up with SQL so good that there's no room for improvement but... I do expect them to make an effort. I've seen so much sloppy SQL where it was obvious that all the programmer cared about was getting an expected result set against limited data and then moving on to the next function to program. If someone has good skills for producing efficient C code, then those same skills can easily be applied to SQL... actually SQL is really a lot easier. One approach is to create a database API to centralize all your database calls and avoid embedded SQL scattered throughout the code which is difficult to maintain or tune. With all database calls passing through the API, skilled database programmers can tune database access without breaking the application. But there is a point at which SQL tuning becomes overly obtuse, obscure, or esoteric, especially for Oracle-specific or other database-specific tuning. That's when the DBA comes in. For instance, I think it should be rare that the programmer be concerned with using Oracle hints. The cost based optimizer is getting good enough now that we can rely on it a great deal for providing adequate SQL execution paths... just as long as the SQL isn't sloppy to begin with. If we introduce too many hints or other obscure SQL tuning efforts then we create a maintenance burden because the data changes and today's good SQL statement may become tomorrow's pig. Just today I saw a query with an 11 table join and 3 of the 11 tables referenced were the same table but with different aliases. Not only could this have been down with just one join to that table, some of the other join tables were not needed altogether. On top of that, to limit the size of the result set they were using distinct because they couldn't figure out how to do it otherwise. Ugggh!! That's what happens with a programmer thinks he shouldn't have to be bothered with developing good SQL technique. Steve Orr Bozeman, MT -Original Message- Sent: Monday, April 01, 2002 1:13 PM To: Multiple recipients of list ORACLE-L I think there's a new myth: Programmers should tune SQL. Harrison says his book is for developers, but consider what he actually covers. Chapter 8, Tuning Table Access, covers many topics that are for DBA's, not developers: -- hit rate in the buffer cache -- db_file_multiblock_read_count -- number of blocks used for the table -- size of data blocks -- depth of index -- histograms -- use of ANALYZE and dbms_stats -- subtle points of index creation -- types of indexes, strategies -- fast full index scan -- bitmap_merge_area_size parm -- alter table minimize records_per_block -- setting up hash clusters -- IOT's, configuring the overflow statement -- periodic rebuild of indexes -- fast_full_scan_enabled=true parm -- lowering the high water mark -- optimizing PCTFREE andd PCTUSED That's from just *one* chapter. Oh sure, he also devotes a few pages to avoiding accidental full table scans caused by SQL that disables an index, etc. But how often are developers going to tune SQL by using the rest of the stuff in this chapter? The root problem is, the phrase tune SQL is a myth. Sure the SQL runs slow, then you tune it and it runs faster. But tuning it often requires DBA knowledge, something DBA's may take for granted, but for developers it's a huge area they have no familiarity with at all. There's a specific set of things you can ask developers to do, but there's another set of things that are required and that you can't reasonably ask developers to do. Harrison's idea that you can get developers to tune SQL is a myth. He's really written a DBA book that delves into tuning SQL, which is a reasonable goal. But to do the reverse -- asking a developer to delve into tuning SQL -- means they have to come to grips with DBA topics,
Re: Do programmers tune SQL?
[EMAIL PROTECTED] wrote: I may open a can of worms - but don't intend to... zymurgy's law: once you open a can of worms, the only way to recan them is to get a bigger can.;-) In our company many developers have DBA access to databases and in fact nobody does anything stupid. I have found that if people don't know what they are doing, they ask. Nobody tries to drop or truncate tables because they have found the script that seesm cool and want to see what it does... Over my 8 years here only once we had a problem - a few tables were dropped by mistake but the tables were dropped by our DBA who run the wrong script. It took almost no time at all to rebuild it. And it happened in a test database. you are in a very good environment, stay there. i have had to give up DBA access to test databases to developers here, and then spend a lot of time rebuilding them because they had no clue as to what they were doing. it took me months of wasted time and long hours to get control back. and i had to keep up with production too. i am obviously reluctant to give it up again, but i am more than willing to work with them to do any tuning needed. I understand restrictions in production databases but more access to development and test would make life easier and am sure more happy faces around. only if you fix what you break, if i have to keep going in and fixing it i have less time in an already busy schedule to do what i need to in the production side. -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. You are not thinking. You are merely being logical. - Neils Bohr to Albert Einstein -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater 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: Do programmers tune SQL?
Well, I sent this to the list yesterday but it never showed up, trying again. Learn the basics of executing SQL from Java: Statement - basic SQL statement Prepared Statement - precompiled SQL statement (bind variables) Callable Statement - calling database procedures/functions Get the O'Reilly book Java Enterprise in a Nutshell. That book was a lifesaver for me when working with Java developers, as it helped me understand and communicate in terms they understood. Suzy DENNIS WILLIAMS wrote: Oh, I guess I'm a little slow on Monday. I've enjoyed the discussion so far. Very close to my situation. Given my situation - i.e., we haven't had many SQL statement problems, but expecting to receive more with Java, I'm wondering how I can get ahead of the game. I have worked on a set of SQL statement recommendations, simple stuff like make sure screen queries use and index. I am considering creating a checklist form for SQL statements for the developers to use. Like include the listing from EXPLAIN PLAN. Does anyone have any thoughts on this approach? I'm not sure if the developers/management would go for it, but I thought it would be worth asking as a starting point. Everybody is new to Java and a little nervous, so they are probably more open to suggestions. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 01, 2002 9:13 AM To: Multiple recipients of list ORACLE-L and your question is? --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: All, We are in transition here at my site. Our primary development tool over the past few years has been a client-server tool named Uniface from Compuware, which does an excellent job of protecting developers from themselves. However, we are switching to Java, and moving more developers from the mainframe to an Oracle-Java system that is being developed. For a Java IDE, it looks like a tool named TogetherSoft. I am concerned that I will need to take a more active role with the developers. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 29, 2002 8:03 PM To: Multiple recipients of list ORACLE-L Greg, You *do* see DBA's doing the bulk of the SQL tuning work in many shops. But it's not necessarily because the developers, or at least some them, can't, or, that many of them don't care (and *many* of them never do give it a thought). I've seen places where the developers begged for the ability to turn on tracing in development, or to have a plan_table and/or the use of autotrace, and were denied. And other cases where the development, testing, and QA environments were so different from production that there was nearly no point. Anyway, just by virtue of their titles, I don't know that a DBA is any better at SQL tuning than a developer or vice versa (and I'm not pointing that comment at you, Greg, but just in general that I don't think the title of DBA or developer makes a difference). It really depends on their backgrounds and skill levels. I've seen, for the most obvious example, many DBA's and developers freak when they see a full table scan, never taking into consideration if that was the appropriate approach. Instead, they just lived by some rule that full table scans are bad. You see lots of things like that. Anyway, as someone who started off as both a DBA and developer, and drifts back and forth between the two and still serving in both roles, I can see both sides. I know DBA's who rant about the developers not giving a flip about performance when they write their code, and in many cases it is true, the issue of performance was never considered. But I also know many developers who *do* care and are hindered from doing so. By the same token, I know a lot of DBA's who are very good at SQL tuning, and tuning and general, and many more who aren't. So, what we can we do? We can work with the developers (and DBA's) and mentor them. We can teach the tricks and efficient styles (whether SQL itself or application design in general). And it really helps if we can provide an environment that mimics production (dollars and budgets make that hard to do in many cases). Sorry for the length, but it touches on something I'm dealing with right now. I'm helping some developers who are getting hammered about why their code performs so poorly in production. Heck, it ran great in all the other environments, there's not much more that they could have done. And yes, I now sit in on the code reviews making suggestions when something could be done better, and testing their code and every SQL statement against production. Often times requires significant work in stubbing out the DML pieces and duplicating the same logic when doing so. But if they aren't given a real
Re: Do programmers tune SQL?
On Fri, Mar 29, 2002 at 03:33:20PM -0800, John Kanagaraj wrote: The reason may be driven by the requirement of 'develop-within-this-deadline' for Developers, and 'get-this-query-to-respond-within-1-sec' for DBAs. The DBA's responsibility (among others) is to run a well tuned system, the Developers' responsbility is to develop a working product, and the two goals twain meet! -- and the two goals twain meet twain Pronunciation Key (twn) n. adj. pron. Two. Perhaps you mean the two goals ner meet, as in ner the twain shall meet? Gotta run, gotta catch a twain. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell 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: Do programmers tune SQL?
Those of you who have been on the list for a few years probably remember me asking stupid questions when I was a duh-veloper. (I remember one in particular where I flipped out because I didn't remember the difference between UNION and UNION ALL and got my a$$ handed to me.) My two cents: Not all DBA's know how to tune SQL, either. When there is a big problem here, I usually get a phone call and a request to look over a tkprof/explain/sql statement. Of course it is always urgent. I have taught a couple of duhvelopers to do it - however I am not going to hold their hands through it. I have gladly helped with some complex statements and explain plans, but usually end up explaining the basics and then handing them Harrison's tuning book and offering to answer questions. If the person doesn't take the initiative, he or she won't remember a word I said. Lisa Koivu Oracle Database Tank Fairfield Resorts, Inc. 954-935-4117 -Original Message- From: Jamadagni, Rajendra [SMTP:[EMAIL PROTECTED]] Sent: Sunday, March 31, 2002 8:48 PM To: Multiple recipients of list ORACLE-L Subject: RE: Do programmers tune SQL? I moved from development to DBA, so now no one comes around and tells us that 'such and such this is not possible in Oracle' .. or 'writing that kind of code will take two weeks'. Some developers love to tune, some aren't afraid to ask after experimentation, some want us to do their work. Then there is another category (which I love to ignore), even if you prove they are wrong, somehow they are always right ... Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! File: ESPN_Disclaimer.txt -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa 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: Do programmers tune SQL?
Greg, Don't I wish the developers would use the tools available to them for tuning. Zilch, None, Nada. I live with what I get from the developers and I have yet been able to get any changes made to the applications. This is one of the problems you encounter where there is 2 distinct departments and philosophies. Heck, I have to fight to get the developers to give me projected record count for 2 years space usage on a table. It's a job and it's a challenge. ROR mª¿ªm [EMAIL PROTECTED] 03/29/02 05:38PM What percent of developers know how to explain and trace SQL, interpret these reports and tune? In my experience it's about 10%, so most SQL tuning is done by DBA's. Is that about right? -- 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: Ron Rogers 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: Do programmers tune SQL?
All, We are in transition here at my site. Our primary development tool over the past few years has been a client-server tool named Uniface from Compuware, which does an excellent job of protecting developers from themselves. However, we are switching to Java, and moving more developers from the mainframe to an Oracle-Java system that is being developed. For a Java IDE, it looks like a tool named TogetherSoft. I am concerned that I will need to take a more active role with the developers. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 29, 2002 8:03 PM To: Multiple recipients of list ORACLE-L Greg, You *do* see DBA's doing the bulk of the SQL tuning work in many shops. But it's not necessarily because the developers, or at least some them, can't, or, that many of them don't care (and *many* of them never do give it a thought). I've seen places where the developers begged for the ability to turn on tracing in development, or to have a plan_table and/or the use of autotrace, and were denied. And other cases where the development, testing, and QA environments were so different from production that there was nearly no point. Anyway, just by virtue of their titles, I don't know that a DBA is any better at SQL tuning than a developer or vice versa (and I'm not pointing that comment at you, Greg, but just in general that I don't think the title of DBA or developer makes a difference). It really depends on their backgrounds and skill levels. I've seen, for the most obvious example, many DBA's and developers freak when they see a full table scan, never taking into consideration if that was the appropriate approach. Instead, they just lived by some rule that full table scans are bad. You see lots of things like that. Anyway, as someone who started off as both a DBA and developer, and drifts back and forth between the two and still serving in both roles, I can see both sides. I know DBA's who rant about the developers not giving a flip about performance when they write their code, and in many cases it is true, the issue of performance was never considered. But I also know many developers who *do* care and are hindered from doing so. By the same token, I know a lot of DBA's who are very good at SQL tuning, and tuning and general, and many more who aren't. So, what we can we do? We can work with the developers (and DBA's) and mentor them. We can teach the tricks and efficient styles (whether SQL itself or application design in general). And it really helps if we can provide an environment that mimics production (dollars and budgets make that hard to do in many cases). Sorry for the length, but it touches on something I'm dealing with right now. I'm helping some developers who are getting hammered about why their code performs so poorly in production. Heck, it ran great in all the other environments, there's not much more that they could have done. And yes, I now sit in on the code reviews making suggestions when something could be done better, and testing their code and every SQL statement against production. Often times requires significant work in stubbing out the DML pieces and duplicating the same logic when doing so. But if they aren't given a real environment, and, they are interested, I have sympathy when seeing them hammered for poor performing code and SQL statements when they did everything they could with what they were provided. Oh well, end of the week rant of sorts. I'm sending everyone a case of their favorite scotch if they just ask ;-) Just a test to see if anyone makes it this far ;-) Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Greg Moore Sent: Friday, March 29, 2002 4:38 PM To: Multiple recipients of list ORACLE-L Subject: Do programmers tune SQL? What percent of developers know how to explain and trace SQL, interpret these reports and tune? In my experience it's about 10%, so most SQL tuning is done by DBA's. Is that about right? -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access /
Re: Do programmers tune SQL?
John Kanagaraj wrote: Greg, The reason may be driven by the requirement of 'develop-within-this-deadline' for Developers, and 'get-this-query-to-respond-within-1-sec' for DBAs. The DBA's responsibility (among others) is to run a well tuned system, the Developers' responsbility is to develop a working product, and the two goals twain meet! Also, most of the older DBAs - at least I can speak for myself - grew out of Development, basically because we showed some troubleshooting abilities and were curious about the innards of Oracle and the OS, and this development experience helps in tuning. I have known a few developers who knew how to tune SQL, but I do admit, they are rare... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 I fully agree with both Greg, about how few developers have heard about tuning, and John about the reasons. I find it very unfortunate that most 'performance' courses (and books) are aimed at DBAs. Everybody states (with reason) that most performance gains come from tuning the code, but somehow this is preached to the wrong audience. Sadly, 'tuning' is something quite different from 'writing efficient code' - as a DBA you'll try to do the best out of what you have at your disposal, perhaps a couple of particularly ugly statements, and features available in the latest release. As a developer, you often start designing with one release, most of the development, QA and early production will be done with the next one, and most of the life of the development will occur under the reign of release n+2. Under these conditions, all the fine tuning so many DBAs delight in is a luxury - coding straight is the necessity. Human nature being what it is, making things complicated is much easier than doing them simple. When I read the operating instructions of a VCR (or when I read the instructions to fill my tax return form) I have this tragic feeling of a world crushed by complexity. There would be much to say about the quality of technical management too. That said, John you ALSO are the exception more than the rule. I have known more than one honest decent DBA who was far from feeling at ease with SQL. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Do programmers tune SQL?
and your question is? --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: All, We are in transition here at my site. Our primary development tool over the past few years has been a client-server tool named Uniface from Compuware, which does an excellent job of protecting developers from themselves. However, we are switching to Java, and moving more developers from the mainframe to an Oracle-Java system that is being developed. For a Java IDE, it looks like a tool named TogetherSoft. I am concerned that I will need to take a more active role with the developers. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 29, 2002 8:03 PM To: Multiple recipients of list ORACLE-L Greg, You *do* see DBA's doing the bulk of the SQL tuning work in many shops. But it's not necessarily because the developers, or at least some them, can't, or, that many of them don't care (and *many* of them never do give it a thought). I've seen places where the developers begged for the ability to turn on tracing in development, or to have a plan_table and/or the use of autotrace, and were denied. And other cases where the development, testing, and QA environments were so different from production that there was nearly no point. Anyway, just by virtue of their titles, I don't know that a DBA is any better at SQL tuning than a developer or vice versa (and I'm not pointing that comment at you, Greg, but just in general that I don't think the title of DBA or developer makes a difference). It really depends on their backgrounds and skill levels. I've seen, for the most obvious example, many DBA's and developers freak when they see a full table scan, never taking into consideration if that was the appropriate approach. Instead, they just lived by some rule that full table scans are bad. You see lots of things like that. Anyway, as someone who started off as both a DBA and developer, and drifts back and forth between the two and still serving in both roles, I can see both sides. I know DBA's who rant about the developers not giving a flip about performance when they write their code, and in many cases it is true, the issue of performance was never considered. But I also know many developers who *do* care and are hindered from doing so. By the same token, I know a lot of DBA's who are very good at SQL tuning, and tuning and general, and many more who aren't. So, what we can we do? We can work with the developers (and DBA's) and mentor them. We can teach the tricks and efficient styles (whether SQL itself or application design in general). And it really helps if we can provide an environment that mimics production (dollars and budgets make that hard to do in many cases). Sorry for the length, but it touches on something I'm dealing with right now. I'm helping some developers who are getting hammered about why their code performs so poorly in production. Heck, it ran great in all the other environments, there's not much more that they could have done. And yes, I now sit in on the code reviews making suggestions when something could be done better, and testing their code and every SQL statement against production. Often times requires significant work in stubbing out the DML pieces and duplicating the same logic when doing so. But if they aren't given a real environment, and, they are interested, I have sympathy when seeing them hammered for poor performing code and SQL statements when they did everything they could with what they were provided. Oh well, end of the week rant of sorts. I'm sending everyone a case of their favorite scotch if they just ask ;-) Just a test to see if anyone makes it this far ;-) Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Greg Moore Sent: Friday, March 29, 2002 4:38 PM To: Multiple recipients of list ORACLE-L Subject: Do programmers tune SQL? What percent of developers know how to explain and trace SQL, interpret these reports and tune? In my experience it's about 10%, so most SQL tuning is done by DBA's. Is that about right? -- 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). -- Please see the official
Re: Do programmers tune SQL?
Rachel Carmichael wrote: My last shop we had one programmer who not only expected me to tune his SQL, he expected me to WRITE his SQL for me. I got emails I need a query that returns this information from these tables. This guy seems to be trying a new tactic these days: DBA mailing lists. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Do programmers tune SQL?
I wouldn't put it past him. But I don't recognize his name on any of those questions :) --- Stephane Faroult [EMAIL PROTECTED] wrote: Rachel Carmichael wrote: My last shop we had one programmer who not only expected me to tune his SQL, he expected me to WRITE his SQL for me. I got emails I need a query that returns this information from these tables. This guy seems to be trying a new tactic these days: DBA mailing lists. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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! Greetings - send holiday greetings for Easter, Passover http://greetings.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: Do programmers tune SQL?
is to develop a working product, and the two goals twain meet! -- and the two goals twain meet twain Pronunciation Key (twn) n. adj. pron. Two. Perhaps you mean the two goals ner meet, as in ner the twain shall meet? Gotta run, gotta catch a twain. === Yes, Ray. That is what I meant. Thanks for the update! John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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: Do programmers tune SQL?
Oh, I guess I'm a little slow on Monday. I've enjoyed the discussion so far. Very close to my situation. Given my situation - i.e., we haven't had many SQL statement problems, but expecting to receive more with Java, I'm wondering how I can get ahead of the game. I have worked on a set of SQL statement recommendations, simple stuff like make sure screen queries use and index. I am considering creating a checklist form for SQL statements for the developers to use. Like include the listing from EXPLAIN PLAN. Does anyone have any thoughts on this approach? I'm not sure if the developers/management would go for it, but I thought it would be worth asking as a starting point. Everybody is new to Java and a little nervous, so they are probably more open to suggestions. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 01, 2002 9:13 AM To: Multiple recipients of list ORACLE-L and your question is? --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: All, We are in transition here at my site. Our primary development tool over the past few years has been a client-server tool named Uniface from Compuware, which does an excellent job of protecting developers from themselves. However, we are switching to Java, and moving more developers from the mainframe to an Oracle-Java system that is being developed. For a Java IDE, it looks like a tool named TogetherSoft. I am concerned that I will need to take a more active role with the developers. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 29, 2002 8:03 PM To: Multiple recipients of list ORACLE-L Greg, You *do* see DBA's doing the bulk of the SQL tuning work in many shops. But it's not necessarily because the developers, or at least some them, can't, or, that many of them don't care (and *many* of them never do give it a thought). I've seen places where the developers begged for the ability to turn on tracing in development, or to have a plan_table and/or the use of autotrace, and were denied. And other cases where the development, testing, and QA environments were so different from production that there was nearly no point. Anyway, just by virtue of their titles, I don't know that a DBA is any better at SQL tuning than a developer or vice versa (and I'm not pointing that comment at you, Greg, but just in general that I don't think the title of DBA or developer makes a difference). It really depends on their backgrounds and skill levels. I've seen, for the most obvious example, many DBA's and developers freak when they see a full table scan, never taking into consideration if that was the appropriate approach. Instead, they just lived by some rule that full table scans are bad. You see lots of things like that. Anyway, as someone who started off as both a DBA and developer, and drifts back and forth between the two and still serving in both roles, I can see both sides. I know DBA's who rant about the developers not giving a flip about performance when they write their code, and in many cases it is true, the issue of performance was never considered. But I also know many developers who *do* care and are hindered from doing so. By the same token, I know a lot of DBA's who are very good at SQL tuning, and tuning and general, and many more who aren't. So, what we can we do? We can work with the developers (and DBA's) and mentor them. We can teach the tricks and efficient styles (whether SQL itself or application design in general). And it really helps if we can provide an environment that mimics production (dollars and budgets make that hard to do in many cases). Sorry for the length, but it touches on something I'm dealing with right now. I'm helping some developers who are getting hammered about why their code performs so poorly in production. Heck, it ran great in all the other environments, there's not much more that they could have done. And yes, I now sit in on the code reviews making suggestions when something could be done better, and testing their code and every SQL statement against production. Often times requires significant work in stubbing out the DML pieces and duplicating the same logic when doing so. But if they aren't given a real environment, and, they are interested, I have sympathy when seeing them hammered for poor performing code and SQL statements when they did everything they could with what they were provided. Oh well, end of the week rant of sorts. I'm sending everyone a case of their favorite scotch if they just ask ;-) Just a test to see if anyone makes it this far ;-) Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Greg Moore Sent: Friday, March 29, 2002 4:38 PM To: Multiple recipients of list ORACLE-L
RE: Do programmers tune SQL?
Title: RE: Do programmers tune SQL? Our developers tend to be of the 'Meet the Holy Deadline at all costs' school and tuning is rarely perfomed; we've had some major resource issues due to bad SQL. Our lead DBA got the idea of revising our migration process so that all SQL code migrates from development into test must have an accompanying explain plan. Shortly after this requirement went into effect, we began seeing a lot of SQL code with RULE hints embedded, as the developers began manipulating the explains with the sole purpose of getting it past DBA review, as opposed to true tuning. Jeffery D Thomas DBA Thomson Information Services Thomson multimedia Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages' -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Monday, April 01, 2002 12:20 PM To: Multiple recipients of list ORACLE-L Subject: RE: Do programmers tune SQL? Oh, I guess I'm a little slow on Monday. I've enjoyed the discussion so far. Very close to my situation. Given my situation - i.e., we haven't had many SQL statement problems, but expecting to receive more with Java, I'm wondering how I can get ahead of the game. I have worked on a set of SQL statement recommendations, simple stuff like make sure screen queries use and index. I am considering creating a checklist form for SQL statements for the developers to use. Like include the listing from EXPLAIN PLAN. Does anyone have any thoughts on this approach? I'm not sure if the developers/management would go for it, but I thought it would be worth asking as a starting point. Everybody is new to Java and a little nervous, so they are probably more open to suggestions. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 01, 2002 9:13 AM To: Multiple recipients of list ORACLE-L and your question is? --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: All, We are in transition here at my site. Our primary development tool over the past few years has been a client-server tool named Uniface from Compuware, which does an excellent job of protecting developers from themselves. However, we are switching to Java, and moving more developers from the mainframe to an Oracle-Java system that is being developed. For a Java IDE, it looks like a tool named TogetherSoft. I am concerned that I will need to take a more active role with the developers. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 29, 2002 8:03 PM To: Multiple recipients of list ORACLE-L Greg, You *do* see DBA's doing the bulk of the SQL tuning work in many shops. But it's not necessarily because the developers, or at least some them, can't, or, that many of them don't care (and *many* of them never do give it a thought). I've seen places where the developers begged for the ability to turn on tracing in development, or to have a plan_table and/or the use of autotrace, and were denied. And other cases where the development, testing, and QA environments were so different from production that there was nearly no point. Anyway, just by virtue of their titles, I don't know that a DBA is any better at SQL tuning than a developer or vice versa (and I'm not pointing that comment at you, Greg, but just in general that I don't think the title of DBA or developer makes a difference). It really depends on their backgrounds and skill levels. I've seen, for the most obvious example, many DBA's and developers freak when they see a full table scan, never taking into consideration if that was the appropriate approach. Instead, they just lived by some rule that full table scans are bad. You see lots of things like that. Anyway, as someone who started off as both a DBA and developer, and drifts back and forth between the two and still serving in both roles, I can see both sides. I know DBA's who rant about the developers not giving a flip about performance when they write their code, and in many cases it is true, the issue of performance was never considered. But I also know many developers who *do* care and are hindered from doing so. By the same token, I know a lot of DBA's who are very good at SQL tuning, and tuning and general, and many more who aren't. So, what we can we do? We can work with the developers (and DBA's) and mentor them. We can teach the tricks and efficient styles (whether SQL itself or application design in general). And it really helps if we can provide an environment that mimics production (dollars and budgets make that hard to do in many cases). Sorry for the length, but it touches on something I'm dealing with right now. I'm helping some developers who are getting
RE: Do programmers tune SQL?
Dennis, I've had to keep after the Java developers here to use bind variables instead of literals. Apparently, it's much easier for them to construct literal SQL. They now understand the importance and use bind variable pretty much all the time. A few months ago they started spawning multiple threads of some Java processes and used a thingey called Connection Pooling to mediate connections to Oracle. They fell back to using literal SQL, until they figured out how to use bind variables with Connection Pooling. Apparently, it's even harder to use bind variables with Connection Pooling. If you make the Java developers aware of these issues up front, maybe they'll go ahead and learn how to code it right initially. Sorry I can't give details, 'cause all I know about Java is how to spell it! ;-) Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- WILLIAMS Sent: Monday, April 01, 2002 11:20 AM To: Multiple recipients of list ORACLE-L Oh, I guess I'm a little slow on Monday. I've enjoyed the discussion so far. Very close to my situation. Given my situation - i.e., we haven't had many SQL statement problems, but expecting to receive more with Java, I'm wondering how I can get ahead of the game. I have worked on a set of SQL statement recommendations, simple stuff like make sure screen queries use and index. I am considering creating a checklist form for SQL statements for the developers to use. Like include the listing from EXPLAIN PLAN. Does anyone have any thoughts on this approach? I'm not sure if the developers/management would go for it, but I thought it would be worth asking as a starting point. Everybody is new to Java and a little nervous, so they are probably more open to suggestions. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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: Do programmers tune SQL?
make them use Java prepared statements. I ended up having to turn cursor sharing to force to deal with the fact that the programmers refused to use prepared statements and insisted on literals. --- Jack C. Applewhite [EMAIL PROTECTED] wrote: Dennis, I've had to keep after the Java developers here to use bind variables instead of literals. Apparently, it's much easier for them to construct literal SQL. They now understand the importance and use bind variable pretty much all the time. A few months ago they started spawning multiple threads of some Java processes and used a thingey called Connection Pooling to mediate connections to Oracle. They fell back to using literal SQL, until they figured out how to use bind variables with Connection Pooling. Apparently, it's even harder to use bind variables with Connection Pooling. If you make the Java developers aware of these issues up front, maybe they'll go ahead and learn how to code it right initially. Sorry I can't give details, 'cause all I know about Java is how to spell it! ;-) Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- WILLIAMS Sent: Monday, April 01, 2002 11:20 AM To: Multiple recipients of list ORACLE-L Oh, I guess I'm a little slow on Monday. I've enjoyed the discussion so far. Very close to my situation. Given my situation - i.e., we haven't had many SQL statement problems, but expecting to receive more with Java, I'm wondering how I can get ahead of the game. I have worked on a set of SQL statement recommendations, simple stuff like make sure screen queries use and index. I am considering creating a checklist form for SQL statements for the developers to use. Like include the listing from EXPLAIN PLAN. Does anyone have any thoughts on this approach? I'm not sure if the developers/management would go for it, but I thought it would be worth asking as a starting point. Everybody is new to Java and a little nervous, so they are probably more open to suggestions. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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! Greetings - send holiday greetings for Easter, Passover http://greetings.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: Do programmers tune SQL?
Names change to protect the innocent/guilty, maybe? -Original Message- Sent: Monday, April 01, 2002 10:19 AM To: Multiple recipients of list ORACLE-L I wouldn't put it past him. But I don't recognize his name on any of those questions :) --- Stephane Faroult [EMAIL PROTECTED] wrote: Rachel Carmichael wrote: My last shop we had one programmer who not only expected me to tune his SQL, he expected me to WRITE his SQL for me. I got emails I need a query that returns this information from these tables. This guy seems to be trying a new tactic these days: DBA mailing lists. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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! Greetings - send holiday greetings for Easter, Passover http://greetings.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: Ball, Terry 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: Do programmers tune SQL?
I think there's a new myth: Programmers should tune SQL. Harrison says his book is for developers, but consider what he actually covers. Chapter 8, Tuning Table Access, covers many topics that are for DBA's, not developers: -- hit rate in the buffer cache -- db_file_multiblock_read_count -- number of blocks used for the table -- size of data blocks -- depth of index -- histograms -- use of ANALYZE and dbms_stats -- subtle points of index creation -- types of indexes, strategies -- fast full index scan -- bitmap_merge_area_size parm -- alter table minimize records_per_block -- setting up hash clusters -- IOT's, configuring the overflow statement -- periodic rebuild of indexes -- fast_full_scan_enabled=true parm -- lowering the high water mark -- optimizing PCTFREE andd PCTUSED That's from just *one* chapter. Oh sure, he also devotes a few pages to avoiding accidental full table scans caused by SQL that disables an index, etc. But how often are developers going to tune SQL by using the rest of the stuff in this chapter? The root problem is, the phrase tune SQL is a myth. Sure the SQL runs slow, then you tune it and it runs faster. But tuning it often requires DBA knowledge, something DBA's may take for granted, but for developers it's a huge area they have no familiarity with at all. There's a specific set of things you can ask developers to do, but there's another set of things that are required and that you can't reasonably ask developers to do. Harrison's idea that you can get developers to tune SQL is a myth. He's really written a DBA book that delves into tuning SQL, which is a reasonable goal. But to do the reverse -- asking a developer to delve into tuning SQL -- means they have to come to grips with DBA topics, and that's not a reasonable thing to ask. He should take a subset of his book (perhaps a third) and call it SQL Tuning For Developers, and give the current book an accurate title ... SQL Tuning for DBA's. -- 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: Do programmers tune SQL?
I may open a can of worms - but don't intend to... Sometimes DBAs and Sys Admins make tuning impossible or at least very hard for developers. I am a developer and do a lot of tuning of statements used by our applications. I have, in our office, the luxury of DBA access to databases and access to UNIX servers. So life is not bad. But I have been in places were security was very strict and: - I had, in database, only user account with limited rights - I didn't have access to PLAN_TABLE so after writing my SQL I had to ask DBA to run it. And the same if I changed it, and then I may have changed it number of times... - it was worse when I had to tune stored procedures. Again, I had only a user account but not the procedure owner account - so I wrote procedure, asked the DBA to compile it, run my statements, and maybe all over again... - I didn't have account on the UNIX server so when the trace file was generated I had to ask the DBA to run TKPROF and send me the output. In an extreme case I run into junior DBA who had never run TKPROF before so it took even more time explaining why I wanted to use explain=... and sys=no etc. Occasionally he had to contact senior DBA and wait for his response. On top of that DBAs had other tasks so sometimes it took quite some time to get a procedure recompiled, trace file generated, or run TKPROF. In one place there was a rule that DBAs had 48 hrs to respond to any request and sometimes it took that long. Sometimes a very simple task, that could be completed in few minutes, took days because of all procedural things. At the end it cost companies lot of money. In our company many developers have DBA access to databases and in fact nobody does anything stupid. I have found that if people don't know what they are doing, they ask. Nobody tries to drop or truncate tables because they have found the script that seesm cool and want to see what it does... Over my 8 years here only once we had a problem - a few tables were dropped by mistake but the tables were dropped by our DBA who run the wrong script. It took almost no time at all to rebuild it. And it happened in a test database. I understand restrictions in production databases but more access to development and test would make life easier and am sure more happy faces around. Witold == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: Do programmers tune SQL?
Having worked mostly as a developer, the myth is that ALL programmers should tune SQL. Poor programmers trying to tune a query just make it unreadable. Good programmers should be able to tune, and depending on the shop, they should be able to do it better than an DBA in some situations (There are many places where the programmers have a better understanding of how the tables interrelate than the DBA) Note I said GOOD programmers. Now, if you have a good programmer, the more information available, the better. True, buffer cache hit may not help him much, but knowing the types of indexes and strategies will help in many cases. Having the information available lets the programmer at least present and discuss the issue intelligently, do you prefer Help, this query runs to slow or This query doesn't run as fast as it should. Index x would help, but it is not being used. I'm checking a low frequency value, but it looks like it ends up in the same histogram buckets as a high frequency code. Again, note that I said good programmers... :) -Original Message- Sent: Monday, April 01, 2002 2:13 PM To: Multiple recipients of list ORACLE-L I think there's a new myth: Programmers should tune SQL. Harrison says his book is for developers, but consider what he actually covers. Chapter 8, Tuning Table Access, covers many topics that are for DBA's, not developers: -- hit rate in the buffer cache -- db_file_multiblock_read_count -- number of blocks used for the table -- size of data blocks -- depth of index -- histograms -- use of ANALYZE and dbms_stats -- subtle points of index creation -- types of indexes, strategies -- fast full index scan -- bitmap_merge_area_size parm -- alter table minimize records_per_block -- setting up hash clusters -- IOT's, configuring the overflow statement -- periodic rebuild of indexes -- fast_full_scan_enabled=true parm -- lowering the high water mark -- optimizing PCTFREE andd PCTUSED That's from just *one* chapter. Oh sure, he also devotes a few pages to avoiding accidental full table scans caused by SQL that disables an index, etc. But how often are developers going to tune SQL by using the rest of the stuff in this chapter? The root problem is, the phrase tune SQL is a myth. Sure the SQL runs slow, then you tune it and it runs faster. But tuning it often requires DBA knowledge, something DBA's may take for granted, but for developers it's a huge area they have no familiarity with at all. There's a specific set of things you can ask developers to do, but there's another set of things that are required and that you can't reasonably ask developers to do. Harrison's idea that you can get developers to tune SQL is a myth. He's really written a DBA book that delves into tuning SQL, which is a reasonable goal. But to do the reverse -- asking a developer to delve into tuning SQL -- means they have to come to grips with DBA topics, and that's not a reasonable thing to ask. He should take a subset of his book (perhaps a third) and call it SQL Tuning For Developers, and give the current book an accurate title ... SQL Tuning for DBA's. -- 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: Norrell, Brian 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: Do programmers tune SQL?
Seen that before. And it wouldn't be so bad except the DBA's were complaining about the poor performing SQL. They blindfolded the developers, tied their hands behind their back, to the point they could not even generate an explain plan, much less do any tracing and use tkprof. And then complained about the developers turning out poor performing SQL. I understand your plight. But I have been in places were security was very strict and: - I had, in database, only user account with limited rights - I didn't have access to PLAN_TABLE so after writing my SQL I had to ask DBA to run it. And the same if I changed it, and then I may have changed it number of times... - it was worse when I had to tune stored procedures. Again, I had only a user account but not the procedure owner account - so I wrote procedure, asked the DBA to compile it, run my statements, and maybe all over again... - I didn't have account on the UNIX server so when the trace file was generated I had to ask the DBA to run TKPROF and send me the output. In an extreme case I run into junior DBA who had never run TKPROF before so it took even more time explaining why I wanted to use explain=... and sys=no etc. Occasionally he had to contact senior DBA and wait for his response. On top of that DBAs had other tasks so sometimes it took quite some time to get a procedure recompiled, trace file generated, or run TKPROF. In one place there was a rule that DBAs had 48 hrs to respond to any request and sometimes it took that long. Sometimes a very simple task, that could be completed in few minutes, took days because of all procedural things. At the end it cost companies lot of money. -- 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: Do programmers tune SQL?
The Developers will also need really big PC's, Last company I worked for was using TogetherSoft and we found 256 MB RAM an absolute minimum requirement. If you don't have much knowlege of Java I suggest you get hold of some skills as well. I have just created a database utility to do what DBMS_UTLIITY.COMMA_TO_TABLE does but with out the restrictions on what can be in the input string. Quite an interesting exercise for learning about Java in the database. Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/04/2002 12:28 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Fax to: Subject:RE: Do programmers tune SQL? All, We are in transition here at my site. Our primary development tool over the past few years has been a client-server tool named Uniface from Compuware, which does an excellent job of protecting developers from themselves. However, we are switching to Java, and moving more developers from the mainframe to an Oracle-Java system that is being developed. For a Java IDE, it looks like a tool named TogetherSoft. I am concerned that I will need to take a more active role with the developers. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 29, 2002 8:03 PM To: Multiple recipients of list ORACLE-L Greg, You *do* see DBA's doing the bulk of the SQL tuning work in many shops. But it's not necessarily because the developers, or at least some them, can't, or, that many of them don't care (and *many* of them never do give it a thought). I've seen places where the developers begged for the ability to turn on tracing in development, or to have a plan_table and/or the use of autotrace, and were denied. And other cases where the development, testing, and QA environments were so different from production that there was nearly no point. Anyway, just by virtue of their titles, I don't know that a DBA is any better at SQL tuning than a developer or vice versa (and I'm not pointing that comment at you, Greg, but just in general that I don't think the title of DBA or developer makes a difference). It really depends on their backgrounds and skill levels. I've seen, for the most obvious example, many DBA's and developers freak when they see a full table scan, never taking into consideration if that was the appropriate approach. Instead, they just lived by some rule that full table scans are bad. You see lots of things like that. Anyway, as someone who started off as both a DBA and developer, and drifts back and forth between the two and still serving in both roles, I can see both sides. I know DBA's who rant about the developers not giving a flip about performance when they write their code, and in many cases it is true, the issue of performance was never considered. But I also know many developers who *do* care and are hindered from doing so. By the same token, I know a lot of DBA's who are very good at SQL tuning, and tuning and general, and many more who aren't. So, what we can we do? We can work with the developers (and DBA's) and mentor them. We can teach the tricks and efficient styles (whether SQL itself or application design in general). And it really helps if we can provide an environment that mimics production (dollars and budgets make that hard to do in many cases). Sorry for the length, but it touches on something I'm dealing with right now. I'm helping some developers who are getting hammered about why their code performs so poorly in production. Heck, it ran great in all the other environments, there's not much more that they could have done. And yes, I now sit in on the code reviews making suggestions when something could be done better, and testing their code and every SQL statement against production. Often times requires significant work in stubbing out the DML pieces and duplicating the same logic when doing so. But if they aren't given a real environment, and, they are interested, I have sympathy when seeing them hammered for poor performing code and SQL statements when they did everything they could with what they were provided. Oh well, end of the week rant of sorts. I'm sending everyone a case of their favorite scotch if they just ask ;-) Just
RE: Do programmers tune SQL?
I agree with much of what you say, but, disagree (respectfully, of course ;-)) with other points. In line. I think there's a new myth: Programmers should tune SQL. I think they should have a core understanding of good SQL practices from a performance standpoint, and, they should be expected to understand some basics. Whether the average developer gives a flip, it varies. Harrison says his book is for developers, but consider what he actually covers. Chapter 8, Tuning Table Access, covers many topics that are for DBA's, not developers: -- hit rate in the buffer cache -- db_file_multiblock_read_count I would expect a *good* developer to understand this and how it impacts choice of execution plans (and why, just maybe, that FTS isn't a bad thing). -- number of blocks used for the table -- size of data blocks -- depth of index -- histograms -- use of ANALYZE and dbms_stats -- subtle points of index creation -- types of indexes, strategies Should also be aware of this and how it impacts their SQL. Not necessarily when to use what strategy, but the benefits of different approaches and how it impacts their SQL. I don't think it's too much to ask a developer to at least have an idea. -- fast full index scan Developers should definitely be aware of IFFS's, how to use them, and when to use them. Don't think it's too much to ask a developer to understand IFFS's. -- bitmap_merge_area_size parm -- alter table minimize records_per_block -- setting up hash clusters -- IOT's, configuring the overflow statement -- periodic rebuild of indexes -- fast_full_scan_enabled=true parm -- lowering the high water mark -- optimizing PCTFREE andd PCTUSED That's from just *one* chapter. Oh sure, he also devotes a few pages to avoiding accidental full table scans caused by SQL that disables an index, etc. But how often are developers going to tune SQL by using the rest of the stuff in this chapter? I don't have the book handy, and maybe this chapter isn't a good example, but much of what he says in the book should be able to be picked up by a *good* developer. Simple things as when to use a correlated versus non-correlated query, how the CBO can transform statements (Oracle doc's are actually pretty good for this). Example, come in this morning and a query had been running since Saturday afternoon. The developer had 10 or 15 correlated sub-queries and no supporting indexes. These tables were between 90K and 500K rows each. Main portion of the query returned 500K rows, meaning each of the correlated sub-queries were going to be executed 500K times, each doing FTS's every time. Now, Harrison's book goes into some detail about when to use IN versus EXISTS, NOT IN versus NOT EXISTS, correlated vs non-correlated, etc. It doesn't require much DBA knowledge to get a grip on that. Anyway, took the sub-queries and turned into a single UNION (ALL) of the 10 tables using an IN clause referencing it. This resulted in a single FTS on each of the tables with the results driving the rest of the query. Dropped from 40 hours and running to under 30 seconds. So, with a basic understanding of how to handle sub-queries, when to use what, and not requiring any DBA knowledge at all, the developer could have been expected to write the SQL properly. And Harrison's book would help someone determine that how to that. And in talking to the developer, she picked up pretty quickly why the new approach was better than the existing one. The root problem is, the phrase tune SQL is a myth. Sure the SQL runs slow, then you tune it and it runs faster. But tuning it often requires DBA knowledge, something DBA's may take for granted, but for developers it's a huge area they have no familiarity with at all. For advanced tuning, knowledge of some DBA topics is helpful. But, in those cases where I run into SQL that was simply coded in a way that gave it no chance to perform, a book like Harrison's could help the developer avoid such pitfalls. There's a specific set of things you can ask developers to do, but there's another set of things that are required and that you can't reasonably ask developers to do. Harrison's idea that you can get developers to tune SQL is a myth. Disagree :-) Ok, noting the difference between developer and good developer ;-) He's really written a DBA book that delves into tuning SQL, which is a reasonable goal. But to do the reverse -- asking a developer to delve into tuning SQL -- means they have to come to grips with DBA topics, and that's not a reasonable thing to ask. He should take a subset of his book (perhaps a third) and call it SQL Tuning For Developers, and give the current book an accurate title ... SQL Tuning for DBA's. Yeah, the book does get into topics out of the scope of the general developer and more in line with a DBA's type of skills. I don't know that I would say only a third of the book is applicable to developers. Anyway, I don't think we shouldn't
RE: Do programmers tune SQL?
There was one guy at a site I used to work for that was interesting. He wrote his own shell scripts and PL/SQL routines and did them well. He was open to learning and all that good stuff. However, he felt that it was the sys. admin's job to write the shell scripts and the DBA's to write the PL/SQL (not the SQL). Its like ya, you want me to write your Java and C code also? Just a funny mix -Original Message- Faroult Sent: Monday, April 01, 2002 7:18 AM To: Multiple recipients of list ORACLE-L Rachel Carmichael wrote: My last shop we had one programmer who not only expected me to tune his SQL, he expected me to WRITE his SQL for me. I got emails I need a query that returns this information from these tables. This guy seems to be trying a new tactic these days: DBA mailing lists. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: Kimberly Smith 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: Do programmers tune SQL?
Are you going to use prepared statements or SQLJ? With prepared statements get some standards in now regarding bind variables (ok that is for both) and to ensure they close their statements as soon as they are done with them (to many open cursors error). -Original Message- WILLIAMS Sent: Monday, April 01, 2002 9:20 AM To: Multiple recipients of list ORACLE-L Oh, I guess I'm a little slow on Monday. I've enjoyed the discussion so far. Very close to my situation. Given my situation - i.e., we haven't had many SQL statement problems, but expecting to receive more with Java, I'm wondering how I can get ahead of the game. I have worked on a set of SQL statement recommendations, simple stuff like make sure screen queries use and index. I am considering creating a checklist form for SQL statements for the developers to use. Like include the listing from EXPLAIN PLAN. Does anyone have any thoughts on this approach? I'm not sure if the developers/management would go for it, but I thought it would be worth asking as a starting point. Everybody is new to Java and a little nervous, so they are probably more open to suggestions. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 01, 2002 9:13 AM To: Multiple recipients of list ORACLE-L and your question is? --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: All, We are in transition here at my site. Our primary development tool over the past few years has been a client-server tool named Uniface from Compuware, which does an excellent job of protecting developers from themselves. However, we are switching to Java, and moving more developers from the mainframe to an Oracle-Java system that is being developed. For a Java IDE, it looks like a tool named TogetherSoft. I am concerned that I will need to take a more active role with the developers. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 29, 2002 8:03 PM To: Multiple recipients of list ORACLE-L Greg, You *do* see DBA's doing the bulk of the SQL tuning work in many shops. But it's not necessarily because the developers, or at least some them, can't, or, that many of them don't care (and *many* of them never do give it a thought). I've seen places where the developers begged for the ability to turn on tracing in development, or to have a plan_table and/or the use of autotrace, and were denied. And other cases where the development, testing, and QA environments were so different from production that there was nearly no point. Anyway, just by virtue of their titles, I don't know that a DBA is any better at SQL tuning than a developer or vice versa (and I'm not pointing that comment at you, Greg, but just in general that I don't think the title of DBA or developer makes a difference). It really depends on their backgrounds and skill levels. I've seen, for the most obvious example, many DBA's and developers freak when they see a full table scan, never taking into consideration if that was the appropriate approach. Instead, they just lived by some rule that full table scans are bad. You see lots of things like that. Anyway, as someone who started off as both a DBA and developer, and drifts back and forth between the two and still serving in both roles, I can see both sides. I know DBA's who rant about the developers not giving a flip about performance when they write their code, and in many cases it is true, the issue of performance was never considered. But I also know many developers who *do* care and are hindered from doing so. By the same token, I know a lot of DBA's who are very good at SQL tuning, and tuning and general, and many more who aren't. So, what we can we do? We can work with the developers (and DBA's) and mentor them. We can teach the tricks and efficient styles (whether SQL itself or application design in general). And it really helps if we can provide an environment that mimics production (dollars and budgets make that hard to do in many cases). Sorry for the length, but it touches on something I'm dealing with right now. I'm helping some developers who are getting hammered about why their code performs so poorly in production. Heck, it ran great in all the other environments, there's not much more that they could have done. And yes, I now sit in on the code reviews making suggestions when something could be done better, and testing their code and every SQL statement against production. Often times requires significant work in stubbing out the DML pieces and duplicating the same logic when doing so. But if they aren't given a real environment, and, they are interested, I have sympathy when seeing them hammered for poor performing code and SQL statements when they did everything they could with what they were provided. Oh well, end of the week rant of sorts. I'm sending everyone a
RE: Do programmers tune SQL?
Well, I cannot speak on the book but you can tune SQL statements. I think a developer should be aware of indexes and how they are used. I think they should be aware of the CBO and how stats are needed. How to generate their stats. I think rebuilding of indexes are not beyond their realm of knowledge. They should be familiar with IOT's. They have to know the in's and out's of their language they are developing in and I believe that SQL is just one of them. The more senior they are, the more I expect of them. I also believe that part of me coming in as an 'Oracle expert' is to teach and guide. And I do that every time I move to a new site. So far I have seen no resistance. Granted, I have had the one or two idiots per site that just don't get it but their problems go way beyond writing poor SQL. I am not speaking from a wish list. I have seen in in action. I have had developers come to me and say, I think we need an index here and low and behold they have been right. We loaded a whole bunch of data once and the stats caused a query to go wacky cause of the way it was written and the developer picked up on this. Together we decided to take the stats off until he rewrote the query. All was fine. I could give you many more cases but this is already way more then I wanted to type. Just on a rant roll. I so believe that you have to work with your developers. I also know that a lot of DBA's out there will not or cannot do this. Some just do not have the ability to communicate or teach. But then you cannot turn around and critize your developers. Most of what you learn is from experience, not from a book (there is that whole OCP vs experience fight again) and you, as the DBA, tend to have it. So you should be able to provide better guidelines then a book. -Original Message- Sent: Monday, April 01, 2002 12:13 PM To: Multiple recipients of list ORACLE-L I think there's a new myth: Programmers should tune SQL. Harrison says his book is for developers, but consider what he actually covers. Chapter 8, Tuning Table Access, covers many topics that are for DBA's, not developers: -- hit rate in the buffer cache -- db_file_multiblock_read_count -- number of blocks used for the table -- size of data blocks -- depth of index -- histograms -- use of ANALYZE and dbms_stats -- subtle points of index creation -- types of indexes, strategies -- fast full index scan -- bitmap_merge_area_size parm -- alter table minimize records_per_block -- setting up hash clusters -- IOT's, configuring the overflow statement -- periodic rebuild of indexes -- fast_full_scan_enabled=true parm -- lowering the high water mark -- optimizing PCTFREE andd PCTUSED That's from just *one* chapter. Oh sure, he also devotes a few pages to avoiding accidental full table scans caused by SQL that disables an index, etc. But how often are developers going to tune SQL by using the rest of the stuff in this chapter? The root problem is, the phrase tune SQL is a myth. Sure the SQL runs slow, then you tune it and it runs faster. But tuning it often requires DBA knowledge, something DBA's may take for granted, but for developers it's a huge area they have no familiarity with at all. There's a specific set of things you can ask developers to do, but there's another set of things that are required and that you can't reasonably ask developers to do. Harrison's idea that you can get developers to tune SQL is a myth. He's really written a DBA book that delves into tuning SQL, which is a reasonable goal. But to do the reverse -- asking a developer to delve into tuning SQL -- means they have to come to grips with DBA topics, and that's not a reasonable thing to ask. He should take a subset of his book (perhaps a third) and call it SQL Tuning For Developers, and give the current book an accurate title ... SQL Tuning for DBA's. -- 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: Kimberly Smith 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,
Re: Do programmers tune SQL?
Jared, Gotta wean you away from that peaty stuff :) Rachel --- Jared Still [EMAIL PROTECTED] wrote: I'll take a case of Lagavulin or Glenmorangie, you decide. Email me off list for my shipping address. Jared On Friday 29 March 2002 18:03, Larry Elkins wrote: Greg, You *do* see DBA's doing the bulk of the SQL tuning work in many shops. But it's not necessarily because the developers, or at least some them, can't, or, that many of them don't care (and *many* of them never do give it a thought). I've seen places where the developers begged for the ability to turn on tracing in development, or to have a plan_table and/or the use of autotrace, and were denied. And other cases where the development, testing, and QA environments were so different from production that there was nearly no point. Anyway, just by virtue of their titles, I don't know that a DBA is any better at SQL tuning than a developer or vice versa (and I'm not pointing that comment at you, Greg, but just in general that I don't think the title of DBA or developer makes a difference). It really depends on their backgrounds and skill levels. I've seen, for the most obvious example, many DBA's and developers freak when they see a full table scan, never taking into consideration if that was the appropriate approach. Instead, they just lived by some rule that full table scans are bad. You see lots of things like that. Anyway, as someone who started off as both a DBA and developer, and drifts back and forth between the two and still serving in both roles, I can see both sides. I know DBA's who rant about the developers not giving a flip about performance when they write their code, and in many cases it is true, the issue of performance was never considered. But I also know many developers who *do* care and are hindered from doing so. By the same token, I know a lot of DBA's who are very good at SQL tuning, and tuning and general, and many more who aren't. So, what we can we do? We can work with the developers (and DBA's) and mentor them. We can teach the tricks and efficient styles (whether SQL itself or application design in general). And it really helps if we can provide an environment that mimics production (dollars and budgets make that hard to do in many cases). Sorry for the length, but it touches on something I'm dealing with right now. I'm helping some developers who are getting hammered about why their code performs so poorly in production. Heck, it ran great in all the other environments, there's not much more that they could have done. And yes, I now sit in on the code reviews making suggestions when something could be done better, and testing their code and every SQL statement against production. Often times requires significant work in stubbing out the DML pieces and duplicating the same logic when doing so. But if they aren't given a real environment, and, they are interested, I have sympathy when seeing them hammered for poor performing code and SQL statements when they did everything they could with what they were provided. Oh well, end of the week rant of sorts. I'm sending everyone a case of their favorite scotch if they just ask ;-) Just a test to see if anyone makes it this far ;-) Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Greg Moore Sent: Friday, March 29, 2002 4:38 PM To: Multiple recipients of list ORACLE-L Subject: Do programmers tune SQL? What percent of developers know how to explain and trace SQL, interpret these reports and tune? In my experience it's about 10%, so most SQL tuning is done by DBA's. Is that about right? -- 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). __ Do You Yahoo!? Yahoo! Greetings - send holiday greetings for Easter, Passover http://greetings.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
RE: Do programmers tune SQL?
Isn't this the DBA's eleventh commandment Rachel's Mother wrote: I'm wrong, I've always been wrong, I will always BE wrong, let's move on from there. As for the programmers I would rather write (right) the SQL for them as it means I don't have to deal with their SQL later. And I write lousy SQL Stored procs are the way to go. That way when you get a good programmmer he can rewrite them for you. In my dreams at least :) Dave -- Dave Morgan [EMAIL PROTECTED] 403 399 2442 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Morgan 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: Do programmers tune SQL?
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Rachel Carmichael I WISH I owned that website :) as for the programmers well, it was a thought. Obviously not the right one in this case, but it was a thought. It was a good idea, thanks for throwing it out there. The things you mentioned are intended to address the situations like I described. Just not helpful in our case. enjoy the rain, I hear you guys are sending it our way Thank you, I enjoyed it very much ;-) 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: Do programmers tune SQL?
well now you'll have to let us know what the solution turns out to be --- Larry Elkins [EMAIL PROTECTED] wrote: From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Rachel Carmichael I WISH I owned that website :) as for the programmers well, it was a thought. Obviously not the right one in this case, but it was a thought. It was a good idea, thanks for throwing it out there. The things you mentioned are intended to address the situations like I described. Just not helpful in our case. enjoy the rain, I hear you guys are sending it our way Thank you, I enjoyed it very much ;-) 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). __ Do You Yahoo!? Yahoo! Greetings - send holiday greetings for Easter, Passover http://greetings.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: Do programmers tune SQL?
I moved from development to DBA, so now no one comes around and tells us that 'such and such this is not possible in Oracle' .. or 'writing that kind of code will take two weeks'. Some developers love to tune, some aren't afraid to ask after experimentation, some want us to do their work. Then there is another category (which I love to ignore), even if you prove they are wrong, somehow they are always right ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
RE: Do programmers tune SQL?
My mantra (developed after years of living with a Jewish mother) is: I'm wrong, I've always been wrong, I will always BE wrong, let's move on from there. What I used to love is Oracle is broken. Really? Was it its leg? Arm? your head? --- Larry Elkins [EMAIL PROTECTED] wrote: Oh yes, it's *always* a DB problem. Why don't you fix your database, it's your problem and your fault ;-) I think most can understand your rant. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Seefelt, Beth Sent: Friday, March 29, 2002 5:49 PM To: Multiple recipients of list ORACLE-L Subject: RE: Do programmers tune SQL? In my shop, 0 out of 3. Which wouldn't be so bad, but they also show 0 interest in learning... and every time a new job goes into production and takes 10 hours to run, its a database problem... ok, i'll stop ranting now... -- 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). __ Do You Yahoo!? Yahoo! Greetings - send holiday greetings for Easter, Passover http://greetings.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: Do programmers tune SQL?
Larry, Mine is Glenmorangie in a port finish. Write me offline for the address :) My last shop we had one programmer who not only expected me to tune his SQL, he expected me to WRITE his SQL for me. I got emails I need a query that returns this information from these tables. His boss set him straight FAST. We also had another progrmmer there who would write a query, tune it to the best of his ability and then say can you help me make this better? He got pretty fast response time from the DBAs. And I fondly remember a different place, where, after finding a slow SQL statement and tuning it and having the programmer re-test he came to me and said Rachel it flies like the wind now :) Oh -- your poor programmers who have queries that don't perform in production as they do in development? Have you tried export and importing the statistics from production into development (dbms_stats) and stored outlines? Rachel --- Larry Elkins [EMAIL PROTECTED] wrote: Greg, You *do* see DBA's doing the bulk of the SQL tuning work in many shops. But it's not necessarily because the developers, or at least some them, can't, or, that many of them don't care (and *many* of them never do give it a thought). I've seen places where the developers begged for the ability to turn on tracing in development, or to have a plan_table and/or the use of autotrace, and were denied. And other cases where the development, testing, and QA environments were so different from production that there was nearly no point. Anyway, just by virtue of their titles, I don't know that a DBA is any better at SQL tuning than a developer or vice versa (and I'm not pointing that comment at you, Greg, but just in general that I don't think the title of DBA or developer makes a difference). It really depends on their backgrounds and skill levels. I've seen, for the most obvious example, many DBA's and developers freak when they see a full table scan, never taking into consideration if that was the appropriate approach. Instead, they just lived by some rule that full table scans are bad. You see lots of things like that. Anyway, as someone who started off as both a DBA and developer, and drifts back and forth between the two and still serving in both roles, I can see both sides. I know DBA's who rant about the developers not giving a flip about performance when they write their code, and in many cases it is true, the issue of performance was never considered. But I also know many developers who *do* care and are hindered from doing so. By the same token, I know a lot of DBA's who are very good at SQL tuning, and tuning and general, and many more who aren't. So, what we can we do? We can work with the developers (and DBA's) and mentor them. We can teach the tricks and efficient styles (whether SQL itself or application design in general). And it really helps if we can provide an environment that mimics production (dollars and budgets make that hard to do in many cases). Sorry for the length, but it touches on something I'm dealing with right now. I'm helping some developers who are getting hammered about why their code performs so poorly in production. Heck, it ran great in all the other environments, there's not much more that they could have done. And yes, I now sit in on the code reviews making suggestions when something could be done better, and testing their code and every SQL statement against production. Often times requires significant work in stubbing out the DML pieces and duplicating the same logic when doing so. But if they aren't given a real environment, and, they are interested, I have sympathy when seeing them hammered for poor performing code and SQL statements when they did everything they could with what they were provided. Oh well, end of the week rant of sorts. I'm sending everyone a case of their favorite scotch if they just ask ;-) Just a test to see if anyone makes it this far ;-) Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Greg Moore Sent: Friday, March 29, 2002 4:38 PM To: Multiple recipients of list ORACLE-L Subject: Do programmers tune SQL? What percent of developers know how to explain and trace SQL, interpret these reports and tune? In my experience it's about 10%, so most SQL tuning is done by DBA's. Is that about right? -- 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,
RE: Do programmers tune SQL?
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Rachel Carmichael Larry, Mine is Glenmorangie in a port finish. Write me offline for the address :) Oh -- your poor programmers who have queries that don't perform in production as they do in development? Have you tried export and importing the statistics from production into development (dbms_stats) and stored outlines? So you decided to change careers on us after that last gig wrapped up? You can drop the DBA part ;-) http://www.drinkgoodstuff.com/dbaliquor_ny.htm. Anyway, using DBMS_STATS for consistency could help generate the same plans in development as would be generated in prod if all other factors were constant (parameters, etc, and no they aren't, something else being slowly addressed). And using outlines to capture and exporting/importing the OL$ and OL$HINTS tables after, or selected rows, from dev to prod could be used as insurance for getting the same plan when moving from dev to prod. But, they would still be executing and tuning against the test data that doesn't, in many cases, remotely resemble production in it's characteristics and distributions. So, assuming we always get the same plan in test that we would in prod because of using the same stats, the query flies in dev, we lock in the plan and move to prod, it still doesn't mean it is the best access paths for prod and the real data. And that's the bigger problem, the best access path in dev isn't necessarily the best access path in prod. And before some sales critter calls, yes, ways to better mimic production data, and tools versus homegrown approaches, are being considered ;-) Everyone have a good weekend. Time to go play in the rain. 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: Do programmers tune SQL?
I WISH I owned that website :) as for the programmers well, it was a thought. Obviously not the right one in this case, but it was a thought. enjoy the rain, I hear you guys are sending it our way Rachel --- Larry Elkins [EMAIL PROTECTED] wrote: From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Rachel Carmichael Larry, Mine is Glenmorangie in a port finish. Write me offline for the address :) Oh -- your poor programmers who have queries that don't perform in production as they do in development? Have you tried export and importing the statistics from production into development (dbms_stats) and stored outlines? So you decided to change careers on us after that last gig wrapped up? You can drop the DBA part ;-) http://www.drinkgoodstuff.com/dbaliquor_ny.htm. Anyway, using DBMS_STATS for consistency could help generate the same plans in development as would be generated in prod if all other factors were constant (parameters, etc, and no they aren't, something else being slowly addressed). And using outlines to capture and exporting/importing the OL$ and OL$HINTS tables after, or selected rows, from dev to prod could be used as insurance for getting the same plan when moving from dev to prod. But, they would still be executing and tuning against the test data that doesn't, in many cases, remotely resemble production in it's characteristics and distributions. So, assuming we always get the same plan in test that we would in prod because of using the same stats, the query flies in dev, we lock in the plan and move to prod, it still doesn't mean it is the best access paths for prod and the real data. And that's the bigger problem, the best access path in dev isn't necessarily the best access path in prod. And before some sales critter calls, yes, ways to better mimic production data, and tools versus homegrown approaches, are being considered ;-) Everyone have a good weekend. Time to go play in the rain. 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). __ Do You Yahoo!? Yahoo! Greetings - send holiday greetings for Easter, Passover http://greetings.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: Do programmers tune SQL?
I'll take a case of Lagavulin or Glenmorangie, you decide. Email me off list for my shipping address. Jared On Friday 29 March 2002 18:03, Larry Elkins wrote: Greg, You *do* see DBA's doing the bulk of the SQL tuning work in many shops. But it's not necessarily because the developers, or at least some them, can't, or, that many of them don't care (and *many* of them never do give it a thought). I've seen places where the developers begged for the ability to turn on tracing in development, or to have a plan_table and/or the use of autotrace, and were denied. And other cases where the development, testing, and QA environments were so different from production that there was nearly no point. Anyway, just by virtue of their titles, I don't know that a DBA is any better at SQL tuning than a developer or vice versa (and I'm not pointing that comment at you, Greg, but just in general that I don't think the title of DBA or developer makes a difference). It really depends on their backgrounds and skill levels. I've seen, for the most obvious example, many DBA's and developers freak when they see a full table scan, never taking into consideration if that was the appropriate approach. Instead, they just lived by some rule that full table scans are bad. You see lots of things like that. Anyway, as someone who started off as both a DBA and developer, and drifts back and forth between the two and still serving in both roles, I can see both sides. I know DBA's who rant about the developers not giving a flip about performance when they write their code, and in many cases it is true, the issue of performance was never considered. But I also know many developers who *do* care and are hindered from doing so. By the same token, I know a lot of DBA's who are very good at SQL tuning, and tuning and general, and many more who aren't. So, what we can we do? We can work with the developers (and DBA's) and mentor them. We can teach the tricks and efficient styles (whether SQL itself or application design in general). And it really helps if we can provide an environment that mimics production (dollars and budgets make that hard to do in many cases). Sorry for the length, but it touches on something I'm dealing with right now. I'm helping some developers who are getting hammered about why their code performs so poorly in production. Heck, it ran great in all the other environments, there's not much more that they could have done. And yes, I now sit in on the code reviews making suggestions when something could be done better, and testing their code and every SQL statement against production. Often times requires significant work in stubbing out the DML pieces and duplicating the same logic when doing so. But if they aren't given a real environment, and, they are interested, I have sympathy when seeing them hammered for poor performing code and SQL statements when they did everything they could with what they were provided. Oh well, end of the week rant of sorts. I'm sending everyone a case of their favorite scotch if they just ask ;-) Just a test to see if anyone makes it this far ;-) Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Greg Moore Sent: Friday, March 29, 2002 4:38 PM To: Multiple recipients of list ORACLE-L Subject: Do programmers tune SQL? What percent of developers know how to explain and trace SQL, interpret these reports and tune? In my experience it's about 10%, so most SQL tuning is done by DBA's. Is that about right? -- 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: Do programmers tune SQL?
Greg, The reason may be driven by the requirement of 'develop-within-this-deadline' for Developers, and 'get-this-query-to-respond-within-1-sec' for DBAs. The DBA's responsibility (among others) is to run a well tuned system, the Developers' responsbility is to develop a working product, and the two goals twain meet! Also, most of the older DBAs - at least I can speak for myself - grew out of Development, basically because we showed some troubleshooting abilities and were curious about the innards of Oracle and the OS, and this development experience helps in tuning. I have known a few developers who knew how to tune SQL, but I do admit, they are rare... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** What percent of developers know how to explain and trace SQL, interpret these reports and tune? In my experience it's about 10%, so most SQL tuning is done by DBA's. Is that about right? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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: Do programmers tune SQL?
In my shop, 0 out of 3. Which wouldn't be so bad, but they also show 0 interest in learning... and every time a new job goes into production and takes 10 hours to run, its a database problem... ok, i'll stop ranting now... -Original Message- Sent: Friday, March 29, 2002 5:38 PM To: Multiple recipients of list ORACLE-L What percent of developers know how to explain and trace SQL, interpret these reports and tune? In my experience it's about 10%, so most SQL tuning is done by DBA's. Is that about right? -- 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: Do programmers tune SQL?
In my previous shop they all wanted to learn. Once I sent them all a document on SQL tuning and one of the developers would just not leave me alone. I did such and such and now its this much faster. I got that for every SQL statement he did. The novelty eventually wore off and he stopped coming over to tell me. -Original Message- Beth Sent: Friday, March 29, 2002 3:49 PM To: Multiple recipients of list ORACLE-L In my shop, 0 out of 3. Which wouldn't be so bad, but they also show 0 interest in learning... and every time a new job goes into production and takes 10 hours to run, its a database problem... ok, i'll stop ranting now... -Original Message- Sent: Friday, March 29, 2002 5:38 PM To: Multiple recipients of list ORACLE-L What percent of developers know how to explain and trace SQL, interpret these reports and tune? In my experience it's about 10%, so most SQL tuning is done by DBA's. Is that about right? -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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: Do programmers tune SQL?
Kim, What document did you send them? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 29, 2002 5:03 PM In my previous shop they all wanted to learn. Once I sent them all a document on SQL tuning and one of the developers would just not leave me alone. I did such and such and now its this much faster. I got that for every SQL statement he did. The novelty eventually wore off and he stopped coming over to tell me. -Original Message- Beth Sent: Friday, March 29, 2002 3:49 PM To: Multiple recipients of list ORACLE-L In my shop, 0 out of 3. Which wouldn't be so bad, but they also show 0 interest in learning... and every time a new job goes into production and takes 10 hours to run, its a database problem... ok, i'll stop ranting now... -Original Message- Sent: Friday, March 29, 2002 5:38 PM To: Multiple recipients of list ORACLE-L What percent of developers know how to explain and trace SQL, interpret these reports and tune? In my experience it's about 10%, so most SQL tuning is done by DBA's. Is that about right? -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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: 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: Do programmers tune SQL?
Greg, You *do* see DBA's doing the bulk of the SQL tuning work in many shops. But it's not necessarily because the developers, or at least some them, can't, or, that many of them don't care (and *many* of them never do give it a thought). I've seen places where the developers begged for the ability to turn on tracing in development, or to have a plan_table and/or the use of autotrace, and were denied. And other cases where the development, testing, and QA environments were so different from production that there was nearly no point. Anyway, just by virtue of their titles, I don't know that a DBA is any better at SQL tuning than a developer or vice versa (and I'm not pointing that comment at you, Greg, but just in general that I don't think the title of DBA or developer makes a difference). It really depends on their backgrounds and skill levels. I've seen, for the most obvious example, many DBA's and developers freak when they see a full table scan, never taking into consideration if that was the appropriate approach. Instead, they just lived by some rule that full table scans are bad. You see lots of things like that. Anyway, as someone who started off as both a DBA and developer, and drifts back and forth between the two and still serving in both roles, I can see both sides. I know DBA's who rant about the developers not giving a flip about performance when they write their code, and in many cases it is true, the issue of performance was never considered. But I also know many developers who *do* care and are hindered from doing so. By the same token, I know a lot of DBA's who are very good at SQL tuning, and tuning and general, and many more who aren't. So, what we can we do? We can work with the developers (and DBA's) and mentor them. We can teach the tricks and efficient styles (whether SQL itself or application design in general). And it really helps if we can provide an environment that mimics production (dollars and budgets make that hard to do in many cases). Sorry for the length, but it touches on something I'm dealing with right now. I'm helping some developers who are getting hammered about why their code performs so poorly in production. Heck, it ran great in all the other environments, there's not much more that they could have done. And yes, I now sit in on the code reviews making suggestions when something could be done better, and testing their code and every SQL statement against production. Often times requires significant work in stubbing out the DML pieces and duplicating the same logic when doing so. But if they aren't given a real environment, and, they are interested, I have sympathy when seeing them hammered for poor performing code and SQL statements when they did everything they could with what they were provided. Oh well, end of the week rant of sorts. I'm sending everyone a case of their favorite scotch if they just ask ;-) Just a test to see if anyone makes it this far ;-) Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Greg Moore Sent: Friday, March 29, 2002 4:38 PM To: Multiple recipients of list ORACLE-L Subject: Do programmers tune SQL? What percent of developers know how to explain and trace SQL, interpret these reports and tune? In my experience it's about 10%, so most SQL tuning is done by DBA's. Is that about right? -- 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: Do programmers tune SQL?
Oh yes, it's *always* a DB problem. Why don't you fix your database, it's your problem and your fault ;-) I think most can understand your rant. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Seefelt, Beth Sent: Friday, March 29, 2002 5:49 PM To: Multiple recipients of list ORACLE-L Subject: RE: Do programmers tune SQL? In my shop, 0 out of 3. Which wouldn't be so bad, but they also show 0 interest in learning... and every time a new job goes into production and takes 10 hours to run, its a database problem... ok, i'll stop ranting now... -- 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: Do programmers tune SQL?
Its an internal document that some developed. -Original Message- Sent: Friday, March 29, 2002 5:43 PM To: Multiple recipients of list ORACLE-L Kim, What document did you send them? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 29, 2002 5:03 PM In my previous shop they all wanted to learn. Once I sent them all a document on SQL tuning and one of the developers would just not leave me alone. I did such and such and now its this much faster. I got that for every SQL statement he did. The novelty eventually wore off and he stopped coming over to tell me. -Original Message- Beth Sent: Friday, March 29, 2002 3:49 PM To: Multiple recipients of list ORACLE-L In my shop, 0 out of 3. Which wouldn't be so bad, but they also show 0 interest in learning... and every time a new job goes into production and takes 10 hours to run, its a database problem... ok, i'll stop ranting now... -Original Message- Sent: Friday, March 29, 2002 5:38 PM To: Multiple recipients of list ORACLE-L What percent of developers know how to explain and trace SQL, interpret these reports and tune? In my experience it's about 10%, so most SQL tuning is done by DBA's. Is that about right? -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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: 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: Kimberly Smith 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).
