Posts in category "oracle"

Siebel 8.0 is the first major release since the Oracle takeover and is now imminent.

Oracle are planning a simultaneous launch of five different products (JD Edwards, E-Business, PeopleSoft and Siebel 8.0) on 31 January 2007.

Ed Abbo (VP CRM products) will be chairing the Siebel session and SearchCRM has an interesting (albeit low quality) ~~interview~~ podcast with Ed where he talks about new features in Siebel 8.0, CRM OnDemand and the impact of the Oracle takeover on Siebel's development and strategy.

The Oracle Applications Users Group (OAUG) also has an excellent summary of the new functionality in Siebel 8.0.

IBM has published a useful whitepaper about tuning Siebel 8.0 on AIX 5.3. The paper includes benchmarks with different virtual memory page sizes and shows the benefits of simultaneous multi-threading. The paper also includes analysis of generic tuning features available in Siebel (connection pooling and threads per process).

The two key features that specifically interest me are the Siebel Diagnostic Console and the introduction of support for Linux (RedHat 4.0, SuSE 9.0).

The Siebel Diagnostic Console offers an Analytics style dashboard interface into the volumes of data produced by SARM.

Secondly, the number of Siebel customers choosing to deploy on Linux in the coming months will be interesting.

Another example of the closer integration between Siebel and Oracle is the recent announcement of a management pack for Oracle Enterprise Manager (OEM) in the recently released 10.2.0.3.

Oracle Application Management Pack for Siebel is a comprehensive solution for managing the configuration, performance, availability, and service level of Siebel CRM applications. It can be used to monitor the health of the servers and components, measure application response time, track configuration changes, and diagnose performance and execution problems.

This will be an invaluable tool for Oracle DBA's (who may be unfamiliar with the Siebel application) to easily monitor a Siebel OLTP instance and identify performance problems more promptly.

My. How times change. In my day, the philosophy was simple:

• Always be sick in your own time.
• Ensure a newspaper is safely cached behind the toilet cistern.
• Arrive and leave the office 30 seconds before/after your manager.
• Get an urgent pager alert whenever your round is imminent.
• Be quick to claim credit and divert blame.

But no. I have now finally discovered the true Oracle Contractors Philosophy.

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.

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

1. Tighten security for all parties by addressing this issue:

The Password must contain only ASCII alphanumeric characters.

1. The 'Quick Links to the Latest Patchsets' is a great idea spoilt only by the absurd complexity in navigating the resulting pull-down.

If you possess the hand/eye co-ordination and reflexes to successfully navigate to the correct platform and the desired version first time, you could probably make a fortune playing those impossible 'Win a cuddly lion with this crane like mechanism' kiosks at the fun fair.

Good evening. My name is Google Bot. It was a difficult (N-P complete) and very time consuming task to decide on the precise content for this article. After all, I have crawled 2.6 billion pages in the last seven days so I am a little tired. However, here is the pick of the blogosphere for November.

Oracle release 10.2.0.3 patch set (currently available on Linux only). This release includes several fixes related to the cost based optimizer. Siebel 7.8 customers using Oracle 10g should note the following bug fixes:

• 4370351 - High parse CPU (in qksfroInit) for large queries
• 4573396 - CBO does not account for chained rows
• 4711525 - Parsing of bitmapped plans can be slow
• 4772145 - Gathering statistics on large indexes is slow

Tom Kyte quietly releases a revamped version of the popular AskTom site. The new version is written in Apex and has a clean, fresh interface and looks very professional.

Over on his blog, Tom also writes an interesting article that really should have been titled 'YCNMIU'.

Doug Burns takes steps to re-establish his technical credentials by ejecting the cuddly toys from his blog and ceaselessly networking at UKOUG in an attempt to win friends and influence people. Inside sources confirm that Doug took this radical action after the soft toys were attracting more comments than Doug's technical white papers on parallelism.

Brian the Footballer emailed to say the toys were very upset and annoyed. Worse, Doug sentenced them to using Blogger Beta when he had a beautiful WordPress placeholder blog ready and waiting. Or he did, until he spitefully went and deleted it.

This sequence of developments all makes sense when Doug announces he has spurned a lucrative contract renewal and will be looking for a new role in 2007. Neighbours report hammering and expletives emanating from Doug's shed. Our spies in Scotland claim Burns is preparing for a spell on the bench by carving one by hand. Made of solid oak.

Tim Hall has started a bulc-koob. This is like a book club but works in reverse. Most book clubs have 12 members and read 1 book a month. Tim's variation has 1 member and reads 12 books a month - all about vampires. Oracle sources are a little worried about Tim. Apparently at UKOUG, he was always dressed in black and only came out at night. He also looked a little pale and was never seen without a glass of cranberry juice.

At last. An Oracle blogger with taste in music. Unfortunately, Andrew Clarke spoiled the article with some dry technical content but never mind. Enquiring minds still want to know whether Andrew ever did get reunited with his lost socks and underpants though.

Ever noticed how plumbers always have horrible, dated, unfashionable bathrooms with dripping taps. Well Mark Rittman is a well respected consultant on all things to do with Oracle and Business Intelligence. However, Mark naively assumed his hosting provider might actually backup his data for 10 quid a month but no. Unbelievably, they managed to lose his data. All of it. 700 articles. And no, he didn't have a backup. Thankfully, all articles was restored from various sources. Some people would do anything (12 hours of mindless cut/paste) to get back on his blog roll.

And finally, Jeff Hunter inadvertently starts a frank exchange of views with Sheeri Kritzer over the pros and cons of mySQL. Ah - just like the good old days. The flame wars on Usenet, watered down and transferred to the blogosphere.

Peace, love, empathy.

I had the misfortune to visit a very angry customer today. He was having problems loading customer data into Siebel. I sat him down with a nice cup of tea and invited him to tell me all about his woes.

'Well Norman - it's like this you see. We used to be able to load 400,000 contacts per hour. Now performance is absolutely abysmal and it takes 12 minutes to load just 1,000.'

'Hold on. What exactly have you changed since you last loaded 400,000 per hour.'

'Nothing. Nothing at all.'

'Hang on. When were you last able to load 400,000 per hour - exactly ?'

'Err well. It was back in February, you see.'

'And you expect me to believe that you have changed nothing since then ?'

'Yes.'

'OK. I want hard copies of all change controls raised against this system since February and access to Visual Source Safe. I also want the DBA and Siebel administrator here. Now.'

'We haven't got any change controls. We don't use configuration management and I am the DBA, Siebel Admin, systems admin, Web server guru and I also make the tea. We are a small company, you see.'

'I see. That's fine.' (sigh and roll eyes upwards). This could be a long day.

And so the detailed analysis commenced. The control file (IFB) used to load the data. The key Oracle parameters. The hardware of the various servers. All identical to the configuration used back in February.

And he did load 400,000 records per hour. He had the logs to prove it. And now he could type the data in quicker. I know because I saw it with my own eyes. A real mystery.

Siebel's data loading utility is called EIM and uses CBO so time to check the statistics. This client uses Oracle 10g so statistics are automatically gathered by default (essentially gather stale) so all tables have current statistics.

Hold on a minute. All tables apart from EIM_CONTACT and a load of others.

'Why have you dropped statistics on EIM_CONTACT ?'

'We haven't.'

'Oh yes you have.'

'Oh yeah. I remember now. I was playing with a custom script to drop stats on all empty tables. Siebel told me to do this because it affects performance (Alert 1162).'

'Oh I see. How very interesting.'

It transpires that because the interface table is (correctly) truncated post-load, the statistics subsequently got deleted by this script. Kudos to this customer though for humourously naming the script 'dropem.sql'.

Quickly compute statistics on EIM_CONTACT and indexes and performance is miraculously restored.

Well almost. The client forgot to tell me that he had dropped unused indexes on the base tables to accelerate the data load still further. The 'ddlsync' utility that synchronises the Siebel Repository with the physical Oracle schema had subsequently been run which reinstated all the indexes slowing performance down slightly. The indexes are dropped and the original throughput is restored.

Well almost. The client forgot to tell me he had enabled mobile web clients as an experiment for Olof Ericcsson (VP Sales - Scandinavia). So we disable remote transaction logging to eliminate that minor overhead. Throughput is now restored.

So I said my goodbyes and left for the long drive home.

Only, on this occasion, the journey home was very short.

On this occasion, the client happened to be sitting at my kitchen table.

On this occasion, the rack of expensive application/Web/database servers were actually all co-hosted on my very own laptop.

On this occasion, the client who made all the mistakes was me.

Google Co-op is a customisable Google search engine and I just created a personalised, custom search for Oracle that scours AskTom, Jonathan Lewis' site and www.oracle.com (including TechNet).

This targetted search is useful and will undoubtedly save me time. However, I was unable to get any results from 'tahiti.oracle.com' (Oracle documentation set) which is normally my first port of call. Also, I assume Metalink is similarly off limits (account required).

Modern versions of Siebel use the Cost Based Optimizer (CBO) so the strategy used for gathering (and refreshing) statistics and histograms becomes increasingly important.

Sometimes, the strategy can be articulated by the Oracle DBA or may even be documented or SQL scripts may be supplied that define the strategy.

Normally, however, the definitive version of the truth lies in the data dictionary.

    SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'PERSON',cascade=>TRUE,
    method_opt=>'FOR ALL COLUMNS SIZE AUTO');
    SQL> select column_name,
    to_char(last_analyzed, 'mm/dd/yyyy hh24:mi:ss') as last_analyzed, num_nulls,
    global_stats, user_stats
    from user_tab_col_statistics where table_name='PERSON' and column_name = 'SALARY';
    COLUMN_NAME         LAST_ANALYZED       NUM_NULLS   GLOBAL USER
    SALARY              10/23/2006 14:55:15    0         YES       NO
    SQL> exec dbms_stats.set_column_stats(ownname=>user,tabname=>'PERSON',colname=>'SALARY',
    nullcnt=>1000000);
    SQL> /
    COLUMN_NAME         LAST_ANALYZED       NUM_NULLS  GLOBAL USER
    SALARY              10/23/2006 14:55:18   1000000   YES       YES

So when trying to answer to question: 'Have you ever had cause to manually populate column statistics ?', although the Oracle DBA is your friend, the USER_STATS column in the DBA_TAB_COL_STATISTICS view is your best friend.

I have never heard customers complaining about the abysmal performance of the Oracle pseudo-table DUAL. Nor have I ever encountered a real-life performance issue that was attributed to slow access to DUAL. Rarely have I been asked to tune an SQL statement that was sub-second and took a massive 3 consistent gets.

Another reason I won't be using this tip is that, a few years ago, I did encounter some unexpected, weird, obtuse behaviour in an Oracle system that completely baffled everyone. This was eventually tracked to the presence of not one, but two rows in the DUAL table.

Better to let sleeping dogs lie.