Hi everyone:

I got performance problems with my installation on an IBM using the AIX 4.3.3 :

There it goes the details:

O.S.: AIX 4.3.3
Database: Oracle 8.1.7.4
Baan version: Baan IV c2
Porting set: 6.1c.06.06
Concurrent users: 190

db_resource , initB4PRO.ora and listener.ora are below.

Before we have migrate the porting set from 6.1c.04.02 to 6.1c.06.06 and the performance not there has been any problem.

After we have migrate release oracle from 8.0.5.2 to 8.1.7.4 .

We do not have performance problems with the interactive sessions.

We do have problems with the batch processing sessions , in particular with timrp1210m000 ( the session during 50% plus )

Thanks in advance for all help .

Max

Listener.ora:
LISTENER =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= B4PRO))
(ADDRESS= (PROTOCOL= TCP)(Host= 148.70.140.22)(Port= 1521))
(ADDRESS= (PROTOCOL= TCP)(Host= 148.70.140.22)(Port= 1526))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU=16384) (TDU=16384)
(ORACLE_HOME= /Oracle/app/oracle/product/8.1.7)
(SID_NAME = B4PRO)
(ENVS='EPC_DISABLED=TRUE')
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 0
TRACE_LEVEL_LISTENER = OFF
init.ora
control_files = (/redo1/ctrl1B4PRO.ctl,
/redo2/ctrl2B4PRO.ctl,
/redo3/ctrl3B4PRO.ctl,
/redo4/ctrl4B4PRO.ctl)
background_dump_dest = /Oracle/admin/bdump
core_dump_dest = /Oracle/admin/cdump
user_dump_dest = /Oracle/admin/udump

db_name = B4PRO

rollback_segments = (r01,r02,r03,r04,r05,r06,r07,r08,r09,r10)

# TUNING PARAMETERS

disk_asynch_io = FALSE
db_files = 250
log_checkpoint_timeout = 0
log_checkpoint_interval = 320000
distributed_transactions = 400
processes = 2000
license_max_users = 20
license_max_sessions = 2000
license_sessions_warning = 1900
dml_locks = 1000
transaction_auditing = FALSE
transactions = 1100
max_dump_file_size = 10240
global_names = FALSE
open_cursors = 10000
REMOTE_LOGIN_PASSWORDFILE = exclusive
enqueue_resources = 5000 # 14/4/97

# Parametri da utilizzarsi per ctrl processi per attivita' di replica
JOB_QUEUE_PROCESSES =16
JOB_QUEUE_INTERVAL =60

# Parametri da utilizzarsi per archive log mode
log_archive_dest = /archlog
log_archive_start = true

# Parametri da utilizzarsi per attivita' di Audit & Trace
# audit_trail = true
# timed_statistics = true
# sql_trace = true

# Parametri da utilizzarsi per attivita' di Tuning

shared_pool_size = 300000000
shared_pool_reserved_size = 30000000
log_buffer = 4194304
db_block_buffers = 40000
db_block_size = 16384
db_file_multiblock_read_count = 32
sort_area_size = 5242880
sort_area_retained_size = 1310720

# Parametri consigliati da Oracle-Baan RoadMap
session_cached_cursors = 4000
cursor_space_for_time = false # per corretto funzionamento jobs
#event="10180 trace name context forever, level 1500"
tape_asynch_io = FALSE

compatible = 8.1.7.4
instance_name = B4PRO
db_domain = WORLD

optimizer_max_permutations = 1500
optimizer_features_enable = 8.0.0
optimizer_index_cost_adj = 10
optimizer_index_caching = 99

local_listener = dummy

db_block_lru_latches = 6
db_writer_processes = 2
db_resource (batch)
dbsinit:021
lock_retry:0
oracle_client_home:/baan1/B40/bse/lib/ora/oracle_home

The stuff you uploaded seems to be setup not too bad. Please see the Baan performance document (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=7665) for help on setting the db_resource file.

The tips for the db_resource will not gain much performance for you probably. Please test if Oracle connects on IPC level instead of TCP. This could lead to a bad performance.

I have some doubts about setting the folowing Oracle varaibles:
- db_file_multiblock_read_count. This is set to a value for datawarehousing. Please lower this to 8
- db_block_size. This is also set for datawarehousing. A pitty this can't be changed easy.

Also check if you did update all table statistics.

If none of this works, use Baan or Oracle tracing to find any queries that consume most of the time. When using Oracle tracing, plrease increase the maximum dump space.

BTW: Did you know you can run MRP on multiple CPU's. The document I mentioned earlier explains how to use this. Also tableboosters can be used (see the same doc).

Hope this helps,
Dick

The problem is in a routine of timrp5000 :


repeat
timrp030.sern = timrp030.sern + 1
db.insert(ttimrp030, db.retry, db.return.dupl)
until db.error() <> edupl

commit.transaction()

We have change in :

select max(timrp030.sern):sern.c
from timrp030
where timrp030._index2 = {:cmrp.item}
selectdo
sern.c = sern.c + 1
selectempty
sern.c = 1
endselect

Now is all OK .