What did I decide on...
What did I decide on… I had an idea of what I wanted to do at OOW (Oracle Open World) and Joel Garry came close to it. I settled on “Database Worst Practices”. Why do yet another “Best Practices” presentation – lots of those out there. My abstract is:
A lot of time is spent by IT professionals studying 'best practices' in our industry. I would like to turn that around and take a look at some 'worst practices', the top things you can do to ensure failure, delays and poor performance in your database application implementations. Instead of focusing on things to do in your database applications we’ll look at things you should never do. Based on my experiences with customers, we’ll investigate the top ten things done wrong over and over again and how to avoid making the same mistakes yourself.
I’ve already got a good start on it, no shortage of examples. So so so many examples. Here is an interesting “point example”. Bet you didn’t know we have a year 2038 problem looming and it is already starting to affect us!
A lot of time is spent by IT professionals studying 'best practices' in our industry. I would like to turn that around and take a look at some 'worst practices', the top things you can do to ensure failure, delays and poor performance in your database application implementations. Instead of focusing on things to do in your database applications we’ll look at things you should never do. Based on my experiences with customers, we’ll investigate the top ten things done wrong over and over again and how to avoid making the same mistakes yourself.
I’ve already got a good start on it, no shortage of examples. So so so many examples. Here is an interesting “point example”. Bet you didn’t know we have a year 2038 problem looming and it is already starting to affect us!
posted by Thomas Kyte at 3:53 PM
27 Comments:
Unfortunately, I won't be able to attend OOW, but would love to know more about what you talk about. Is it in the works to make that available?
Wikipedia has a good section on anti-patterns. Even though the examples tend to be in something called "Java", I recognise Big Ball Of Mud, Error Hiding, Cargo Cult Programming, Reinventing the Square Wheel and many others.
I don't know what your 10 worst practices are. I bet I will recognize many of them, though. Just for giggles, here are some of my favoriate -- in no particular order.
. Storing dates as strings (numbers too)
. Making a column NULLable, then doing NVL() everywhere you address that column.
. Fetch across commit
. No declaritive constraints (PK, FK, Unique, and check too). Instead, relying on Unique indexes to provide the PK and Unique constraints.
. Pasting on security at the end of the project
. Spending lots of $$s on a nice big high performance SAN, then mounting up one huge (parity RAID) LVOL (volume) for the entire database server to use for everything.
. Using 'Smart Keys', or otherwise mashing two or more data elements into one column for the PK. Like Year/Account Type/Order Type/Order Number as varchar2(15).
. Insufficient or in appropriate (not needed or not useable indexes)
. Failure to capture growth metrics so you can go to the boss and say something like 'In four months we will run out of disk, and I know this because over the past year the database has been growing by . . .'
. Testing recovery once and never again.
. Declaring all string columns as VARCHAR2(4000).
. Believing that 'all of my ratios are good, so everything is as good as it can get.'
. Relying only on tools like OEM (yes it, and the others are nice products) to tell you everyghing you need to know about the database and its internals.
. Believing (some high priced consultant name here) just because he says so and the boss paid lots of $$s to secure his services.
I am sure there are lots of other worst practices that others may know -- me too. These just came sort of rolling off the top of my head as I wrote this.
Trying to make stored procedures so flexible that they end up writing code to run at run time. (via execute immediate or dbms_sql)
Entity value tables.
Reorganizing data again and again because it will be faster.
Number 1 worst mistake (IMHO) starting to design/build the solution before you have identified the problem.
But have we fixed the Y31086 problem?
Word: vozffrit
Anonymous said....Is it in the works to make that available?
This is an easy one -
Tom ALWAYS makes his presentation available for later download - especially the major ones - so I'm pretty sure this OOW one will definitely be ready after the event.
Hi Tom,
providing some worst practise is a very good idea. I think if i read your slides (hopefully you'll publish them :-) ) i'll see these "terrible" things again. Maybe it would be a good slide for our development guys to avoid reenginiering.
Thomas
Another couple of points that I thought of on the drive in this morning:
. Using the database as a data store and putting everything else -- including integrety constraints -- in the application. If you are going to do this, you might as well just use a VSAM file system and be done with it.
. Trying to be database agnostic -- lowest common denominator -- write once and run on any database. . . I have found through years of seeing it happen over and over again that companies actually spend more effort (read $$$s) on coding to be able to run on any of several databases than they would by writing one well built application and then porting it to take advantage of the facilities each of the couple of databases their customers use.
. Forgetting that the performance of every single query is important because they all affect scalability and if you build a quality application, your user base will expand and you will have to be able to scale.
It would also a good idea to not only tell about worst practices, but also (try to) tell why people do these bad things. Explaining which myth or oracle 5 bug is the reason behind it (apart of pure ignorance that is)
That is so cool. You post in your blog what you want to do a presentation on and a lot people in the blogospher provide you with content and subject matter!! (Not that you don't have enough from you experience over the years.) It would be interesting to monitor scenarios that people post here, have your blog readers vote on their top-ten list, and compare the results with what you feel are your top-ten based on your experiences.
Many years ago I went to see a presentation by Grady Booch. He was proceeded by a psychologist who had been studying engineering and computer people for years. He made a comment that IT people are typically above average in intelligencd but below average in social skills. I watched as everyone smiled and nodded their heads...they all knew someone else like that.
That is so cool. You post in your blog what you want to do a presentation on and a lot people in the blogosphere provide you with content and subject matter!! (Not that you don't have enough from you experience over the years.) It would be interesting to monitor scenarios that people post here, have your blog readers vote on their top-ten list, and compare the results with what you feel are your top-ten based on your experiences.
I think it has to be N-RTF-CM'ing (Not reading the concepts manual)
When you get "senior developers" who go straight in to a project without looking at what the tool (Oracle) can do out of the box.
The number of systems we have (from vendors) that re-invent the wheel is staggering (but we have little come back on).
XML - "we do it our way" as CLOBS
Text indexing - "we do it our way" as an external application
PDF / object storing - "we do it our way" as hard coded links to a separate large disk store
Referential integrity - "we do it our way" with extra tables and hard coding
Business logic - "we do it our way" by hard coding in the Java
Please Please Please
- read the concepts manual
- don't re-invent the wheel
- don't do it the first way that comes into your head
- think about what this system will have to do in 5 years time (and you won't be around to support it)
You can tell I work in an IT support department
Guy
Bet you didn’t know we have a year 2038 problem looming and it is already starting to affect us!
Hmmm ... yes. I first learned about it when I was reading up on the looming Y2K problem 'round about 12 years ago. Basically, standard 32-bit UNIX time stamps are represented as the number of seconds since Start of Epoch, which is Midnight, UTC, on January 1, 1970. There are actually 31 bits available to represent the seconds, plus a sign bit. Thus, you can represent dates from December 13, 1901 up until January 18, 2038. After that, the date presumably "rolls over" and it's 1901 all over again.
So, if you have UNIX-based applications which are looking at dates 30 years in the future (such as the loan pay-off date for 30-year mortgages), there are going to be some problems in about a year and a half. Just like some financial institutions had to deal with Y2K as far back as 1970.
Thus, the push is on to switch to 64-bit dates.
Bob Shepard
Plagiarism !
My UKOUG '04 presentation - I don't have original abstract, but on the second slide was:
"Database disasters: Really, really, really, really silly things that people do with Oracle systems that that they think is really, really, really smart idea at the time"
hee hee hee... it'll be good fun to see a different slant on a similar topic.
What a coincedence, I just got an email from SearchOracle on the same subject:
http://go.techtarget.com/r/318580/1801211
That's how not to approach your presentation I think Tom. You have to wonder, if someone who has access to a production database an is making any of those mistakes.....
Do we also need to be told not to shoot the database server with Howitzer?
-Alexander
I think there is only one worst practice:
'having best practices'
Never, Never, Never attempt to transfer the production database server into another rack while it's still running in production (common sense, but I've seen it happen)
BLEW MY MIND......
Hi, I would like to present at OOW. How do I go about sending an extract/paper for OOW? Is there a call for papers????
> Hi, I would like to present at OOW. How do I go about sending an extract/paper for OOW? Is there a call for papers????
Unfortunately the call for papers closed on the 22nd of June...
- Mark
I will be at OOW along with a colleague so will definitely register for your session. Let's hope the lineup to get in is not going be crazy like a couple of years ago where it took half an hour to get all the registered folks into the room.
Why is it that "worst" practices are so frequently practised ?
It happens with such monotonous regularity, that I expect to see it where ever I go.
It's not like you've got to be a member of a secret society to get hold of "best" practice information. The information is freely available to all and sundry. So.... what is it about human behaviour that leads so many to avoid doing the things required to do the best job possible ?
To be honest, I think you'd be better off putting your efforts into another topic, because what you're trying to address here is changing human behaviour and habit. It's not something that can be resolved in one session.
And besides, I think you'll be preaching to the choir. I'm sure alot of those that read your material and attend your conferences are in agreement with your views, and either practise what is being preached, or (for reasons known only to themselves) they choose not to.
Why do "worst practices" develop? Clearly, the intent is 'ease of development' or 'database independence' or similar presumably-desirable goal. A lot is based on judgement, and good judgement comes from bad experience (as the old saying goes).
Similarly, the most negative review (with which I do not agree) of Expert 1-on-1 on Amazon follows, in part:
"...the attitude, which the book conveys, is tremendously wrong, not even wrong but reflecting a dangerous trend in the computer industry: the all solving one and only middleware, the one i am expert in: In Kyte's case Oracle. And by reducing the solution domain of software to one tool, they denounce at the same time software engineering quality goals, like maintainability, reliability, portability, reuseability for the sake of....? Depends really on what aspect they are looking at, respectively "advocating", but mostly for the sake of efficiency. But first off, efficiency is not the only required attribute of software, albeit clearly an important one and and second efficiency is not reducible too not one solution domain. Kyte cite's some projects, which were on the brink of failing, because basically they didn't take enough advantage of the oracle database functionality... this seems a fairly subjective choice, i had contrary experiences: many a project was on the brink of failure or even failed, because of using too "much" oracle functionality. Kyte and the book, represent to me a typical "lobby" of developers, which tend to reduce the software problem to Oracle functionality. Software Engineering isn't only PL/SQL (....or justice sake for example Java)... "
In process of writing my own paper of some logical database design problems and searching a bit for materials I found this page. So now I've written it and one can find it here http://www.gplivna.eu/papers/data_waste_or_data_base.htm.
In short they are as follows:
1. Lack of documentation
2. The only well-informed person has left a while ago
3. Lack of initial design
4. Lack of naming conventions
5. All logic in application / db has only tables
6. Many persons have already taken part to make data waste bigger each with his own imagination and level of understanding
7. Physical attributes of database objects
7.1. No constraints and referential integrity
7.2. Cryptic and uninformative column/table names
7.3. Many columns like notes without any classifiers. Each user enters data in his own style
7.4. Character data type instead of date, number
7.5. Chaotic indexes
Tom its wierd that I am not able to register this session even though it shows available the OOW schedule builder does not display an error message nor does it allow me to register for this session S281206 - Database Worst Practices , if you have some tricks I will be more than happy to try it. thanks
the other talk is a "keynote", maybe they do not register for keynotes (I've no idea what the process is personally)
POST A COMMENT
<< Home