hello
we are having a problem with baan performance. diferent sessions hangs and the performance of baan system is very poor. the oracle server procesor is 0% and with the debugger we see the execution stopped on select statements. if you make the same query in sql-plus, the processor activity increase and the query is executed in sql, but in baan the execution of the session stays stucked at the select.
the oracle statistics are good, and oracle people has looked at the system with no result. our porting set is 6.01.07.03.
db_resource:

#dbsinit:01
dbsinit:021
ora_column_format:7
ora_init:0101000
rds_full:2
ora_max_array_fetch:2
ora_max_array_insert:2
lock_retry:0

when the processes number decreases the system start to respond normally. we might be reaching some kind of limitation but we don't know wich one. as an example: the sales integration to finance goes like that: first n invoices takes 20-30 minutes per invoice to be integrated, from a moment on the system respond and the rest n invoices are done 10 per minute or so.

any idea about the way of investigate this?

thanks in advance.

Is it a general problem or session related? When did this start to happen?

If it's session related:
1. use TT_SQL_TRACE=0200 to find the slow query (or use ORAPROF=1.0 )
2. use SQL_TRACE=true and tkprof to find the slow query from the oracle side.

There is a solution avaliable which describes important oracle settings. Are you aware of that solution?

Regards,
Han

It is a general problem, but it gets worse when there are active sessions with heavy sql. the problem is that oracle is apparently "not aware" that there is requests to the data base and the processor is 0% executing. when it starts to work normally the processor increases its activity significantly.
we were having a look to baan solution nr 166049 and we changed db_resource parameters but it was worse (we had ora_max_array_insert:2 and we changed 1 and we put baan_oracle_prefetch:2) we didn't try hidden oracle parameters, that's next test.
we have recently growed a lot in company numbers, so it seems that we have something to enlarge but memory, space, everything seems to be much more than enough.

If you're on Oracle 7 as your profile says you shouldn't touch solution #166049.

you may be hitting some high end limitations as far as # of objects in the DB if you've "increased the # of companies" significantly.

Keep in mind that each Company brings with it ~2200 tables and each table has at least 1 index and possibly more.

Oracle 7 doesn't work well with large numbers of objects in your system. If you've got 10k objects then oracle will show a marked degradation in performance.

You could try to

1. Split your companies across multiple databases
2. Upgrade to 10g (still not great, but you can have more objects)


You should also check your oracle alert logs and trace files as well as do some DB monitoring for Locking issues, etc.

Dave

Sorry Dave,
My profile was not updated, (it is ok now) we use oracle 9 and windows 2003 server.

we have recently growed a lot in company numbers, so it seems that we have something to enlarge but memory, space, everything seems to be much more than enough.

True every new company creates about 2500+ tables . We found a way to this by sharing logical tables for all modules like tp , tr , ts etc which we dont use . And then new tables created for us are hardly 400 (lot of which are customized .)

So u too can do this . Delete all the tables of newly created companies after taking appropriate backups . Share tables in ttaad4120m000 , convert to runtime , restart baan and create tables again for those companies . This will reduce the substantial overhead from shared memory and baan logic service will be able to respond very fast giving very much better throughput .

PS - If u dont know which tables to share better consult ur functional expert before doing so .

we have changed ora_storage parameter (..\baan\lib\ora\ora_storage)
it was:
*:*:T:group:0214:MSGSIZE:PCTFREE 5 TABLESPACE BAANDB INITRANS 3
*:*:I:group:014:MSGSIZE:PCTFREE 5 TABLESPACE BAANIDX INITRANS 3

now it is :
*:*:T:group:0214:MSGSIZE:PCTFREE 5 TABLESPACE BAANDB INITRANS 3
*:*:I:group:0214:MSGSIZE:PCTFREE 5 TABLESPACE BAANIDX INITRANS 3

and the performance improved significantly.