topic Re: %_HINT in Application Development and Automation Discussions

%_HINT

Former Member — Tue, 19 May 2009 13:51:50 GMT

Hi,

is there a way to specify in Open-SQL hint to optimizer dynamically? I had in mind something like dynamic where clause.....

thx

Re: %_HINT

Former Member — Tue, 19 May 2009 14:02:50 GMT

Well, if you just leave out the hint, it will optimize dynamically.

Rob

Re: %_HINT

Former Member — Tue, 19 May 2009 15:17:35 GMT

what should be the purpose of dynamic hint ?????

Hints are for exceptions, only if the optimizer has problems, and these problems should be very specific.

Dynamic where-clause is dynamic where-clause, read documentation

Re: %_HINT

former_member207438 — Tue, 19 May 2009 18:51:00 GMT

You can build your hint and put it into a variable. If the variable is initial the hint is not passed to the database engine.

For example:


data:
  lv_hint type c length 30.

  lv_hint = 'index("&TABLE&")'. " Oracle hint

  SELECT *
  INTO TABLE lt_mara
  up to 10 rows
  FROM mara
  %_hints oracle lv_hint.

Read SAP note 129385 about hints for your database type.

Re: %_HINT

Former Member — Wed, 20 May 2009 08:34:05 GMT

maybe I should be more specific. I'm working on some add on Croatia(Foreign Trade), and I'm selecting all kind of data from BSID, BSAD, BISK , BSAK well not always with key/index fields and therefore I figured out that in some cases optimizer uses default index, which is not the best. Local, on our development server, I tried in this case %_HINT ORACLE "FIRST_ROW" and performance is better, but what if some customer doesnt have Oracle? In this case I thought to replace specific DB hint with some string, and this string should be customer specific(set up through customizing).

Re: %_HINT

HermannGahm — Wed, 20 May 2009 09:35:32 GMT

Hi Mario,

you can extend Edwards example:


data:
  lv_hint type c length 30.
 

select hint into lv_hint
from cust_hint_table
...
 
  SELECT *
  INTO TABLE lt_mara
  up to 10 rows
  FROM mara
  %_hints oracle lv_hint
  %_hints db2 lv_hint
  %_hints db4 lv_hint
  %_hints db6 lv_hint
... .

Kind regards,

Hermann

Re: %_HINT

Former Member — Wed, 20 May 2009 10:36:57 GMT

ok, that is not to bad, but also not to elegant. is it possible also to generic specify for which DB id hint?

Re: %_HINT

HermannGahm — Wed, 20 May 2009 10:49:57 GMT

Hi Mario,


> ok, that is not to bad, but also not to elegant.

true.


> is it possible also to generic specify for which DB id hint?

like this?

 
data:
  lv_hint type c length 30.
 
 
select hint into lv_hint
from cust_hint_table
*where dbsys = sy-dbsys*
AND ...
...
 
  SELECT *
  INTO TABLE lt_mara
  up to 10 rows
  FROM mara
  %_hints oracle lv_hint
  %_hints db2 lv_hint
  %_hints db4 lv_hint
  %_hints db6 lv_hint
... .

ADBC is completely native and dynamic (SQL in strings like JDBC)...

but i don't think that this is appropriate. I would stick with the OPEN SQL Hints.

Kind regards,

Hermann

Re: %_HINT

Former Member — Wed, 20 May 2009 10:54:03 GMT

I thought on something like this, instead of

%_hints oracle lv_hint

%_hints db2 lv_hint

%_hints db4 lv_hint

%_hints db6 lv_hint

to use only one:

%_hints lv_hint

and system knows that is Oracle DB or DB2....

Re: %_HINT

Former Member — Wed, 20 May 2009 11:11:29 GMT

I must say I find this discussion useless, please explain, how you would fill the dynamic hint variable.

In theory it can be possible, and practise if is probably very rare.

I would to see the logic which fills lv_hint.

Maybe somebody can propose the self-optizing SELECT statement. If performance is low it will generate a hint. if still low it tries another. Please provide decision logic

Re: %_HINT

Former Member — Wed, 20 May 2009 11:22:17 GMT

No Siegfried, self optimizing SELECT is not what I meant(Ich glaube, dass Sie nicht ein Zyniker tief in Seiner Seele sind). As this AddOn could be deployed to many different SAP systems with different DBs, I want to write only once per select %_HINT lv_hint. In lv_hint then would be db-string and optimizer hint. How could be this done, is pure programming logic which is not relevant.

Re: %_HINT

Former Member — Wed, 20 May 2009 13:03:31 GMT

Mario - if you look for hints in SAP standard programs, I think you'll see that SAP uses sy-dbsys to determine the database and therefore the format of the hint to construct.

Rob

Re: %_HINT

Former Member — Wed, 20 May 2009 13:42:43 GMT

> self optimizing SELECT is

no, in fact it is not even impossible, that you trigger a hint, if the ST04 figures out, that a statement is slow. not done but not impossible.

dynamic hint, still I would not recommend it.

There is no 1:1 relation between the hints of the different databases, usually there is an index hint, but this is not always the first choice.

And even the index hint needs table name, and index name. Better write it by hand.

I would assume that most of your dynamic hints will simply not work, but the have one or the other bug and are simply ignored.

Overall, you should use hints carefully and only in exceptions, your idea is against this rule.

Siegfried

Re: %_HINT

HermannGahm — Wed, 20 May 2009 14:27:54 GMT

Hi Mario,

I'm afraid, that is not possible. You have to specify the hint for each database.

Even a DBI Hint uses the DB specific part infront because even the DBI hints

that don't go to the database can be database specific.

Back to your topic:

Dynamic hints from customizing ... We had a once a customer who build

a whole frame work (on one DB platform) wich dynamic hints from a customizing

table. With that he was able to quickly react on problem queries for example in case

of bugs without code changes (if the dynamic hint solution was implemented).

So the hints were "switchable" which actually was a nice thing.

I've never seen it over database borders. And in general i would say it is for

sure no common practise.

But if you consider that more and more DB manufacturers offer solutions to

obtain a "execution plan stability" without changing the code ... your thoughts

are just going in the same direction ... on the ABAP side.

However i don't know if it would work over database borders because the

possibilites with hints between the databases are VERY diverse. And there

is still lots and lots of undocumented stuff. So i don't know if it will turn out

to be sucessfull. For the customer i mentioned, within one database it turned

out to be sucessfull in terms of reacting quickly on problems without code or

configuration changes.

Kind regards,

Hermann

Re: %_HINT

Former Member — Wed, 20 May 2009 15:15:16 GMT

ok, I will simply use code like Hermann Gahm said, with my own customizing in Z*table or z-program

thx all

mario