Siebel 77 and Oracle 9.2.0.6

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.

ANy advise will be helpfull.

have you traced the specific problems, see where time time being taken?

I know of a case where the DB is at 9i but the optimizer is still a 8i...because of similar issues.

Init.ora
======
db_name = "sblprod"
instance_name = sblprod
service_names = sblprod

control_files = ("/oracle/ora92/oradata/sblprod/control01.ctl",
"/oracle/ora92/oradata/sblprod/control02.ctl",
"/oracle/ora92/oradata/sblprod/control03.ctl")

open_cursors = 3000
max_enabled_roles = 60

db_block_size = 8192
db_block_buffers = 983040 # Changed 10/02 ver 4.0
#db_cache_size=805306380 # db_cache_size= db_block_buffers * db_block_size
#db_cache_size=905306380
#db_cache_size=1273741824
#db_cache_size=2073741824 # Changed 09/01 ver 1.0
db_writer_processes=8
#db_file_multiblock_read_count=64
db_file_multiblock_read_count=16 # chyanged 10/02 ver 4.0
Parallel_automatic_Tuning = TRUE

buffer_pool_keep = (buffers:158000,lru_latches:12) # changed 10/02 ver 3.0
buffer_pool_recycle = (buffers:294448, lru_latches:10) # changed 10/02 ver 3.0


NLS_SORT = BINARY
shared_pool_reserved_size = 944371840
#shared_pool_reserved_size = 188743680 # Changed 09/08 ver 2.0

cursor_sharing = EXACT
session_cached_cursors = 200

#shared_pool_size = 1643718400 # Changed 09/01 ver 1.0
#shared_pool_size = 1943718400 # Changed 09/08 ver 2.0
#shared_pool_size = 2443718400 # Changed 09/08 ver 4.0
shared_pool_size = 3443718400 # Changed 09/08 ver 4.0
#large_pool_size = 504857600
large_pool_size = 804857600 # Changed 09/08 ver 2.0
java_pool_size = 1600000

log_checkpoint_interval = 51360000
processes = 3200
log_buffer = 4048000

log_archive_start = true
#log_archive_dest_1 = "location=/interfaces/sblprod/"
log_archive_dest_1 = "location=/arch"
log_archive_format = arch_%t_%s.arc
#log_archive_max_processes = 2 #check

undo_management=auto # Changed 09/01
undo_tablespace=undo_tbs # Changed 09/01
undo_retention=7200 # Changed 09/01

background_dump_dest = /oracle/ora92/admin/sblprod/bdump
core_dump_dest = /oracle/ora92/admin/sblprod/cdump
user_dump_dest = /oracle/ora92/admin/sblprod/udump

remote_login_passwordfile = exclusive # Changed for upgrade
#remote_login_passwordfile = none

#os_authent_prefix = ""
#mts_servers=0

#compatible = "8.1.7"
compatible = 9.2.0
#sort_area_size = 259715200 # changed 09/08 ver 2.0
#sort_area_retained_size = 26214400 # changed 09/08 ver 2.0
workarea_size_policy=AUTO # Changed 09/01 ver 1.0
pga_aggregate_target=2147483648 # Changed 09/01 ver 1.0


#sort_multiblock_read_count=32 # obsolete
utl_file_dir=/interfaces

#optimizer_mode=Rule # Changed for upgarde Changed 09/11 ver 3.0
#optimizer_mode=CHOOSE #all parameter has to change if opt_mode=choose

# Oracle recomeded parameter to work with optimizer mode

optimizer_mode=CHOOSE # Changed for oracle upgrade
#hash_area_size= 419430400 # 2*sort_area_size # changed 09/08 ver 2.0
hash_join_enabled=true
optimizer_dynamic_sampling=1
optimizer_features_enable=9.2.0
#optimizer_index_caching=20
optimizer_index_caching=0 # changed 10/02 ver 4.0
partition_view_enabled=false
query_rewrite_enabled=false
query_rewrite_integrity=enforced
star_transformation_enabled=false

# Siebel recomended parameter for optimizer mode

#optimizer_max_permutations=100
optimizer_max_permutations=2000 # changed 10/02 ver 4.0
optimizer_index_cost_adj=1

timed_statistics=True

log_checkpoints_to_alert=TRUE
#db_block_lru_latches=70 # obsolete

#_system_trig_enabled=false # explicitly set fpr upgrade

db_files=350

job_queue_processes=2 # Changed for upgrade
#job_queue_processes=0

nls_date_format='DD-MM-YYYY'

#fast_start_io_target = 327680
lock_sga = TRUE #check may be obsolete
pre_page_sga = TRUE

resource_limit = TRUE
#EVENT = "10619 trace name context forever, level 1"
utl_file_dir='/interfaces','c:\temp'

Quote:

---

Originally Posted by davey23uk

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

Quote:

---

Originally Posted by pando

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 ??

Our Database is close to 1 terabyte

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

Quote:

---

Originally Posted by dbasupuser

(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.