Monday, April 25, 2005

The Birth of Asktom

Next blog should be on "how AskTom was born" ;)

I'l like to thank Kelli Wiseth and the Oracle magazine staff of 2000 for getting it all going in the beginning.

The genesis of Asktom is this usenet posting from 1999. Someone posted the concept of updating the Oracle data dictionary directly – something no one, expert, professional, newbie, whatever should suggest under any circumstances. Something goes horribly wrong and support tells you to do it – OK. But for any other reason, sorry, but no. The thread on usenet was asking about how to rename a column, before that feature was introduced (8i was the current release at the time of that posting, rename column was 9i).

There was a simple answer (two of them). One was the answer I always gave – create a view, this is what views are for. You can reorder columns, you can rename them, you can do whatever you want – all with a view. The other was a new idea. There is a drop column – you can add new column, update it and null out the old, drop the old. (Neat thing, if you goto the thread view by clicking on the thread title, you can see that Jonathan Lewis and I gave exactly the same answers, always made me feel good to see that).

Well, someone followed up with “what do you think about updating sys.col$?”. I, definitely having an opinion on this, said “I think it is a hugely *BAD* terrible idea. Words cannot describe how bad it could be.”. In an offline talk – I discovered the concept for the idea came from…. Oracle.com – Oracle.com/oramag to be exact. It was in a user contributed ‘tip’. I got in touch with the editor at the time, Kelli Wiseth (she left Oracle for a while and came back, left again... See this for an ongoing example of her work). Turned out they had no one vetting the material supplied, they just assumed it was “ok”. Given that I definitely had some feelings about that – they asked me if I would mind doing that. I agreed. I would accept or reject the tips for each print copy of the magazine and the online edition.

Over the next couple of weeks/months, it came to light that they got questions in addition to tips from readers all over the world. They had no method of answering the questions and couldn’t really do anything with them. Kelli asked me if I would like to take a peek at some and the concept of Asktom was born in 2000. The very first question ever asked and answered is available here. As you can see the format has changed a little over time, but the concept is the same.

In conjunction with the print column, we took the Asktom online in February/March of that year as well. It started as a perl application running on OTN that used email. A reader would come to the form, type in a question, hit submit and it would end up in my inbox. That lasted about 6 weeks. When I started coming in to work and having 40 or more questions in my email – that concept ended. I had no way to control the influx of questions, I had no way to turn it off when I didn’t have the time.

So, that was when the machine ‘asktom.oracle.com’ (also known back than as osi.oracle.com, OSI stood for Oracle Service Industries, the name of the division I worked in. I ran the web servers and databases for that divisions internal and external systems.) was born. I wrote by hand Asktom "version 1.0". It was a “black and blue” color scheme (wish I had kept a static page or two, the wayback machine on the internet archives doesn’t have a copy from early 2000) and had about 4 or 5 pages. I used PL/SQL (of course) and at the time “owarepl” , a pre-cursor to both webdb’s lightweight listener and mod_plsql.

Shortly thereafter, the people that originally wrote webdb were off doing a new thing – the thing that became HTML DB. When it was almost ready for prime time (way before we would even think about letting it outside of Oracle), Joel Kallman offered to prove that it could work by rewriting asktom in this tool. Since I had all of the API’s done – the API’s to save a question, update a question, ask for more information from the person submitting the question and so on – this would be a test of the pre-HTML DB environment to build a user interface and handle all of the security, navigation and so on. We blossomed from basically 5 pages to over 40 today simply because of this switch. There is no way I would have by hand coded 40+ pages – there would be features we would not have without HTML DB.

In the early days, I used to get 5,000 or so views a week. Today I get about 80,000 to 120,000 views a day from about 15,000 unique IP addresses per day. In the last month, well over 2,000,000 page views from over 100,000 unique IP addresses. It is hard to say how many people that 100,000 represents given that some people use more than one IP address (dhcp at work/home) and firewalls make large communities of users look like the same IP.

If you are interested, here are my hit counts for the last 17 months


TRUNC(TIM COUNT(*) COUNT(DISTINCTIP_ADDRESS)
--------- ---------- -------------------------
01-DEC-03 806320 32192
01-JAN-04 884535 35273
01-FEB-04 968818 34761
01-MAR-04 1242763 60635
01-APR-04 1322224 68658
01-MAY-04 1213422 72447
01-JUN-04 1597191 92343
01-JUL-04 1453072 79602
01-AUG-04 1544078 84743
01-SEP-04 1870786 101935
01-OCT-04 1752753 104764
01-NOV-04 2608425 104638
01-DEC-04 1749504 112242
01-JAN-05 2190787 118164
01-FEB-05 2499986 130210
01-MAR-05 2126486 134406
01-APR-05 1691208 115697


So that's it, that is the story behind the story. I'm still violently opposed to anyone suggesting to update the dictionary without support sitting there with them for any reason. I compare doing so to deliberately putting a virus on your machine. It might not hurt you today, but maybe a year from now when you upgrade and the dictionary corruption affects you for the first time... Whenever I see such a "tip", I go way out of my way to try and get rid of it, erase it. I've spent many hours getting rid of said "tips". They keep popping up in the strangest places.
POST A COMMENT

33 Comments:

Blogger Bis said....

Thanks a zillion to the guy, who posted that by updating the SYS.COL$ tables, that asktom was born.After all the guys shooting silver bullets at the databases did have something good to give to Oracle users and we know what a gift it was to have asktom for our reference.

Mon Apr 25, 08:11:00 AM EDT  

Anonymous Anonymous said....

Nice to know the history

Mon Apr 25, 08:26:00 AM EDT  

Blogger Alberto Dell'Era said....

So, if a lesson has to be taken from this (hi)story, is that AskTom has been the unplanned son of a lot of coincidences ...

Or not - since it appears to me that you are a very logic-driven human, had you planned/sketched to build [something like] asktom since 1996 instead, and just sought the opportunities to make it real, adapting the plan to the circumstances ?

Ie have you acted similarly to Archer, who accepts and seeks any adventure opportunity that He finds in the Galaxy, or to T'Pol, who calculates any move like a chess-player ?

Mon Apr 25, 10:54:00 AM EDT  

Blogger Thomas Kyte said....

So, if a lesson has to be taken from this

I'm Archer for sure. I've never really had any master plan. Opportunities pop up and you either take them or not.


If there hadn't been a really cool program being started in 1990 at GRC...

If I wasn't blocked from going to that program (it was going to be in Ada using Ingress on SCO - very cutting edge...)...

I wouldn't have volunteered to work on a project in New Jersey (I had a 161 mile commute, left on sunday night, came back friday night for 2 years)...

I wouldn't have learned more about databases than I ever could have, from some really knowledgable people on that project (heterogenous data integration)....

I wouldn't have met the Oracle guys....

They wouldn't have hired me in 1993 (putting me back in Virginia).....

And I wouldn't be here.


If I had had a different manager who didn't want to keep me on a project in 1990 -- my life would have been very very different indeed. And one of the reasons why I won't hesistate to help someone in my group find a better position for themselves in Oracle in a heartbeat. I'd rather them be happy in Oracle on a different floor than looking across the street.

Mon Apr 25, 11:16:00 AM EDT  

Blogger Alberto Dell'Era said....

Many thanks - this Blog is turning out to be interesting for many different reasons ;)

Mon Apr 25, 11:46:00 AM EDT  

Anonymous Anonymous said....

WFWFASFASFSAF

Mon Apr 25, 12:50:00 PM EDT  

Blogger Robert said....

Tom,

Very much enjoying the change of pace of reading your blog on various topics.

Regards,

Robert.

Mon Apr 25, 01:27:00 PM EDT  

Blogger Tarry said....

AskTom is indeed a treat for all oracle users.

Mon Apr 25, 03:21:00 PM EDT  

Anonymous Bernice said....

I am glad how circumstances in life have led you to where you are today. Asktom and your books are the first places I visit when I have a question. I have learned more about oracle in the past year since I discovered these resources than my first 2.5 yrs working with Oracle. You are an excellent teacher. Please continue to share your knowlege.

Mon Apr 25, 05:46:00 PM EDT  

Blogger P said....

Nice one!

Mon Apr 25, 08:30:00 PM EDT  

Anonymous Todor Botev said....

Tom,

Enjoying your Blog more and more.

I think it would be very interesting if you could tell us from time to time war stories from your professional life. What specific problems do you find at the customer sites and how do you approach them. Not only the technical side of it but overall - how do the people at the customer site meet and support you etc.

The sales side of your work is also interesting for me - how do you help convinsing the customers that Oracle is the right choise. What kinds of questions do come to you?

Of course it should be in a way that nobody (customer or its stuff) will be compromised.

Tue Apr 26, 03:49:00 AM EDT  

Blogger Thomas Kyte said....

I think it would be very interesting if you could tell us from time to time war stories

Well, I use those on asktom and in my books -- with care. They are dicey, you have to be careful.

I remember once, I was going to present to a user group. As I look out in the audience, I spy a familar face. It was slide 14 sitting right there in the front row -- how "not" to do something. I could not pull it, they had hard copy. I had to go down and tell them what was going to happen, they would recognize themselves but no one else would. They were a really good sport.

I've had others that were not such good sports. Even though they would be the only ones to recognize themselves from the story -- they didn't want to be reminded.

So, I generally try to reproduce the effect. If I was working with someone having a hard parse issue -- I don't need to reveal to anyone the details. I just have to reproduce the issue, measure it and say "this is the signature you are looking for".

War stories make great headlines. They are real "grabbers". But they come off wrong too. If you have to make up data/numbers/conditions to hide identities -- you open yourself to being totally wrong (the story you fabricate might not have the right details for the solution proposed. No one wins, you look "not smart" to people who know and you "mislead" people who don't know the right answer)

So, the war stories are few and far between. This database stuff can be looked at in a clinical and technical fashion.

Besides, too many war stories and you start sounding superior. "I was with these guys last week, let me tell you what stupid things they were doing...."

I'll phrase it as "I've seen this happen. It happens when you do this [demo]. This is how to see it is happening [measurement]. This is why it is a bad thing to have this happen. This is how to avoid having it happen".

In fact, as I'm going through the second edition of Expert On e on One, I'm trying to use war stories wherein I or someone I work with was directly the cause of the problem (we too make mistakes every day, every day, every single day).

So, I guess my point is -- war stories sell papers. They can backfire. When "made up", they can be humiliating to the author (no pile of excuses can excuse made up 'facts' that don't add up). Clinical technical presentations of this stuff can be made.

Most of my sales calls are round tables calls these days. I like that a lot more than death by powerpoint. Give me a room full of naysayers, skeptics and a whiteboard. That is when I work best. Throw out your technical concerns and lets talk them through. It works very well. Having a good working knowledge of the other products out there, coupled with an in depth knowledge of how Oracle works makes it so I can pretty much work my way out of any hole they've dug for me. Of course, there are people that just think they won't be turned for any reason -- they are the most challenging and it is always fun to turn them from time to time (doesn't always happen but when it does :)

Tue Apr 26, 08:04:00 AM EDT  

Anonymous Matthias Rogel said....

funny, one of my first postings
in oracle-newsgroups was also
about editing dictionary tables
http://groups.google.com/groups?hl=de&lr=lang_de|lang_en&threadm=3Vx2a.9227%24V6.12081%40news.indigo.ie
however, there is no site
askmatthias 8-)

Thank God !

Tue Apr 26, 10:53:00 AM EDT  

Blogger Thomas Kyte said....

funny, one of my first postings

wonder if the guy in the thread knows that there is more than one DML statement with DBMS_JOB.REMOVE.

he said:

... In fact, tracing dbms_job.remove using log miner reveals that the remove, procedure does exactly this, as well (deletes one row from the job$ table)and nothing else. ...

I would not have thought to use log miner, rather just use SQL_TRACE and you can see that is not true.

update the data dictionary -- never, never, never.

DELETE FROM JOB$ WHERE JOB = :B1

UPDATE SYS.SNAP$ SET AUTO_FUN = 'null' WHERE (SOWNER,VNAME) IN (SELECT RC.OWNER, RC.NAME FROM SYS.RGCHILD$ RC, SYS.RGROUP$ R WHERE R.JOB = :B1 AND RC.REFGROUP = R.REFGROUP) AND INSTSITE = 0


that was 10gR1, maybe it changed from whatever release he had (but that's the point isn't it -- DON'T DO IT)

Tue Apr 26, 03:57:00 PM EDT  

Anonymous Bill "Shrek" Thater said....

even if i didn't AskTom at RMOUG, it's good to see you blogging. and it's a REAL good thing there's no AskShrek.;-)

Thu Apr 28, 10:40:00 AM EDT  

Anonymous Anonymous said....

Ever come across Gembase from Ross Systems? It's supposed to be a cross-system application development too. Updates the data dictionary directly. You'd love it. Not!

Thu Apr 28, 06:08:00 PM EDT  

Blogger Sami said....

Dear Tom,

It is very interesting to know the story behind the story. I always admire you and you are the teacher for most of the oracle professionals.

Thanks for all your help through asktom. Keep up your good work.

Thu Apr 28, 07:29:00 PM EDT  

Anonymous Anonymous said....

Wow, this is really interesting. why? because i know the guy personally who had written that tip in oramag about dropping the column. He is my friend. After it was published, he told me to read about the current issue of oramag and about his "published" material. After I read it, I called him and we had argument over the article. My point was that this cannot be supported method, but his point was why oracle would have published his article then. My other point to him was that by updating the dictionary, how was he actually deleting the space used by the column from the database blocks.

Fri Apr 29, 11:26:00 PM EDT  

Anonymous Anonymous said....

We had to update the data dictionary once in order to meet a client requirement. It was not on a production database. It was a staging instance for data publication. Our backup was another database.

This was in version 8i. We have to publish about 25 Gigabytes of data a day to production. The only sensible way we could think to do this was with transportable tablespaces.

We had another requirement where we could have absolutely no downtime no matter what.

So we followed the standard design where we have 2 users and 2 tablespaces in production. One is active and the other is inactive. The active one has synonyms pointing to it.

Our problem was that we could not rename tablespaces. We were using the same tablespace in our staging environment everyday. If we dropped the production tablespace and then imported we violated the iron clad rule from upper management and the client(there was no negotiating, the client wouldn't write the check if they did not get this).

We tried altering the export metadata file for the transportable tablespace to rename the tablespace, but since this was binary it failed.

Our only option was to update ts$, rename the tablespace, do a transportable tablespace, publish to production. Name the tablespace back in the staging instances.

We ran dbverify on the transported tablespace and backed it up to another database. If we lost our staging database we use our backup database.

We had some odd problems occasionally. Such as the data dictionary info in memory was not the same as in the datafiles. Flushing the shared pool fixed this most of the time. A few times we had to bounce the instance.

This has been in production for 2.5 years and has been migrated to 9i. I am not on the project anymore. I know Oracle now has a rename tablespace option in 10g.

I ran this by Steve Adams and he said we shouldn't have any problems.

Ryan

Sun May 01, 02:45:00 PM EDT  

Blogger Thomas Kyte said....

We had to update the data dictionary

sorry but I steadfastly and wholeheartedly disagree

The customer had a rule that you could not implement a sound system? Why do they care what the tablespaces are named? What purpose could that have.

Do you know what this will do to you with patch X.Y.Z? With upgrade to version A? What might already be broken -- that you just haven't hit yet?

Sorry, but that the client wouldn't sign a check doesn't cut it -- the client is dangerous.

I can say in 10g, there are three updates. Who knows how many 9i might need or that 8i needs to be "correct".

Nope - sorry, not a good story to me. Client was wrong, answer should have been "ok client, you can get Oracle support to sign off and support this....."

Updating the dictionary, nope. (and how can you say this wasn't production? it certainly sounds like it)

Sorry -- this is something I am firmly and totally on a single side of a very high fence on. No if and or buts about it.

Sun May 01, 03:39:00 PM EDT  

Anonymous Anonymous said....

The technical staff didn't have access to the client. This came back to us from management. I can't say no to my boss or for that matter managers several levels above him.

I didn't explain the problem properly. Let me start from the beginning. We had to publish 25 GBs of data a day to a Hybrid OLAP/OLTP system that has an always on requirement with no downtime.

The 25 GB publication representated part of the data in the production system. So our solution was to have two tablespaces in production and two uses that contained our published data.

User 1 owned tablespace 1
User 2 owned tablespace 2.

At any given time one of the two users was 'active'. So our synonyms pointed to that user.

We have one tablespace in our staging database. So if we publish the same tablespace every day, we have a problem. We cannot have the same tablespace name in a database at the same time.

We could not drop the previous tablespace from production because it violated the contract.

Only option to make this work was to change the name of the tablespace in our staging instance(which the client never saw and we could afford to lose). Then we publish to production with a new tablespace name.

There was no other option. Those were the rules set out by management. How many people are in a position to tell a client no? If I don't do it, someone else will.

Ryan

Sun May 01, 11:39:00 PM EDT  

Blogger Thomas Kyte said....

The technical staff didn't have access to the client.

Your only answer is "the software doesn't to that"

IF you have 2 users (user1, user2)

then you should have two tablespaces (ts1, ts2)

and only one is active (say ts1 is) so you can drop ts2 and attach it.

tomorrow ts2 will be active and you have ts1 to import.

heck, you could have used a rolling tablespace name.


I don't see why and haven't seen any compelling issues that would have even made me consider this in the back of my head.

Mon May 02, 07:22:00 AM EDT  

Anonymous Anonymous said....

How do you use a rolling tablespace name if all your data manipulation is done in one tablespace? The only way to do that would have been to do the data manipulation twice(or copy over what you did) to two different tablespaces.

Our hardware cluster was on the same cluster as development. We tried this during the day and it slowed down the system too much that we couldn't develop. The batch scripts ran all night and would not be delivered until 7AM.

We basically got a set of files in from a data warehouse group which we would load into the staging environment and manipulate. However, they all wouldn't come in at the same time. Some would come in at 10 PM and some at 3AM. So we basically needed the whole night for processing.

We did this with an asynchronous design using Korn Shell. Basically a set of queues. If one thing did not work, someone got beeped at 2 AM. It was fairly complex.

Sometimes you have to make due with the situation you are dealt. You let management know the risks and the options. Ultimately they make the decision.

Given the constraints we had from the client, management, and the hardware, this was the only option. Alot of people get put in these situations.

Mon May 02, 08:45:00 AM EDT  

Blogger Thomas Kyte said....

You could roll the tablespace names, the code doesn't care what tablespace a table is in.

We'll have to agree to 100% disagree here. I would not permit this in any circumstance. Period. The software didn't do a rename tablespace, you work within the boundaries of the software. Period.

Mon May 02, 08:50:00 AM EDT  

Anonymous Anonymous said....

please explain what you mean by roll the tablespace?

i think you mean to use two different tablespaces in stage and copy the data back and forth right?

User A does the data load and the data manipulation. Data is copied to user B right?

We didn't have the hardware to handle that extra copy. Would have to be done during the day. We tested it and it slowed down the environment too much. We couldn't develop.

If you mean something else, please explain.

Mon May 02, 10:07:00 AM EDT  

Blogger Thomas Kyte said....

I meant use two tablespaces

Because the SOFTWARE doesn't do that.

And we need to live in the confines of the software.

The confines of the software... That is what this boils down to.

We will have to agree to disagree. I don't see either of us budging an inch on this one.

I view it as you didn't spec out the staging system correctly, you view it differently.

Mon May 02, 10:14:00 AM EDT  

Blogger Thomas Kyte said....

I meant use two tablespaces

Because the SOFTWARE doesn't do that.

And we need to live in the confines of the software.

The confines of the software... That is what this boils down to.

We will have to agree to disagree. I don't see either of us budging an inch on this one.

I view it as you didn't spec out the staging system correctly, you view it differently.

Mon May 02, 10:15:00 AM EDT  

Anonymous Anonymous said....

We were not allowed to spec anything out. We were given this hardware and told to use it. You are in a unique position in your career where you can say no to customers and demand additional resources.

Most of us are not and have to deal with the situation that we are handed. That includes client requirements, hardware limitations, and managers saying 'no' even when they don't know anything.

We would have run into more problems if we had used two tablespaces later on. This was for a product line that was being marketed to multiple to multiple customers. Larger clients would get changes to meet their requirements, but the base tool needed to stay the same to keep costs inline.

We had a client that we had to deploy to that had 30,000 users. It went to every employee at a large national corporation(larger than Oracle). We were required to deploy to the client site. They had sensitive data that they did not want to leave their environment. Plus throw in the fact that the maintenance team was being offshored, we had to keep it as simple as possible.

The client provided us with a certain set of hardware. It was pretty good, but not sufficient for us to use two tablespaces and copy the tables over everyday.

The contract was signed and deadlines were created before the technical staff was brought in. This is fairly common.

If you are given X requirements, with Y restrictions, and told to deliver Z, then sometimes you just have to do it and let management know the risks.

Ryan

Mon May 02, 12:32:00 PM EDT  

Anonymous Anonymous said....

Ryan,

Touching the data dictionary is a cardinal sin and should never be done unless support says so. I think that by doing this, the database becomes desupported by Oracle support. A Huge No No.

I do see where you are coming from though.

Tom,

He bring up an interesting point. I have also had sales people make promises that the software does something that it does not actually do. Not only have Oracle sales people done that, but so have others and you have to sort of come up with work arounds to make it work.

Sometimes it's fun to try and solve some sort of problem, and sometimes it's a nightmare. Not a situation I like being put into.

Mon Feb 27, 12:12:00 PM EST  

Anonymous Giridhar said....

I started copying the quesions once a month and add those files to a CD. Even though i am not connected to the internet, i can still read the questions and answers. Thanks tom..

Giridhar

Mon Apr 17, 07:36:00 AM EDT  

Blogger Vishal said....

I guess you should thank the original person who posted question on usenet. You would not be same world renowned Tom Kyte if it wasn't for asktom.oracle.com

Sun Jun 03, 01:47:00 PM EDT  

Anonymous Anonymous said....

Its good to see the first ever posted question. But i cant find it in the latest asktom.oracle.com. i mean the way i can see my posted questions. Dont you have the initial questions that way.

And one more thing does Lisa Phillips still visit asktom. Just curious :o)

Tue Dec 04, 08:10:00 AM EST  

Blogger Thomas Kyte said....

this is the original article.

It predates the AskTom website - it was done via email way back when, so, it will not be in the online archives...

No idea if she still visits or not :)

Tue Dec 04, 08:29:00 AM EST  

POST A COMMENT

<< Home