Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to control developers writting better SQL

Re: How to control developers writting better SQL

From: <oratune_at_aol.com>
Date: Tue, 24 Oct 2000 21:54:21 GMT
Message-ID: <8t50ea$t3u$1@nnrp1.deja.com>

In article <39F576A6.1FDF_at_yahoo.com>,
  connor_mcdonald_at_yahoo.com wrote:
> 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
>

Excellent suggestions, all. I especially agree with the last statement

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

There is not much worse than a murder of careless developers run wild with errant and inefficient SQL. It is one of the surest routes to failure.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Oct 24 2000 - 16:54:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US