User login
|
|
Frontpage Sponsor
|
|
Poll
|
How big is your Baan-DB (just Data AND Indexes) 0 - 200 GB 16% 200 - 500 GB 28% 500 - 800 GB 2% 800 - 1200 GB 9% 1200 - 1500 GB 9% 1500 - 2000 GB 14% > 2000 GB 21% Total votes: 43 |
Baanboard at LinkedIn
|

|
|
|
 |

22nd May 2002, 15:12
|
Member
|
|
Join Date: Dec 2001
Posts: 62
|
|
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 15:19.
|

22nd May 2002, 18:29
|
Newbie
|
|
Join Date: May 2002
Posts: 2
|
|
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
|

22nd May 2002, 19:07
|
 |
Unconfirmed User
|
|
Join Date: Aug 2001
Location: Lufkin, Texas
Posts: 194
|
|
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.
|

22nd May 2002, 19:44
|
 |
Guru
|
|
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,081
|
|
Baan: Baan 4-5,5.2(Reger),LN-6.1,Infor LN-10.x -
DB: Oracle,MS-SQL -
OS: HPUX, 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.
|
|

22nd May 2002, 23:28
|
Senior Member
|
|
Join Date: Aug 2001
Location: Grand Rapids, MI, USA
Posts: 225
|
|
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
|

23rd May 2002, 10:01
|
Senior Member
|
|
Join Date: Aug 2001
Location: Germany
Posts: 135
|
|
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 02:33.
|

24th May 2002, 12:16
|
Member
|
|
Join Date: Dec 2001
Posts: 62
|
|
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.
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
Thread Tools |
|
Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|