Siebel 77 and Oracle 9.2.0.6

[tamilselvan]

1. SP size
Shared Pool 3 GB is too big. There is no need for this much size in Siebel because Siebel always uses bind variables. You rarely find "literal SQL" in Siebel. More over, Siebel does not use PKG, Procedure and Function. If you set SP too big, then the freelists for the shared SQL statements with in the SP will become longer and longer. You will notice performance degradation over a period of time. This is the main reason, Siebel and Oracle recommends to flush the SP every day night.

2. Optimizer_max_permutations =2000
I know Siebel query involves joining more than 40 tables in 7.5 release. In RBO, that may not be a problem. Because there are only 15 access paths, RBO will consider. But, Siebel 7.7 uses CBO and setting max value of 2000, Oracle may not produce optimum (best) execution plans for a query involved 40 tables joining. By lowering the value, you just descrease the parse time.
Have you tested your system with higher value for this parameter?


3. log_buffer and log_parallelism
I would prefer to set 1 mb for log_buffer and 8 for log_parallelism. Both the values gave me good performance in Siebel 7.5.

4. How many user connections do you anticipate in your system?
Watch out PGA usage.

If possible, post here statspack report with interval of 5 min.

Tamil