Just wondering if anyone has implemented siebel 7.7 with Oracle 9.2.0.6.
We are currently upgrading our siebel 7.0.5 on Oracle 8.1.7 (Rule based Opt) to Siebel 7.7 with Oracle 9.2.0.6(Cost Based Opt).
We have made several tweaking of the init param basd on Siebel/Oracle's suggestions and overall for the Cost opt. The Application overall is performing good except for some exceptions where the screen hangs and or take much longer than the 7.0 environment. The UAT(User Acceptance Test) is over and some users have concern with this environment when they compare to the old 7.0 environmment. It is very hard to convince the management that this would be a ongoing tuning process which might take a little while to get every thing comparable. I have seen in few forums that people have been using rule based OPT still with the 7.7 Optimizer or atleast using the optimizer_features_enabled=8.1.7 since the 9.2.0 is having some issues. Please advise any issues you had to address during the implementation.
have you traced the specific problems, see where time time being taken?
(Physical read/Consistent Gets)db file sequential read is where it is spending most times. We have increased the db_buffer_cache almost 1 & 1/2 and still planning to increase, since memory is not an issue on this Box. Please suggest if you see anything odd/obvious in the init.ora.
normally you trace the process and fix the SQL and not adding memory blindly
looking your init.ora there are some funny values:
none of your control file is multiplexed? that is basics you know, looking this, have you multiplexed your redo logs?
how big is your database? isnt you SGA so exaggerated? 7GB db cache, 900mb shared pool reserved size (what on earth would need 900mb shared pool chunk...?!), 3gb shared pool, you have TONs of PL/SQL codes and not sharing SQL? 800mb large pool, are you using MTS?
optimizer_index_cost_adj=1.... who told you to set that?
your utl_file_dir is duplicated, you should be using directory anyways
normally you trace the process and fix the SQL and not adding memory blindly
looking your init.ora there are some funny values:
none of your control file is multiplexed? that is basics you know, looking this, have you multiplexed your redo logs?
how big is your database? isnt you SGA so exaggerated? 7GB db cache, 900mb shared pool reserved size (what on earth would need 900mb shared pool chunk...?!), 3gb shared pool, you have TONs of PL/SQL codes and not sharing SQL? 800mb large pool, are you using MTS?
optimizer_index_cost_adj=1.... who told you to set that?
your utl_file_dir is duplicated, you should be using directory anyways
We are on SAN so multiplexing redo may not be required but controlfile yes we have to do that.
As far as SGA goes...if you see the gradual incremental values which was to support this application. Yes it needs that much of db_buffer_cache since we have tables with 180 million rows and more and have multi tables joins for most queries. It is doing a lot of outter joins and loops.
Optimizer_index_cost is strongly recomended by Siebel no exception. We have changed it to 100 and it degraded performance.
No we are not using MTS. You think Large pool is less ??
I agree 3 GB shared_pool is unusual but spreport kept reporting high reloads until we got it to 3 GB. I am not sure decresing the shared_pool_reserved size will do any good...unless you see it is required...also I have noticed everytime we flush the shared_pool these quries runs good.....Oracle recomended to have a cron job or dbms job to periodically flush shared_pool. ANy ideas why/how flushing shared_pool can make some querry run faster ??
We are on SAN so multiplexing redo may not be required but controlfile yes we have to do that.
Multiplexing doesnt only save you from hardware failure
As far as SGA goes...if you see the gradual incremental values which was to support this application. Yes it needs that much of db_buffer_cache since we have tables with 180 million rows and more and have multi tables joins for most queries. It is doing a lot of outter joins and loops.
When you tuned your db cache did you see many many many buffer requests?
Optimizer_index_cost is strongly recomended by Siebel no exception. We have changed it to 100 and it degraded performance.
It is recommended for ALL OLTP systes but normally you set it to 20 - 30 but 1.... is like saying index all my queries. Is there any Siebel doc which says this should be 1 and not any other value...?
No we are not using MTS. You think Large pool is less ??
If you dont have MTS why are you setting large pool for? If you use RMAN or Parallel Query (dont think so anyways) you dont need more than 16MB or so
I agree 3 GB shared_pool is unusual but spreport kept reporting high reloads until we got it to 3 GB.
You have a high parse/execute ratio? i.e few parses and high number of executions.
Are the SQL statements shared? 3GB fits like million of SQLs!
I am not sure decresing the shared_pool_reserved size will do any good...unless you see it is required...
Do you know what is reserved size for? It is used to reserve high chunk of memory for a large memory allocation. There is nothing which can be 900MB. How did you determine this is a valid value?
Also I have noticed everytime we flush the shared_pool these quries runs good.....Oracle recomended to have a cron job or dbms job to periodically flush shared_pool.
That is plainly bull**** ok. Oracle recommends the opposite
ANy ideas why/how flushing shared_pool can make some querry run faster ?
Because your SQL may run fine for same predicate but different values, for example
select * from X where status = 'A' is probably very fast but
select * from X where status = 'B' can be slow because 99% of the table has that sort of data
This shares same SQL PLAN but if you use the first plan for second query then your plan is bad. Assumming your SQL are being shared
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
Last edited by tamilselvan; 10-21-2005 at 03:30 PM.
Reply With Quote
Originally Posted by davey23uk
Bookmarks