Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: DBA Hacks Book

RE: DBA Hacks Book

From: Cary Millsap <>
Date: Fri, 18 Jun 2004 09:44:49 -0500
Message-ID: <017401c45542$d1c1d480$6601a8c0@CVMLAP02>


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


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.
Nullius in verba

Upcoming events:
- Performance <> Diagnosis 101: 6/22 Pittsburgh, 7/20 Cleveland, 8/10 Boston
- SQL Optimization <> 101: 5/24 San Diego, 6/14 Chicago, 6/28 Denver
- Hotsos Symposium 2005: March 6-10 Dallas - Visit for schedule details...

-----Original Message-----
From: [] On Behalf Of rob zijlstra
Sent: Friday, June 18, 2004 8:40 AM
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!  


Rob Zijlstra.      

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Fri Jun 18 2004 - 09:44:29 CDT

Original text of this message