Funny Questions

Yesterday I wrote about a funny question I got, one that was unrelated to Oracle and databases but that sounded like it could have been. One of the comments received on that entry was to add an unusual story page to asktom, where I would hilight 'funny or strange' questions.

I think short term that might be amusing to some but long term it would be damaging overall. I think people should feel free to question authority, to ask why, to say “no, I don’t agree” and the threat of being on the “wall of shame” page or something like that would make people hesitate. I don’t ever want anyone to be afraid to ask a question, and having a page about questions that are ‘funny’ would be setting a dangerous precedent.

I get lots of questions, you only see about one third of them.  Today, right now there are 9,149 questions answered and published and 17,873 questions that were answered but not published on asktom.  In that 17,873 I’m sure there are some (I know) that those of us with years of Oracle experience would look at and say “wow, can you believe that”.  But that is the point – for people to have a place where they can ask those sort of things, to get jump started.  To get pointed to the right place.  I do not want people to be in fear of showing up on the funny pages.  I think it would send the wrong message.

So, while putting together a page of funny questions would be rather easy, it would go against what I’m trying to do with the Q&A stuff.  I remember some of the questions and misconceptions I had about databases and how they should or did work.  I remember when I thought DeCorte was a SQL keyword (see, now that would make a funny entry on a page of funny questions — “remember the guy who thought the DBA’s name was a keyword! Wasn’t he silly!”).

So, I’ll stick to pointing out ‘instant message’ speak – but leaving judgement as to what is a “funny or strange” question out of the picture.  Unless I get another one like yesterdays, which was funny as in “made me laugh” funny – not as in naive about databases funny.  I don’t want to discourage anyone from asking anything.

I guess I just think it might come across as being “mean”. I might be blunt sometimes, but hope it doesn’t ever come across as mean.  I like this stuff a lot (this database stuff) and really do want people to be successful with it.  If they are not successful with it, they won’t use it to its fullest potentional.  I want them to get to the next level. If they cannot get beyond level 1, they’ll never get to level 2 and higher. The “funniest” stories would probably come from them and I don’t want to scare those off.

Ahh, the Power of googling

Many times people ask me what the strangest/funniest question(s) I get are. Well, this morning I got one that made me laugh and it is probably googles fault.

I have been asked about web browser problems "Help, internet explorer cannot connect to this internal site and my help desk cannot figure it out" Amusingly enough, "clear cache" works frequently.

I have been asked how to get MS Word to install (rebooting works wonders on windows).

I have been asked autocad questions. I have been asked how to do things in SQLServer (many times I can answer those, but I’m never sure if I’m telling them the best way).  I’ve been asked to diagnose mysql issues. 

I’m asked career advice a lot.  The only answer I give is “you have to do something that makes you happy, but I’m not going to tell you to be a DBA or Developer, you have to decide that yourself..”.  That is a scary question, way too much pressure on that one (give the wrong answer and ruin someones life).

But this one from this morning made me laugh.  Some of the key words in there that caused me to read this a couple of times: “cursor java code tables”.  Sounds like it might be right for me (well, except for that java bit, but I digress).  Well, I looked at it for a while – trying to figure out what the heck a cursor trail was and then looked at the site they referred to and that little light bulb went off.  CURSOR TRAILS, that thing you can do in javascript to make the mouse perform illogical things on your screen.  It had nothing to do with the database – they wanted to know how to do the javascript to make little mouse trails!!

It was especially funny because my son Alan asked me the same question not too long ago.  He has put together perhaps the most obnoxious web page to look at (purple on black, javascript pop ups, the eyes that follow the mouse – with mouse trails and all). 

Since things happen in threes, I have at least one more mouse trail question in my near future.  I just wonder what that person thought about asktom.   I have a feeling after looking at their website that they might not have ever heard of Oracle before.  The question almost looks like it fits on the site anyway.  Anyway, it’ll make me laugh from time to time today.

Different Skill Levels

This was a question I received about managing people with diverse skill levels. I felt is was more appropriate for here, rather than there.

I am curious to know that having known so many things in depth, your expectations level from your staff must be very high. Then, how you are able to manage these people? Especially, I am sure there could be occasions when you might have expected something in one way and they might have done in other way, and with time constraints, you can not go back and re-write something. This could be as simple as maintaining standards to writing single SQL (rather than PL/SQL) etc., as we all are different, and we all do not know everything at the same level...

Actually, this year I have no "staff".  For the first time in 14 years.  And it suits me just fine so far. But I can still take a stab at this.  I remember in 1992, I had to hire some 30 people to staff a project. Have you ever tried to do that?  We ended up interviewing weekends (had the day job during the week plus hire people). We wanted to get people in and out really fast and used a hotel (funniest interview – we couldn’t get a conference room and a person I worked with – Janice – and I interviewed someone in a regular hotel room, with the bed and all.  Very strange setting).  After tons of interviews, you start to lower your expectations (you just want to get it over with).  Needless to say – we ended up with a team that had really good people, people in the middle and people that didn’t quite measure up.

I think this describes your situation. Yes, in the beginning, I expected everyone “got it” – if they nodded and said “I understand”, you thought they understood.  You learn really fast “I understand” can also mean “I have no idea what you just said, but there are lots of other people in this room, there is no way I’m going to say that out loud”. Lots of false starts.  But this is why writing things down (remember specifications?  documentation?  Sometimes some of that was done before a line of code was written) is important.  And why peer code reviews (when working on a software project with lots of fingers in the pie) are really important.

But you get to learn pretty fast who knows what, and who you can ‘trust’ as the person ultimately responsible for delivering the end product.  You learn who consistently would pump out a user interface that people could intuitively use (and didn’t have every screen looking like a brand new piece of artwork – have you ever worked with a GUI developer that made every screen a different color scheme, put the buttons in different places, ugh – you want to just say to them “what are you thinking!”).  You would learn who the coders were that you could just say ‘I need a routine that does X given Y” and they would just do it and it would work.  You would learn who you had to give 1 hour goals to (some people need to be micromanaged for one reason or another).  You would learn who works good in a team of 2 or 3 concurrent coders working together on the same thing, and who must always code alone (I always had to code alone :).  In short, you learned the group dynamics – who were the natural leaders and who were the natural followers and who needed to be managed into a different position in life.  Funny thing is – if you didn’t have a bit of all of them, you wouldn’t be able to do anything.  Given a room full of leaders, you just have lots of head butting.  Given a room full of people that needed to be guided – without the leaders you would not get far fast.

I actually prefer not to think of it as different skill levels, but just different skills.  It takes all types to make a project successful.  There are people that like the configuration management/quality assurance aspects.  There are people that like to have daily milestones and write code from specification.  There are people that like to be given a hard problem and no guidance.  There are people that like to be given a hard problem and a couple of bodies with heads to work on it.  I think you learn over time who is good at what you and dole out the work that way.

Walking into a new team and having to learn that all from scratch would be the hardest.  If you were just adding an individual to a team, you could try them out (nothing super mission critical until you learned what they are capable of).  But an entirely new team, that would be hard going at first, until you learned what everyone was good at.  But even then, if you are watching, that might only take a week or two to understand.

But I would use it as a reason to educate.  I remember this one guy – he didn’t quite understand the concept of a “subroutine”.  It was for him I instituted a rule that your code must fit on a screen.  You could buy the biggest monitor you want, but I needed to be able to see the top of the subroutine and the bottom on the screen (declares didn’t have to fit, the logic did).  The reason – I would take his code and hold down ctl-f in vi (page down) and you would see the same pattern over and over and over.  I’d ask “why” – “because I needed to do that loop to 10 things”, why not put the loop into a subroutine and call it?  “Oh, didn’t think about that, block copy worked well”.  That code would go back – that had to be fixed.  But this brings back the importance of CODE REVIEWS again – if you let the problem fester too long, you won’t be able to fix it and might have to live with it for a while. If you are monitoring it, you catch it first. 

So, it is part education (do it this way, let me show you).  It is part spec’ing it out (tell me, how are you going to do this).  It is part monitoring (tell me, what is the implementation looking like, how is it going). 

Minion and Political Correctness

Over the weekend there was a fairly spirited debate going on. I blame it all on Howard J Rogers, and a recent blog entry of his. The basic thought: "are some people above reproach for some reason".

The debate wasn’t over the answer to that question, that question has a fairly obvious (to me) and easy answer:

NO

No one is above reproach, everything can and should be debated. Experts are often wrong. Besides, being above reproach would be way too much pressure. You would actually have to be right all of the time.  Some people are correct frequently, but no one is 100% correct all of the time.  A simple typo can alter the meaning of a simple sentence.  A discussion forum I belong to had someone post “The Importance of proof reading”:

"Setting db_cache_advice will incur some latch overhead, though it is usually very significant."

See how two tiny little letters, I and N, could make all of the difference in this sentence!  Without them, well, it not only reads funny but says the wrong thing (this was a metalink support note being quoted, it has been fixed).  Mistakes are easy to make, we all make them, point them out as they happen. Fixing material is easy, really.

Back to the debate however, the debate was about the use of a particular word – “minion”.  Some people found the use of that term to be derogatory and others not so. (sidebar: I myself have never thought of the word minion in the positive sense and the one time it was flung out on asktom, I’m pretty sure the person using it did not mean it in a positive way).  It made me think about how hard it is to communicate sometimes, over cultural boundaries, in text, without seeing the face.

I don’t like talking on the phone, I find it hard to carry on a conversation without seeing the facial expression.  Two times in the last dozen years at Oracle I’ve had conference calls go really bad.  I remember both of them.  In both cases, I aborted the call prematurely (politely but – we really needed to just hang up) and visited them in person.  After 5 minutes of face to face – we got down to business and things went really well.  But without being able to see each other, read the body language, see the raised eyebrow, crossed arms, dis-interest, whatever – it was just not working.

The same thing happens when we “converse” in text. We try many things to make up for it – smileys, “air quotes”, tons of detail, “don’t get me wrongs” and such – but the fact is you can read the same text many different ways. But conveying the emotion, the intent – that is really hard to do.  A joke not interpreted as a joke becomes an insult. Sarcasm is missed and taken as honest advice instead (“suuuurrrreee, go ahead, type format and see what happens”)

Over the years, I’ve learned that “the less you know someone, the less you should read into the text”.  What I mean is, if someone I know pretty well sends me something – I have enough information about “them” in my mind to be able to read a possible message behind the message.  On the other hand, if I don’t know someone very well, I ignore hidden, possible messages and just go by what is there – giving the benefit of the doubt in all cases, being forgiving of things that come off insulting – but might not have been intended that way.

For example “Hey Kyte …”. Calling people by their last name where I come from isn’t a sign of respect, but in other parts of the world it is and “Hey Tom…” would be considered rude where they are. First couple of times I got the “Hey Kyte” – it didn’t sit right. Now, it is just another thing to call me.

“I have doubts…”, doesn’t sound right, sounds like they are questioning “me” – not that they have confusion surrounding what was said.  Depending on the context, “I have doubts” many times comes out sounding wrong.  I silently translate that into “I have some followup questions”.

“Please do the needful ASAP”.  I’ll translate that into “If you can get back to me with anything, even “I don’t know”, as soon as you have time, I’d much appreciate it”. 

I find if I treat things that way, things go much smoother (for me). Guess I’m saying it takes more to ruffle my feathers than it used to 10 years ago. I give the written word the benefit of the doubt. Don’t get me wrong – if you come along and say ‘well, that’s the biggest pile of rubbish I’ve ever seen written’ without any sort of supporting evidence whatsoever (and people do :) expect to receive a truly smart response from me. But, it takes something that strong to elicit that sort of response.  If you come along and say “I don’t think that is quite right” and provide some supporting evidence – great, we’ll see what the truth is.  And that is what is important isn’t it?  How stuff actually works, today. So please, bring it on.

On a related note, beware political correctness too. Consider this article:

The word "fail" should be banned from use in classrooms and replaced with the phrase "deferred success" to avoid demoralising pupils, a group of teachers has proposed.

Sugar and spice and everything nice, but it doesn’t accurately describe the reality that “they failed that grade”.

So, keep the comments and criticism coming.  Be polite (sure, makes sense), but don’t over correct.  Be careful in your choice of words, words have meanings and come with their own baggage sometimes. I tend to read and re-read my replies a couple of times and try to edit out the flaming sarcasm when appropriate (remember, sometimes sarcasm is useful in making a point and not insulting to the person reading it).

Beam Scotty Up

Makes me feel old reading this: Scotty passed away. I'm a big Star Trek fan (never got into Star Wars, but Star Trek...). Sad.

Yes, the last two days have been busy, Wed was full, by the end of the day I was totally wiped out.  Did 5 sessions that day.  It is strange that while I’m talking, I could go forever. But the moment after I stop, that’s it.  Stick a fork in me, I am done. I don’t want to talk anymore, I just want to take a nap or something. Fortunately, someone was watching out for me and my flight left on time and arrived a half hour early.  That – that just never happens in real life, but it did yesterday.  Unfortunately, we came in to the “G” gates which I believe stands for “godforsaken” at Dulles International Airport.  When you land there, you know you will spend more time actually getting from your gate to your car than you did in the air.  First is the forced march from the plane, 5 miles to the “remote gate”.  Then the wait for the bus.  Then the bus to the main terminal (but you do get the see the ENTIRE airport, and I mean the ENTIRE airport, not just some of it but every single plane).  Then, the neatest sort of maze where you go down an escalator, up one and down another one (can you spell “tunnel”, tunnel would be nice).

Today I did two sessions at the OGUC (Oracle Government User Conference).  At least that was near home, downtown Washington DC. Very nice turnout, I was surprised. I got to do a keynote (drove them crazy, sent them slides and at the last minute said “nah, I don’t want to do that, I want to do a revised things you know presentation”.  You want to make conference organizers crazy, change everything at the last minute).  It worked out well though.  I had a nice short version of it and time for Q&A.  Then a 10g Release 2 update.  That was, how do you say, different.  Normally, when doing a session – I’m talking about something I’ve used for a while.  I have four followup questions that I was not able to address (I didn’t have my own laptop on stage, I couldn’t check out what the right answer was). I’ll be posting that on asktom tomorrow (the Q’s and A’s I didn’t have today).  Everyone was really eager to hear about it though. I think we had near 300 people come (in the middle of july, in DC, I was surprised).

On a totally different note, I was reading HJR’s blog and he wrote something that ‘disturbed’ me (not that he disturbed me, but rather the concept).

 Is there some rule, perhaps, that means Tom is considered above the fray for this sort of "correction", but that I am fair game?

I certainly hope not.  I make mistakes all of the time. I dig yeahbutts.  I want to be told “no, that is not quite right”.  I’ll argue back if I think they were wrong, but we’ll come to a conclusion.  I really really hope that just because I said it, people don’t assume it must be universally true (you must be convinced).  That would be so so wrong.  I know it happens to some degree, so I try to be careful what I say about the database, but trust me – I don’t get it right all of the time (hardly).  The number one reason I’m a “script happy dude” is because 999 times out of 1,000 if I didn’t “show it”, I would be totally wrong. Don’t ever be afraid to question what I’ve written on asktom (frankly, sometimes when scanning I just “miss” the point sometimes). 

OOUG

I am on my way to the Ohio Oracle User Group for the day tomorrow. Looking at the agenda, I can see I overdid one of my slots. It is a full day affair starting at 9:15 with one of my favorite 'fluffy' presentations on "Things you know"

After that I’m doing Tools I use. It still surprises me sometimes how many people don’t know about simple things like autotrace, tkprof and the like. To me though the real meat of that presentation is when I talk about instrumentation.  This is something I think is missing from many developed applications. One thing I really like about HTML DB is the fact that it is fully instrumented whether you want it or not (just like 10g, not that 9i and before were not, but 10g really is). Nothing worse than walking into a customer with a slow application and having the answer to “so, where do you think it is slow” be answered with “somewhere between line 1 and line 10,000,000 – we are not sure really”.

Then, the problem presentation.  I probably will have to adjust the schedule on the fly there.  I have an hour to do SQL Techniques and it has been a while since I finished it in an hour.  It is funny how the seminar sections grow over time – every time I do it, I have maybe 5 more minutes of material somehow.  This time, I have this to consider while talking about scalar subquery caching and reducing function calls.  Neat stuff.

The next session is on Efficient Schema Design – I’ll be able to skip one of the sections in this since I’ll have covered it to some level in the SQL Techniques.  That might get us back on track. 

I close up with Top 5 things done wrong.  The first slide simply says:

• Lack of bind variables
• Lack of bind variables
• Lack of bind variables
• Lack of bind variables
• Lack of bind variables

Get the point?  The next on has the real list

• Lack of bind variable
• Not having a good testing environment
• Not having any configuration management
• Trying to be database independent
• The DBA versus Developer relationship

Funny thing to me is that most of the talk is not “design” related so much as environmental issues.  Your IT’s mindset and procedures are the things done wrong mostly.  That last point really irritates me – the DBA vs Developer.  I see it over and over and over again.  The DBA feels their job is to protect the database from the evil developers.  The developers on the other hand try to find any way possible to not have to engage the DBA.  This always leads to suboptimal implementations and lots of finger pointing.  It is the ultimate self fulfilling prophecy – The DBA’s will have proven that the database needs protection (since the application is so poorly performing and resource intensive) and the developers will have proven that they need to work around the DBA’s in order to meet schedules.  It does not happen everywhere – but it does happen a lot.   

If you would like to help out a fellow DBA with her first really big presentation, see this request for information.  Sounds like an excellent topic and I’m surprised we don’t see more of them in this genre.  I’m all for it.

First Major Hurdle for Volume I

I've just, as in minutes ago just, passed the first major hurdle for Volume I of my next book. I finished the first drafts of everything. It ended up with 15 chapters and is sort of my "concepts" guide if you will. It roughly covers the first 9 chapters of Expert One on One Oracle, in more detail than before. It took longer than I thought.

We have tech edited up through chapter 9 and I have chapters 10 and 11 in my inbox to do.  A couple of chapters are “finished”, as in I’ve seen the pdf proofs of them and that will be what is put onto paper.  The deadline for everything is August 11th, so we are in the home stretch now.  I’d really like to have the book in the store for Oracle Open World in September and in order to do that – the 11th is sort of a drop dead date.

I’ve learned a couple of new things going through this rewrite process.  It has been interesting.  Lots of things “technically” changed from 8i to 9i and 10g.  Things you might not notice until you go looking for them.  But it just drives home that being able to test and measure things leads to better understanding.  The tuning advice of 8i and even 9i is not as relevant in 10g and some of the setup “rules of thumb” have just been turned upside down.  The experience of the past is useful, but unless you know how to see what is going on – it’ll just be magic.

One analogy.  I was just looking through Effective Oracle by Design.  I was writing about scalar subqueries.  While doing that I noticed I wrote this extremely ‘useful’ comment:

But somehow, the second query is more efficient.

In hindsight, I’m sort of embarrassed by writing that.  I didn’t know then what I know now – I know precisely why the scalar subqueries can be more efficient in that case (and now I even know how to make some of those queries even MORE efficient than they were in the book).  A feature called scalar subquery caching is involved and I knew about it vaguely (well, I don’t know if it is called that, that is what I call it)  When we figured it all out – with some help from Jonathan Lewis – new opportunities to use them opened up.  So, why is is important that we actually understand what is going on (going beyond the incredibly useful statements like “try this, I did once and it was great, maybe it will be for you too”)?  Because now that I understand exactly what is happening, I can use this feature even better.  If I just observed “hey, sometimes this thing works good” and didn’t understand it, I wouldn’t know when to apply it – or how to best apply it.

So, in the rewrite, I’ve gotten clarity on some things I knew but didn’t know why (I can safely say, there are no comments like “but somehow” in this book!).  I’ve gotten my thinking readjusted on things I thought I knew (eg: the shared_pool_size init.ora parameter is always smaller than the actual shared pool size – show SGA will always show a variable size much bigger than the shared_pool_size.  NOT true anymore…). 

All in all, I’m feeling fairly positive about the next release of the book – sometimes second editions are superficial updates, I didn’t want to do that and I can safely say I did not do that.  There is a lot of new material in there.

Now, if I can just make it to August 11th :)

On a non-related note.  Has anyone else ever had a bird take out their internet connection?  I did this morning.  It was going along great and then all of a sudden — nothing.  Looked to the sky to see if a big storm was coming (that can take it out) but it was clear.  So, I haul the ladder out to the field:

 

and noticed a bird had left a nice present on it (it was a bit dirtier before the photo).  As I’m cleaning it off, I notice this label on the long bar — which I’m sort of holding onto for balance:

It says “This device emits radio frequency energy, Keep two feet (0.6 meters) away from this point, Before servicing or upgrading, unplug indoor power connection”.  I should have read the documentation first I suppose.  Now I know.

Personally Speaking

Someone asked Don't you find this blogging a one way street to your personal life? I understand there's a lot of your life missing but I've seen… just today and someone else pointed to this article.

Well, I don’t have a problem sharing some of that information – yes I was born in Baltimore Maryland, lived for a short while in the early 70’s in Pittsburgh Pennsylvania, lived most of my childhood in Bethlehem Pennsylvania until I went to college at the University of Pittsburgh and then moved to Virginia after graduation and with the exception of a year in New Jersey (I got tired of commuting every week 161 miles each way to work!), have been here since.

But, that is all information you can get from the internet.  Sure, you might have to pay $9.95 or something, but you can get all of that (and more probably).  In fact, I got a Christmas card from an asktom reader once, a little startling and surprising since I have an unlisted number and address.

There will be some places I won’t go here though. Things I’m proud of, I’ll talk about (soccer and Megan for example).  Things I would tell a casual acquaintance.  Small talk, opinion.  Absolutely.  But I would also only write that which I would also get up on a stage and project on the big screen and talk about as well. 

I view asktom as sort of a blog (who knew, I was blogging before it was cool).  “Dear Abby” in the newspaper was a blog way before that.  A healthy dose of opinion mixed with a bit of attitude (I have definite ideas on how some things should be done – I think that comes across).

But unlike the examples in that referenced article above – I don’t see this as coming back to “bite me” later.  I have seen some blogs of ‘famous’ people (as in known in the technical community) where they probably cannot say that – as they have retracted more than one article one might consider ill advised to have published in the first place, but I think I’m a bit more reserved that way.  I truly would not write something I would not say to an audience.

I doubt that in the 2016 election, anything I say here will come back at me :)  You’ve probably noticed that you don’t know my political views, what if any religion I might be, if I contribute to charities or not. Those and others are topics I share with close friends (as they are a big long dark hole to fall down into).  Heck, I even avoid online discussions about natural and synthetic keys (too much opinion).

So, I don’t think this is a one way street – I control the traffic see, I only write about what I want to. Someone asked me once to describe me as a kid.  I passed on that (too much information).  Other than to say that I looked a lot different back then than I do now…

 

Everything I ever said on the usenet newsgroups going back to 1994 is there, searchable and retrievable (all I can say is, I was perhaps even more competitively passionate back then, if you read some of my postings on the sybase and informix newsgroups).  While I might say something in a toned down voice today, I don’t think I’d take anything back.  That is what the escape key is for!  (How many therapeutic emails I’ve written that never got sent.  Oh, I’ve had some brilliant flames, that never got read by anyone but me). 

If you asked me something too personal in a face to face conversation, I would defer answering.  Same thing holds here.

Truly Mobile Blogging

Can you tell i am bored? Testing mobile blogging. Another exciting day at the airport! Hour five and counting. I cannot believe i typed this in on my phone!

RTFM

Read that fine manual. Is that by itself enough? I ask this for a couple of reasons. First, one of my favorite blogs to read wrote about it, and secondly I got a disturbing email regarding it.  It read in part (published with permission, I would not publish a private email without asking)

On your ask tom web site you recommend people use usenet news groups. I tried that and I found the oracle.server group to be extraordinarily hostile. I was amazed. I have been in computing for 20 years and I am well aware of the fact that a lot of geeky people have limited social skills, but not THAT bad. I have to admit not having dealt with usenet groups in a few years and the way the culture has declined is really something! It was a case of getting some information but it was definitely not worth the cost in terms of the major hostility that came with it.

I asked the person for a pointer to the thread and read it.  It contained juicy bits like

• It appears that you are either incapable or unwilling to RTFM.
• The only gotcha I see here is the one about reading the manual before engaging the keyboard.
• It is well known for a fact all Windows users never read anything.

None of those were exceedingly productive statements.  None of them would actually solve the problem.  I can understand sometimes the frustration after seeing repeated questions, or what seem like very basic questions – but the old RTFM answer doesn’t seem to be worth the keystrokes to type it in.

I do give RTFM answers on asktom, but with a twist.  It is in the form of “here, it is documented at this <link>”, with the ability and offer to come back later and ask a specific followup question.  I do recommend and point people to the documentation (when was the last time you read the concepts guide?).  But I don’t think I give RTFM answers (Martin, this is not about your comment on the creating passionate users entry!). 

I very much agree with Kathy Sierra on this point. Everyone is a “newbie” sometime in their life at something.  I will answer with a gentle reminder such as “well, when I typed your subject into the search field, I found these 5 articles, did you see them?”.  I will answer with “materialized views are fully documented <here>, if you have a specific question after reading about them, feel free to come on back”.  I will answer with “based on the questions you are asking – I suggest you crack the concepts guide, you know, if you read it from cover to cover and retain just 10% of it, you’ll already know 90% more than most people about Oracle!”. 

But I don’t think RTFM by itself is a productive answer.  Others have said as much as well.

It is funny, on the newsgroups no one is compelled to answer.  I wonder why sometimes people feel compelled to spout the RTFM phrase then.  We could all be a little “gentler” (but I’m not backing off of my “instant message speak” stance!  I’ve actually received resumes, cover letters, and business ideas with those constructs).  Remember, we were all newbies once upon a time.  The thing about my job that gives me greatest pleasure?  Seeing the light bulb pop on for someone.  You never get to see that if you just RTFM them.

And how is this for deja-vu, yes, I am on hour 3sitting in the fantastic LGA terminal again, with at least 3 more hours to go (just as I was typing this sentence, I got an alert on my phone, 5:05 pm estimated take off, it is 2:10 now).  At least I found a plug, and I'm not giving it up! This would be utter misery without a network.  Since I carry a desktop computer on my back - I can work anywhere :)

How does it happen

I used to run Linux on the laptop, then I got myself a new laptop with all of the bells and whistles and never put linux directly on it (all of my databases run on the laptop in vmware, and I access the real servers with VNC).

So, everything is going along, no worries.  Until this morning.  I swear, I did not change anything, I didn’t install any new software, I didn’t remove old software.  I just rebooted.  And then it happened.  First I noticed that my speakers were “crackling”, but only when I moved my mouse, or typed, or spun up the hard drive.  In fact, sound wasn’t working at all, just being annoying.  So, reboot again (figured maybe it wants a rest, so gave it a nap).

No joy.  So, I figure – just remove the drivers and let them reinstall.  Blue Screen – bang.  Try again, blue screen – bang.  Thinking “third time is a charm”, I disabled them and then tried to remove them – bang, blue screen again.  Ok, safe mode, remove them – reboot, sound – sweet sound.

Only, no CD/DVD.  Gone, detected but “Code 39 error loading drivers, drivers may be corrupt or missing”.  Drivers are there, drivers are signed, drivers look ok.  So, remove them, reboot, no joy.  Repeat.  Repeat more than once.  Try upgrading drivers — nope, the ones I have are perfect, just not loading.  Review all discussion forums I know, google and google and finally — finally discover:

Problem: My DVD-ROM drive disappeared! Also, the little red light on the drive is permanently on and the drawer won't open.
Possible Solution: This is a well known problem with XP, if you cannot access CDROM, DVD, CDR or CDRW drives or get a Code 31 or 39 error in Device Manager then the solution is to run Regedit and delete Upperfilters and LowerFilters at:  HKEY_LOCAL_MACHINE\ System\ CurrentControlSet\ Control\ Class\ {4D36E965– E325– 11CE– BFC1– 08002BE10318} And then restart the PC. See Q2700008 on http://www.microsoft.com/technet/

Of course, why the heck didn’t I think about that! And sure enough, that does it – some 3 hours later. I’m left sitting there thinking “what would my mom do with this”.  Well, besides call me and have me say “send an email to Bill at ms.com”.  I swear, I did nothing – it was working fine all day Saturday, all day Sunday.  All last week.

It has made me even more sure that my decision to not install anything important on the base OS was a smart move.  Everything I really use is on servers elsewhere.  I want a smaller OS that just runs my browser, shows me my movies, lets me get to my email, runs a word processor.  That is it (oh and lets me sync my file systems – I’ve been using unison for years now, it is sweet.  I sync my laptop to a linux server in my basement and one at work as often as possible.  Nice feeling having three copies of that which matters).

3 hours, gone.  But at least all of my devices appear to be working for now.  Just makes me wonder what is next.  Might be time to buy another internal drive and check out how linux would go on this laptop.  Speaking of disk drives, I picked up a Lacie Databank to host my vm’s on.  I got the 60gig version (so I travel with about 140gig of storage – things have changed, that is many times larger than my first Oracle database benchmark!), it is as neat looking in real life as that picture is.

Point Taken

Alberto Dell'Era said Looks like that your blog is turning (partially) into the "TomSays" of "askTom", where you push Oracle topics instead of pulling them, for public discussion. If that's going to be, perhaps you may consider opening a separate section in asktom. And Rob ZIjlstra said.... basically the same thing.

I guess they have a point – it was really hard to get the code examples to fit into the blogspot templates.  So, next week, I will do what I’ve done once or twice before and “ask myself” a question about LOG ERRORS on asktom – look for it over there, I’ll be sure to put a link to it on the home page so it is easy to find.  But I do want to discover the in’s and out’s of LOG ERRORS before I start applying them.  I think that is important – and the basic reason my “work in progress book” on 9iR1, 9iR2 and 10gR1 will have this much to say about 10g:

<this space intentionally left blank>

I cannot imagine promoting or advising the use of a new feature that I’ve never actually used in “real life” in a book – people take that seriously.  It would not really be possible to advise people on when to use them or not yet (proof: look at the previous 3 blog entries!  We learned a lot but what I mostly learned was I have more to learn about them – they look exceedingly promising so far though as we understand them).

Anyway, Rob asked “what do you eat/drink when you bar-b-que and how many people are normally there”.  Well, last weekend being the 4th of July we definitely did that.  There was about 15 adults and what seemed like dozens of kids (but I’m told it was about 10).  A friend of mine, Sean Dillon, took some shots. His comment on there about “defector” relates to the fact that he starts at Microsoft tomorrow!  Right across the street from my building (which used to be his building).  You can see one of my at home jobs which is running the grill.  That night I was cooking simultaneously some spicy sausage (my personal favorite), burgers, chicken and the top rack was filled with veggies (at first, you have to rotate the food around).  I timed it perfectly as everything came off of the grill at exactly the same time.  Kids get soda and water to drink (you see an example of each with Megan) — and water to throw of course (the shot of Alan).  I usually have a beer with sausage (although I have a feel that Rob from Holland would beg to differ, I’ve a feeling he would not consider what we call beer is actually beer).  Then it is red wine.  But just beer and wines for a bbq setting.

Later that night we set off the requisite fireworks in the driveway, a good time was had by all.

Almost forgot, in response to this comment and this one, the answer is yes - I got it last week and wow, it is very very cool. It even works over the satellite connection I have at home. My son is hooked on it too. Thanks, yet another distractions

How cool is this part IIa

Alberto Dell'era said previously Is perhaps big_table ordered on disk by "id", ie by the PK of t1,t2,t3 ? If yes, I wonder what would be the results if it got randomly "disordered" (re-created using a CTAS with "order by dbms_random").

What a difference.  Instead of adding an order by dbms_random, I updated the primary key of BIG_TABLE to be random and re-ran the test with the constraints on (the check and primary key constraint).

Very promising, I had a “fake example” with the sorted data for the index, it was:

• insert, no error logging, 10.1 seconds elapsed, 7.36 cpu seconds
• insert, error logging, 47.6 seconds elapsed, 42.55 cpu seconds
• procedural code, 21 seconds elapsed, 17.99 cpu seconds

It is (after randomizing the sorted order of the primary key)

• insert, no error logging, 30 seconds elapsed, 26.56 cpu seconds
• insert, error logging, 68 seconds elapsed, 47.02 cpu seconds,
• procedural code, 54 seconds elapsed, 40.56 cpu seconds

Obviously, there is more to test!  The LOG ERRORS still generated more redo — but less than 2x the redo (not 5 times).  It generated only a little more undo and the latch gets were very comparable this time.  Alberto – thanks for picking up on that one.

What I’d like to do is re-run the stuff with sorted/not sorted data.

Add a test to have 5% of the data fail (randomly), 10%, 15%, 20% and 25%.  I believe anything above 25% indicates a serious failure in the ETL process and so wouldn’t be real world.

Check out update, delete and merge times similarly.  We’ll work that out over the course of next week. 

Hats off to Alberto!

How cool is this part II

Taking a deeper look at the LOG ERRORS option for DML in 10g Release 2.

Ok, so I’ve done the insert component of the testing and found something rather dramatic.  We’ll let this one unravel as we go through it.  I set up a test to compare

• normal insert that could fail and just undo everything
• insert with LOG ERRORS
• procedural code with exceptions

and how each would go.  I did it with three levels of constraints:

• no constraints (beyond NOT NULL)
• a check constraint
• a check plus primary key constraint

and in two modes:

• direct path for the inserts (conventional of course for the row by row)
• conventional path all around.

In short – direct path, LOG ERRORS rocks.  Conventional path, we might have an issue, something to get incrementally tuned in the implementation.  But remember, this is still pre-production, we’ll have to come back yet again later.  The test used a table structured like this:

create table t2
as
select *
  from big_table
 where 1=0;

alter table t2
add constraint t2_check
check (object_id>0);

alter table t2
add constraint t2_pk
primary key(id);
                                                                               
begin
 dbms_errlog.create_error_log
 ( 'T2' );
end;
/
and there were corresponding T1 and T3 tables for the regular insert and the procedural code (no error log tables were created for them).  I would comment out the constraints or not depending on the test being performed.  The code used looked like this:

insert /*+ APPEND */ into t1
select * from big_table;

insert /*+ APPEND */ into t2
select * from big_table
LOG ERRORS
REJECT LIMIT UNLIMITED;

DECLARE
 type array is table of 
 t3%rowtype index
      by binary_integer;
 data   array;
 errors NUMBER;
 dml_errors EXCEPTION;
 l_cnt number := 0;
 PRAGMA exception_init
       (dml_errors, -24381);
 cursor c is
    select * from big_table;
BEGIN
 open c;
 loop
   fetch c
      BULK COLLECT
      INTO data
      LIMIT 100;
   begin
      FORALL i IN 1 .. data.count
               SAVE EXCEPTIONS
        insert into t3 values data(i);
   EXCEPTION
   WHEN dml_errors THEN
     errors :=
        SQL%BULK_EXCEPTIONS.COUNT;
     l_cnt := l_cnt + errors;
     FOR i IN 1..errors LOOP
       dbms_output.put_line
       ('Error iteration ' ||
   SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
        ' Oracle error is ' ||
   SQL%BULK_EXCEPTIONS(i).ERROR_CODE );
     end loop;
   end;
   exit when c%notfound;

 END LOOP;
 close c;
 dbms_output.put_line
 ( l_cnt || ' total errors' );
end;

Now, using a modified runtstats utility that allows for 4 different test cases, and running the above with 1,000,000 rows (BIG_TABLE is a copy of ALL_OBJECTS with a primary key field ID added) I observed:

• direct path insert, no error logging, 9 seconds elapsed, 4.66 cpu seconds
• direct path insert, error logging, 8.78 seconds elapsed, 4.78 cpu seconds
• procedural code, 19.36 seconds elapsed, 18 cpu seconds

That was with all constraints enabled.  Results were similar with one constraint/no primary key and no constraints.  Then, I turned the append hint into xappend – and used conventional path inserts.  What a difference an X makes:

• insert, no error logging, 10.1 seconds elapsed, 7.36 cpu seconds
• insert, error logging, 47.6 seconds elapsed, 42.55 cpu seconds
• procedural code, 21 seconds elapsed, 17.99 cpu seconds

Hmm.  The big difference is the amount of undo generated – 163m for the insert with error logging, 28/29 meg for the insert/procedural code.  Additionally, the insert/error logging did about 16million latch gets, the others did in the 1 to 1.5 million range.  The big difference was in the cache buffers chains latch – it (the error logging) hit the buffer cache much more frequently.

So, so far, this looks really promising for direct path loads, but we’ll have to look into the conventional path further.

How cool is this

Taking a look at a new 10g Release 2 feature, DML Error Logging.  Also, a stupid thing I saw in the news.

First for the most ridiculous news item of the day.  Astrologer Sues NASA Over Comet Mission.  Hello?  Anyone at home?  What a waste.

Ok, back to the real world.  I’ve found that the most dreaded words someone writing for a magazine can see are “your column is short, do you have 400 words to add”.  Well, that can be as bad as “your column is long, chop 400 words”.  I got the “too short” over the weekend and had to find another thing or two to write about for my next column.  I vaguely recalled that I knew this feature was coming but I forgot to look for it.  In my search for an extra 400 words, I stumbled upon it again.

DML Error Logging, instead of the 100,000 row update/insert/whatever failing because a single row doesn’t quite work out, we can have the 99,999 successful rows go through and have the one bad row logged to a table!  I mean, this isn’t just cool, this is “change my world, rewrite the books, stop the presses, wow, knock me over with a feather”.

And – the best part – it is really quite easy and intuitive.  This is cool.  Here is a quick demonstration.  We’ll just take the SCOTT.EMP table:

ops$tkyte-ORA10GR2> create table emp
  2  as
  3  select * from scott.emp where 1=0;
 
Table created.
 
and using a new supplied package – we’ll generate the error log for it.  You could create this by hand, you could specify the name via this API, you could do lots of stuff, this is the shortest way.  It’ll default everything and just name the error log table ERR$_<tablename> which seems sensible:

ops$tkyte-ORA10GR2> exec dbms_errlog.create_error_log( 'EMP' ); 
PL/SQL procedure successfully completed.

 
ops$tkyte-ORA10GR2> desc err$_emp
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ORA_ERR_NUMBER$                  NUMBER
 ORA_ERR_MESG$                    VARCHAR2(2000)
 ORA_ERR_ROWID$                   ROWID
 ORA_ERR_OPTYP$                   VARCHAR2(2)
 ORA_ERR_TAG$                     VARCHAR2(2000)
 EMPNO                            VARCHAR2(4000)
 ENAME                            VARCHAR2(4000)
 JOB                              VARCHAR2(4000)
 MGR                              VARCHAR2(4000)
 HIREDATE                         VARCHAR2(4000)
 SAL                              VARCHAR2(4000)
 COMM                             VARCHAR2(4000)
 DEPTNO                           VARCHAR2(4000)
 
There is the error logging table, they picked datatypes that would be “safe” for each column — if you inserted a JOB that was too long, no worries, it has to fit into a VARCHAR2(4000).  EMPNO isn’t really a number in your input? No problem, it’ll fit into a VARCHAR2 as well.  The other columns you see in here are useful too — the ORA-XXXX error number, the text of the error, the ROWID of the offending row(s) from an update or delete, the operating type (I/U/D) and optionally a ‘tag’ you provide as part of your SQL operation.  That tag will be useful to determine what step of your ETL (extract/transform/load) process failed. 

So, let’s try this out, we’ll add some constraints:

ops$tkyte-ORA10GR2> alter table emp add constraint emp_check_sal check(sal > 900);
Table altered.
 
ops$tkyte-ORA10GR2> create trigger emp_trigger
  2  after insert on emp for each row
  3  begin
  4      if ( :new.ename = 'ALLEN' )
  5      then
  6        raise_application_error
  7        (-20024, 'Failed Validation' );
  8      end if;
  9  end;
 10  /
Trigger created.
 
ops$tkyte-ORA10GR2> alter table emp add
  2  constraint emp_pk primary key(empno);
Table altered.

and using just the old fashioned INSERT, we can see what normally would happen:

ops$tkyte-ORA10GR2> insert /*+ APPEND */ into emp
  2  select * from scott.emp;
insert /*+ APPEND */ into emp
       select * from scott.emp
*
ERROR at line 1:
ORA-02290: check constraint 
           (OPS$TKYTE.EMP_CHECK_SAL) violated
 
ops$tkyte-ORA10GR2> select * from emp;
no rows selected
 

A big “nothing” is what happens, the SQL either entirely happens or doesn’t happen at all.  But, add “LOG ERRORS” and check it out:

ops$tkyte-ORA10GR2> insert /*+ APPEND */ into emp
  2  select * from scott.emp
  3  LOG ERRORS REJECT LIMIT UNLIMITED;
12 rows created.
 
Now, since anyone that can spell Oracle knows there are 14 rows in emp – something happened here.  Something good in a way.  We had errors (bad) that were not ignored (good) but logged off to the side (better) and we still took advantage of BULK OPERATIONS (best of all).  We can review the bad records:

ops$tkyte-ORA10GR2> select ORA_ERR_NUMBER$,
  2         ORA_ERR_OPTYP$,
  3         EMPNO,
  4         ORA_ERR_MESG$
  5    from err$_emp;

ORA_ERR_NUMBER$ OR EMPNO ORA_ERR_MESG$
--------------- -- ----- ---------------
           2290 I  7369  ORA-02290: chec
                         k constraint (O
                         PS$TKYTE.EMP_CH
                         ECK_SAL) violat
                         ed
 
          20024 I  7499  ORA-20024: Fail
                         ed Validation
                         ORA-06512: at "
                         OPS$TKYTE.EMP_T
                         RIGGER", line 4
 
                         ORA-04088: erro
                         r during execut
                         ion of trigger
                         'OPS$TKYTE.EMP_
                         TRIGGER'

It even works during UPDATES:

ops$tkyte-ORA10GR2> update emp
  2     set sal = sal - 2000
  3   where sal between 2000 and 3000
  4  LOG ERRORS ('My Update')
  5  REJECT LIMIT UNLIMITED;
 
3 rows updated.
 
But really, we didn’t update three rows.  Note this time I passed in a nice little name, which we can now use against this cumulative error log table (the failed inserts are still in there as well)

ops$tkyte-ORA10GR2> select ORA_ERR_NUMBER$,
  2         ORA_ERR_OPTYP$,
  3             ORA_ERR_TAG$,
  4             ORA_ERR_ROWID$,
  5         EMPNO,
  6         ORA_ERR_MESG$
  7    from err$_emp
  8   where ora_err_tag$ is not null;
 
  ORA OR ORA_ERR_T ORA_ERR_R EMPNO ORA_ERR_MESG$
----- -- --------- --------- ----- ---------------
 2290 U  My Update AAAM04AAE 7698  ORA-02290: chec
                   AAAAGdAAD       k constraint (O
                                   PS$TKYTE.EMP_CH
                                   ECK_SAL) violat
                                   ed
 
 2290 U  My Update AAAM04AAE 7782  ORA-02290: chec
                   AAAAGdAAE       k constraint (O
                                   PS$TKYTE.EMP_CH
                                   ECK_SAL) violat
                                   ed
We have the row data (EMPNO is filled in) as well as the rowid’s of the bad rows.  We can see what caused the error — the check constraint violation, and see how many rows violated the check constraint — two in this case.  Our update would have modified 3 rows had all of the rows been “OK”, but it really only did 1.

This new feature changes everything.  No more excuses in the future, BULK operations are in style, they are cool, they are efficient and you’ll be able to erase a ton of code (which believe it or not is a cool thing, erasing code). 

Can you tell I’m sort of excited about this new feature?

How small can it get

I was reading gizmodo and stumbled upon this nifty little gadget

I thought to myself “wow”, how much smaller can  you get?  I remember when I started giving presentations, the state of the art was a 35mm projector (we still have boxes of the old slides) but they were professionally done and quite expensive.  Then came along the LCD screen that sat on an overhead projector and in the olden days was basically black and white (or orange and bright orange).

Then came the projectors.  Full color, 640x480 resolution, huge, heavy, delicate.  These things were monsters, if you ever used one you know what I mean.  The case they came in was metal – solid case, heavy case, huge case.  As big as a large college dorm refrigerator, only heavier.  And bright silver, you could not misplace this thing.  The inside of the case was filled with tons of foam padding because these things were so delicate. In order to use them, you had to close all blinds on the windows, turn off the lights, plunge into total darkness, otherwise you could not see them.  I myself prefered color transparencies (I would print them using a relatively cheap deskjet, it took a good 10 minutes per transparency but the results were pretty good) for that reason, I really need to see the room to talk to the room.

Anyway, these projectors were beasts, you certainly didn’t travel with them – you had them shipped if you needed.  And expensive, hugely expensive.

Now, I’ve watched over the years as they get smaller and lighter and brighter – to the point where they are now smaller and lighter than my laptop – but this one here puts it into an entirely new realm.  That is getting close to pocket size.  And if the marketing is accurate, it’ll be bright and durable.  And the price looks fairly low.  Now, it is only 800x600 but in a pinch that is more than enough pixels to push slides and run a sqlplus demo or two.

Now I guess I’m expecting something that looks like a maglight flashligh with a usb connection for power in the future!