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
(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.
Then your execution plans are using poor indexes, either because that's all they've got to work with (no correct indexes) or the data distribution makes CBO believe other indexes are better.
Increasing buffer pool will not help it search poor indexes any better.
If you've got specific queries/bus fcns performing poorly, As Davey23uk said - trace those fcns - and see what it's doing. This identifies the specific sql. Use explain plan on that to see why. Then see what you can do to influence that specific sql to get a better plan.
"False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20
Reply With Quote
Originally Posted by davey23uk
Bookmarks