Re: How to control developers writting better SQL

Posting to an Oracle group criticizing DBAs in general and you don't want to be flamed?!?!?! Tsk, tsk.

I for one am a DBA who does regularly check SQL statements being run on a database. If I follow your definition given below, then I must not be a "real life DBA". And all this time I thought that I was. Other DBAs at our facility must not be real life DBAs since they check SQL queries too.

Every week, I get a scheduled report of the most "offensive" SQL queries running against the database. If the query is more than an ad-hoc query (i.e. run more than a few times), then I flag the query. Most times, I submit the query to our development team for a rewrite. In about 50% of those cases, I'm the one who ends up tuning the query. I have to be the one who tunes the query because I often know much more about SQL and tuning SQL statements than the developer.

And how is it that the "DBAs are the ones who are create a problem" as you pointed out? The DBA didn't write the query. If I never ran a query written by a developer, then my database would run perfectly. Are you saying that the DBA creates the problem because he doesn't check the SQL statements? Isn't part of development tuning the code, including the SQL statements?

One final note, feel free to flame me. I'm not ashamed to have written the above statements. I stand by my words.

Just my 3.14159265 cents worth,
Brian

EnderW wrote:
>
> As a nonDBA, I must point out that DBAs are the ones who are creating a
> problem. I have yet to see a real life DBA who checks sql staements
> that are being run in the database ( Ok I am lying I have seen only one
> till now) and then there are those DBAs who don't know much SQL ... No
> flames please...
>
> In article <8t6gpj$2h9$1_at_nnrp1.deja.com>,
> sbrendan_at_my-deja.com wrote:
> > I would agree that a peer-to-peer code review is the best way to go -
 a
> > DBA must be present to help out with good SQL practices. Leading the
> > way for a team of developers will go a long way in getting better SQL
> > submitted to your database. This in conjunction with a code version
> > control system (i.e. RCS, PVCS, SourceSafe, etc) will help in
 resolving
> > the issue of poorly written SQL.
> >
> > Also, have a bookshelf full of SQL books hanging around and refer your
> > developers to them. Who knows, they might just pick up the books and
> > learn some good techniques.
> >
> > Cheers.
> > - Brendan
> >
> > In article <972416580.28601.1.nnrp-12.9e984b29_at_news.demon.co.uk>,
> > "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> > > I always feel that scheduling a peer-group
> > > review once per week helps. Everyone gets
> > > to read a bit of everyone else's code.
> > >
> > > --
> > >
> > > Jonathan Lewis
> > > Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
> > >
> > > Connor McDonald wrote in message <39F576A6.1FDF_at_yahoo.com>...
> > > >Dasari wrote:
> > > >>
> > > >> Educating developers to write better SQL is far most easier but
 they
 never
> > > >> follow they have their own problems. For instance for same
 purpose
 many
> > > >> developers write same SQL in different styles confusing Oracle.
 Please do
> > > >> not call this as bad design of application, but merely an issue
 of
> > > >> controlling all this
> > > >> to make a DBA life easier. Monitoring for time consuming SQLs and
 tuning
 is
> > > >> a continuous process anyway.
> > > >> Can I say something like a .sql file with all the SQL statements
 written
 by
> > > >> the developers called SQL library, If they want to write a SQL
 pl.
 check
 in
> > > >> this file
> > > >> make sure one is not available for the purpose and append into
 this
 file
 and
> > > >> a DBA frequently monitors this file for any new SQL's that
 require
 basic
> > > >> tuning.
> > > >> Does this make sense.
> > > >> How you'll do this, I would be very grateful if you could share
 some
> > > >> thoughts / ideas.
> > > >>
> > > >> TIA
> > > >> Dasari
> > > >
> > > >Anything other than good training for your developers, as well as a
> > > >committment from them toward performance will always be a poor
> > > >substitute.
> > > >
> > > >Some brute force things you could consider:
> > > >
> > > >- insisting on an explain plan when new sql routines are being
> > > >deployed. The developer may not have looked at it, but its better
 than
> > > >not having run an explain at all.
> > > >
> > > >- implementing stored outlines for the decent SQL's once they are
 in
> > > >place; then anything thats introduced that doesn't have a stored
 outline
> > > >could go back to the developer
> > > >
> > > >- anything in v$sqlarea that goes above some pre-imposed limit on
 disk
> > > >reads, buffer gets or executions goes back to the developer
> > > >
> > > >- put offending people into lower priority resource plans
> > > >
> > > >- centralise the most critical application cursors into a common
 place,
> > > >say several PL/SQL packages and then publish these. Then a
 developer
> > > >needs to justify getting his key SQL's into the main repository.
 (You'd
> > > >be virtually guaranteeing re-use in this way).
> > > >
> > > >- disallow anything that contains literals, ie, insist on bind
 variables
> > > >
> > > >But to stress again - the moment your developers have that "Hey -
 we've
> > > >got a big server, I don't need to worry about that kind of stuff",
> > > >you're doomed...
> > > >
> > > >HTH
> > > >
> > > >
> > > >--
> > > >===========================================
> > > >Connor McDonald
> > > >http://www.oracledba.co.uk (mirrored at
> > > >http://www.oradba.freeserve.co.uk)
> > > >
> > > >"Early to bed and early to rise,
> > > > makes a man healthy, wealthy and wise." - some dead guy
> > >
> > >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> --
> Ender Wiggin
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================