Why the Hint doesn't work?

[Former Member]

Dear Team,

I meet one issue ,when I write the SQL in the ST05 and click the Explain Plan in the CRM system:

It doesn't use the index CRM_JEST~0 and Then I use the HINT to USE the CRM_JEST~0,but it doesn't work.

Could you pls tell me why?

I try to execute in SE11 it really goes faster than lighter less than 1 seconds.

[stefan_koehler · Accepted solution]

Hello Neil,

well at first i have to mention three very important points:

1. Your table is client depended, so the SAP system automatically creates a WHERE clause like MANDT = :A0 AND ... <YOUR FILTERS>
2. The DBLS uses bind variables by default. The SQL is not executed (or parsed) with literals like in your example in ST05.
3. The SAP system uses an alias for tables - so the HINT or any other table related code has to use that alias.

Point 1 (and maybe in combination with point 2) is probably the reason why the SQL runs fast by SE16 (and SE16 uses a ROWNUM clause and FIRST_ROW HINT as well in your case).

However the HINT should be recognized in any case. Can you please try to put the table and index name in the " tags like ("CRM_JEST" "CRM_JEST~0"). This issue is SAP specific due to the special characters in object names.

I have written a blog about the the bind / literal behavior in a SAP environment (maybe you are interested): http://scn.sap.com/community/oracle/blog/2012/08/03/oracle-db-optimizer-part-i--understanding-in-lis...

Regards

Stefan

[Former Member · Accepted solution]

Hello Koehler,

Thanks for your professional reply. I have tried to use the hint ("CRM_JEST" "CRM_JEST~0").It still doesn't work.

Then I try to run the same SQL in the Test system.It truely use the correct index.I check the table size and cluster factor and statistic information all the same.

Could you pls kindly have a look and give some recommendation?

Thanks,

Neil

SQL:

Select

T0.header

From crmd_order_index t0 inner join CRMD_ORDERADM_H t1 on

T0.client = t1.client and t0.header = t1.GUID

inner join CRM_JEST t2 on

T0.client = t2.MANDT and T0.header = T2.OBJNR

Where t1.PROCESS_TYPE  IN ('ZCMP','ZDMP')

AND t0.SALES_ORG IN ('O 50001104','O 50001105')

AND t0.SERVICE_ORG IN ('O 50004212','O 50004221')

AND t1.ZZAFLD00000D IN ('01','02','03','04','05','08','09','11')           

AND t2.STAT IN ('E0001', 'E0002', 'E0003', 'E0004')

      And t0.OBJECT_TYPE = 'BUS2000120'

   And t0.client = '800'

   AND t2.INACT = ' '

[Former Member · Accepted solution]

Just guessing out of the blue, is PGA_AGGREGATE_TARGET the same on test and prod?

Cheers Michael

[stefan_koehler · Accepted solution]

Hello Neil,

that is a complete different SQL as your previous one (note the table alias T2 for example - you need to use that in the HINT syntax).

However two more important points to mention:

1. The SQL still includes literals (that is very unusual) and makes a huge difference on the optimizer (and the calculated execution plan of course)
2. The provided information is not enough (especially because of the use of HASH JOINs and NESTED LOOPs with batching).

The screenshots are too small (or my eyes are too bad ), but as far as i can see there is a huge difference in the row estimation. Such issues can hardly be analyzed and fixed through a forum. 

If you need further analysis or assistance - please check my profile / homepage. I have specialized on such (short-term) requests.

Regards

Stefan

[volker_borowski2 · Accepted solution]

The screenshots are too small (or my eyes are too bad ), but as far as i 

Ja, I see that the same way.

Just for Information to all people that like to provide stats:

Rightclick the stats box and use "SAVE to FILE" and in the following box "SAVE TO CLIPBORAD"

When putting it into SDN, format as Courier New -> voila

As for the EXPLAIN:

The OBJNR seems to be a guid and there is a common bug in the EXPLAIN with GUIDs.

It is only for the explain, the select itself is doing it properly.

To get the "correct" explain in ST05, you need to add the hextoraw() function to the SQL,

allthough it is not in the original SQL. (SAP note 551400).

GUID-Where compares tend to display Full Table Scans in ST05,

but do not worry, they are doing not!

You should also check regional settings, as the date format in both stats screens differs.

I am not aware of any bug or configuration that would make a difference in this case,

but one never knows, so I would like to have it identical...

Volker

[Former Member · Accepted solution]

Dear Borowski,

Thanks great for your correct answer ,I have resolved it now.

Thanks,

Neil

[Former Member]

In addition to above suggestions please check oracle versions with installed patches on both systems. May be this issue related to oracle CBO bugs. If you have no more idead you can try to trace optimizer plan.

Regards

Roman

[Former Member]

Hi Neil,

First of all check the database statistics have been collected without a problem on DB13. Additionally, try to update stats with DB20 and try to execute the query and check the results.

>> I try to execute in SE11 it really goes faster than lighter less than 1 seconds.

Could it be the reason that you executed the query on SE11 with the default values? By the default values, first 500 rows fetched from the database and presented. So, because of the other query does not have a restriction, its runtime is longer than SE11.

Best regards,

Orkun Gedik