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.
dwilliams_at_lifetouch.com

-----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
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]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: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).