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 7th January 2002, 17:47
gguymer's Avatar
gguymer gguymer is offline
Unconfirmed User
 
Join Date: Aug 2001
Location: Lufkin, Texas
Posts: 194
gguymer is on a distinguished road
Baan: Baan IVc4 SP12 - DB: Oracle 8i, 9i - OS: AIX 4.3, 5.3
Thumbs up Oracle 8i performance solution

After upgrading to Oracle 8.1.7 from 7.3.4, we started having major performance problems. Queries that took minutes started taking hours both in the Baan and native Oracle environments. I had a suspicion that the Oracle optimizer was at the heart of this and it was. I'm not sure what those changes were, but they definitely had an impact on existing queries. After looking through Oracle Metalink issues regarding the 8i optimizer, I was made aware of an optimizer setting called "optimizer_features_enable", which by default, is set to the current Oracle version. We found that after setting it to "8.0.6", that our performance returned to normal for Baan and native Oracle. I know that the Baan/Oracle8 driver was compiled under version 8.0.6 and had heard that other Baan sites using Oracle 8.0.x were running OK, so that is why we tried that particular setting. Baan also recommends setting "optimzer_max_permutations" to 1500 and other Baan sites do too. Hope this helps.
Reply With Quote
  #2  
Old 7th January 2002, 18:08
patvdv's Avatar
patvdv patvdv is offline
Board Master
 
Join Date: Aug 2001
Location: Belgium
Posts: 2,167
patvdv is on a distinguished road
Baan: n/a - DB: n/a - OS: AIX, HP-UX, Linux
optimizer_features_enable

Hadn't heard about the optimizer_features_enable. Something worth checking. The optimizer_max_permutations definitively has a major impact on some queries as it avoids the Oracle optimizer to go into too big a loop. Default number of allowed permutations is about 80,000!

Some of our sites are running 8.1.7 with the optimizer_features_enable set to default and without any major issues. Thanks for the info though!
__________________
Regards,

Patrick Van der Veken - Admin & Founder - (c) 2001-2017 baanboard.com/baanforums.com
Reply With Quote
  #3  
Old 8th January 2002, 12:09
bherkend's Avatar
bherkend bherkend is offline
Junior Member
 
Join Date: Jan 2002
Location: Hannover
Posts: 21
bherkend is on a distinguished road
Baan: ERP - DB: Oracle - OS: UNIX
Setting the value to 8.0.6. includes the following features (PARAMETER of init.ora) of the optimizer:

index fast full scans (9i) (FAST_FULL_SCAN_ENABLED)
bitmap access path for tables woth only btree indexes
(B_TREE_BITMAP_PLANS)
complex view merging (9i) (COMPLEX_VIEW_MERGING)
push join predicate (9i) (PUSH_JOIN_PREDICATE)

Parameter was introduces in 8.0.4.

See Oracle Metalink Note 68462.1
Reply With Quote
Sponsored Links
  #4  
Old 16th January 2002, 18:31
P-Matt's Avatar
P-Matt P-Matt is offline
Member
 
Join Date: Oct 2001
Location: WI, USA
Posts: 42
P-Matt is on a distinguished road
Baan: IVc2 - DB: Oracle 8.1.6 - OS: NT 4.0
Unhappy Oracle Performance Tuning

I'm tagging to this original thread because I think the optimizer may be part of my solution, but I have many more questions.

Here is the scoop.

NT 4.0 sp5, BaaN IVc2, Oracle 8.1.6 - upgraded April 01 from 7.3.4
Sessions started to run slow after the upgrade. Baan Tech support had me run :

analyze table {tablename} estimate statistics sample 20 percent;

for all of my tables, then I ran:

analyze index <tablename>$IDX1 compute statistics;

for what appears to be all indexes on all tables.

We also changed the value for max_permutations to 1500.

That seemed to fix the system for awhile. However, the system locked up in December, and ever since that reboot (and several after that), sessions are geting slower and slower. I have run the analyze commands agan, but that didn't seem to help this time.

Anyone have any ideas for me? Baan Tech says to upgrade Oracle to 8.1.7, check for fragmented tables and indexes, and upgrade my porting set.

What do you guys think? There appears to be an awful lot of credible knowledge here.

Thanks in advance.
Also, does anyone know where can I find documentation on how to tune Oracle/Baan?
Reply With Quote
  #5  
Old 17th January 2002, 02:03
James James is offline
Retired Founder
 
Join Date: Aug 2001
Location: Bristol, UK
Posts: 158
James is on a distinguished road
Baan: Triton,Baan IV,ERP - DB: Oracle 7/8i/9i - OS: HPUX,Tru64,Linux
Hi P-Matt,

There could be many reasons for your problem.

Firstly, how often do you run the analyse stats? This needs to be run at minimum once a week (!) and if time allows run at 100%. A good optimum value is 40% (any higher than 40% then Oracle chooses to 'compute' stats at 100% anyway). Even after as little as 3 days, table stats can quickly become obsolete (for busy tables) - Oracle will recognise that the stats are no longer valid and may choose not to use the stats at all and opt for potentially time consuming table scans.

Secondly its normal that over time, sessions appear to go slower. Data is constantly being appended to tables, thus inserts and selects on these tables will be slower over time. Even more so if the tables become fragmented. Lots of companies look into data warehousing or archiving to purge old data from large/frequently used tables.

Db-reorgs should be performed quite regularly on a Baan environment. At least once a year, even twice. A lots depends on how the Oracle environment has been setup also. Are all your Baan company tables in their own Oracle tablespace? And the indexes, are they in another tablespace? You can try and dump the data for one Baan company, delete the tables in Oracle, then reimport the dump - this is a quick and easy way of removing fragmentation, but you must first make sure the ora_storage file is setup correctly. It is preferable that each table is loaded into 1 Oracle extent - for this you need to estimate the size of each table, then set the appropriate initial extent size in ora_storage.

What are you using as the storage medium? (RAID etc.) Have you eliminated any disk problems? And which sessions are running slow?

Is the performance problem consistent? Or just at month end/beginning when load is high and users complaining the most?

Upgrading to 8.1.7, or upgrading the Porting Set are unlikely to solve the performance problem - but can help with other things.

From an Oracle point-of-view, have a DBA check out stats, and fragmentation. For Baan, check the settings in the ora_storage and db_resource files - plently of info elsewhere on this message board regarding these files.

Email or PM me if you need more info.

Good luck.
__________________
James Gittins
(c) 2002 Baanboard.com
Reply With Quote
  #6  
Old 17th January 2002, 10:15
Martin Martin is offline
Senior Member
 
Join Date: Aug 2001
Location: Germany
Posts: 135
Martin is on a distinguished road
Baan: IVc, ERP5c - DB: oracle 9i, 10g, 11g - OS: Windows, HP-UX, SLES8/9/10/11
could be useful for performancel to recreate the indices for reorganize the index-tree. So do for each index : alter index indexname rebuild.
Could be done online.

Martin
Reply With Quote
  #7  
Old 17th January 2002, 10:43
Han Brinkman's Avatar
Han Brinkman Han Brinkman is offline
Guru
 
Join Date: Aug 2001
Location: The Netherlands
Posts: 1,155
Han Brinkman has a spectacular aura aboutHan Brinkman has a spectacular aura about
Baan: All - DB: Oracle/ms-sql/db2 - OS: *nix/windows
Fragmentation/rebuild

To my opinion you should check if the index tree is not so nice anymore. That could be the case if you e.g. have archived. If you only add new records to tables the rebuild doesn't help. On metalink you can find queries in order to get an advice if index rebuild would help.

One of my customers has exported/imported their 30Gb Baan Oracle database to get ride of the fragmentation. The fragmentation is gone however it did give us a better performance. What I understood from Oracle is that since 7.3.4 Oracle handles the extend information better so having fragmentation is not that bad.

Han
Reply With Quote
  #8  
Old 17th January 2002, 10:53
Martin Martin is offline
Senior Member
 
Join Date: Aug 2001
Location: Germany
Posts: 135
Martin is on a distinguished road
Baan: IVc, ERP5c - DB: oracle 9i, 10g, 11g - OS: Windows, HP-UX, SLES8/9/10/11
Hi Han,

the problem is not the fragmentation. If you have a lot of inserts in a table, also a lot of indices are created, so the b-tree of the index is split in a lot of chunks. Rebuild the index, recreates the indextree so Oracle could read the indextree faster.
If a rebuild of an index is usefull you could check as following :

analyze index index_name validate structure
then query the index_stats view :
select (del_lf_rows/lf_rows)* 100 as index_usage from index_stats.
if index_usage > 20% then recreate the index.

Martin
Reply With Quote
  #9  
Old 17th January 2002, 20:27
SRWSRW SRWSRW is offline
Newbie
 
Join Date: Aug 2001
Posts: 2
SRWSRW is on a distinguished road
Post

The only serious performance problem we hit 7.3.4 to 8.1.6 was the damned BAAN pdaemon.

This seemed to suddenly start eating as much CPU
as it had done with early versions of 7.3.4 before
we told it to mellow out people don't print stuff
that frequently.

Thanks to Anil and Peter for reminding me about
max_permutations.

We went to great lengths to effect this with Oracle 7.3.4 (Yes Oracle know how) but it disappeared after the upgrade.

A quick session trace suggests pdaemon might benefit from max permutations. Hmm there is a pattern developing here.
Reply With Quote
  #10  
Old 17th January 2002, 23:17
P-Matt's Avatar
P-Matt P-Matt is offline
Member
 
Join Date: Oct 2001
Location: WI, USA
Posts: 42
P-Matt is on a distinguished road
Baan: IVc2 - DB: Oracle 8.1.6 - OS: NT 4.0
Tuning this Monster

I would like to thank everyone for their input.

James: Admittingly, I do not run the analyze often enough. I was told to run this only when the system is not being used - which when my batch jobs run so long, is never. Can I run the analyze while other sessions are running?

How many extents on an index is too many?
I did what the Baan Tech said and did a reorg on one of the tables (tfgld419300) - the index for idx1 went from 17 to 19.
via the sql:
select segment_name,count(*) from dba_extents
where segment_type='INDEX' having count(*) > 5
group by segment_name;

Martin: When I issue the sql you supplied, I see an index_usage of ~1.345% on this particular table. Others came back with 0.

I think you and Han have a point with the index-tree thing.

Whatever happended, happened when the system locked up that night. I have a test server that has a duplicate of this database, and it is running fine. I'm trying to compare things, but don't really know where to start.

What a mess huh?

I am continuing to read my performance tuning sections of manuals...

Thanks again for everyones input and help.
Reply With Quote
  #11  
Old 18th January 2002, 08:56
Francesco's Avatar
Francesco Francesco is offline
Guru
 
Join Date: Aug 2001
Location: Antwerp, BE
Posts: 727
Francesco is on a distinguished road
Baan: 5b - DB: Oracle - OS: Solaris
Unhappy Baan Oracle

Solaris 2.6, Oracle 8.05, Baan5.0b, two tier

We have been (and are) suffering major performance issues for some time now. We tried just about every Oracle setting there is. Some seem to work for a short time, but eventually it all goes down the drain again.

Tired of fighting fires, we ended up throwing more CPU's and memory at our E10K. This gave us some air but Oracle is still not responding properly to Baan requests.

We are now experiencing a bunch of 107 errors, caused by time-outs in the communication between Baan and Oracle. Running the queries directly in Oracle works just fine. This leads me to supsicions towards the Baan Oracle driver.

Other performance nuisances are caused by Safari and Vertex. Vertex fragments the shared memory pool at an incredible rate. Constant flushing and pinning seems to have a positive effect on our system's performance.

I don't believe there is a silver bullet in these matters. It is a long and hard recursive road of observing, identifying and improving.

Perhaps we could all compare system settings. ha...ha...ha
__________________
Cheers,

Francesco
..............................................................

Admiral Business Solutions | My World | Baan Board | IT Happens!

"If everyone is thinking alike, then somebody isn't thinking" -- George Patton
"It's easy to cry 'bug' when the truth is that you've got a complex system and sometimes it takes a while to get all the components to co-exist peacefully." -- Doug Vargas
Reply With Quote
  #12  
Old 18th January 2002, 09:39
Han Brinkman's Avatar
Han Brinkman Han Brinkman is offline
Guru
 
Join Date: Aug 2001
Location: The Netherlands
Posts: 1,155
Han Brinkman has a spectacular aura aboutHan Brinkman has a spectacular aura about
Baan: All - DB: Oracle/ms-sql/db2 - OS: *nix/windows
Trying to answer two post:

P-matt: sure you can analyze online.

19 extents for a table is not that much. However if it went from 17 to 19 the reorg didn't help, did you modify the ora_storage before doing the reorg with help of the baan tools? You have to because otherwise it probably will not help.

Index usage seems all right to me.

You have to exclude things: is it a general problem, is it a problem of a particular session, if it is try to trace and check if the session uses a bad query.

Some thing to check which I remember by head of my latest performance issues:
Check your index optimization settings in the ora_storage: if you use level II it should be 0214, if not you can have strange performance problems.

The same applies if Baan has defined the references wrong meaning that a delete of a record results in a full table scan in another table in order to check if a reference exists. A bug we found in 5c a few months ago...

Run a SQL_TRACE of slow queries, run tkprof. A session that took 1 hour to show the results on the screen took only 30 seconds after I removed the statistics ... The analyze can have a bad effect also!

On batch jobs use ORA_MAX_ARRAY_xxxx, RDS_FULL settings. Try to run batch jobs in full host mode.

Francesco: of course quering the oracle db directly is more efficient than Baan does. Baan has to translate the baan sql to Oracle, read the Baan dd etc. That's the consequence of being flexible, i.e. being able to support Oracle/Informix etc.

I agree that most of the time there is not a golden tip to solve matters like these. However they can be solved in nearly all situations but it takes time.

Kind regards,
Han
Reply With Quote
  #13  
Old 18th January 2002, 09:44
Martin Martin is offline
Senior Member
 
Join Date: Aug 2001
Location: Germany
Posts: 135
Martin is on a distinguished road
Baan: IVc, ERP5c - DB: oracle 9i, 10g, 11g - OS: Windows, HP-UX, SLES8/9/10/11
I would give you same generell information about tuning ORACLE 8.1.x and BaaN:

It is very difficult to tune Oracle, because every Hardware constellation is different. So you need a software to view, what the database is done when Baan running. You could take Spotlight from Quest Software (very expensive) or take the new Oracle 9i Client. With the 9i Client a new Diagnostic Pack is delivered with a very god Performance Manager. You could get a lot of Reports, detailed Information for each SQl-Statement, Locks .....
This is the only way, to see what is happening in Oracle, when Baan is running.

Some tipps for Oracle 8.1.x :

Have you recreated the TEMP-Tablespace defined as "local managed"
Have you recreated Rollback-Tablespace defined as "local managed" ?
Have you more than one Rollbacktablespace and split the Rollback segments over this Tablespaces ?
In which order Rollback segments will be assign online in the initxxx.ora ? (RBS1,RBS2,RBS3 .... ) Better this way : 4 Tablespaces for Rollbacksegments, assign RBS1,RBS2 to TBS1, RBS3,RBS4 to TBS2, RBS5,RBS6 to TBS 3 and RBS7,RBS8 to TBS4.
Now change the Parameter in the initxxx.ora to :ROLLBACK_SEGMENTS=(RB1,RBS3,RBS5,RBS7,RBS2;RBS4,RBS6,RBS8).
Never, never put the controlfiles on the same Disk as the Datafiles, this will degree Performance.
Have you set the JAVA_POOL_SIZE to zero ?

Hpe this help a litte bit.

Martin
Reply With Quote
  #14  
Old 18th January 2002, 14:57
JamesV JamesV is offline
Senior Member
 
Join Date: Aug 2001
Location: Grand Rapids, MI, USA
Posts: 225
JamesV is on a distinguished road
Baan: Baan IV/5 - DB: Oracle 7/8/8i,Informix, SQL Server, UDB - OS: AIX, HP-UX, Tru64, Solaris, NT/2000
Francesco,

Since you suspect the settings for the Baan Oracle driver, can you please post your db_resource settings for comment.

I have to go right now (appt in 1 minute) but one thing I would look at would be the use of the async IO since you are at an old version of Solaris -- are you using aysnc IO?

-- Jim
__________________
Jim VanderMey
VP of Technical Operations
Open Systems Technologies
Grand Rapids, Michigan, USA
jvandermey@ostusa.com
Reply With Quote
  #15  
Old 18th January 2002, 15:10
bherkend's Avatar
bherkend bherkend is offline
Junior Member
 
Join Date: Jan 2002
Location: Hannover
Posts: 21
bherkend is on a distinguished road
Baan: ERP - DB: Oracle - OS: UNIX
Fransesco,
check ora_timeout (on unix ! ) to use {0,60,60,60,0} instead of {60,60,60,60,60} to get rid of 107 errors.
Beate
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
Oracle 9i on AIX has poor performance Caner.B Performance & Tuning 7 22nd January 2004 07:48
Performance Issues after migrating Baan IVc4 to W2K and Oracle 8i dsplingaire Performance & Tuning 4 27th January 2003 16:22
performance decrease when migrating to Oracle toolslp Tools Development 5 30th November 2002 01:53
Oracle Performance Tuning Myths gguymer Performance & Tuning 2 18th May 2002 18:38
Herstellkostenberechnung in BaaN 4c Kai's Forum Users Deutsches Forum 4 5th July 1999 01:00


All times are GMT +2. The time now is 00:58.


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