Re: why administrator refuse to give permission on PLUSTRACE

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

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

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

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

> 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. There are developers that are highly skilled at these tasks, and there are DBAs whose time is stretched so thin, that he can devote little to no time to finding the application's programming logic that is at fault for the poor performance.

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. On one table, there is a primary key column defined as NUMBER(22,4). As the developer is using ADO to connect to the Oracle database, he defines the bind variable data type on the primary key column as adSingle. With cursor sharing still disabled, update performance using the SQL statement drops from roughly 100 executions per second to 1.5 executions per second. How would the DBA resolve this performance problem using just his knowledge, and how might the developer be better qualified to do the same, when the goal is for at least 1,000 executions per second?

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

As I stated, I am just curious.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Nov 10 2007 - 21:35:49 CST