How to prevent passing of hints generated by Oracle Driver ?

[baaniac]

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

[thessdethall]

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

[gguymer]

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.

[NPRao]

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 -

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.

[JamesV]

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

[Martin]

Hi,

the problem, that you would read the table with a not full qualified index. Try the following sql-statement :

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

[baaniac]

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.