RE: DBA Hacks Book
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 18 Jun 2004 09:44:49 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of rob zijlstra
Sent: Friday, June 18, 2004 8:40 AM
To: oracle-l@xxxxxxxxxxxxx
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.
Other related posts:
