Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to control developers writting better SQL
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