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
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #16  
Old 22nd June 2004, 10:38
KhRamil KhRamil is offline
Junior Member
 
Join Date: Jun 2004
Location: Russia
Posts: 15
KhRamil is on a distinguished road
Baan: 5.0c - DB: Oracle - OS: AIX
Baan: 5.0c - DB: Oracle9 - OS: AIX

Hi, Vitorm!
We had similar problems...
Try to use CBO (Cost-Based-Optimiser) on Oracle.
Reply With Quote
  #17  
Old 18th August 2004, 15:06
FriarTuck's Avatar
FriarTuck FriarTuck is offline
Member
 
Join Date: Jan 2002
Location: Universe, Milky Way, Sol, Earth, North America
Posts: 41
FriarTuck is on a distinguished road
Baan: IVc4, 6.1c.06, SP7 - DB: Oracle 8.1.7 (64bit) - OS: HP-UX 11.0 (64bit)
Lightbulb How old are your statistics?

Although this thread is old, I felt compelled to add to the discussion for the sake of furthering the knowledge base. Dikkie Dik has already brought up the subject of statistics, but I thought I'd expand upon that idea.

Do make sure that you regularly analyze the tables/schema. For the CBO to perform its mission adequately, a recent table analysis must be done. A lack of statistics (or out of data ones) may cause the CBO to choose its path incorrectly (or switch out for RBO). An example early on here at my office was a tank in performance when the only tables analyzed were for outbound. Including the entire schema (all tables) brought performance back for the other sessions.

You could set up cron to run a script that contains (in part) something like this:

Code:
exec dbms_stats.gather_schema_stats(ownname =>'baan',-
        estimate_percent => NULL, cascade => TRUE)
This would compute (as opposed to estimate) statistics on the entire Baan schema including indicies (cascade option). This can take a long time.

Most people would agree that at least estimating stats once a week is necessary. Many would further say that a weekly compute is good too. I, myself, run a compute on Sunday and estimate Wed-Sat. YMMV, so caveat scripter. You could also do this via DBMS_JOB, but (quick escape) I'll leave that as an exercise for the reader.

Note further that RBO is considered by Oracle as "old school" and O9i R2 will be the last of the bunch to even offer RBO. Unless my notes are wrong, 10g will be CBO only (Oracle Note 189702.1)

Cheers,
FT
__________________
#include <disclaimer.h>

A bad day behind a Nikon is better than any good day at the office.
Reply With Quote
  #18  
Old 18th August 2004, 19:03
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,028
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 use -

Code:
Oracle Analyze Schema

exec DBMS_UTILITY.analyze_schema('BAAN','ESTIMATE',ESTIMATE_PERCENT=>20,
method_opt=> 'FOR TABLE');
exec DBMS_UTILITY.analyze_schema('BAAN','ESTIMATE',ESTIMATE_PERCENT=>20,
method_opt=> 'FOR ALL INDEXES');

Table Level Analyzer

ANALYZE TABLE BAAN.T&1  ESTIMATE STATISTICS 
SAMPLE 20 percent FOR table  FOR ALL INDEXES  
FOR ALL COLUMNS
__________________
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
Sponsored Links
  #19  
Old 18th August 2004, 20:52
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
Both NRao and FairTruck are right

NPRao is right for 8.i and before, FriarTuck is right for 9i and onwards. Please use the right tool in the right version.

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
  #20  
Old 18th August 2004, 21:09
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,028
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
Quote:
This would compute (as opposed to estimate) statistics on the entire Baan schema including indicies (cascade option). This can take a long time.
This really depends on the size of the databases.
Here is more info from Oracle-Metalink:

Quote:
Bookmark Fixed font Go to End

Doc ID: Note:116178.1
Subject: Analyze Command - Compute Statistics vs. Estimate Statistics
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 27-JUL-2000
Last Revision Date: 28-APR-2003

PURPOSE -------
To give a brief explanation of the difference between using compute statistics and estimate statistics with the analyze command.
SCOPE & APPLICATION -------------------
Intended for DBA's, analysts, etc.
ANALYZE Command - Compute Statistics vs. Estimate Statistics
------------------------------------------------------------
COMPUTE STATISTICS When computing statistics, an entire object is scanned to gather data about the object. This data is used by Oracle to compute exact statistics about the object. Slight variances throughout the object are accounted for in these computed statistics. Because an entire object is scanned to gather information for computed statistics, the larger the size of an object, the more work that is required to gather the necessary information.
ESTIMATE STATISTICS When estimating statistics, Oracle gathers representative information from portions of an object. This subset of information provides reasonable, estimated statistics about the object. The accuracy of estimated statistics depends upon how representative the sampling used by Oracle is. Only parts of an object are scanned to gather information for estimated statistics, so an object can be analyzed quickly. You can optionally specify the number or percentage of rows that Oracle should use in making the estimate.
--------------------------------------------------------------------------------
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.
There is an init.ora parameter [b]OPTIMIZER_MAX_PRERMUTATIONS[b] and the default value is 80000. The combination of this value and the fact that the queries in question have a large number of joins, the parse time is extremely high. By setting this parameter to 1000 you can improve the execution time.

Also refer to the threads -

Baan Sessions Slow to Open
Oracle level 2 performance
__________________
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
  #21  
Old 19th August 2004, 09:11
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
About optimizer_max_permutations:
- For Oracle 8 the default is unlimited (actual value 80.000 means unlimited)
- For Oracle 9 the default is 1500
- For Oracle 10 the parameter has become hidden and unsupported for a change. The default value there is 2000.

For Oracle 8 it was one of the major performance parameters to check when running Baan. For Oracle 9 and anwards the default is much better and so new parameters become important.

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
  #22  
Old 8th November 2004, 21:35
bsyeven bsyeven is offline
Junior Member
 
Join Date: Sep 2001
Location: Phoenix, Arizona
Posts: 19
bsyeven is on a distinguished road
Oracle 9 stats

Not sure if anyone is stil reading this, but just in case - there are documented bugs with dbms_stats in 9i, especially when computing stats on indexes. You may think it is computing stats, but if you check the stats manually you will see they are inaccurate. I would highly recommend that you check to see if you are experiencing this problem and if you are, then I recommend you use the old fashioned ANALYZE INDEX . . . COMPUTE STATISTICS for your indexes, OR apply the patch (3078144) from Oracle that fixes the problem. See Metalink note 270361.1 for more info.

A few other things: make sure you are using PGA_AGGREGATE_TARGET, LMT with ASSM, automatic UNDO and all the other new 9i features and have them tuned properly. If you continue to have performance problems, look at statspack and see where your bottleneck and what are the most poorly performing queries.

Good luck,
Brandon Allen
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
BaanIV to BaanV migration jean01 Tools Administration & Installation 12 7th February 2005 14:48
Migration from ORACLE7 to ORACLE9 ccbaan Operating Systems & Databases 5 17th June 2003 14:00
Migration from BaanIV to Baan V Old Vens Open World, Portal & Decision Manager 7 23rd October 2002 01:59
Migration Triton to BaanIV sangamsd General Discussion & Chat 1 23rd January 2002 11:55


All times are GMT +2. The time now is 19:44.


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