30 second guide to tuning Siebel

Bottom up

Identify resource intensive SQL statements using Statspack (ADDM, custom scripts, Spotlight). Siebel is a black box that pumps out lengthy, complicated SQL statements with lots of (outer) joins. Level 7 snapshots are useful as ‘sprepsql’ may be used to reveal the full query text which is often tantalisingly truncated in the summary reports. In addition, the associated query plan for any statement can retrospectively dumped.

The DBA can determine which queries are executed and how many times. There is no point tuning a query that is only executed monthly whereas shaving fractions of a second from a fundamental query executed thousands of times may prove more worthwhile.

30 second guide to data warehousing

Many years ago, my horrid manager refused me a wonderful opportunity to go on an all expenses paid training course all about data warehousing in some exotic location.

I was moaning about this to a colleague over lunch. She was an ex-teacher and happened to work in the prestigious data warehousing consultancy group. ‘There, there Norman. Don’t cry. Tell me exactly what you wanted to learn from this course ?’

Rule is dead, long live Rule

A long day tuning SQL queries using Siebel 7.8 and Oracle 10gR2…

We used the Siebel recommended settings (TechNote 582). We used the Oracle recommended settings.

We gathered table statistics.

We gathered index statistics.

We gathered column histograms.

We dropped statistics on empty tables (Alert 1162).

We set some miscellaneous (magic) underscore parameters to encourage CBO to use the correct index.

We poured over 10053 trace files.

We used a 15 year old, deprecated, desupported optimizer technology to reduce a complex 27 table (outer) join query with a subquery to subsecond from an hourglass.

Read more

history of Oracle

A couple of people stumble across this blog searching for the ‘History of Oracle’ but ultimately go away disappointed.

For those people, there is a brief but interesting timeline (covering 1977 to 2001) detailing the development of Oracle Corporation in this freely available screensaver available from Club Oracle.

The screensaver is the one titled ‘Oracle Defining Moments - 25 Years of Technology Innovation’.

state of the database nation

A Gartner/IDC report summarising the state of the database market in 2005 contains some interesting nuggets of information.

The database market is still growing at 9.4% (which surprised me a little).

OpenSource databases account for less than 1% of the market but are growing fast (47%).

The Linux platform (thanks mainly to Oracle) is showing the strongest growth (84%).

Despite these two statements of fact, Oracle are not perturbed by the threat of OpenSource (pass the salt cellar).

bits and large pieces

Oracle have an interesting collaborative project (MegaGrid) to develop a very large grid database designed to stretch technical and infrastructure resources to the absolute limit.

I also gathered from the informative US news media that a cat had been trapped up a tree in a Seattle suburb and that (shock, horror, gasp) the US Government has secretly been tracking all phone calls since 9/11.

This appeared to be a big, breaking news story but seeing as US immigration took the trouble to take my fingerprints and photograph on entry to (and departure from) the US, I honestly didn’t think that the Homeland Security department would simply discard this data.

further afield

I am going on a training course about Oracle RAC at Microsoft’s offices in Redmond (near America) so I get to wear chinos and a polo shirt and play with an Xbox in reception.

In fact, it’s even better than that. This course is actually described as a ‘Technical Hands-On Workshop’.

My only previous exposure to RAC was a few years ago, rote learning the Oracle manuals parrot fashion for an interview at a large UK Telco. The interviewer was an experienced, senior Oracle DBA and, inevitably, much to my embarassment, he found me out almost immediately.

axe murderer

A couple of years ago, I was working in Amsterdam (near Holland) chasing world records for loading lots of data into a Siebel database.

It wasn’t working very well. The client was unhappy so long hours were called for.

The Unix team said the expensive SAN was performing optimally.

The DBA team said Oracle was performing optimally.

The Siebel team said Siebel was performing optimally.

Alex Ferguson unexpectedly joined a conference call and said the Manchester United team were performing optimally.

new month, new job

I now work for Oracle based in Thames Valley Park, Reading in the UK.

I am usually the last to hear any news about corporate takeovers.

Does this mean I will have an Oracle sanctioned blog named ‘Siebel Evangelist’ ? Probably not.

out of the closet

My name is Norman Brightside. I work for Siebel Systems based in Egham in the UK. I am a Senior Architecture Specialist in Expert Services. Expert Services provide a range of consulting services from architecture workshops to performance troubleshooting.

My work is technical (usually includes Oracle), varied, includes travel to Siebel customers located within the UK and Europe and perhaps, most importantly, involves meeting interesting people and learning.