Re: Do programmers tune SQL?

From: Witold Iwaniec <wiwaniec_at_novalistech.com>
Date: Mon, 01 Apr 2002 13:59:15 -0800
Message-ID: <F001.00438420.20020401135915@fatcity.com>

I may open a can of worms - but don't intend to...

Sometimes DBAs and Sys Admins make tuning impossible or at least very hard for developers.

I am a developer and do a lot of tuning of statements used by our applications. I have, in our office, the luxury of DBA access to databases and access to UNIX servers. So life is not bad.

But I have been in places were security was very strict and:

• I had, in database, only user account with limited rights - I didn't have access to PLAN_TABLE so after writing my SQL I had to ask DBA to run it. And the same if I changed it, and then I may have changed it number of times...
• it was worse when I had to tune stored procedures. Again, I had only a user account but not the procedure owner account - so I wrote procedure, asked the DBA to compile it, run my statements, and maybe all over again...
• I didn't have account on the UNIX server so when the trace file was generated I had to ask the DBA to run TKPROF and send me the output. In an extreme case I run into junior DBA who had never run TKPROF before so it took even more time explaining why I wanted to use explain=... and sys=no etc. Occasionally he had to contact senior DBA and wait for his response.

On top of that DBAs had other tasks so sometimes it took quite some time to get a procedure recompiled, trace file generated, or run TKPROF. In one place there was a rule that DBAs had 48 hrs to respond to any request and sometimes it took that long.

Sometimes a very simple task, that could be completed in few minutes, took days because of all procedural things. At the end it cost companies lot of money.

In our company many developers have DBA access to databases and in fact nobody does anything stupid. I have found that if people don't know what they are doing, they ask. Nobody tries to drop or truncate tables because they have found the script that seesm cool and want to see what it does... Over my 8 years here only once we had a problem - a few tables were dropped by mistake but the tables were dropped by our DBA who run the wrong script. It took almost no time at all to rebuild it. And it happened in a test database.

I understand restrictions in production databases but more access to development and test would make life easier and am sure more happy faces around.

Witold

---

Witold Iwaniec
Sr Software Developer
NovaLIS Technologies
wiwaniec_at_novalistech.com
http://www.novalistech.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Witold Iwaniec
  INET: wiwaniec_at_novalistech.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Apr 01 2002 - 15:59:15 CST