Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: why administrator refuse to give permission on PLUSTRACE

Re: why administrator refuse to give permission on PLUSTRACE

From: Peter J. Holzer <>
Date: Sun, 28 Oct 2007 15:24:17 +0100
Message-ID: <>

On 2007-10-27 13:15, DA Morgan <> wrote:
> Robert Klemme wrote:
>> On 27.10.2007 08:11, DA Morgan wrote:
>>> Mark D Powell wrote:
>>>> I do not consider autotrace as being a necessity.
>>> I do.
>>>> Regular explain plan is usually good enough.
>>> The emphasis being, in my oopinion on "usually."

Yup. I've seen cases where explain plan showed one plan but Oracle would choose a different plan for the same query issued from the application.

>>>> I suspect the DBA does not want
>>>> developers running poorly performing SQL on production in order to get
>>>> an autotrace to tune the SQL with when the developer should have just
>>>> ran explain plan first.
>>> Here I agree and I will go one step further. No developer should ever
>>> have access to a production database except as an end-user utilizing the
>>> application. I've yet to see a legitimate reason for any developer to
>>> have production access privs.

I have a different idea: Every programmer should be required to administrate at least one system where his software is deployed.

Seriously, I agree that you don't want most programmers anywhere near a production system. But then, those are probably the programmers which you don't want to develop software which runs on your system anyway.

>> I'm not sure I agree here - at least not with that level of strictness.
>> There may be situations where a collaborative team effort (engineering
>> and DBA) is needed to hunt down an issue that occurs on a production
>> system.

> Assuming a competent DBA, one that knows how to run traces, one that
> knows how to create a StatsPack or run AWR, or one that can run queries
> against ASH ... there is nothing a developer can learn on a production
> server they can't learn from reading Cary Millsap's book, Jonathan
> Lewis' book, and looking at the metrics created by the DBA.
> A developer rummaging around a production instance trying to diagnose
> what the DBA can not? If you can provide a scenario where this looks
> like a good idea I'd be interested in considering it.

The programmer knows what the queries are for. So while the DBA may be better at identifying hot queries and may even be better at optimizing them (although I think that is a skill that a DB programmer should have), the programmer may notice that a particular query can be completely eliminated or that his assumptions about user behaviour were way off and he needs to restructure part of his applications.

Sure, he can get all this from the DBA. But if he doesn't already know what he's looking for he may not be able to tell the DBA what he needs to know. Waiting for a few hours between each question and answer is also not exactly beneficial for concentrated work. If the programmer can just look at the DB for himself (or do it together with the DBA, which has the advantage of a second pair of eyeballs at the slight disadvantage of more communication overhead), finding bugs or performance problems can be a matter of minutes instead of hours or days: "Wait, that's odd - can we look at this? - No, that's ok, so look at that - Yes, that looks fishy, why does it do that? - Oh, I think I got it."

        hp (Sysadmin, programmer, and (a little bit) DBA)

   _  | Peter J. Holzer    | It took a genius to create [TeX],
|_|_) | Sysadmin WSR       | and it takes a genius to maintain it.
| |   |         | That's not engineering, that's art.
__/   | |	-- David Kastrup in comp.text.tex
Received on Sun Oct 28 2007 - 09:24:17 CDT

Original text of this message