Hi all,

Our previous system was BaaN 4c3 on HP-UX 11 with Informix 7.31. A migration is being carried to IBM AIX 5.2 App-DB Server system on which Oracle9.2 is installed.

The performance is worse than before! First suspicious is Oracle8 driver. Since a lot of new features came with Oracle9i version, how wise is to use an Oracle8 driver. (Porting set used is the most recent one: 6.1c.07)

We are, also, not sure about the effectiveness of the Locally Managed tablespaces (A lot of tables and indexes have their number of extents in the range 100-200). Besides, what about the UNDO tablespace? UNDO_RETENTION: 3 Hours caused the undo tablespace to grow up to 10 Gigs.

An interesting observation was that the batch executions caused the DB server cpu's to saturate (Used: 100% with just 15% for Wait I/O), nevertheless Application Server was at most 40% used. DB has 2 CPU and App has 4 CPUs.

An example for the poor performance is when a multi-occurence session is started in an idle phase of the system, First Record, Next Record, and Last Record choices resulted in a wait for ~10 seconds (as indicated by ORAPROF output). However when the query found in that output is directly run in sqlplus gave immediate result. A solution to that problem was the 'baan_oracle_prefetch:0' db_resource setting.

But now even if sthe ystem is not busy, to open an ordinary session (Display Package VRCs) takes some time. Tabbing between fields is also slow.

Are there any Oracle or Baan parameters that can help to the system performance?

We are very interested in moving to oracle 9i from oracle 805 and also moving to AIX 5.2 from AIX4.3.3. As such I do not have experience with your particular configuration but do have AIX/orcale experience.

It sounds as though you have two problems. The slow data-base retrieval of first/last record (DB server) and slow tabbing from field to field (BW client/Apps server)

There are a couple suggestions that may be worth considering.

Firstly the first-record (|<) and last-record (>|) problem is fairly common with our oracle setup when large tables are involved. Our symptons look the same ie: one cpu 100% and little i/o wait.
We try to get users to not use '|<' and have even disabled it on a couple of sessions.

Secondly, have you followed baan recommendations as to the oracle parameter settings for shared-pool-size, open_cursors and buffers etc.

You presumably have new storage devices. Have you got enough devices to have indexes, data, oracle-system, roll back and log table spaces on separate devices as well as the placement of AIX paging files and the baan system.

Anxious to know how you solve the problems

Terry

First things I am thinking about:
- have you analyzed the tables?
- What are current settings (init.ora, db_resource, tabledef and ora_storage(2))

Further:
- As it seems so simple to to prove that the system is running slow, please provide a ORAPROF=0.5 information or better SQLTRACE=true trace on some problem queries.

Further more I like to refer to the Baan Performance Guide (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=7665)

Kind regards,
Dick

I would be very interested in knowing if you have been able to resolve your performance issues. We are now in the process of upgrading our Baan IVc environment to AIX 5.2, Oracle 9i and are experiencing almost exactly the same symptoms. Specifically taking 4-5 minutes to open session tisfc1500m000.

Thanks

Torwin,

I had to update my post earlier that we solved our performans problems ,sorry for that.

Well I can say that we are now 8 time faster than our HP server and Informix with batch sessions like calculate cost prices , MRP etc... and also faster with other sessions.

We are using IBM Power4+ 1450 Mhz CPU's . And we recently updated to Aix 5.2 ML 2 . We don't have any performans problems with the OS and the server itself.

We solved our performans problems by changing Baan and Oracle parameters.

Here is the db_resource content,
dbsinit: 01
oracle_service_name:BAAN
oracle_client_home:/data_1/oracle/app/oracle/product/920
nls_lang:AMERICAN_AMERICA.WE8ISO8859P9
ora_default_tablespace:datspace
ora_temporary_tablespace:temp
ora_column_format:8
baan_oracle_prefetch:0
ora_init:0111000
ssts_set_rows:5
ora_max_array_insert:2
lock_retry:20*100,10*1000,5*1000


We did also changes on oracle like using raw devices instead of filesystems and using rollback segments instead of undo Tablespace.
I am still not sure if it was necessary to do this changes on the database but it works fine with us.

And I dont think that slow opening baan sessions are related with aix.

Also check for Dikkie Dik's thread and the pdf file further information.
http://www.baanboard.com/baanboard/showthread.php?s=&threadid=7665

If you want I can also send you our Oracle parameters we are using.


Caner

Caner,

Thanks very much for the information, please provide the Oracle configuration also.

Thanks again.

We had a similar performance issue, and we resolved with DBA's, Oracle information and with the help of BaaN Support.

Here is the detail information -

OPTIMIZER_MAX_PERMUTATIONS restricts the number of permutations of the tables the optimizer will consider in queries with joins. Such a restriction ensures that the parse time for the query stays within acceptable limits. However, a slight risk exists that the optimizer will overlook a good plan it would otherwise have found.

A value of 80000 efffectively corresponds to no limitation at all. Setting this parameter to a value less than 1000 normally ensures parse times of a few seconds or less.

Title: General Solution Error 107 on (Unknown) in Oracle Database
Author Kerr, Dave: 3569 Creation Date: 07/30/02 Alternate ID:
Solution No: 159170 Solution Version: Status: Published
Product Oracle-NT Support: 234 Sub Product Session: ora7_srv/ora8_sr
Package tt Version Release
Solution Type: KR: Failure External to Baan
Xref T:159170: General Solution Error 107 on (Unknown) in Oracle Database
Xref T:3518: Level 2 driver. Slow response of some sessions at startup. Oracle < V7.3.4.2
Solution description:
VERSION(S):
All

SITUATION IDENTIFIED IN:
Oracle

ATTACHMENTS:
N/A

SITUATION DESCRIPTION:
When starting a session or during a long running process the bshell errors out with: Error 107 on (Unknown)

Error 107 traditionally indicates a lock at the database level on a particular table.

For large joins the Oracle Optimizer can take a very long time to parse the query. For Oracle version 8i and below the default for maximum number of permutations is 80,000. For Oracle 9i this number has dropped to 2,000.

If the Optimizer takes too long in calculating an execution plan and returning the first record to BaaN, the driver will
time out and return an error Error 107 on (Unknown).

In Oracle 8 query execution and prefetch is a single step. So a query taking less time in execution and longer time
to bring the first row could get time out and result error 107 on (Unknown)

SOLUTION DESCRIPTION:

For Oracle 8 and above the first step to resolving this error is to add the
following parameter to your $BSE/lib/defaults/db_resource
baan_oracle_prefetch:0
* Note: This parameter requires porting set 7.1c or 6.1c.06.01 *
-OR-
increase your ora_timeout values for select for update
for example: ora_timeout:{300,120,120,120,120}

The first parameter baan_oracle_prefetch will split the query execution and fetch into different steps. So if the execution is fast and the fetch is slow you would not get an error 107.

The second parameter, ora_timeout, will cause the Oracle driver to wait longer for database to return the first rows.
-------------
If these parameters do not resolve the issue, there are some parameters in Oracle itself which can be set.
Each of these parameters do the same thing--they limit the number of permutations that the Oracle Optimizer will go through to generate an execution plan. Set the following parameters in
your init<SID>.ora file and restart your database.

For Oracle Versions greater than or equal to 8.0.5:
optimizer_max_permutations = 1500

For Versions greater than or equal to 8.0.4.2 but less than 8.0.5:
event = "10180 trace name context forever, level 1500"

For Versions greater than or equal to 7.3.4.2 but less than 8.0.4.2:
event = "10181 trace name context forever, level 1500"

There is no solution for this issue in versions below 7.3.4.2

EXAMPLE(S):
N/A

WORK-AROUND:
N/A
--------------------------------------------------------------------------------

Torwin,

Our Oracle conf. file is attached.

Caner