At one former employer, I used to publish a "DBA Newsletter". It went to
the DBA, sys admin, and development staff. It included a "STIES" award
(Silliest Things I've Ever Seen). It did not include any names, but did
include code and/or design snippets. It also included kudos for significant
improvements, tips and techniques, and other stuff.
Here are a few sanitized excerpts:
---
In an effort to get some of our ducks in a row, I have some comments and
questions about our current <someDB> database schema and the associated
application. This may be largely moot when Gazelle goes live - I have not
been informed of what that may change. If Gazelle uses any part of <someDB>
though, it would be an opportune time to rethink <someDB>. The changes
required are not difficult, but could be significant.
If you have any knowledge of the future of <someDB>, Gazelle or any
information about the questions/comments here, please call the database
hotline at xxx-xxxx, or "TYP KNET" (the good ones were all taken). Email to
the author is also acceptable. Your call can be anonymous (if made from an
external phone).
Any information or help directly leading to the arrest of inefficient
processing or the conviction to fix any significant design flaws will be
generously rewarded with Krispy Kreems!
-Don Granaman
DBA/Duck Herder
[...]
General
1) Table names should be singular, not plural.
"A CUSTOMER may have an ACCOUNT.", not "A CUSTOMERS may have an ACCOUNTS."
Isn't ". where ACCOUNT.ACCOUNT_ID = ." more intuitive than ". where
ACCOUNTS.ACCOUNT_ID = ."? This is referring to a particular account.
[Also, Plural table names actually break some of the features of some CASE
tools!]
2) Tables should be named logically - for what they really are.
I am a "CUSTOMER", not a "CUSTOMERS" or an "ACCOUNT".
I have a name (in DB-speak, NAME may be an attribute of a CUSTOMER.)
I do not have an ACCOUNT_NUMBER or a BALANCE_DUE (but one of my accounts
may).
I may also have one or more telephone numbers and one or more addresses.
An ACCOUNT may have a BALANCE_DUE and an ACCOUNT_NUMBER, but it doesn't
usually have its own TELEPHONE_NUMBER or MIDDLE_INITIAL.
[...]
STIES Award
===========
If the primary key of "CUSTOMERS" is "ACCOUNT_NUMBER", then isn't
"CUSTOMERS" really "ACCOUNT"? I do believe it is. It is an "imposter" table!
But, then again perhaps not. Perhaps it's schizophrenic! It does have
columns like NAME, VOICE_PHONE, FAX_PHONE, and EMAIL_ADDRESS. These sound
more like they belong to a CUSTOMERS (sic). I'm so confused... Is this
really two entities duct taped together into a single table? Or is it an
intentional and well-considered denormalization? Inquiring minds want to
know!
Let's see... The CUSTOMERS table has a primary key of ACCOUNT_NUMBER.
The <cleansed1>_PREFERENCES table has a primary key of ACCOUNT_NUMBER, which
is also a foreign key to CUSTOMERS. So it must be a one-one optional
relationship! It has only one other column: <cleansed1>_ORDER_LEVEL
varchar2(1).
The <cleansed2>_PREFERENCES table has a primary key of ACCOUNT_NUMBER, which
is also a foreign key to CUSTOMERS. So it must be a one-one optional
relationship! It has only one other column: <cleansed2>_ORDER_LEVEL
varchar2(1).
The <cleansed3>_PREFERENCES table has a primary key of ACCOUNT_NUMBER, which
is also a foreign key to CUSTOMERS. So it must be a one-one optional
relationship! It has only one other column: <cleansed3>_ORDER_LEVEL
varchar2(1).
Hmmm... There is a pattern emerging here! Someone kept creating new tables
to add new attributes to ACCOUNT (oops! I meant "CUSTOMERS")! This is the
perhaps the most entertaining bit of physical design I've ever seen. Add on
the indexes and this is a *LOT* of overhead to just add three nullable
varchar2(1) columns to CUSTOMERS!
Let us see if it the pattern continues...
[... It does - for another four tables ... Details spared for ORACLE-L...]
So, from a database 101 perspective, doesn't this mean that all of these
additional columns are actually attributes of an ACCOUNT? (Oops! I mean,
"of a CUSTOMERS")
So, what we really should have is a table named ACCOUNT (instead of
CUSTOMERS - that should be a distinct table) that looks something like:
[... and so on - including very specific details of the necessary changes to
existing code ...]
[...]
How come nobody ever talks to the DBAs about this kind of stuff? If it's a
breath thing, well... WE still have cube walls! You don't have to be in the
direct line of fire. Jim just laid out a domain on a brand new E10K as the
future home for <someDB> without a clue what it might look like in a
month... or two.
[Note to ORACLE-L: The development staff had just moved to a new building
and a "pod" environment - cubes without walls.]
Don Granaman
OraSaurus (and Duck Herder)
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Wednesday, April 16, 2003 9:13 PM
> I vote for #4!
>
> --- Cary Millsap <cary.millsap_at_hotsos.com> wrote:
> > My $0.02...
> >
> > Developers usually don't have access to a high-concurrency test
> > environment
> > in which an expensive monitoring tool would make that much difference
> > anyway. The most powerful tools I've seen?
> >
> > 1. Performance specifications - Functional specs contain a budgeted
> > number
> > of LIO operations that the code is allowed to consume. A good rule of
> > thumb
> > is 10 LIOs per (non-aggregated) result set row per table. For
> > example, a
> > 4-way join returning one row gets a budget of no more than 40 LIOs.
> >
> > 2. Profiling (tkprof, autotrace, etc.) - EVERY piece of code gets
> > traced and
> > run through a profiler. If a piece of code breaks its specified LIO
> > budget,
> > then it's not approved for check-in.
> >
> > 3. Execution plan analysis (explain plan) - EVERY piece of code has
> > its
> > execution plan checked by a performance analyst. Execution plans are
> > generated with PRODUCTION db statistics, not test db statistics.
> >
> > 4. The Wall of Shame - Write inefficient code, and your code goes up
> > on the
> > Wall for everybody to see. People whose names appear continually on
> > the Wall
> > of Shame are not selected as candidates for reproduction.
> >
> > Who is the referee in all this? The performance analyst. Some
> > performance
> > analysts are DBAs, some are developers, some are architects, and some
> > are
> > none of the above. It doesn't matter who takes on the role, as long
> > as it's
> > someone competent and credible.
> >
> >
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> >
> > Upcoming events:
> > - Hotsos Clinic 101 in London, Reykjavik, Ottawa, Dallas, Denver,
> > Sydney
> > - Visit www.hotsos.com for schedule details...
> > - IOUG-A Live 2003, Orlando, 10am Monday 28 April: "Oracle
> > Operational
> > Timing Data"
> >
> > -----Original Message-----
> > Faroult
> > Sent: Wednesday, April 16, 2003 3:24 PM
> > To: Multiple recipients of list ORACLE-L
> >
> > "Grabowy, Chris" wrote:
> > >
> > > Yes, they are teaching you what to look for...
> > >
> > > > 1) Dictionary Cache Hits (ARGH!!!!!!!!!!!!!!!!!)
> > > > 2) Percentage of Current Processes (as in we have 30 processes
> > > > attached and 300 processes configured in the init.ora)
> > > > 3) Sessions Waiting for Lock
> > > > 4) Total Sort Rate (rate of sorts per minute) on disk and in
> > memory)
> > >
> > > ;)
> > >
> > > And in my dealings with account execs, to close a deal, the hosting
> > AE
> > > will make sure the client gets whatever reports they want...
> > >
> > > Can I go back into my corner now?
> > >
> > > -----Original Message-----
> > > Sent: Wednesday, April 16, 2003 11:29 AM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > > it should be the hosting company's job to teach the clients what to
> > > really be looking at.
> > >
> > > --- "Grabowy, Chris" <cgrabowy_at_fcg.com> wrote:
> > > > (stumbling out of my lurker corner)
> > > >
> > > > Is it possible that some clients expect to see those cache hit
> > ratio
> > > > reports? Arent there still quite a few Oracle sites that are
> > still
> > > > hung up on RBO and cache hit ratios??
> > > >
> > > > -----Original Message-----
> > > > Sent: Wednesday, April 16, 2003 9:44 AM
> > > > To: Multiple recipients of list ORACLE-L
> > > >
> > > >
> > > > Ah but see, I didn't say 'DBA privs'. Most of the tools can be
> > used by
> > > > an account that has select only on any of the v$ or dba_ views.
> > > >
> > > > I also said "database aware". Which means they understand what
> > the
> > > > results mean.
> > > >
> > > > And then there are things like....
> > > >
> > > > the hosting company we use has reports on performance etc that
> > > > authorized users can look at. Reports are on CPU, Webservers,
> > page
> > > > downloads and Oracle. Here's the sad part. There are 4 Oracle
> > > > reports:
> > > >
> > > > 1) Dictionary Cache Hits (ARGH!!!!!!!!!!!!!!!!!)
> > > > 2) Percentage of Current Processes (as in we have 30 processes
> > > > attached and 300 processes configured in the init.ora)
> > > > 3) Sessions Waiting for Lock
> > > > 4) Total Sort Rate (rate of sorts per minute) on disk and in
> > memory)
> > > >
> > > > NONE of these are really useful, as they are reported on a daily
> > > > basis.
> > > >
> > > >
> > > > The first we know is really useless, and one of these days I'm
> > going
> > > > to install a cron job to run Connor's BCHR script, just for
> > giggles.
> > > >
> > > > The percentage of current processes makes no sense to me, as we
> > run
> > > > with connection pooling.
> > > >
> > > > Sessions waiting for Lock on a daily basis? I suppose it could be
> > > > useful, if only to point me to an app to start tuning.
> > > >
> > > > And what really worries me is that the Sort Rate report is the
> > "most
> > > > popular" (most viewed) of all of them. Not by me, I just learned
> > about
> > > > these reports yesterday.
> > > >
> > > > I need to have a LONG talk with the hosting company.
> > > >
> > > >
> > > > --- April Wells <awells_at_csedge.com> wrote:
> > > > >
> > > > > I will go along with that.
> > > > > Whole heartedly in fact
> > > > > But with a disclaimer.
> > > > > They (the users with access to the tools) have to be willing to
> > > > learn
> > > > > the
> > > > > tool, and what the results MEAN. If they are willing to learn
> > > > > (really learn, not just hear part then think they know all
> > there is
> > > > > to know about
> > > > > tuning code), I will give them access to the tools in a min.
> > > > >
> > > > > We have one developer who I trust implicitly. She wants to
> > learn,
> > > > > tries to use good coding standards and tries to write the
> > tightest
> > > > > code possible. I
> > > > > would give her DBA privs on the development instance in a
> > > > heartbeat,
> > > > > and any
> > > > > tool at my disposal to help her do her job.
> > > > >
> > > > > BUT...
> > > > >
> > > > > When you deal with parts of the user base (some of the
> > developers)
> > > > > that think it is really cool to have a tool that has a really
> > neat
> > > > > button that
> > > > > does everything for them, so they don't have to worry about the
> > > > > details of
> > > > > their code (SQL Navigator is our company's code generator of
> > > > > choice... not
> > > > > toad, that would be silly), then you have to deal with them
> > having
> > > > > been
> > > > > 'educated' about tuning and what they should tell the DBAs to
> > do to
> > > > > the
> > > > > system to tune it... and then they get an ORA-4030 error and
> > decide
> > > > > that
> > > > > there needs to be a UNIX Kernel change because that will make
> > their
> > > > > code
> > > > > run... you find out that a little bit of knowledge is a truly
> > > > > horrible
> > > > > thing.
> > > > >
> > > > >
> > > > > April
> > > > > -----Original Message-----
> > > > > To: Multiple recipients of list ORACLE-L
> >
> === message truncated ===
>
>
> __________________________________________________
> Do you Yahoo!?
> The New Yahoo! Search - Faster. Easier. Bingo
> http://search.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Don Granaman
INET: granaman_at_cox.net
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed May 07 2003 - 23:16:39 CDT
