Re: How to control developers writting better SQL

Comments embedded.

In article <8ta209$1ie$1_at_nnrp1.deja.com>,   EnderW <ender29_at_my-deja.com> wrote:
> Well actually I can tell horror stories to what some DBA or people who
> claim they are DBAs did.

Now we get to the heart of the matter. Yes, there are those in the workplace who claim to be DBA's but haven't the good sense to realize that they know very little about that which they speak. Some feel entitled to the position and react in a quite defensive manner when questioned about their decisions or actions. I've worked with a number of these and have had, in most cases, to clean up the mess after they have left for 'greener pastures'. The sad part of their leaving wasn't the fact that they left, but that they went to another company where the managers and other employees didn't know as much the 'DBA' did so he/she could work without fear of reprisal and perpetuate his/her mistakes on an unsuspecting enterprise.

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

A good case in point to my statement above.

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

Not necessarily, but there are times when the bad seem to outnumber the good. Certainly they are far more noticeable and their actions are far more visible and unsettling. And, if the DBA is not checking the code for proper SQL and clean execution plans, especially on systems where the resources are not optimum, the DBA is falling down on the job. Not to say that Dave is wrong; far from it, as I agree with him fully. On systems I have administered the SQL code was checked for query plans and I/O statistics and any code that didn't meet the requirements was either re-written by a DBA or sent back to the developer with instructions/examples of how the code should look. Many applications I have worked on involved remote instances and poorly written SQL would have brought not only the instance to it's knees but the network as well. If you truly have a system that has more horsepower than it needs, even fully loaded, and the resource hit is small even at peak times tuning the SQL can take a back seat. But many systems are not throwing around unused horsepower and on these systems the SQL statements the developers write should be scrutinized and passed through explain plan to ensure they are not doing anything offensive to the instance.

The problem is truly careless or less-than-knowledgable developers writing inferior code. That the DBA's you've been exposed to have not done their job properly should not reflect on the many trained DBA's who do.

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

--
David Fitzjarrell
Oracle Certified DBA


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