Re: How to control developers writting better SQL

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. Received on Wed Oct 25 2000 - 06:39:32 CDT