Niall's Oracle Pages

Apparently around Don's way there is a classic database problem. No-one has surnames and they all have the same first name. One could of course use an artificial key, but Don has hit on a better solution. Sequence numbers for surnames. You never know it could be the way forward, in fact it might make the UK National ID Card a simpler implementation.

Niall 666

I just noticed that OTN has a new (to me) section for blogs from Oracle Executives.

At the moment there are precisely 3, all apps, Senior VP blogs there. (What a terrible job title by the way - I'll bet my bottom dollar there isn't anyone with a post of Junior VP out there).

John Wookey who is VP for Applications Development.

Jesper Andersen who is VP for Applications Strategy.

Cliff Godwin who is VP for Applications Technology.

It will be interesting to see how these blogs pan out - I hope that they represent a long term committment from Oracle to more informal as well as formal communication from Oracle executives. I'd love to see a Larry Ellison blog as well (even if was just about fast sail boats).

Mark Rittman posted some interesting thoughts on confidentiality and blogging today. Tom Kyte picked up on them as well. Don Burleson has posted a number of links regarding hypothetical legal issues as well. Anyway Mark asked for thoughts from other bloggers, and partly by way of making a point, I thought I'd comment here.

I happen to think, and am as confident as an educated layman can be that the legal position will be somewhere around the same place as well, that common sense and the concept of duty of care will go a long way here.

I'm commenting on Mark's blog - I haven't notified him or asked permission to do so. This is because, it seems to me, that by posting opinion in the public domain it is fair to comment upon it.

Having looked through my blog history it occurs to me that there is almost nothing there that reflects upon my work, this is because of a duty of care (and arguably a contract of employment). Equally there are emails I have received that I have not commented upon. Same thing goes, private conversation isn't public knowledge. I'm not a consultant - but it seems to me that the consultant/client relationship is equally a private one. I'd not expect to see client problems shown on a blog, except where they illustrate a general point ("a client of mine had this slow batch job, turns out it committed every 100 rows to save rollback space").

So far, so straightforward. If it is in the public domain - its fair game, you may or may not converse with the author beforehand some I would others aren't worth the hassle, but commentary is fair. If it is private or you have a duty of care then don't publish without generalizing.

There are a couple of other issues that do come up from time to time. The first is what to do when you mess up. I personally leave the mess up in place (unless I spot it as I post - doh!) and post an update, probably elsewhere, or an acknowledgement in the comments. Others publicly correct the original, which is OK but sometimes the original error is education itself, and yet others silently correct the article, this last I find really annoying because although the article gets corrected the publisher has no control over where it has been replicated to and commented upon.

The second (its an american constitution thing I think) is whether bloggers are Journalists or not. For me, we aren't (except rarely), journalism is (in my book) a valuable and important profession holding authority to account and explaining the world. Blogging is op-ed. Valuable and important, not Journalism. I respect the BBC,the Telegraph, the Economist etc not for the opinion (though I wish I could write that well) but for the real journalism that makes up the bulk of the output.

The annual conference of the UK Oracle Users Group is now only 5 weeks or so away. This conference, which is the largest Oracle gathering in Europe and is a user driven rather than Oracle marketing driven event, takes place in Birmingham UK from October 31 to November 2nd. For those of you that book early enough and can get there on Sunday this year we are introducing an Oak Table day. 10 presentations you won't get to see at the conference (including Graham Wood on why you don't need 10046) and with limited audience (meaning you get to pester the oakies afterwards). The conference agenda is up here, and I am lucky enough to have had two presentations accepted. These are

In Search of Lost Time:

This will be a practical biased introduction to and demo of performance tuning based on extended sql trace. If you've read Cary Millsap's book or heard of response time based tuning, but haven't really seen how it might apply to your day to day work, then you are the audience I have in mind. It won't be a rehash of Cary's book, it will attempt to show how you can apply this approach to performance tuning in real life.

Snatches of Eternity:

This is a suggestion as to how one might implement end to end tracing in the versions of Oracle currently in wide use for connection pooled and uninstrumented applications. Hopefully it will address a whole class of applications that are currently almost untunable. It will however require some experienced dba intervention in application setup.

Mogens normally press gangs me onto the server panel as well. A brief glance at the agenda suggests that Wednesday morning will therefore be somewhat intense for me.

In addition I now am co-chair of the Oracle on Windows special interest group and intend therefore to be around for the focus pubs on Monday night. Come see us. Suggest content for the SIG meetings in 2006 and even better volunteer to present. It may become an out of focus pub later on but there you go.

Monday night is also I think the night of the bloggers dinner that Mark Rittman suggested. As Mark is also a sig chair I think it is clear that this will happen after the focus pubs. I intend to be there, I'm sure Lisa Dobson of the Oracle Newbies blog, and an excellent SIG deputy chair will be there (we'll keep her supplied with alcohol rather than water just in case) and hopefully Tom Kyte might make an appearance as well.

I was intrigued by one of Cary Millsap and Tom Kytes favourite new features in 10.2 I thought I'd have a look at a 10.2 trace file. There surely is a lot more information in there. I have a table t1 which is just a copy of all_objects with no indexes etc. I ran select count(*) from t1; against it in 10gr1 and 10gr2. About as simple a test case as you can get.

The 10.1 output

PARSING IN CURSOR #9 len=23 dep=0 uid=61 oct=3 lid=61 tim=8536154153 hv=2648217100 ad='25729404'
select count(*)
from t1
END OF STMT
PARSE #9:c=20029,e=403533,p=343,cr=116,cu=0,mis=1,r=0,dep=0,og=1,tim=8536154147
EXEC #9:c=0,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=8536154621
WAIT #9: nam='SQL*Net message to client' ela= 5 p1=1111838976 p2=1 p3=0
WAIT #9: nam='db file scattered read' ela= 40330 p1=4 p2=5148 p3=5
WAIT #9: nam='db file scattered read' ela= 1646 p1=4 p2=5153 p3=8
WAIT #9: nam='db file scattered read' ela= 13053 p1=4 p2=5162 p3=3
WAIT #9: nam='db file scattered read' ela= 765 p1=4 p2=5166 p3=3
...

WAIT #9: nam='SQL*Net message from client' ela= 19237 p1=1111838976 p2=1 p3=0


Now here is the 10.2 output


PARSING IN CURSOR #4 len=23 dep=0 uid=61 oct=3 lid=61 tim=881471072 hv=2648217100 ad='6cf05b48'
select count(*)
from t1
END OF STMT
PARSE #4:c=265625,e=1683003,p=544,cr=274,cu=0,mis=1,r=0,dep=0,og=1,tim=881471065
EXEC #4:c=0,e=75,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=881471221
WAIT #4: nam='SQL*Net message to client' ela= 5 driver id=1111838976 #bytes=1 p3=0 obj#=52566 tim=881471274
WAIT #4: nam='db file scattered read' ela= 28810 file#=4 block#=548 blocks=5 obj#=52566 tim=881500264
WAIT #4: nam='db file scattered read' ela= 1658 file#=4 block#=553 blocks=8 obj#=52566 tim=881502205
WAIT #4: nam='db file scattered read' ela= 16724 file#=4 block#=562 blocks=7 obj#=52566 tim=881526262
WAIT #4: nam='db file scattered read' ela= 1091 file#=4 block#=569 blocks=8 obj#=52566 tim=881527647
WAIT #4: nam='db file scattered read' ela= 321 file#=4 block#=578 blocks=7 obj#=52566 tim=881528277
WAIT #4: nam='db file scattered read' ela= 332 file#=4 block#=585 blocks=8 obj#=52566 tim=881528833
WAIT #4: nam='db file scattered read' ela= 32976 file#=4 block#=594 blocks=7 obj#=52566 tim=881562089
WAIT #4: nam='db file scattered read' ela= 2543 file#=4 block#=601 blocks=8 obj#=52566 tim=881565062
WAIT #4: nam='db file scattered read' ela= 10338 file#=4 block#=610 blocks=7 obj#=52566 tim=881575679
WAIT #4: nam='db file scattered read' ela= 6777 file#=4 block#=617 blocks=8 obj#=52566 tim=881582714
WAIT #4: nam='db file scattered read' ela= 1402 file#=4 block#=626 blocks=7 obj#=52566 tim=881584403
......

WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=52566 tim=887316187
WAIT #4: nam='SQL*Net message from client' ela= 40457 driver id=1111838976 #bytes=1 p3=0 obj#=52566 tim=887356685


So 10.2 has the timestamps for the wait events (as Tom mentioned), in addition the p1 to p3 data items get a meaningful description emitted where there is one.

Well of mice and small children.

I have a Microsoft wireless mouse. I have a not quite two year old boy called Sam. I have a toilet. The three have interacted in a perhaps predictable manner. Suggestions for a suitable replacement mouse (must work with Linux) are welcomed.

Just saw via orablogs that Scott Spendolini has moved on. HTMLDB is, in my view anyway, probably the must have new feature that Oracle have released in the recent past. Apologies to Sue Harper but I'd much rather folk used HTMLDB than JDeveloper for almost all normal size developments. This is partly a healthy disregard for java/j2ee, but partly a suggestion that HTMLDB has nearly all most normal organisations would want from an in-house dev tool. For those interested in terms I'm approximately defining normal organisation as <5000 employees, single country of operation and maybe <$500m turnover. People serious about data, but those that tend to buy business apps and do in-house development for custom apps only. (alternatively people like my employer though Im not always tempted to use the word normal to describe them).

Anyway I trust that this works well for Scott and he benefits from the change of emphasis. I hope that HTMLDB doesn't suffer too much from his absence.

two daft things today,

1. I don't have an iso utility that can extract files from an iso image. (long story - not looking for recommendations here; I just don't have one). So that is fire up vmware, use the iso of the SQLServer CTP that was just released as the cd drive, then copy the contents of the virtual cd drive (which was a file on the host) back to the host so as I am ready to install this. roll on the day when the os can mount an iso image as a cd/dvd drive. (tomorrow its vs.net 2005 RC all 3.5gb. )

2. Show developer around HTMLDB 1.6 explain cool features but slightly shoot myself in the foot by saying that I should really wait for v2 due any day now. Get home discover that HTMLDB 2 is here already (linux is here as well.)

This asktom thread

In which Burleson consulting employees (and a part owner) break their own guidelines in order to offer largely baseless attacks on fellow professionals.

Nope, not posts to forums from people who ban their employees from posting to forums. Not vendors explaining that 'we never have this trouble with SQL server', not designers^H^H^H architects saying that integrity will be handled by the n-tier xml based client, nope pretty much nothing can prevent my joy tonight.

The Ashes are home.

16 years we've waited. 16 years of second - or to be honest more often than not 8th - best. Not Now.

At the end of any series though, there are champagne moments and reflections. Here are mine.

Champagne moments

1st morning: Aus 190 all out at Lords - the moment we believed it could be done

Brett Lee in the second test -Please God not another McGrath

Ricky Ponting at Old Trafford - Yes we'd have had it won if it hadn't rained, but what defiance!

That Hoggard 4 at Trent Bridge - Who'd have known he had it in him

Pietersen today - Not so much grace as power under pressure

Dodgy decisions

Ian Bell ahead of Graham Thorpe. Why oh why?

Putting England in when McGrath has just stepped on a cricket ball - cheers Ricky

Putting Kevin Pietersen in any catching position anywhere - just say no

Trying to hit Warne out of the attack with 13 left to get and just the tail to come - take a bow Jones

Taking the light - twice - Hayden and Langer. I have no explanation

whingeing about substitutes - its whingeing poms Ricky, not whinge about the poms

Leading Lights

Freddie. England's man of the series. 14 overs on the reel for a quick. 400 runs at 40 24 wickets at 27.

Brett Lee. That man is seriously fast, seriously dangerous. To see him trying to kill Freddie in the fading light and then clapping arms around the opponent at the death. marvellous

Man of the Series.
No question for me. 40 wickets at under 20, 250 runs at 27. That innings of 90 that should, should have been a century. The fact that no-one, except just possibly Pietersen, had any clue about what was happening next. The man who very, very nearly won two test matches on his own. Shane Warne. Come back in 4 years mate - we'll give you stick, but when you are gone we'll tell our kids about the blonde magician. Its been an utter privilege to watch.

TUSC are offering free help and support to businesses directly affected by Katrina. This is an excellent initiative. Well done to Rich Niemic and the rest of TUSC.

There is a nice little example on the Daily WTF of how not to do things. Its a T-SQL example but neatly illustrates some common pitfals. Check out the responses here.

Lessons to pick up.

Use correct datatypes - storing numbers in chars. Bad Idea 1.

Use the features of the db, don't reinvent the wheel in the name of cross platform compatability. SQL Server has an identity datatype that would have avoided the code entirely. Bad Idea 2

Think about boundary conditions - do you want '00' to loop or be repeated for ever. Lack of thought 1

Think about error handling - them users are tricky so and sos. Lack of thought 2

In an article dated June, but published today Don Burleson is at it again - enlisting big names in the aid of advertising books. This time the name is Charles Phillips, president of Oracle Corp. The quote from a search Oracle article "Moore's Law is coming to an end ... we are not seeing the same price performance improvement we have seen in the past," Phillips said.

The 18-month rule where chip performance would double and prices would drop no longer applies as strictly as it once did, Phillips continued.

This seemed a little odd to me. First I didn't recall that Moore's Law had anything to do with price/performance - just number of transistors on a chip. Second, I just wanted to know what Charles Phillips had said in the quote that was missed out. Was it like those movie posters that read 'Brad Pitt's performance is amazing...' when the review stated 'Brad Pitt's performance is amazing, but can't save this turkey'.

So what does Moore's Law actually say? It turns out I was incorrect. You can read it here, here or here. In summary Moore suggested that over the period 1965-1975 the complexity of integrated circuits as measured by the number of components they contained would double every year. So it was a, remarkably prescient, piece of Crystal Ball gazing. Just nothing to do with pricing. (though costs get a mention).

as to my second concern, well who knows - Phillips keynote isn't available on-line, why open-source keynotes aren't openly available is anyones guess - it is, I suppose, just possible that he was joining the long line of people who have pronounced exponential growth in computing power dead for a while, more likely it seems to me he was promoting Oracle products to a fairly important audience, using a common misconception of a 'Law' is a handy rhetorical device here.

But back to this new article.

It quotes without noting the inaccuracy a misstating of moores law - googling moores law takes less than a second to determine the accuracy of the quote

It repeats a quote from a secondary source without any apparent means of determining its accuracy

It uses a chart with no vertical scale - who knows it could be frames/sec for the Adventure text game

Perhaps one is intended to draw the conclusion that the book being promoted is equally well researched and illustrated.

Just read Lisa's Oracle Newbie blog and saw she has some helpful tips for newbies. Tom Kyte had a similar set of suggestions on his blog a while ago. It got me thinking though. There ought to be a comparable set for experienced folk who take it upon themselves to offer help. I thought these might help. Newbie tips in Italics. Oldie Tips in bold.

State your problem clearly. On a forum I frequent you may get hit with a "Crystal ball broken today, please explain" if you don't state your problem clearly.

Ask for pertinent information - not a stack dump - you aren't employed by support

Explain what you want to do. Please include sample data and any errors you encounter.

explain why you are asking for more information. You know how it is when support just ask you to provide more traces when you've clearly explained the issue. Now you know how it feels.

Do not be offended when someone asks you what the business justification is. You may be trying to do something stupid, and we're just trying to figure out why.

Don't be offended when someone asks something apparently daft. They may be trying to apply their skills and knowledege to a situation they shouldn't. There are far fewer daft people, and far more rally odd situations than you have experienced.

Tell me what you've done already. Do you want me to assume you did a full backup before I tell you to drop a tablespace?

Read all of the posters questions and replies carefully. Maybe he already told you.

Please understand we do this for free. Sure, we learn how to solve problems in the process, but you get more out of this than we do.Getting upset with me just puts you on my ignore list.

Remember you do this for free and out of a sense of community. Acting like the grumpy old man from number 42 isn't what you got into this for. In any case, when you think you know more than the next guy - that's the day you stop learning

I don't care if your problem is URGENT!! to you. In fact, if you mark your message URGENT!!! I will probably ignore it. You DO have support, right?

When you decide to ignore something. Ignore it - don't reply only to say I'm not going to help you. How rude is that?

I guess a lot of what is being said to newbies boils down to, can you explain fully what you are trying to do, why you are trying to do it and why you think its a good idea? A lot of my advice consists of treat newbies with respect and care, think of the reputation someone well-known dbas have for frostiness and unhelpfulness, you don't want to add to their number right?

The only thing I'd add for the newbies is If you have an error message or error number, no matter how unintelligible it is - for goodness sake tell us.

Thanks to Rob Vollman I came across oracle-wtf tonight. Inspired by the Daily-WTF its an Oracle specific what on earth were they thinking site. It should probably be read side by side with AngryDBA.

Anyhow, this little example caught my attention. Why. Well a little while ago I came across this gem in a live system.


CREATE TABLE (table name removed)
(...
"MONTH" VARCHAR2(255),
"AGE" VARCHAR2(50),
"WEIGHT" VARCHAR2(50),
"SEX" VARCHAR2(255)
...
) ;


It doesn't suffer from the problem referred to in Oracle-wtf though and that last column has humourous possibilities.