Hi All,

Following are the optimizer related parameter which needs to be tuned for Optimal Performance of Oracle 10g database with Siebel applications. These parameter play a very important role in Siebel application performance, as they influence the access paths, choice of physical joins, selectivity and cardinality. Siebel is CRM product and focuses on retrieving FIRST_ROWS_N as soon as possible. Whenever Siebel want throughput for a sql it passes +ALL_ROWS hint in sql which overrides the instance level parameters. Below are the parameters and values which worked for most of the environment.

1- OPTIMIZER_MODE=FIRST_ROWS_10
2- OPTIMIZER_FEATURE_ENABLE= lateset patchset version
3- OPTIMIZER_COST_ADJ=10
4- OPTIMIZER_INDEX_CACHING=60
5- OPTIMIZER_DYNAMIC_SAMPLING=2
6- OPTIMIZER_MAX_PERMUTATIONS=100
7- CURSOR_SHARING=EXACT

It's quite late, rest i will post later.

Regards
Gaurav