Re: How to control developers writting better SQL

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.