Re: How to control developers writting better SQL

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