Oracle DB Environment Parameters for Siebel

Debugging performance issues is probably the most common activity for any ‘decent’ Siebel installation. The process for this is quite methodical -

Check logs
Retrieve SQL
Run SQL while connected to database using any of the DB tools
Hit your head against the wall, and repeat steps

There are only a few tools to help you simplify the process, but the activities are not really automated completely. One of the oft repeated mistakes that Siebel newbies do (if Siebel newbies exist in this age) -

Run SQL in Oracle DB through SQLPlus / SQL Developer / Toad
See the performance drastically different (positive or negative)
Blame everyone right from Tom to Ellison, shrug their shoulders and wait for DBA to solve world hunger

If only they realize that Oracle DB is pretty consistent - regardless of what Siebel will do.

When you open SQLPlus / Toad or any of your favourite tool for Siebel query debugging, the first thing you have to do is set environment parameters. These hints are used by Siebel to ‘optimize performance’, and the absence of those may result in different set of results for the same query.

alter session set optimizer_mode = first_rows_10;
alter session set "_hash_join_enabled" = false;
alter session set "_optimizer_sortmerge_join_enabled" = false;
alter session set "_optimizer_join_sel_sanity_check" = true;

These are the same magic words that Siebel application is going to use.

Now, you should see similar performance (caching not considering) from both Siebel application and when connected directly to the database.

Recent Articles