Hi,

We are in the process of upgrading our system from:

6.1b.03.03 and Oracle 7.3.4

to

6.1.c.07.01 and Oracle 8.1.7

Everything seems to go well but after the database conversion our oracle performance goes down hill on many but not all sessions. Mostly seem to be item related.

Here is my db_resource file:

***START

dbsinit:01
rds_full:5
ora_max_array_insert:10
ora_max_array_fetch:10
max_free_cursors:20
retained_cursors:50
ssts_set_rows:1
bdb_max_session_schedule:6
ora_init:0111000
oracle_client_home:/opt/oracle/product/8.1.7
ora_column_format:7

***END

I tried the oracle7 driver but it did not make a difference. If I point my Baan environment at a 7.3.4 database it works fine.

Thanks

Mark

You'll need to remember to change your 'analyse stats' script to reflect the new Oracle installation. Or at least ensure table stats are still being analysed.

please post you init.ora-file

# $Header: initx.orc 7001300.3 93/06/16 12:28:26 mkrishna Osd<unix> $ Copyr (c) 1992 Oracle
#
# include database configuration parameters
#=========================================================================================
ifile = /opt/oracle/admin/baan4p/pfile/configbaan4p.ora
#compatible = 8.1.7.4
#compatible = 8.0.6
#compatible = 7.3.4.0.0
# -- rollback spread over 4 tablespaces on different disks
rollback_segments = (R01,R06,R11,R17,R02,R07,R12,R17,R03,R08,R13,R18,R04,R09,R14,R19,R05,R10,R15,R20)
#rollback_segments = (R01,R02,R03,R04)
#rollback_segments = (r0)
#
#
# CHANGED PARAMETERS - Tuning Note at eof numbered by change. Add changes to top of this
# section
#==========================================================================================
#
#
# BEGINNING TUNING PARAMETERS FOR ORACLE 8.1.7
# *** 8192 block size ***
#==========================================================================================
#cursor_space_for_time = false # MGH 8/12/03 For move to 8.1.7 -- Metalink
#cursor_sharing = exact # MGH 8/12/03 for move to 8.1.7 -- default is exact

_db_block_hash_buckets = 25600 #BBP 11/13/97 set same as db_block_buffers
db_block_buffers = 25600 # 200 MB KMK 08/12/97
db_block_lru_latches = 6
db_files = 400
db_file_multiblock_read_count = 8 # bbp 10/22/97
db_writer_processes = 6
disk_asynch_io = TRUE # TAC 100903 baanboard
dml_locks = 15200

global_names = FALSE

java_pool_size = 0 # TAC 100903 baanboard

log_archive_start = true
log_buffer = 1048576 # bbp 10/22/97
log_checkpoint_interval = 9999999999
log_checkpoint_timeout = 3600 # bbp 4/28/98
log_checkpoints_to_alert = true # rjh 1/28/98

max_dump_file_size = 20480 # limit trace file size to 20 Meg each #bbp 11/6/97
max_enabled_roles = 100
max_rollback_segments = 30

open_cursors = 5000
optimizer_features_enable = 8.0.6
optimizer_max_permutations = 1500
os_authent_prefix = ""
processes = 200

remote_os_authent = FALSE
resource_limit = TRUE

session_cached_cursors = 50 # bbp 10/22/97
shared_pool_size = 188743680 # 180 MB KMK 06/03/97
sort_area_size = 1048576 # 1MB bbp 10/22/97
sort_area_retained_size = 512000 # 500K KMK 8/14/97

#TRACE PARAMETERS
#timed_statistics = true # if you want timed statistics
#sql_trace = true # only for test
#user_dump_dest = /opt/oracle/admin/baan4p/udump

#TUNING NOTES:
#==========================================================================
# 4
# Baanboard.com - Oracle 8i performance solution
# 3
# METALINK Note: Init.ora Parameter "CURSOR_SHARING" Reference Note TAC 100803
# This parameter should only be set to FORCE in 8i when the risk of
# suboptimal plans is outweighed by the improvements in cursor sharing.
# eg: If you can answer YES to these 2 questions then
# CURSOR_SHARING=FORCE may be worth considering:
# 1. Are there a large number of statements in the sharedool
# that differ only in the values of their literals?
# and
# 2. Is the response time low due to a very high number of
# library cache misses? (ie: hard parses and library cache
# latch contention)
#
# 2
# METALINK Note:222132.1 TAC 100803
# max_rollback_segments = transactions/transactions_per_rollback_segment or 30 whichever is greater.
# transactions = session * 1.1; sessions = (processes * 1.1) + 5;

# 1
# METALINK Note:105518.1 TAC 100703
# it is recommended to set db_writer_processes equal
# to the number of LRU latches (db_block_lru_latches) and
# not exceed the number of CPUs on the system

how did you convert

bdbpre/bdbpost
exp/imp
oracle migration tool

import export

-special storage setting (first extent) ?, did you check the
number of extents of these tables with slow perfomance ?
-did you already run update statistics for tables and indexes ?

please try first:

lock_retry:0 #very important on unix !

ssts_set_rows:5
bdb_max_session_schedule:10


then in next step unset, if this will not help,

ora_columns_format:7

if you get problems with binary tables (ttadv334 ,...),
first export with baan-tools with old ora_columns_format
(with delimiter), than bdbpost without ora_column_format:7

We had the same thing happen to us. Oracle 7 memory requirements are 3 - 5 M per session but, in Oracle 8i it jumps to 15 M per session. You typically have 4-5 Oracle sessions that are spawned per user that is logged onto Baan. If you do the math, then it becomes apparent that you may need more memory. One thing that you can do to reduce per user memory is to reduce the sort_area_size from 1048576 down to 131072 (128K) because each session will consume an amount of RAM equivalent to setting for for sort_area_size. Ours was set too high and reducing it did not cause any performance problems. Check your paging activity because high paging is a good sign that memory is not adaquate. We ended up going from 8 G of RAM to 20 G of RAM on our IBM RS6000 system. Be sure to run STATSPACK and check the statistics to see what the highest wait stats are. Oracle 9i requires more memory than 8i does per session, so be prepared for that move as well.

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Thanks for the info. It seems that before we can go further we still need a clean database migration. Seems we are having problems creating indexes for the larger tables. It is taking hours to run the index create and then we get an error that we cannot add extents for temp. We have uped the allocated space several times and now we are up to 2 gigs.

Does anyone know a formula I can use based on the size of a table so I know how much temp and sort I will need to create the indexes?

Thanks:D

We had this problem and

optimizer_max_permutations = 256

solved the problem.

regards

Baan recommends that you set optimizer_max_permutations = 1500.