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.
> dwilliams_at_lifetouch.com
>
> -----Original Message-----
> Sent: Monday, April 01, 2002 9:13 AM
> To: Multiple recipients of list ORACLE-L
>
> and your question is?
>
> --- DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM> 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.
> > 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).
>
> __________________________________________________
> 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: wisernet100_at_yahoo.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).
> --
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Suzy Vordos
  INET: lvordos_at_qwest.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).