Re: How to control developers writting better SQL

Not sure what organizations you have been exposed to. At my site, we have systems that have plenty of horse power to handle some less than perfect SQL so we don't worry about those too much.

Then we have systems that require tuning to give adequate performance. Those we isolate the most expensive SQL both in terms of I/O per execution and in terms of cumulative I/O per day. WE rewrite the SQL and send it back to the developers to incorporate into the next build.

Then we have systems that are so heavily used that a single statement executing with a full table scan will bring the system to it's knees. Every piece of SQL is reviewed before going to production.

Even if the DBAs at your shop don't ever review the SQL in any way, I'm not sure how you can justify the statement that the DBAs cause the problem. Ultimately developers are responsible for writing code that is functional and that performs acceptably. Production DBAs spot the code that doesn't meet those criteria. The problem originates in the same place the code originates.

--
Dave A


"EnderW" <ender29_at_my-deja.com> wrote in message
news:8t7sda$a03$1_at_nnrp1.deja.com...


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