Baanboard.com

Go Back   Baanboard.com > Forum > Baan SIGs > Performance & Tuning

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
37%
Installation Wizard into new VRC
39%
Manual into existing VRC
3%
Manual into new VRC
21%
Total votes: 38

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 20th July 2003, 19:28
Caner.B's Avatar
Caner.B Caner.B is offline
Senior Member
 
Join Date: Aug 2001
Location: Turkey
Posts: 107
Caner.B is on a distinguished road
Baan: B-IV c3 Mcr0 - DB: Informix ,Oracle 9.2 - OS: Hp-Ux, AIX 5.2
Oracle 9i on AIX has poor performance

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?
Reply With Quote
  #2  
Old 21st July 2003, 03:02
tjbyfield's Avatar
tjbyfield tjbyfield is offline
Guru
 
Join Date: Jun 2002
Location: Sydney Australia
Posts: 356
tjbyfield is on a distinguished road
Baan: baan4c4 - DB: oracle 805 - OS: AIX 4.3.3
Oracle 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
Reply With Quote
  #3  
Old 28th July 2003, 11:24
Dikkie Dik's Avatar
Dikkie Dik Dikkie Dik is offline
Guru
 
Join Date: Sep 2002
Location: Netherlands
Posts: 585
Dikkie Dik is on a distinguished road
Baan: Triton 3.0 and higher - DB: All - OS: All
Some suggestions

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

Kind regards,
Dick
__________________
BTW: this post has been made on my personal view. My employer might not share my point of view.
Reply With Quote
  #4  
Old 21st January 2004, 18:13
torwin torwin is offline
Member
 
Join Date: Mar 2002
Location: England
Posts: 39
torwin is on a distinguished road
Baan: IVc4 - DB: Oracle 8.1.7/9i - OS: Solaris 2.8/AIX 5.2/HP-UX11
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
Reply With Quote
Sponsored Links
  #5  
Old 21st January 2004, 18:45
Caner.B's Avatar
Caner.B Caner.B is offline
Senior Member
 
Join Date: Aug 2001
Location: Turkey
Posts: 107
Caner.B is on a distinguished road
Baan: B-IV c3 Mcr0 - DB: Informix ,Oracle 9.2 - OS: Hp-Ux, AIX 5.2
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/s...&threadid=7665

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


Caner
Reply With Quote
  #6  
Old 21st January 2004, 19:03
torwin torwin is offline
Member
 
Join Date: Mar 2002
Location: England
Posts: 39
torwin is on a distinguished road
Baan: IVc4 - DB: Oracle 8.1.7/9i - OS: Solaris 2.8/AIX 5.2/HP-UX11
Caner,

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

Thanks again.
Reply With Quote
  #7  
Old 21st January 2004, 19:42
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,032
NPRao will become famous soon enough
Baan: iBaanERP-5.2a(Reger),SSA-ERP-LN-6.1,Infor LN-10.x - DB: Oracle-10g,11g,12c,MS-SQL - OS: HP-UX, Linux, Windows
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
--------------------------------------------------------------------------------
__________________
The art of perfection does not lie in doing extraordinary things but, doing ordinary things extraordinarily well. [-N. Prashanth Rao]
How To Ask Questions The Smart Way,BaaNBoard,NPRao
Reply With Quote
  #8  
Old 22nd January 2004, 07:48
Caner.B's Avatar
Caner.B Caner.B is offline
Senior Member
 
Join Date: Aug 2001
Location: Turkey
Posts: 107
Caner.B is on a distinguished road
Baan: B-IV c3 Mcr0 - DB: Informix ,Oracle 9.2 - OS: Hp-Ux, AIX 5.2
Torwin,

Our Oracle conf. file is attached.

Caner
Attached Files
File Type: txt ora_conf.txt (1.9 KB, 172 views)
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Anyone have any BAAN/Oracle security links petefinnigan Operating Systems & Databases 3 9th September 2003 23:45
Error installing B4c4 with Oracle 8.1.7 on NT syllepage Tools Administration & Installation 2 22nd July 2002 14:52
Baan on Oracle Guru for Hire Will travel OracleBaanGuru Jobs and Resumes 1 19th June 2002 16:57
How to prevent passing of hints generated by Oracle Driver ? baaniac Performance & Tuning 6 24th May 2002 12:16


All times are GMT +2. The time now is 23:22.


©2001-2017 - Baanboard.com - Baanforums.com