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 8th October 2001, 17:43
richard richard is offline
Senior Member
 
Join Date: Oct 2001
Location: France
Posts: 178
richard is on a distinguished road
Baan: triton 3.2/Baan IV/LN - DB: Oracle 9.2.0.6/informix 31 - OS: HP UX 11.23
Oracle level 2 performance

We are migrating from informix level 1 to oracle level 2.
The results are rather good (15% less disk space, queries much faster), but some sessions slower.

Specially: tccom1101, tccom2101, tdsls4101 and tdpur4101. Also the first-set/last-set actions are slow. (about 15 seconds).
The response time was 3 minutes before changing optimizer_max_permutations to 1500.
The time is so bad at the very first execution. Did you make the same experience and what were the actions to solve the problem ?
Reply With Quote
Sponsored Links
  #2  
Old 8th October 2001, 18:01
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
Oracle stats

Hi Richard,

The optimizer_max_permutation will prevent the Oracle optimizer from going into a 'loop' when looking for the best query execution path. The default value for max. number of permutation is 80,000!

Also make sure that you have set up the optimizer_mode to CHOOSE and that you have up-to-date histogram data (statistics) on all your Baan tables. If no statistics exist, Oracle will switch to rule-based execution pathes which is bad. Only cost-based optimization (CBO) should be used.
__________________
Regards,

Patrick Van der Veken - Admin & Founder - (c) 2001-2017 baanboard.com/baanforums.com
Reply With Quote
  #3  
Old 8th October 2001, 20:36
naabi0 naabi0 is offline
Member
 
Join Date: Sep 2001
Location: Alabama
Posts: 81
naabi0 is on a distinguished road
histograms

Are you talking about computing statistics for columns?
Reply With Quote
  #4  
Old 9th October 2001, 10:31
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
Yes
__________________
Regards,

Patrick Van der Veken - Admin & Founder - (c) 2001-2017 baanboard.com/baanforums.com
Reply With Quote
  #5  
Old 11th October 2001, 01:27
bsyeven bsyeven is offline
Junior Member
 
Join Date: Sep 2001
Location: Phoenix, Arizona
Posts: 19
bsyeven is on a distinguished road
Question

I don't believe histograms will have any effect on Oracle's processing of Baan SQL because Baan uses bind variables. When the CBO processes a query containing bind variables, it must use default selectivities. If anyone disagrees, please explain.

I would be interested to know if you do notice an improvement after computing columns stats - please let me know.

Thanks,
Brandon
Reply With Quote
  #6  
Old 11th October 2001, 07:50
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
statistics

thats correct.

which database version you are using ?
we have 8.1.7 and i have set the optimizer mode to "first_rows"
(because Baan set a sql-hint for use first_rows).
the optimizer_max_permutation is set to 800.
But the best performance hint is to activate parallel query an update for tables and indexes, so i have now responstime for starting sessions for 3 seconds and skip to last row for 5 seconds.
Reply With Quote
  #7  
Old 11th October 2001, 15:48
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
CBO

It's not a matter of 'or-or' but rather 'and-and'. First of all, I think every one would agree that CBO is the best way to drive any SQL query for Baan. The way the CBO will work depends on several things:
  • parameter OPTIMIZER_MODE
  • session OPTIMIZER_GOAL
  • SQL hints
The order of taking precedance is from bottom to top. Thus a hardcoded SQL hint will overrule the standard if OPTIMIZER_MODE if they wouldn't agree.

Using OPTIMIZER_MODE=CHOOSE does not exclude the FIRST_ROW hints (hints take precedence) but makes sure that any queries where the SQL hint is incomplete, invalid or missing will use a proper execution plan IF statistics are available. A good example of invalidhints is any statement with an ORDER BY clause. In those cases the FIRST_ROWS hint is ignored.

As to the bind variables: the Oracle CBO does have problems with optimizing queries when bind variables are used in 'like' and range constructs but from experience I can say that failing to update the statistics on a weekly basis (or more frequent) *does* cause a performance hit.

I am not an export in Oracle matters so if someone can give me better insights, I will stand corrected!
__________________
Regards,

Patrick Van der Veken - Admin & Founder - (c) 2001-2017 baanboard.com/baanforums.com
Reply With Quote
  #8  
Old 11th October 2001, 18:30
bsyeven bsyeven is offline
Junior Member
 
Join Date: Sep 2001
Location: Phoenix, Arizona
Posts: 19
bsyeven is on a distinguished road
Histograms and Bind Variables

I agree that computing statistics regularly (weekly) is a must for the CBO to work optimally, but you do not need to compute statistics on columns. Column statistics = histograms. These are ignored by the CBO when using bind variables and default selectivities are used instead.

This is all you need to run:
EXECUTE dbms_utility.analyze_schema('BAAN','COMPUTE')


You do NOT need to run:
analyze table ttxyz123100 compute statistics for columns . . .
Reply With Quote
  #9  
Old 11th October 2001, 18:34
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
My mistake

That's my mistake, the statistics we calculate are not for the columns, I assumed they were the same thing. Another thing learned today
__________________
Regards,

Patrick Van der Veken - Admin & Founder - (c) 2001-2017 baanboard.com/baanforums.com
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
new Oracle Row Level security paper petefinnigan Operating Systems & Databases 0 10th November 2003 15:08
Baan IVc2 and Oracle 8.1.7 performance problem mpenno Performance & Tuning 2 3rd September 2003 19:50
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 8i performance solution gguymer Performance & Tuning 19 26th January 2002 14:07


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


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