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.

The disadvantages of this approach are that the bind variables and username who issued the query are not available which may be important. Secondly, it is not obvious which Siebel screen is associated with the problematic SQL statement(s).

Middle out

Increase Object Manager logging to dump the SQL statements together with timing information for prepare, fetch and execute. Search the log files for any SQL taking more than one second. The advantage here is that the username for the session. the sequence of screens visited and all bind variables are included so it is easier to reproduce in SQL*Plus.

Patterns of usage and resource intensive queries should start to appear. You may even identify groups of users (by job role or geographic location) who have different types of problems.

Top down

A little radical for most DBA's (developers, integrators and highly paid consultants for that matter). Seek out the users, sit down and talk with them.

Watch how they actually use the application for 30 minutes. This is often most enlightening. You witness first hand how users actually use the system. Sometimes it turns out that they are not always using the application efficiently (for various reasons).

You can see the typical business scenarios, the frequently used views (tables), the type of searches users specify, what they like, what they hate, what is fast, what is slow. In fact, your visit may well end up lasting a lot more than 30 minutes.

Or you may need a combination of all three.

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 ?'

'Well Sue, I just feel so stupid. I don't even know what a fact table is, or a slowly moving dimension let alone a star schema - all that fancy data warehousing terminology'.

'Shut up and listen. You buy a sandwich in Tesco. The sandwich costs 2.55 GBP. You have a table called TRANSACTION with a column called PRICE. There are other tables called PRODUCT, REGION, STORE, DATE and CAMPAIGN. There are a load of foreign keys from the fact table to the dimensions and the data model is highly normalised.'

'The TRANSACTION table is a fact table because it records a fact - an event that actually happened. Fact tables tend to be large. Just think of all those massive queues for all the the checkouts at all the Tesco stores.'

The other tables are called dimensions - these tables tend to be smaller and describe elements of the business and allow managers to report on sales by product/region/store/campaign/month/year/quarter.

'Oh I see but what about a star schema ?'

'Draw a picture with the fact table in the middle and the dimension tables around the edges. Connect the tables together. What do you see ?'

'Oh I see. A pretty star. OK then. What about a snowflake ?'

'Draw 7 stars and join them up. What do you see ?'

'Oh I see. A lovely snowflake. Thanks a lot, Sue. That really has been very useful.'

'No problem. Data warehousing isn't actually that hard.'

'Now what is the Pareto Principle ?'

Unfortunately, my helpful teacher suddenly remembered she had an urgent meeting to go to and the '30 second Guide to CRM' was postponed.

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.

We opened an SR with Oracle Technical Support.

We opened an SR with Siebel Technical Support.

We used a stored outline.

We went home.

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'.

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).

Market share:

1. Oracle - 44.6%
2. IBM - 21.4%
3. Microsoft - 16.8%
4. OpenSource (MySQL, Ingres) - 1%

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.

The US authorities must have some interesting infrastructure to store all this data. I wonder if their database exceeds the largest commercial database (Yahoo's 100TB data warehouse on Oracle).

I am going on a training course about Oracle RAC at Microsofts 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.

Much to my surprise, he still recommended me for a second interview. After the interview, I asked him about my lack of practical, hands-on Oracle RAC experience. He replied 'Not many people have it. With your background and attitude, you could pick it up quite easily'.

However, I failed to get the job. The manager thought I wouldn't be 'firm enough' with the Oracle developers. And you know what, back then, he was absolutely correct.

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 wasnt 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.

The truth was that nothing was performing optimally. We could have loaded data quicker by typing it in.

So, we were all scratching our heads, dying to get into Amsterdam, and yawning at 23.47 one night when a gentleman appeared with a bottle of red wine.

'Has anyone got a corkscrew ?'

Well it made a pleasant change from 'Is it finished yet ?'

'No'

'Oh. It's my birthday today and I'd like to share this bottle of wine with the team.'

Purely, in the interests of team morale, I took a decisive step forward.

'Just get a biro and sink the cork into the bottle. Then we can all have a quick drink and watch glance and OEM for another 3 hours.'

'Sorry. I do not understand what you are meaning.'

'OK. Just give me the bottle.'

I grabbed a biro, plunged it into the cork and pushed. Nothing happened. I pushed harder. People (including managers) were now looking at me, exchanging knowing glances (Mad Englishman). Nothing happened.

So, I pushed even harder. Nothing happened. I put the bottle between my knees and pushed even harder. Finally, the biro plunged into the cork and the cork consequently plunged into the bottle.

However the impact was slightly more forceful than I intended and I was sprayed with a fountain of red wine. My pristine white shirt only accentuated the visual impact.

I paused and gathered myself. Red wine was splattered all over my shirt. I looked a complete mess. People were sniggering which quickly developed into hysterical laughter.

I proffered the 3/4 full bottle of red wine, to the birthday boy, expecting some gratitude.

'Thanks. Have you got any cups ?'

So, instead of being known as 'that Siebel guy who helped us load 84 million records in 23 hours with error checking and reconciliation', I was forever labelled as 'the axe murderer'.

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.

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.