Re: why administrator refuse to give permission on PLUSTRACE

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 19 Nov 2007 00:00:18 -0800
Message-ID: <1195459217.371231@bubbleator.drizzle.com>

Charles Hooper wrote:
> On Nov 10, 3:17 am, DA Morgan <damor..._at_psoug.org> wrote:

>> But lets say that the next time someone in management asks a developer
>> to do a DBAs job the developer just said "I don't have the proper
>> training to be a DBA and might inadvertently do serious damage. If you
>> want me to touch prod you will have to give me appropriate training to
>> be a DBA."
>>
>> Would the developer be fired? I doubt it and if there are plenty of
>> better jobs to be had.
>>
>> Would the DBA be told to do it? Quite likely.

>
> Daniel, I am just curious, how does one define whether an
> application's performance problem is the responsibility of the DBA or
> the application's developer?

That is usually something determined by an organization's processes. Hopefully documented but otherwise based on custom.

In most large organizations the complaint goes to a help desk that is there to triage and direct the issue to whoever has been designated. That might be a DBA or a developer or in many cases an Application DBA who has ultimate responsibility for the application and is neither a database DBA or a developer.

 > Does it matter how large the IT
> department is - does it matter if there are 2 people in the IT
> department (1 developer and 1 DBA), 10 people in the IT department (9
> developers and 1 DBA, or 5 DBAs and 5 developers), or 1000 people in
> the IT department (of which there are 100 developers and 10 DBAs). If
> the ratio of developers to DBAs is 10 to 1, is it reasonable to expect
> the DBA to understand the program logic and identify the specific code
> fragment that causes a CPU spike, or some other indicator in a
> Statspack report?

Only in the sense that in a small organization where people often/usually wear multiple hats it may well be that one person has multiple jobs. If someone is both a developer and DBA then there are no issues with them being in prod with DBA privs. But if they are a developer who has been denied access to v$, dba_, etc. objects and wouldn't know how to use StatsPack they don't belong in prod.

>> If the DBA showed some integrity and demanded training would the DBA get
>> it? I think so as long as that person was reasonable and professional.

>
> Would that be training in the poorly performing application logic, or
> training in reading trace files, Statspack, or other tools meant for
> tuning the instance?

Depends on what they demand wouldn't it. They might demand OEM Grid and use ADDM and the Tuning Advisor.

>>>> Are developers trained in the use of StatsPack?
>>> You've never seen someone question the usefulness of StatsPack?
>> That wasn't the question. The question was whether developers are
>> trained in the tools used to diagnose database issues.
>>
>> If you wish to claim StatsPacks of little or no value then I'd like
>> to see you address that to Jonathan Lewis. <g>

>
> I believe that was a reference to Cary Millsap's Optimizing Oracle
> Performance book, in which Cary Millsap describes various problems
> with Statspack reports, including the problem of Statspack not being
> able to properly time scope a problem. A significant performance
> problem that lasts 5 minutes per hour may not be easily identified due
> to the averaging of the performance problem over the duration of the
> Statspack snapshot duration. Jonathan Lewis alludes to this problem
> in his latest posting on Statspack reports:
> http://jonathanlewis.wordpress.com/2007/11/10/analysing-statspack8/#comments
>
> It is, however, very impressive what types of problems Jonathan is
> able to identify from looking at a Statspack report.

The point of what I wrote was not to indicate that StatsPack will solve all problems any more than will any other single tool or device. Rather my point was that a developer who can not run a StatsPack is wholly untrained in the information required to identify the problem. Whether they gather the information with SQL, StatsPack, AWR, ASH, etc. it still comes down to being trained in some methodology more useful than "I have to do something and the first thing that pops into my head is something."

>> Wonderful. I agree. But irrelevant. The issue we have been beating to
>> death here is the claim that developers belong on production boxes to
>> identify problems. If the developer was good enough the bad code
>> wouldn't be on the box in the first place.

>
> Is it not possible that some problems may not be present in the
> development database which are present in the production database?

Of course it is possible. Many things are possible. From where I sit this "need" developers have to be on prod is about ego not ability. There is a hierarchy that puts DBAs over developers in most people's minds and they are equating self-worth with having DBA privs. It is no different from surgeons being paid more money than general practitioners. They have extra training, extra schooling, and they deserve the extra compensation. The problem in our part of the world is that there are many DBAs that are not qualified to use a keyboard. Thus developers see the hierarchy without the value it should represent.

> Or
> the opposite, problems in the development database that are not
> present in the production database. For instance, a problem that only
> presents itself when 100 sessions are connected to the database, one
> of which is connected over a high latency WAN link, and a batch job is
> started which updates many rows in many tables. What about the
> opposite, where the application performs slowly in development, but
> quickly in production due to different initialization parameter
> values, different data blocks that are already in the buffer cache,
> better performing computer hardware on the production box, etc.?

Again ... of course. But that does not excuse putting an unqualified developer onto a production box with DBA privs.

>> But it is ... so what experience or knowledge of tuning tools does
>> the developer have to identify the problem statements? So far not a
>> single developer has been able to tell me the name of the tool and the
>> methodology they would use.

>
> In my opinion, every developer using Oracle databases should be able
> to read a 10046 trace file at level 12, should be able to interpret
> wait events, should be able to read an explain plan, and should be
> able to write a functionally equivalent SQL statement that performs
> better than the original.

I agree. But they can't. It is sad but true: They can not.

 > There are developers that are highly skilled at these tasks

No disagreement here. But the number of cses where this is true is at most a couple of percent.

> For example, assume that that DBA has complained to the developer that
> the shared pool is a mess due to the application programmer's use of
> constants in most SQL statements.

Then the developer should get his or her bottom into their seat and fix the problem, in dev and test, pronto.

What you just wrote is reality ... the DBA identifies the issue. The developer should have the skill to fix it. This is not a reason for the DBA to grant SYSDBA to the developer and let them into prod.

> As I stated, I am just curious.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

I agree with essentially everything you said.

The problem with our profession is that the sole qualification to become a DBA is to put these three letters on your CV and fool some manager or HR department into hiring you. The same is true for developers. That some are qualified and competent is great but blurring the lines of responsibility makes no more sense in a database than it does in a surgical theatre. I don't care how competent an anesthesiologist is ... I don't want him performing a triple bypass on me.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Nov 19 2007 - 02:00:18 CST