This website is not affiliated with, sponsored by, or approved by SAP AG.

Different results when using %_hints

Development (ABAP Development WorkBench, ABAP/4 programming)

Moderators: Snowy, thx4allthefish, Gothmog, YuriT

Different results when using %_hints

Postby BOBA on Mon Aug 13, 2007 9:38 am

On release 4.6c and using the %_hints addition, I get different results in the development box from the quality box. Both have the same active indexes. In development, the performance trace indicates it worked as expected. In quality, it shows that a different index was used. Here is the code:
Code: Select all
SELECT
   VBRK~VBELN    "billing document
   VBRP~POSNR
   VBRK~FKART    "billing type
   VBRK~VKORG    "Sales org
    into Table IT_VBRK_KONV
     From VBRK
      inner join VBRP
       on VBRK~MANDT = VBRP~MANDT
          and VBRK~VBELN = VBRP~VBELN
    Where  VBRK~RFBSK in S_RFBSK
       and VBRK~VKORG in S_VKORG
       and VBRK~VBELN in S_VBELN
       and VBRK~FKART in S_FKART
       and VBRK~VTWEG in S_VTWEG
       and VBRK~FKDAT in S_FKDAT
       and VBRK~KUNAG in S_KUNAG
       %_hints oracle 'INDEX ( "VBRK~Z01" )'.
Index Z01 uses MANDT, RFBSK, VKORG, KUNRG and here is what the performance trace looks like in development
Code: Select all
Execution Plan



SELECT STATEMENT ( Estimated Costs = 1 , Estimated #Rows = 1 )

        FILTER

            TABLE ACCESS BY INDEX ROWID VBRP

                NESTED LOOPS

                    TABLE ACCESS BY INDEX ROWID VBRK

                        INDEX RANGE SCAN VBRK~Z01

                    INDEX RANGE SCAN VBRP~0
Can anyone help me understand why in the quality system even after rebuilding and analyzing the indexes, we get the following results in the Performance Trace
Code: Select all
Execution Plan



SELECT STATEMENT ( Estimated Costs = 18,723 , Estimated #Rows = 56,350 )

        FILTER

            TABLE ACCESS BY INDEX ROWID VBRP

                NESTED LOOPS

                    TABLE ACCESS BY INDEX ROWID VBRK

                        INDEX RANGE SCAN VBRK~LOC

                    INDEX RANGE SCAN VBRP~0
Note, the index LOC uses fields MANDT and LCNUM and this makes no sense to me because LCNUM is not being selected.

Thanks in advance

BOBA
 
Posts: 138
Joined: Tue Oct 29, 2002 3:26 pm
Location: Pennsylvania

Postby ssii on Mon Aug 13, 2007 4:19 pm

I am not a specialist but did you update the statistics in Quality system ?

I know that Oracle uses this statistics to choose the best index.

SSII
SSII is back
ssii
 
Posts: 222
Joined: Sat Nov 11, 2006 9:57 am
Location: France

Postby m@t on Tue Aug 14, 2007 1:24 am

And if the statistics in the dev box are different from quality box, you might well get different results.

I think the hint is in the use of the word "hint". It gives the db server a hint how to proceed, but it isn't definite.

m@t
TULY Image The quality of answers is roughly proportional to the quality of the question.

The downside of being better than everyone else is that people tend to assume you're pretentious.
m@t
 
Posts: 4118
Joined: Thu Sep 04, 2003 7:37 am
Location: Switzerland

Postby christmaslights on Tue Aug 14, 2007 5:03 am

In spite of the name, I've always thought that if you specify a hint the optimizer must take notice of it. I think you may have the hint syntax wrong and so the optimizer is ignoring it. The only way I've managed to make an Oracle index hint work in SAP is to specify the table name as well. The following has worked for me:

Code: Select all
  %_HINTS ORACLE 'INDEX ( "&TABLE&" "SETHEADER" "SETHEADER~2" )'.
Using SAP R/3 version 4.7 BASIS 6.20 under SunOS on an Oracle 10.2.0.2.0 database
christmaslights
 
Posts: 857
Joined: Wed Jun 16, 2004 5:13 am
Location: Hampshire, England

Postby BOBA on Tue Aug 14, 2007 8:20 am

Code:
%_HINTS ORACLE 'INDEX ( "&TABLE&" "SETHEADER" "SETHEADER~2" )'.

What is the purpose of &TABLE& in your code example? I previously tried the statement
Code: Select all
%_hints oracle 'INDEX ( "VBRK" "VBRK~Z01" )'
but it did not seem to work.

Thanks
BOBA
 
Posts: 138
Joined: Tue Oct 29, 2002 3:26 pm
Location: Pennsylvania

Postby christmaslights on Tue Aug 14, 2007 9:13 am

BOBA wrote:
Code:
%_HINTS ORACLE 'INDEX ( "&TABLE&" "SETHEADER" "SETHEADER~2" )'.

What is the purpose of &TABLE& in your code example? I previously tried the statement
Code: Select all
%_hints oracle 'INDEX ( "VBRK" "VBRK~Z01" )'
but it did not seem to work.

Thanks

I'm sorry to say I don't know exactly. I don't use hints very often and that particular one I used a few years ago. Before posting I tried to find a site to explain the syntax but couldn't find anything useful. &TABLE& looks like a variable to me. SETHEADER is the actual table name - you may not need this but I think I did because I was using table aliases, or perhaps I didn't and it just ignored it. The statement as it stands works for me; the same statement without the &TABLE& does not work.

Try the &TABLE out in your DEV system specifying different indexes to see if it makes any difference.
Using SAP R/3 version 4.7 BASIS 6.20 under SunOS on an Oracle 10.2.0.2.0 database
christmaslights
 
Posts: 857
Joined: Wed Jun 16, 2004 5:13 am
Location: Hampshire, England

Postby BOBA on Tue Aug 14, 2007 10:11 am

SETHEADER is the actual table name - you may not need this but I think I did because I was using table aliases, or perhaps I didn't and it just ignored it. The statement as it stands works for me; the same statement without the &TABLE& does not work.
It appears that the &TABLE& is needed. This is the result when using it. By the way, even though I used "&TABLE&", it substituted T_00 which is the alias for VBRK in my join
Code: Select all
SQL Statement

SELECT
/*+
  INDEX ( "T_00" "VBRK" "VBRK~Z01" )
*/
  T_00 . "VBELN" , T_01 . "POSNR" , T_00 . "FKART" , T_00 . "VKORG" , T_00 . "VTWEG" ,
  T_00 . "KNUMV" , T_00 . "FKDAT" , T_00 . "KUNAG" , T_01 . "ZZ_WORKD" , T_01 . "ZZ_WORKE" ,
  T_01 . "ZZ_WORKF" , T_01 . "ZZ_WORKG" , T_01 . "ZZ_WORKH" , T_01 . "ZZ_WORKI" ,
  T_01 . "ZZ_WORKK" , T_01 . "ZZ_WORKL" , T_01 . "FKIMG" , T_01 . "VRKME"
FROM
  "VBRK" T_00 , "VBRP" T_01
WHERE
  ( T_01 . "MANDT" = :A0 AND T_00 . "MANDT" = T_01 . "MANDT" AND T_00 . "VBELN" = T_01 . "VBELN" )
  AND T_00 . "MANDT" = :A1 AND T_00 . "RFBSK" = :A2 AND T_00 . "VKORG" = :A3 AND T_00 . "VTWEG" =
  :A4 AND T_00 . "FKDAT" BETWEEN :A5 AND :A6


Execution Plan



SELECT STATEMENT ( Estimated Costs = 24,519 , Estimated #Rows = 56,350 )

        FILTER

            TABLE ACCESS BY INDEX ROWID VBRP

                NESTED LOOPS

                    TABLE ACCESS BY INDEX ROWID VBRK

                        INDEX RANGE SCAN VBRK~Z01

                    INDEX RANGE SCAN VBRP~0
and when I take it out, this is what I get without it
Code: Select all
SQL Statement

SELECT
/*+
  INDEX ( "VBRK" "VBRK~Z01" )
*/
  T_00 . "VBELN" , T_01 . "POSNR" , T_00 . "FKART" , T_00 . "VKORG" , T_00 . "VTWEG" ,
  T_00 . "KNUMV" , T_00 . "FKDAT" , T_00 . "KUNAG" , T_01 . "ZZ_WORKD" , T_01 . "ZZ_WORKE" ,
  T_01 . "ZZ_WORKF" , T_01 . "ZZ_WORKG" , T_01 . "ZZ_WORKH" , T_01 . "ZZ_WORKI" ,
  T_01 . "ZZ_WORKK" , T_01 . "ZZ_WORKL" , T_01 . "FKIMG" , T_01 . "VRKME"
FROM
  "VBRK" T_00 , "VBRP" T_01
WHERE
  ( T_01 . "MANDT" = :A0 AND T_00 . "MANDT" = T_01 . "MANDT" AND T_00 . "VBELN" = T_01 . "VBELN" )
  AND T_00 . "MANDT" = :A1 AND T_00 . "RFBSK" = :A2 AND T_00 . "VKORG" = :A3 AND T_00 . "VTWEG" =
  :A4 AND T_00 . "FKDAT" BETWEEN :A5 AND :A6


Execution Plan



SELECT STATEMENT ( Estimated Costs = 18,723 , Estimated #Rows = 56,350 )

        FILTER

            TABLE ACCESS BY INDEX ROWID VBRP

                NESTED LOOPS

                    TABLE ACCESS BY INDEX ROWID VBRK

                        INDEX RANGE SCAN VBRK~LOC

                    INDEX RANGE SCAN VBRP~0
So, your example definitely helped. The worst part about it is that I got better performance when it did not use the index I wanted it to use :oops:

One more note, it also works using
Code: Select all
%_hints oracle 'INDEX ( "&TABLE&" "VBRK~Z01" )'
BOBA
 
Posts: 138
Joined: Tue Oct 29, 2002 3:26 pm
Location: Pennsylvania

Postby JDO on Tue Aug 14, 2007 11:41 am

Hello,

OSS 129385 Database-Hints in Open SQL
OSS 772497 FAQ: Oracle Hints

Juergen
JDO
 
Posts: 17
Joined: Mon Dec 11, 2006 1:11 pm
Location: Germany

Postby BOBA on Tue Aug 14, 2007 3:48 pm

Thanks to everyone. Hopefully others will also benefit from your responses.
BOBA
 
Posts: 138
Joined: Tue Oct 29, 2002 3:26 pm
Location: Pennsylvania

Postby christmaslights on Wed Aug 15, 2007 4:00 am

BOBA wrote:The worst part about it is that I got better performance when it did not use the index I wanted it to use :oops:

So, the cost based optimizer actually did what it is supposed to do and picked the right access path.....I find that reassuring.

Thanks to Juergen for the OSS numbers which I had forgotten about. The explanation given for the &TABLE& substitution, which I think I almost understand, is:

&TABLE [[<block>,] <table>]& --> <table name>
is replaced with the name which uses the database interface for the entered table opposite the database. Application developers generally do not know this, since the R/3 database interface generates synthetic table aliases of its own accord.

...which is why in the code example given it is filled with T_00.
Using SAP R/3 version 4.7 BASIS 6.20 under SunOS on an Oracle 10.2.0.2.0 database
christmaslights
 
Posts: 857
Joined: Wed Jun 16, 2004 5:13 am
Location: Hampshire, England

Postby q4u on Fri Aug 17, 2007 7:40 am

Hi,
How did you get to see this?

Code:
SQL Statement

SELECT
/*+
INDEX ( "T_00" "VBRK" "VBRK~Z01" )
*/
T_00 . "VBELN" , T_01 . "POSNR" , T_00 . "FKART" , T_00 . "VKORG" , T_00 . "VTWEG" ,
T_00 . "KNUMV" , T_00 . "FKDAT" , T_00 . "KUNAG" , T_01 . "ZZ_WORKD" , T_01 . "ZZ_WORKE" ,
T_01 . "ZZ_WORKF" , T_01 . "ZZ_WORKG" , T_01 . "ZZ_WORKH" , T_01 . "ZZ_WORKI" ,
T_01 . "ZZ_WORKK" , T_01 . "ZZ_WORKL" , T_01 . "FKIMG" , T_01 . "VRKME"
FROM
"VBRK" T_00 , "VBRP" T_01
WHERE
( T_01 . "MANDT" = :A0 AND T_00 . "MANDT" = T_01 . "MANDT" AND T_00 . "VBELN" = T_01 . "VBELN" )
AND T_00 . "MANDT" = :A1 AND T_00 . "RFBSK" = :A2 AND T_00 . "VKORG" = :A3 AND T_00 . "VTWEG" =
:A4 AND T_00 . "FKDAT" BETWEEN :A5 AND :A6


Execution Plan

SELECT STATEMENT ( Estimated Costs = 24,519 , Estimated #Rows = 56,350 )

FILTER

TABLE ACCESS BY INDEX ROWID VBRP

NESTED LOOPS

TABLE ACCESS BY INDEX ROWID VBRK

INDEX RANGE SCAN VBRK~Z01

INDEX RANGE SCAN VBRP~0
q4u
 
Posts: 419
Joined: Wed Mar 08, 2006 7:02 am

Postby m@t on Fri Aug 17, 2007 8:01 am

SQL Trace

m@t
TULY Image The quality of answers is roughly proportional to the quality of the question.

The downside of being better than everyone else is that people tend to assume you're pretentious.
m@t
 
Posts: 4118
Joined: Thu Sep 04, 2003 7:37 am
Location: Switzerland

Postby vieryfl on Wed Sep 05, 2007 1:16 am

The right syntax for this is :

%_HINTS ORACLE 'INDEX("VBRK" "VBRK~Z01")'.

For m@t:
You can get that from TCode ST05 - SQL Trace
Activate - Run Program - Deactivate - Display Trace
vieryfl
 
Posts: 23
Joined: Mon Mar 15, 2004 9:11 am
Location: Indonesia

Postby christmaslights on Wed Sep 05, 2007 5:05 am

vieryfl wrote:The right syntax for this is :

%_HINTS ORACLE 'INDEX("VBRK" "VBRK~Z01")'.

Didn't work for me. It is possible it might work if you're not using a join.
Using SAP R/3 version 4.7 BASIS 6.20 under SunOS on an Oracle 10.2.0.2.0 database
christmaslights
 
Posts: 857
Joined: Wed Jun 16, 2004 5:13 am
Location: Hampshire, England


Return to ABAP

Who is online

Users browsing this forum: No registered users and 5 guests



This website is not affiliated with, sponsored by, or approved by SAP AG.