| 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
You've said it perfectly, I think.
If more people followed the recipe you've laid out, there would be FAR =
fewer
performance problems in production applications.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* 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 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 2:02 PM
To: oracle-l_at_freelists.org
Subject: RE: DBA Hacks Book
Cary,
=20
I stand corrected of course. I didn't cross my mind that the discussion =
also
included things like a programmer writing something like
=20
for each row (1,000,000 of them)
c =3D parse(an insert statement with literal =
values in
execute(c)
=20
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.
=20
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-. =20
=20
Thank you and have a nice weekend,
=20
Rob Zijlstra
=20
=20
_____ =20
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
=20
Rob,
=20
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.
=20
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:
=20
for each row (1,000,000 of them)
c =3D parse(an insert statement with literal =
values in
execute(c)
=20
Now, the database can do all sorts of clever things on the back end, =
like
the CURSOR_SHARING=3DFORCE 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.
=20
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=3D1800? 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=3D1800 is =
completely
appropriate, so it has to be left to the user (not the programmer) to =
decide
when to do it.
=20
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.
=20
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.
=20
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
=20
>> 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.
=20
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'.
=20
The sentence above only means to me, that the person who developed the =
db in
question should try to work smarter instead of complaining!
=20
Greetings,
=20
Rob Zijlstra.
=20
=20
=20
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- 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 - 14:26:46 CDT
 |
 |