Re: How to control developers writting better SQL

Well actually I can tell horror stories to what some DBA or people who claim they are DBAs did. At one time, I had an argument with a DBA who pointed out that paging activity shows that a system is healthy. I kind of wanted to point out to him while a certain level of paging activity might be expected, it kind of points to file system trashing cases. No offense but when someone gets the title of a DBA, they think they are the king of the hill and this drives me crazy. This attitude I cannot accept. What you are is measured by what you know and how coperative you are. That doesnot mean that I havenot met DBAs who are good but they are far and between unfortunately.

In article <svf8megou9df68_at_corp.supernews.com>,   "Dave A" <dave_and_vanna_at_hotmail.com> wrote:
> 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.
>
>

--
Ender Wiggin


Sent via Deja.com http://www.deja.com/
Before you buy.