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
  #1  
Old 22nd May 2002, 14:12
baaniac baaniac is offline
Member
 
Join Date: Dec 2001
Posts: 62
baaniac is on a distinguished road
Baan: TRITON 3.1b, Baan IV b2/c2/c4, BaanERP 5.0b - DB: tbase, Oracle, Informix - OS: Sun Solaris, OSF, HP-UX
How to prevent passing of hints generated by Oracle Driver ?

Hi all,

We are facing a typical problem with BaanERP 5.0b with Oracle 8.05 as the back-end.

We have found that whenever any query is fired from a Standard Baan session to Oracle, it is sometimes getting wrong hints at Oracle side.

E.g. - Our query at Baan level is as follows :-

select tdsls401.orno, tdsls401.pono, tdsls401.srnb, tdsls401.cuno
from tdsls401
where tdsls401._index1 = {:orno.f}
order by tdsls401._index1

If we run query this query in a Baan session, it gets hint as "Full Scan".
When we fire the same query at Oracle level, it gets hint as "Index_Scan".

And, it is resulting into drastic difference in performance.

Likewise, the Baan session is running very slow.
If we run the same query at Oracle level, it is nearly 8 times faster than that at Oracle level. !!!

What might be the reason for this ? Is there any way at Baan end to prevent hints passed to Oracle ?

Details of our system are as follows :-
Server - HP
Backend - Oracle 8.0.5
BaanERP Version - 5.0b
Porting Set - 6.2a.03.01
OPTIMIZER_MODE at Oracle - CHOOSE
Statistics Analyze - regularly, on a weekly basis.

Thanks

Last edited by patvdv : 22nd May 2002 at 14:19.
Reply With Quote
  #2  
Old 22nd May 2002, 17:29
thessdethall thessdethall is offline
Newbie
 
Join Date: May 2002
Posts: 2
thessdethall is on a distinguished road
Analyze

Hello,

I have the same question about the optimizer_mode and the analyze command.

If baan put hints on sql queries, i think that mean that Oracle do not use the statistics. So my question is should the database be analyzed if baan do not use statistics ?

best regards
Reply With Quote
  #3  
Old 22nd May 2002, 18:07
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
The Baan / Oracle driver translates the Baan SQL statements into their Oracle equivalent including hints that it then passes to Oracle to execute. The hints in the SQL statement cause the optimizer to behave in a somewhat predictable manner. The optimizer works off the statistics that are collected from the analyzed tables and indexes. If those statistics are not kept up to date, then the optimizer makes faulty decisions based those outdated statistics. Both Oracle and the Baan have settings that can be modified to influence the behavior of either the driver and/or the optimizer. Whether the hints are there or not, bottom line is that the optimizer is being called.
Reply With Quote
  #4  
Old 22nd May 2002, 18:44
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
I am not aware if these options are allowed in the older BaaN versions, but we are on the BaaN-5.2 where you can use the "hints" in your SQL coding...

Here is the info from the help manual -
Quote:
BaanERP Programmers Guide
Hint types
-------------------------------------------------------------------------------
In the following subsections we describe each of the hint types:
Index hint
Ordered hint
Buffer hint
Array fetching hint
Array size hint
Row mode hint
'No hints' hint
String hint
Related topics

Index hint
--------------------------------------------------------------------------------
An index hint advises the query processor to scan a table with the specified index. For example:

select bpid, nama
from tccom100
where bpid > ' 1000' and nama >= 'Z'
hint use index 2 on tccom100

This index hint suggests to the query processor to scan table tccom100 using index 2. Optionally, you can specify the mode, ascending or descending, of the index. For example:

select bpid, nama
from tccom100
where bpid > ' 1000' and nama >= 'Z'
hint use index 2 on tccom100 desc

Table tccom100 must be accessed using index 2 in descending order. With one index hint you can hint more than 1 index for the table. There is a maximum of 10 indexes you can specify per index hint. If you specify more than one index hint the query processor may use one of these indexes to scan the table or it may use a combination of the indexes. Consider for example the following query:

select iscn, bpid
from tccom100
where (iscn = 570 or cadr = 'J10000001') and nama >= 'A'
hint use index 1,2 on tccom100

Assume index 1 is on column iscn and index 2 is on column cadr, furthermore assume there is an index 3 on column nama. By hinting on both index 1 and index 2 this query can be solved by firing two index scans.

Index scan 1 on index 1:

select iscn, bpid
from tccom100
where iscn = 570 and nama >= 'A'

Index scan 2 on index 2:

select iscn, bpid
from tccom100
where cadr = 'J10000001' and nama >= 'A'

Performing these two index scans may be faster than executing one index scan on index 3.
__________________
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
  #5  
Old 22nd May 2002, 22:28
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
Hints and Oracle Driver

The ability to explicitly pass hints is part of the Baan 5.1/5.2 tool set. You cannot do this at earlier versions.

You can change the optimizer behavior through a combination of changes to the query (if you have access for source code changes), optimizer codes in ora_storage, db_resource settings, etc. but the result is difficult to predict without some testing.

You may also need to check your dbsinit to reduce the amount of strictness in the index selection. This value should be set to 021 to reduce strictness.

-- Jim
__________________
Jim VanderMey
VP of Technical Operations
Open Systems Technologies
Grand Rapids, Michigan, USA
jvandermey@ostusa.com
Reply With Quote
  #6  
Old 23rd May 2002, 09:01
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,

the problem, that you would read the table with a not full qualified index. Try the following sql-statement :
Code:
select tdsls401.orno, tdsls401.pono, tdsls401.srnb, tdsls401.cuno 
from tdsls401 
where tdsls401._index1 =inrange {:orno.f, 0 ,0}
and {:orno, 99, 99} 
order by tdsls401._index1
Hope this helps.


Martin

Last edited by patvdv : 27th May 2002 at 01:33.
Reply With Quote
Sponsored Links
  #7  
Old 24th May 2002, 11:16
baaniac baaniac is offline
Member
 
Join Date: Dec 2001
Posts: 62
baaniac is on a distinguished road
Baan: TRITON 3.1b, Baan IV b2/c2/c4, BaanERP 5.0b - DB: tbase, Oracle, Informix - OS: Sun Solaris, OSF, HP-UX
Thanks everyone

Thanks everyone for your kind feedback.

The problem has been identified with Baan source; which was wrongly coded. It had a cartesian (Cross) product of 2 tables; which caused wrong hints being passed to the Oracle.
A solution is available on Support site for the problem.

Thanks again and sorry for rather mis-leading everyone on the root cause of the problem.
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 Driver not properly working after upgrading the Porting set query1 Tools Administration & Installation 4 11th October 2004 12:44
Baan & Connectware Driver Hangs Rajasekhar Tools Administration & Installation 2 18th February 2004 21:32
Migrate from MSSQL to ORACLE patrickmonks Operating Systems & Databases 12 30th October 2003 15:45
Error installing B4c4 with Oracle 8.1.7 on NT syllepage Tools Administration & Installation 2 22nd July 2002 13:52
Baan on Oracle Guru for Hire Will travel OracleBaanGuru Jobs and Resumes 1 19th June 2002 15:57


All times are GMT +2. The time now is 13:50.


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