| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: DBA Hacks Book
Cary,
I stand corrected of course. I didn't cross my mind that the discussion also included things like a programmer writing something like
for each row (1,000,000 of them)
c = parse(an insert statement with literal values in
it)
execute(c)
Let my try to comment on this:
The 3 yrs I worked in an Oracle environment, the environment was ALWAYS divided in a 'DEV', 'TEST' and 'PROD' db. As a developer I worked in DEV, never (and that means NEVER) in TEST and only in PROD when I had to correct some faulty input. And 'we developers' knew that every access from us to PROD & TEST was logged.
This way of working was very good, because DEV, TEST & PROD were almost a copy of each other ( same number of tables, rows in tables etc etc). Now, of course we made mistakes like the one above and then we went to the DBA and said something like 'TOAD doesn't respond anymore, can you take a look?'
Now I want to stress (once more I'm afraid) an important point. The DBA didn't only kill the session, but he also explained every time what went wrong and why it went wrong. Of course he was no angelic person, sometimes he could say things rather forcefully, but the point is, he said them and had not the attitude of 'these things are in the realm of a DBA, so get lost'.
Also the fact that we made these mistakes in DEV and NEVER in PROD, made his (and ours!) live a lot easier.
Now, what I'm trying to say (and it's more difficult to express this in English than I thought) is that as a developer you mostly look at the db as a black box. Certainly when you start with pl/sql you haven't the faintest idea what happens when you issue a command like 'commit'. I think you agree with me if I say that as a developer you need to have a lot of knowledge of the system you're working with, i.e. the Oracle db / network traffic and that means access to the information about it. And the easiest way is to talk to a DBA -if he has time and is in the right mood etc etc-.
Thank you and have a nice weekend,
Rob Zijlstra
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Cary Millsap
Sent: vrijdag 18 juni 2004 16:45
To: oracle-l_at_freelists.org
Subject: RE: DBA Hacks Book
Rob,
I like what you're saying here a lot. I was educated as a developer ("software engineer," we called it), and what you're saying is what would have been printed on our flag if we had had one.
I spoke a couple nights ago in New Orleans at a NOOUG meeting, and a similar issue arose. How can a database prevent an application developer (a DBA's "user") from doing something he's not supposed to do? The answer, I think, is that it's impossible. I'll give an example of what I mean. Imagine that an application program inserts a million rows into a table with the following code:
for each row (1,000,000 of them)
c = parse(an insert statement with literal values in
it)
execute(c)
Now, the database can do all sorts of clever things on the back end, like the CURSOR_SHARING=FORCE thing to pretend that the literal values in the VALUES clause are really bind variables, thus "softening" the parse workload. But there's no way for the db back-end to conserve the network capacity that has been consumed by the application's issuance of the 999,999 unnecessary parse calls. Each of those bullets is already out of the barrel by the time the db even sees what has happened. There's also no way the db can conserve the time spent blocked on read(SQLNET_IN, .) calls (whose time gets logged to the 'SQL*Net message from client' timed event) that occur between adjacent pairs of dbcalls. The only thing the db back-end can do is respond as efficiently as it can to what the "user" has asked it to do.
Programmers need to prevent their users from doing stupid things, but it's not always possible. The user MUST be an active participant in the performance optimization process, or performance will suffer. Here's another example, this time out in the familiar territory on the application front-end. Imagine a report that prints out sales history information since some date specified in an input argument. What if a user specifies since-when=1800? Then the program will report on sales history that includes the pre-Napoleonic war era. How many "Are you sure?" prompts should it take to warn a user not to do that? The hard part of the problem is that perhaps there is a legitimate circumstance in which since-when=1800 is completely appropriate, so it has to be left to the user (not the programmer) to decide when to do it.
I think the answer is education. Just like an application programmer must, on occasion, give application users enough rope to hang themselves, a database must, on occasion, give application developers enough rope to hang THEMselves. You can parse a million times if you want (to the database's credit, the code will still work), or you can write code that's far more efficient if you write it to parse just once. An application system can run efficiently only if people at all the layers are suitably educated. The Oracle kernel developers certainly are; this is why the Oracle Database is such a popular product. But a lot of application developers are not, and this is why a lot of applications have serious performance problems. And a lot of users are not, which is why some companies fail spectacularly with the same exact technology stack as a competing company that succeeds spectacularly.
Net-net, I think what you're saying is exactly true for functional requirements. The code at layer N+1 has to produce the answer by layer N, no matter how layer N (the "user") asks for it. But performance requirements are very different. There are some problems (like my first example) in which the db simply CAN'T make an application behave efficiently-no matter how smart, self-tuning, etc. it gets-because the application (the db's "user") has required it to do something stupid.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Nullius in verba
Upcoming events:
- Performance <http://www.hotsos.com/courses/PD101.php> Diagnosis 101: 6/22
Pittsburgh, 7/20 Cleveland, 8/10 Boston
- SQL Optimization <http://www.hotsos.com/courses/OP101.php> 101: 5/24 San
Diego, 6/14 Chicago, 6/28 Denver
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of rob zijlstra
Sent: Friday, June 18, 2004 8:40 AM
To: oracle-l_at_freelists.org
Subject: RE: DBA Hacks Book
>> I work with developers who use our databases all the time in ways that
aren't anticipated but I can't discuss it without a lot of unfriendly
language.
Here we go again.
In prehistoric times (about 1980) when I started to learn to program, one of the first things I learned that if a user does something that you hadn't anticipate, it was not the fault of the user. Of course it only meant that the programmer didn't use his brains enough to foresee these things. He should make a better program, and certainly NOT try to explain to the user that 'he shouldn't do that and that'; no, if he was a real programmer, the user could NEVER even do 'that and that'.
The sentence above only means to me, that the person who developed the db in question should try to work smarter instead of complaining!
Greetings,
Rob Zijlstra.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Jun 18 2004 - 13:59:04 CDT
 |
 |