Posts from 2006

Cousin John called with best wishes for the New Year and just wanted to mention the Billy Connolly double CD. He was absolutely delighted with this thoughtful gift and was looking forward immensely to listening to it in the car.

I graciously accepted his thanks and reciprocated by thanking him for my M&S Men's Grooming Set complete with black leather carry case. I assured him that when we next meet, my nasal hair will be trimmed, my bushy eyebrows will no longer meet in the middle and my ears will be fluff-free.

Then he went and spoiled it by claiming 'Just one small problem, Norman. There were no actual CD's inside the case.' He kindly offered to send the empty case back so once again, I am condemned to make the trek into town, fighting with all the bargain hunters, to stand in a refund queue that spans 0.8 miles and requires external (armed) security guards to police.

As an interim measure, I asked him whether he could just enjoy the liner notes and look at the photographs until I manage to resolve the problem.

Norma was horrified. She immediately asked me to call Uncle Robert (lucky recipient of Tony Hancock's Half Hour audio CD). Unfortunately, Uncle Robert also ripped off the wrapping and expectantly opened the CD only to find an empty jewel case with a mysterious note attached: 'More in cupboard.'. He duly went to the cupboard but didn't find any CD's.

I told him this CD captured Hancock's greatest and most surreal work; the ultimate in silent comedy rather than a sick joke by us.

Regularly refreshing Oracle statistics on all tables, indexes and column histograms flushes the shared pool and can occasionally lead to some unexpected and unpleasant surprises.

Thankfully, Oracle 10g automatically maintains 31 days of statistics history which means it is trivial to revert to a previous set in order to restore service to the production environment while you investigate further.

A perfectly valid strategy for statistics gathering is to gather, test, validate, save and leave well alone.

    SQL> select dbms_stats.get_stats_history_retention from dual;

    GET_STATS_HISTORY_RETENTION
    ---------------------------
    31

    SQL> select dbms_stats.get_stats_history_availability from dual;

    GET_STATS_HISTORY_AVAILABILITY
    ---------------------------------------------------------------------------
    14-NOV-06 10.26.57.421000000 AM +00:00

    SQL> exec dbms_stats.restore_schema_stats(ownname=>'SIEBEL',as_of_timestamp=>trunc(sysdate)-7)

    PL/SQL procedure successfully completed.

Sorry Sir but I havent done my homework because I have really bad stomach ache.

How the big clubs approach the problem...

Manchester United - Alex Ferguson chooses to invite Jose Mourinho to the boardroom to share a bottle of expensive Portuguese wine. Pointedly applaud United fans who chant 'Your coat's from Matalan' on the way to the tunnel. Then shut up and assemble a team that actually looks capable of challenging Chelsea.

Liverpool - Appeal to the House of Lords, the Citizen's Advice Bureau and the European Court of Human Rights after Mourinho has the audacity and bare faced cheek to put his finger to his mouth after a Chelsea goal in the Carling Cup Final. Hold a annual 5 minute silence at Anfield for all those fans suffering post traumatic stress disorder as a consequence.

Arsenal - Arsene Wenger is so busy abusing match officials and getting embroiled in fisticuffs with small-time managers of small-time clubs, he forgets all about the mind games. Go to the back of the class.

Everton - Take legal advice after Mourinho accuses your centre forward of falling over a little too easily. Go running to your mummy in tears when this fails and everyone laughs at you.

In the 1980s, some ladies chose to wear dark trouser suits and blouses in an effort to match their male counterparts in the workplace. This fad was known as power dressing.

One of my least favourite films is 'What Women Want' starring Mel Gibson. I also thought Kurt Cobain looked pretty cool in a dress and Dave Grohl managed to carry off wearing a bra (and a baseball cap) without looking ridiculous.

So, in the interests of equality and as a concerted effort to break down social and gender stereotyping, I decided to visit a client today wearing a dress. It started out pretty well with several wolf-whistles from builders on the way to the station.

However, it simply took me an age to arrange everything in the right place to get my photograph taken for my visitors pass and people tended to stare at me, look distractedly out of the window and avoid eye contact (more than usual).

The whole exercise was a very interesting experiment but, suffice to say, one that won't be repeated.

The disciplinary hearing is on Friday afternoon which also means I will miss the Christmas party. Sigh.

Tuesday - An empty black hold-all crashes to the floor from its precarious position on top of the wardrobe alongside miscellaneous Christmas packages.

Wednesday - A full length mirror falls and rotates through 180' from being propped up against the fireplace and sandwiched against a box of files. I tried to reproduce the path the mirror must have taken but there simply isn't enough room for the mirror to have fallen the way it did.

Thursday - A digital cordless phone in the kitchen spontaneously develops a faulty connection on the rocker switch and beeps incessantly. Yet another interrupted night's sleep.

Even more spooky, each occurrence happened at 03:58 precisely.

Time to call Yvette Fielding and get the Most Haunted crew around.

Oh aye Janice, listen. When my Dougie gave up smoking, the transformation was miraculous. He seemed like a new man. It was absolutely fantastic. His breath was fresh and clean. His teeth were gleaming white and he just seemed to have much more energy. Why, one day, he even smiled. And, Janice, let me tell you, my Dougie became a tiger in the bedroom, if you know what I mean.'

Some Scottish men were mistakenly prescribed Viagra instead of anti-smoking pills but some mean spirited pharmacist noticed just in the nick of time.

Finally, after nine years, we now learn that Princess Diana (Queen Of Our Hearts ©) was actually killed by a drunk driver who was driving too fast, in an effort to avoid paparazzi, through a narrow tunnel in Paris.

There was no MI5 conspiracy so the Daily Express no longer has anything to write about.

Jonathan Lewis poses a very interesting question:

Have you ever wondered how hard the optimizer has to work to produce a plan ?

Well actually Jonathan, curiously enough I have. Many times, in fact.

Siebel 7.7 (released in 2004) was the first version of Siebel to support the Oracle cost based optimizer (CBO) and pleased a lot of demanding Oracle DBA's who looked contemptuously on the 10 year old rule based optimizer (RBO) technology and had long petitioned Siebel to finally add support for CBO to improve performance, support table partitioning, parallel query, bitmap indexes and lots of other features they wanted to use (i.e learn about).

Inevitably, this major architecture change brought improvements in performance and was generally welcomed by new customers. However, the use of CBO also introduced some new issues for existing, long-standing Siebel customers who upgraded to 7.7 from previous releases.

For example, if you dug very deep with tkprof and ran comparative tests, it was common to observe that parse times were increased with CBO. This was because versions of Siebel prior to 7.7 used the rule based optimizer which follows a fixed set of, 15, err, rules to determine the query plan to be used. Consequently, parsing in RBO is deterministic and relatively quick.

Academics proved that it was actually possible for a well-trained monkey to learn and recite the 15 rules and accurately predict the query plan that Oracle will select. Clever Siebel 2000 developers discovered they could influence the chosen query plan by reciting magic incantations, altering search specifications, sacrificing lambs, nullifying the use of unhelpful indexes and even modifying the ordering of WHERE clauses in the SQL statements.

However. as Siebel finally moved into the 1990's and embraced CBO, things suddenly became very different. The strategy for CBO is to simply give the optimizer as much data as possible so the optimizer can make the 'best' decision regardless. Talented Siebel developers everywhere quaked in their boots and now sacrificed their lambs, praying for a contract renewal.

So whenever Siebel customers complained to me that parse times were too high, I was often tempted to ask them:-

'How many pages of A4 did it take to print out this problematic query with the high parse time ?'

'Err, 16.' 'How many tables are joined ?'

'Well, err, 38.' 'And how many outer joins are in this query ?'

'Well, err, 12.' 'How many indexes are defined on those 38 tables ?'

'Well, err, 189 ?'

'So, if I gave you detailed statistics on those 38 tables together with even more statistics on those 189 indexes together with frequency histograms of all of the columns involved, how long would it take you to parse that query and tell me the best query plan to return the data as quickly as possible ?'

'Well, err, I'm not sure'

'And how long do you think it would it take you to write a computer program to crunch all that data and return the best query plan ?'

'Well, err, I really don't know'

'So do you still think that 3.4 seconds is really too long to parse this statement for the first time and sub-second thereafter ?'.

'Well, err no, I guess not'.

Now I am lucky enough to work for Oracle, I have visibility to a wide variety of complicated CBO related bugs including detailed analysis from Technical Support and Engineering of 10053 trace files.

Consequently, I have nothing but complete respect for the software engineers who develop, maintain, fix and seek to improve the Oracle cost based optimizer.

[ I can thoroughly recommend Jonathan Lewis' book as an excellent introduction with plenty of examples to illustrate how the CBO works. ]

I was convinced I had my address book quadruple-mirrored (Thunderbird, Palm, Gmail and ScheduleWorld) secured on resilient disk storage using RAID-5 with offsite backups.

However, when I came to start writing my Christmas cards, this assumption proved be to false. I think something must have gone awry during my disaster recovery testing in the summer.

Anyway, if you can remember aunties name, street name and town, you can obtain the correct postcode from the Royal Mail.

Then, armed with the full postal address, you can (probably) obtain her phone number from British Telecom so you can thank her for the thoughtful present in January.

Finally, if you bribe a call centre employee (or root around in the bins outside a major UK bank), you can also obtain her bank and credit card details to find out how much she actually spent on you this year.