I'm usually that way (the 90% 'calm' and ok). It was nice to see it spelled out though.
I'm usually that way (the 90% 'calm' and ok). It was nice to see it spelled out though.
Real email, received today from another Oracle person - asking me a question:
A developer wants to represent all data types as Varchar2(2000) so that they won't have to change sizes in the future. This includes Numbers and Dates. The DBA wants to know if there are any management, performance, or indexing implications.
Ouch (wonder why they stopped at 2000 with the varchar2?) That hurts doesn't it...
Here was my response:
They are suggesting one of the worst ideas known to human data processing people. I cannot over state how BAD AN IDEA THIS IS ENTIRELY.
Funny thing - dates are 7 bytes, you cannot "under" or "over" size them (you never CHANGE their size), timestamps are fixed sizes as well - 7, 11, or 13 bytes depending on fractional seconds and timezone needs.
Funny thing part two: if someone defines a Number(2), we can "alter table t modify X number(3)" to increase the size. But, if someone was to MAX OUT all numbers - we CANNOT shrink them!!!
Funny thing part three: same with varchar2 - we can make them bigger anytime we need - immediately, absolutely. We cannot however fix the oversized varchar2 in the future when the column has data - we can GROW, we cannot SHRINK
Funny thing part four: I spend 20 minutes on this topic - using the right datatype - in all of my seminars, over and over and over again.
Here is a cut and paste from my book Effective Oracle by Design.
Use the Correct Datatype
Using the correct datatype seems like common sense, but virtually every system I look at does one of the following:
• Uses a string to store dates or times
• Uses a string to store numbers
• Uses VARCHAR2(4000) to store all strings.
• Uses CHAR(2000) to store all strings, wasting tons of space and forcing
the use of a lot of trim function calls
• Puts text in a BLOB (raw) type
I have a very simple rule: Put dates in dates, numbers in numbers, and strings in strings. Never use a datatype to store something other than what it was designed for, and use the most specific type possible. Furthermore, only compare dates to dates, strings to strings, and numbers to numbers. When dates and numbers are stored in strings, or stored using inappropriate lengths, your system suffers:
• You lose the edit upon insertion to the database, verifying that your dates are actual dates and numbers are valid numbers.
• You lose performance.
• You potentially increase storage needs.
• You definitely decrease data integrity.
How many of you know what ORA-01722 or ORA-01858 errors are off the top of your head? I bet many of you do, because they are so prevalent in systems where numbers are stored in strings (ORA-01722: invalid number) and dates in strings (ORA-01858: a non-numeric character was found where a numeric was expected).
How Data Integrity Decreases
Using an incorrect datatype is wrong for many reasons, but the first and foremost is data integrity. Systems that use strings for dates or numbers will have some records with dates that are not valid and numbers that are not numbers. It is just the nature of the game here. If you permit any string in your date field, at some point, you will get dirty data in there.
Without data-integrity rules in place, the integrity of your data is questionable. I’ve needed to write the functions to convert strings to dates but return NULL when the date won’t convert. I’ve also needed to try one of five date formats to see if I can get the date to convert. Can you look at 01/02/03 and tell what date that is? Is that yy/mm/dd, dd/mm/yy, or something else?
How Performance Suffers
Beyond the obvious data-integrity issues associated with incorrect datatypes, there are other subtle issues. To demonstrate, we’ll use an example of a table with two date columns. One will be stored in a string using YYYYMMDD and the other as a DATE type. We will index these values and analyze the tables completely.
ops$tkyte@ORA920> create table t
3 select to_char( to_date('01-jan-1995','dd-mon-yyyy')+rownum, 'yyyymmdd' ) str_date,
4 to_date('01-jan-1995','dd-mon-yyyy')+rownum date_date
5 from all_objects
ops$tkyte@ORA920> create index t_str_date_idx on t(str_date);
ops$tkyte@ORA920> create index t_date_date_idx on t(date_date);
ops$tkyte@ORA920> exec dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all indexed columns size 254', cascade=> true );
Now, let’s see what happens when we query this table using the string date column and the real date column. Pay close attention to the Cost and Card= component of the plan:
ops$tkyte@ORA920> set autotrace on explain
ops$tkyte@ORA920> select * from t
2 where str_date between '20001231' and '20010101';
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=406 Bytes=6090)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=5 Card=406 Bytes=6090)
2 1 INDEX (RANGE SCAN) OF 'T_STR_DATE_IDX' (NON-UNIQUE)
ops$tkyte@ORA920> select * from t where date_date between to_date('20001231','yyyymmdd') and to_date('20010101','yyyymmdd');
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=15)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=15)
2 1 INDEX (RANGE SCAN) OF 'T_DATE_DATE_IDX' (NON-UNIQUE)
So, what happened there? Well, the optimizer understands VARCHAR2 types and it understands DATE types. The optimizer knows that between the two DATE items December 31, 2000, and January 1, 2001, there is only one day. The optimizer also thinks that between the two string items ‘20001231’ and ‘20010101’ there are a whole bunch of values. The cardinality is thrown off.
But, so what? What do we care if the cardinality is wrong? It won’t affect our output—the answer. That is correct, but it could have some impact on our overall performance. Consider a different query against the same data, asking for effectively the same result set:
ops$tkyte@ORA920> select * from t
2 where str_date between '20001231' and '20060101';
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=2034 Bytes=30510)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=12 Card=2034 Bytes=30510)
ops$tkyte@ORA920> select * from t where date_date between to_date('20001231','yyyymmdd') and to_date('20060101','yyyymmdd');
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1823 Bytes=27345)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=12 Card=1823 Bytes=27345)
2 1 INDEX (RANGE SCAN) OF 'T_DATE_DATE_IDX'
(NON-UNIQUE) (Cost=6 Card=1823)
Note: As discussed in Chapter 6, different database parameter settings will influence the cost of various operations. You may need to increase the range of dates to see the same effect as shown in this example, but it will happen at some threshold.
This time, the fact that we hid a date in a string has a serious side effect. Our query plan has changed. We are now full-scanning for the string date but index-range scanning for the DATE type date. So, besides the fact that there is nothing stopping someone from inserting 20009950 as a date value into our field, the use of a string has withheld valuable information from the database. We lose all around.
How You Might Increase Your Storage Requirements
In addition to using the proper base datatype such as number, date, or string, you should also use the most specific type you can. For example, use VARCHAR2(30) for a field that is up to 30 characters in length; do not use VARCHAR2(4000).
“I work with a modelers group. My modeler would like to define every VARCHAR2 field with
the maximum length, which means that a table with 20 VARCHAR2 fields will all be defined with a maximum of 2000 or 4000 bytes. I tried to talk to him about the reasons we identify data with correct lengths and names in order to understand what we have in our database. He told me that it doesn’t matter, since Oracle just stores the length, etc., and there is no overhead. I don’t believe this is true, but have been jumping between so many databases that I cannot find a document on the internals of Oracle storage. Can you help me out here with this question?”
My gut response was, “This is your data modeler, my goodness!” They are the ones who are supposed to be telling you that it is vital to use the appropriate length when defining fields! That is their job. Let’s just forget about things like storage for a minute, why don’t we ask him:
• What is going to happen when users pull this up in a query tool that formats each field based on the width of the column in the database? They’ll see one column and need to scroll way over to see the second, the third, and so on.
• Say the code prepares a query that selects ten columns that are VARCHAR2. The developers, for performance, would like to array fetch (very important). They would like to array fetch say 100 rows (very typical). So, you have 4,000 10 100 = almost 4MB of RAM the developers must allocate! Now, consider if that were ten VARCHAR2(80) fields (it’s probably much smaller than that). That’s about 78KB. Ask the data modeler how much RAM he is willing to kick in for this system.
• Now, the developers start to build a data-entry screen to put data into the database. Wow, that code field can be 4,000 characters long and that first name can be 4,000 characters long. How the heck is anyone going to know what sort of data can really go in there?
Tell your data modeler group members that they need to consider the length as a constraint. Just as they use primary and foreign keys, they should use the proper and correct length on fields. You can always expand a field via a command like alter table t modify c varchar2(bigger_number). There is no reason to use the maximum length everywhere. It will hurt the applications you develop, because they will mistakenly allocate many megabytes of RAM. Just think of the array fetch example with an application server. Now, it’s not just 4MB; it’s 4MB number of connections. You are talking some real memory here for a single query, and you’ll be doing a lot of them at the same time.
End Ask Tom
A CHAR(2000) will consume 2,000 bytes of storage whether you put in the letter a, the string ‘hello world’, or 2,000 characters. A CHAR is always blank-padded. Additionally, are you thinking about using an occasional index in your system? If so, beware of storage-related problems. Consider the following:
tkyte@ORA817.US.ORACLE.COM> create table t ( a varchar2(4000), b varchar2(4000));
tkyte@ORA817.US.ORACLE.COM> create index t_idx on t(a);
create index t_idx on t(a)
ERROR at line 1:
ORA-01450: maximum key length (3218) exceeded
NOTE: In Oracle9i, the maximum key length is larger – but the restriction still exists. For example, an index on T(a,b) would raise: ORA-01450: maximum key length (6398) exceeded in Oracle9i.
My system has an 8KB block size. I would need to use at least a 16KB block size to index a single column, but even then, if I tried to create a concatenated index on T(A,B), it would fail there!
The same holds true for your numbers and the new Oracle9i TIMESTAMP datatypes: When appropriate, use scales and precisions on those fields in order to better define your data integrity and to give applications that much more information about the data itself.
In short, never be tempted to use anything other than a DATE or TIMESTAMP datatype to hold a date value, and never be tempted to use a VARCHAR2 to hold a number. Use the appropriate and correct type for each to ensure maximum performance, and more important, to protect your data integrity.
I really work my email hard - I hate a scrollbar and when it appears on my inbox, it is time to fix it. Right now I have six things in there
By the end of Monday - the 2nd, 3rd, 4th, 5th and 6th should be deleted... Hopefully the same will be said about the new lot of email that comes in. I have a very simple set of rules:
It is that last item that causes all of the problems - when there are too many "to do" items in my inbox. That is why I avoid the scroll bar - if it appears, I have more than 15-20 items in there and that is just too many to-do's to do. So, I keep it small. If something slips off the end - I might scroll down to it and reply to it (usually reply to it again actually, I replied the first time saying something like "I got your email and will pay attention to it soon") saying I don't know if I'll be able to get to this.
Anyway, I stumbled on this more comprehensive list of email "stuff".
#5 is of course my all time favorite. "they may give the impression of childishness and illiteracy". Indeed!
Many good ones in there, a few "eh, I'm not sure about that". There are a lot of them to read - but the best are mostly up front.
The Rome seminar ended yesterday - and based on the feedback, it went well. I was worried with the translation that was happening but based on comments received on the blog and the feedback forms - it worked out well.
Spent today walking about - I did not want to fly back today, the Wednesday before Thanksgiving. I'd rather wait for all of the "civilian" travelers to get out of the airports first :)
A friend of mine, Marco Adelfio (he is one of the two developers working on the 'beta asktom'), recommended I walk up to Gianicolo hill - which is what I decided to start with. So it was off to the River Tevere again, as you can tell, the weather was not the best:
It was nice that so many things were so close to my hotel, I haven't been in a car since Saturday when I arrived. I made it to the stairs that would lead me up to the top of the hill:
and the view was worth the climb. It wasn't optimal for taking pictures, a little too dark, but I got a couple of good shots:
After walking around up there, I set off for the Pyramid of Cestuis. It rained like crazy on the way there but the promise of clearing skies can be seen in the background of this shot:
Then, it was off to the Terme di Caracalla ruins. On the way there, I spied this building on a hill - you have to like the architecture around here, every building is pretty cool in its own right - but some just stand out:
Any way, Terme di Caracalla is a highly recommended place to go if you ask me. Yes, it cost an entrance fee - but it was worth it. I like ruins and these were very nice - the grounds were in excellent condition, it was a really nice place:
I really liked the way this shot came out, a bit "ghostly" looking with the fuzzed out windows from the sun. As you can tell - by now, the weather was perfect:
Then it was off to the S. Giovanni square for a look around and then back to the Coliseum for a last view:
And then back to the hotel. A nice big 5 plus hour circle around Rome. Feet hurt, shoes are wet, but I saw a lot of things I hadn't seen before. Back home tomorrow...
I've bugged Jonathan Lewis for a long time to do a blog. Even if it was just a blog to point to new articles he wrote on his site so we could easily subscribe to them.
He finally took it from me and others to heart and well - the result is really good. I suggest you subscribe. I have not been disappointed yet, take this recent example - something you don't see every day, but it'll be filed in the back of my head. If it comes up again with someone I'm working with - I'll appear to be "magic".
But it is just something I learned from the forums...
Something I talk about in my seminars - I spend a great amount of time in my "SQL Techniques" talk about how participating in the community at large is what really made me have the amount of Oracle knowledge I have today. Examples of people "one-up-ing me" on asktom (I really enjoy that), examples of what I've learned recently about Oracle by reading others works.
Really nice blog he's got going there. Check it out.
I liked this entry "How Long...". But only because I've had so many people give me queries and say "this takes too long". When I look at the
b) underlying data
c) the query again
I can only answer - "what are your expectations, are they even reasonable - this is a big question against a ton of data" sometimes.
Many times people don't think about "how long" should it really take. And I like the way he just threw it out there as a question - sans answer.
Today was the first day of my seminar in Rome. It started off bad and sort of went down from there :) Only kidding, everyone worked together hard to make it work.
When I arrived, I first asked to see the printed material - in Copenhagen they missed printing one of the sections. Well in Rome, we missed all of them! We arranged for a quick printing (and they were fast, by mid-morning that was corrected).
Then there was the sound system. It was being simultaneously translated (how UN like - little box off to the side with the translation team). They could not hear me however - lots of "echo". Not only that - but microphone after microphone would squeal, squeak and otherwise misbehave. They blamed my voice! Oh well - I think I used seven different microphones today - made a joke out of it.
Having the simultaneous translation was a bit strange. Apparently Italian takes 40% longer to say the same thing as you would in English. So, I was to speak slowly. I tried, but it was hard. Imagine adding 40% to the runtime of your long batch job - it'll never finish in the batch window. Well, I have two days of stuff to talk about. And at full speed, it takes - two days. I'll try not to miss the window - but who knows (I am exactly on schedule right now - if I skip one small section...)
Watching the translators was interesting. One of them just sat there and talked - arms at their side. The other one - she talked just like me - her arms were waving, hands gesturing - even though she wasn't addressing the audience. I talk with my arms - a lot, she was following that right along.
Also, the timing of jokes was way off. I would say something - and very much expect a chuckle. Nothing, nothing, nothing and then a bit of laughter. They were running behind by a second or two - very nerve wracking at first. But then I realized they were working very hard to either listen in English and translate themselves or listen to the translation where the words were coming out really really fast (that 40% thing).
By the middle of the day, I think we finally fell into a pattern - it started working.
But, I am more tired than usual. The microphone problems really caused some stress up there - any sudden movement and "squeal". They would switch a lapel microphone with a handheld (ever tried to type standing up with the keyboard on a low table, holding something in one hand?).
Tomorrow the sound people promise a better day. I'm getting a Madonna microphone - wrapped about the head. I can hardly wait... :)
It is fun still.
The Internet connection in my hotel is very limited. Wireless only in the lounge. I wonder what the Italian guys in the lounge are thinking of the guy in the corner sitting cross legged on the sofa without shoes and typing away is all about... I bought my six hours of online time, I'm going to use it.
I know precisely what he is talking about. Many of my friends - I've met in the "community". Many of the people I talk to every day - I've met in the "community".
One of my recurring themes "participate".
I was very much warmed by the reaction to Mark Rittman's unfortunate chain of events. The Oracle Community - wow, very nice feeling indeed. Those that participate make it all worthwhile - all being "all" really.
I've used the community in the past, present and will be in the future. Hope it keeps up :)
Spent the entire day walking - from 9am to 5pm. Got to see many of the main sites. The weather was perfect - you could not ask for a nicer day. About 70F (19C), sunny. Cool in the shade, warm in the sun - very nice.
I am staying in a hotel right off of the Piazza del Popolo and started there. Decided to walk down to the Piazza di Spagna - where the "Spanish Stairs" are:
Now, when I got there, this huge crowd was standing around - yelling, blocking traffic. Lots and lots of people with cameras. Wasn't sure what it was:
Turns out - that was where Tom Cruise and family was staying before/after his nuptials in the countryside last night. I did not stick around for a "sighting" - was time to move on. So I continued down the road a bit to the Fontana di Trevi. In the US we just don't have buildings like this:
My ultimate goal after that was the Quirinale - and after getting there, I spied this window:
And of course, the address of the house with the neat window was none other then:
Continuing on - the goal was now the Piazza Venezia. I started off towards it - walking along "normal Rome streets" - like this one:
Again, we don't really have anything like this back home. What I liked about Rome and many other European cities is that you can in fact walk most everywhere - and many people do. In most US cities, I would have had to of taken a cab here and there and everywhere. I finally arrived at the Piazza Venezia:
After climbing the stairs and walking all around it - I spied my next target, the coliseum:
so I started off towards it - lots of stuff to see everywhere. I really like that many times the streets are actually closed off - and everyone just spills into them. No cars = no smell = no noise (and there is lots of noise in general in Rome, those little motor bikes are noisy).
I had been to the coliseum before, in 2001 - but I never went in. This time I paid my fee and walked around:
And found the next place I was headed to:
After spending quite a bit of time walking around the ruins down there (I like ruins for some reason, just interesting to look at), I headed off to the Pantheon:
There, I checked email and saw my daughter Megan was online. I texted her - she is at Disney World with her brother this week. She was happy, yesterday they got to see "Fear Factor" being shot live. Something about people having to eat worms and have scorpions placed on their head. She thought it was pretty neat. It was nice to be able to chat with her for a couple of minutes (but I think I'd rather be in Rome then watching Fear Factor "live"...).
After the Pantheon, I was on my way be to the Tevere:
Which I walked along on my way back to the Piazza S. Pietro. I was there yesterday but wanted to visit again with the sunshine. It was worth it:
Then it was back towards the hotel - I was quite worn out by then. But on my way back, I spied a sidestreet full of people and walked down it. It led to the Via del Corso - which was full of shops (open on a Sunday - I was surprised). So, instead of going back to the hotel, I walked up and down the street:
We just don't have anything like that back home. The people had competely taken over the street. Any car that attempted to drive on it could go maybe 1-2 miles per hour. Lots of interesting people/things to look at. Almost like a big block party.
It was good. I have now 4 cities I really like walking around:
if you like walking and seeing some pretty cool sites - I do recommend them all.
Week before last, I was in Edmonton, Montreal and Quebec City in Canada. In Edmonton I got to talk about the upcoming 11g release of the database and in the other two cities I did my database "worst practices" and "instrumentation 101" talks.
Once again, we did the Montreal and Quebec City talks in a movie theatre, just like last year. I'll say it again, that is the perfect venue for a talk - the screen is HUGE, the sound system is awesome, the sound proofing of the room is beyond compare, the chairs have cup holders and they are comfortable (maybe too comfortable - the sessions were in the morning and in Quebec City someone snored really loud once, we all had a good laugh at that).
Oh, by the way, when I was in Edmonton, the residents liked to tell me that they are the "most northern big city in Canada" meaning, they are as close to the North Pole as big cities go in that country. And you can tell - it was icy, snowing and dark already... First snow of the season.
Got to Rome around noontime on Saturday, the weather was cool and sometimes damp (showers). It was good walking weather actually and that is what I did. As the sun was going down, the sky with the clouds (that occasionally rained on me) made for some good shots:
The weather wasn't horrible or anything, very nice actually. The sun would come out and then hide for a while and every now and again a very small shower would happen. When the sun did come out, it was very nice - a bit warmer (in the mid 60's F, about 16-18C). Made for nice shots here and there:
Now, tomorrow is supposed to be sunny and 70F (20C), so hopefully lots more shots like this:
Are part of the future. Plan on hitting many of the sites starting in the morning and throughout the day. It has been about 5 years since I was last in Rome and way back then, I was not nearly as good a "tourist". I have a plan for what I want to see tomorrow and a camera in hand. Should be good.
Then it is back to work and then back to home.
Was a busy week. Started Sunday night flying across the ocean to Birmingham England to participate in part of the UKOUG. I got to do a keynote Tuesday morning (felt very tired at 9:30am on stage) and a pair of technical sessions - one on Tuesday night and one Wednesday afternoon. As always - a good show.
On Tuesday night I was talking with one of the UKOUG organizers and she was describing the stress she goes through year after year to pull it off. I can only imagine everything they have to remember. It was funny - because she told me a story about a dream she had - which explained an email I got from her. All of the other keynote speakers have historically wanted to have a rehearsal - I never have (and never will). She sent me an email a couple of days before the conference:
Please note your keynote will take place in Hall 1.
You will need to be on-site by 8:45am to get to Hall 1 before 9am to be
in place before the open the doors for the delegates.
Will you require a rehearsal?
I made fun of the rehearsal comment in my reply:
rehearsal? That would just ruin the surprise for me regarding what I'll be saying :) No need.
So, it was nice of her to ask - but not necessary. Turns out the reason she asked - she had a dream where I was yelling at her for not scheduling my rehearsal. Now that, that is stress - when your dreams start telling you what emails you need to send :) Not that I would yell at anyone that like... I just got a kick out of hearing that.
On Wednesday night I flew over to Copenhagen Denmark for a two day seminar on Thursday and Friday. Those are just tiring (another one coming up this Monday and Tuesday in Rome). 8 hours of doing stuff. But I still think the attendees have the hardest job - they have to stay awake! (well, they try hard to anyway). But even though they are tiring and take a lot of work to deliver - they are one of the more fun parts of my job, something I really enjoy doing.
Friday night - one of the guys from Miracle in Denmark took me out for a nice dinner. Afterwards, we joined Mogens for a couple of beers and whiskeys at a local bar. Stayed out way way way too late (I got to sleep for an entire 2 1/2 hours before having to get up to catch my flight Saturday morning). Fortunately, got to sleep on the 2 1/2 flight from Copenhagen to Rome Italy, which is where I am now...
Saw this license plate, brilliant. I asked Megs, my 11 year old daughter what she thought it meant. She did not get it:
XX3 & XY1
do you? Just perfect. She did get it when I said one word though... Any guesses as to the word?
The second paragraph points out why in such a clear way. "abc" means "this" or "that". No problems with that.
And what if the person grading the paper doesn't know "text-speak".
Higher education indeed. Stupid is one word for it.
Wonder if any of these were "staged" for the comedic effect.
Doubtful, unfortunately - I can imagine they are all real.
I was browsing Laurent Schneider's blog and he pointed me to co.mments - a way to monitor comments left on blogs, even if the blog does not support such a thing (like blogger doesn't). Looks very promising, that would always drive me crazy - when I left a comment, I'd usually want to see what others had to say about it later. This will let me do that easily now.
I don't read Joel on Software often, but this entry on measuring "software productivity" popped up and caught my attention. I know exactly what he means about "scamming the system". I was talking with a friend recently who works in an organization where metric based performance is practiced. The "best of the best" in the organization is chosen not by who sells the most, who delivers the best value, who is really good - but rather by who did the most "checklist items". The goal was to make performance management "entirely objective" - make some things worth "points" (like Joel talking about 'function points'). The end result - the best performers end at the bottom of this measurement process and the ones that do not contribute to much at the top. Wrong way to measure.
This slashdot thread caught my attention too (warning, 'strong' language is used). I do not agree with every sentiment expressed there (and the language wasn't really necessary to make the point) - but the gist I do agree with. That language/framework is really complex, too complex for many things.
And lastly, a nice peek at XML.
At Oracle Open World, I gave a "worst practices" talk. In this talk, I describe a series of "really bad things" people keeping doing and over and over again.
This week, I've been in Canada doing some customer seminars. I gave the talk in Montreal yesterday (November 9th) and today again in Quebec City. Well received both times - and made all of the more fun by having some supporting "evidence". Evidence that I do not make this stuff up. Here is an email I received on the morning of the 9th (I like it when that happens, something in the news/email/whatever ties directly into what I'm doing that day). This is reproduced with the senders consent (emphasis added by me):
Sorry about the unrequested email, but I couldn't resist...
I read your Worst Practices presentation the other day - Very nice, hit a bit close to home for comfort in many cases!
Then today I got an email from one of the contract "developers" our organization deals with, it describes a rewrite of a system that was rolled out a few years back. It was a bit experimental and was always problematic - architectural mess - stuff flying around in files between ftp sites and windows shares and in and out of databases. I (and my cohort DBA) kept asking "Why doesn't this just stay in a database and you query it from wherever".
... BUT .... It was developed shortly after one of our architect types had heard of XML, so XML had to be used, it wasn't really important what it was to be used for - it was just to be used... and so it was decreed, and it was made so, and it was good... well until the XXX "database" thing started crapping out every few days... but then some sys admin wrote a script to check and restart XXX every few minutes, and it was good again.... fast forward a few years.... decision is taken to rewrite and since our Oracle databases don't seem to crash every seventh minute, move the backend from XXX to Oracle....
Here is the "punch line" from the email describing the database aspects of the proposed system (slightly edited to remove reference to specific client):
"My current design for the Oracle-ized (Oracle 10g) version requires only a single Oracle table, which will have two columns: a pseudo key (simple varchar2) which will likely actually contain the path to a corresponding document in the WebDAV environment, and a document column of XMLType which will contain the xml for an individual "notice" within the [[snip]], plus an index on the pseudo key column."
Excellent - one table, with a key and XMLType column - the perfect system... Is this a a cut and paste off slide 21 of your Worst Practices ppt or what???
If I could make this stuff up I could quit my job and work in stand-up.
Indeed, requires only a single Oracle table, which will have two columns - that is a cut and paste of that slide!
I do not make this stuff up, it is real.
This was really insightful I thought..
Be careful who you learn from - indeed.
Sitting here in Montreal - on my way to Quebec. Almost didn't get the chance to get to Quebec since my travel agent changed my ticket and forgot to ticket this segment. Meaning, I've gone from Washington Dulles to Toronto, to Edmonton, to Calgary, to Montreal - have yet to go from Quebec back to Montreal to Washington Dulles - and all are OK except for this one segment.
I didn't even know you could mess up a ticket that way! Interesting. Anyway, after a pleasant 15 minutes at the desk trying to get a boarding pass (it was pleasant, they were trying to help), they sent me to the "ticketing line".
Slowest line invented in the history of mankind...
So, bored, I got on the phone with travel - they typed away and said "hmm, interesting, did not know that could happen" (I am not alone!). They said "hold while we talk to ticketing". What follows is 20 minutes of really bad music piped into my mobile phone, in line for "ticketing". They came back on and said "ok, check it, we got it fixed". I got to leave the line (dirty looks from others in the line - misery loves company).
So, here I am, waiting for the plane....
And I read this. Laughing out loud - I cannot agree more. The driver "tip" is the hardest of them all sometimes. Something needs to be done!
Tomorrow Quebec, Friday/Saturday at home with the kids. Then off to UKOUG. You should really all hit many time zones in a single week. Good for the brain...
Tomorrow, if I get time to type it in - a really funny story about my worst practices presentation and slide "21".
It was time again for a "friendly" game of poker
It had been a while since we all got together - but the are always fun. One of our last get together's was a couple of months ago, right on Aprils Fools day :). I had a lot of fun with that post - it generated more traffic to my blog on a weekend than ever happened before.
Alas, the result for me was about the same as last time however:
I actually came back a bit from that - did not get entirely wiped out. But took six hours doing it - all in good fun.
Kathy Sierra rarely disappoints with her postings and this one is no exception. I really liked this quote:
Where we used to prepare students for a "job for life", now we must prepare students to be jobless. We must prepare them to think fast, learn faster, and unlearn even faster ("yes, that drug was the appropriate way to treat the XYZ disease, but that was so last week. THIS week we now realize it'll kill you.")
"but that was so last week". I really liked that bit. At OOW I did a keynote entitled "Things you think you know". It is about how our preconceptions - prior (dated) experiences preclude us from doing things right sometimes. The knowledge we think we have needs re-adjusting (frequently). I've speculated that the more experience with Oracle you have, the worse off you may be.
Things change, all of the time. One example I use to talk about this is the "case of the unindexed foreign key".
Not everyone knows that in Oracle, if you have an unindexed foreign key and you UPDATE the parent tables primary key (bad idea) or DELETE from the parent table (this definitely happens in real life) a full table lock is taken on the child table.
Sounds straightforward - but things change over time.
In version 5.1.5c (my first version of Oracle) foreign key constraints (added word constraints on Nov 5th, of course foreign keys existed - there were no constraints) did not exist, no problem.
In version 6 of Oracle - which introduced the primary/foreign key syntax, having an unindexed foreign key would cause no locking/concurrency issues at all. The reason - in version 6, primary keys and foreign keys were just metadata in the dictionary. We didn't enforce them, they were syntactic sugar. No enforcement, no locking issues.
Starting in version 7.0 however, the integrity constraints were enforced and the rules of the game changed. A full table lock on the child table would be taken for the duration of the transaction if the child table had an unindexed foreign key AND you updated the parent tables primary key OR deleted from the parent table. So, unless you un-learn what you learned in version 6, you ran into some problems (and many people did, I remember hitting this for the first time - thought it had to be a bug, but of course - it was not).
The rules of the game stayed in place for a couple of releases, 7.0, 7.1, 7.2, 7.3, 8.0, 8.1.5, 8.1.6, 8.1.7 - and then they changed. Subtly - but they changed. Many people came to the conclusion that in 9i Release 1 - Oracle stopped taking the full table lock. This was not the case at all. Rather, Oracle changed the duration of this table lock. The child table is still definitely locked - but for the duration of time it takes to UPDATE the parent primary key OR DELETE a row from the parent table. The relevant bit in the documentation regarding this is:
Oracle no longer requires a share lock on unindexed foreign keys when doing an update or delete on the primary key. It still obtains the table-level share lock, but then releases it immediately after obtaining it. If multiple primary keys are update or deleted, the lock is obtained and released once for each row.
Many people glanced at this and read the first sentence, ran a quick test and came to the conclusion that Oracle doesn't lock the child table anymore (did not read in depth). The reason their test "worked" and seemed to show that unindexed foreign keys didn't lock the table anymore was due to the way the test was written which usually went like this:
create table p ( x int primary key );
create table c ( x references p );
insert into p values (1);
insert into c values (1);
update p set x = x;
insert into c values ( 1 );
Now, in prior releases (7.0 through 8.1.7) that last insert in Session2 would have blocked since session 1 took a full table lock on C when the update to P happened and kept the lock until you committed. Now, in 9i, the table lock was held for the duration of the UPDATE statement only! So, Session2 would not block anymore. We have to have a different test to see the locking, something like this:
create table p ( x int primary key );
create table c ( x references p );
insert into p values (1);
insert into p values (2);
insert into c values (1);
insert into c values ( 1 );
delete from p where x = 2;
Session1 is now blocked (as will be any session that now tries to modify C and hasn't already modified C in their transaction!)
It ain't the things you don't know that get you in trouble, it is the things you know that just ain't so (or ain't so anymore, or ain't so ALL OF THE TIME - eg: rules of thumb) that get you in trouble.
I get lots of email (that I don't ask for...). Just yesterday for example I got a 2172kb (yeah, over 2mb) email from a DBA who thought I'd want to see a couple of their statspack reports to help them tune their system. I was less than impressed with that (when you travel, your internet connection is sometimes fast and sometimes not fast - regardless - sending someone you've never communicated with before a 2.1mb email.... nothing compressed either!)
Anyway, the one I got this morning was a lot "smaller" (2kb) and was from someone wanting to get some of their technical papers published. They didn't know where or how to send them for consideration.
I didn't either truth be told, but I asked around - and as it turns out, there is a process. You can go to this Oracle Magazine page and submit your ideas there. The entire process, expectations, and so on are spelled out. Just thought I would mention it - in case you have some papers lying about you'd like to be considered for publication.
Darn, should have read that last paragraph on that page myself before I started writing for them :)