topic Re: Max blocking factor problem in Application Development and Automation Discussions

Max blocking factor problem

former_member595195 — Tue, 21 Jun 2016 15:35:36 GMT

Hello, I'm using max blocking factor for this query but it seems that is not working correctly, idk if i'm doing it wrong pls help.

SELECT rbukrs docnr ryear segment prctr

INTO TABLE t_faglflexa

FROM faglflexa

FOR ALL ENTRIES IN i_detalle_aux

WHERE rbukrs = i_detalle_aux-bukrs

AND docnr = i_detalle_aux-belnr

AND ryear = i_detalle_aux-gjahr

AND rldnr = '0L'

%_HINTS ORACLE '&MAX_IN_BLOCKING_FACTOR 100&&MAX_BLOCKING_FACTOR 100&'.

I checked in st12 and it's no calling it.

Ty all.

Re: Max blocking factor problem

Sandra_Rossi — Tue, 21 Jun 2016 16:16:12 GMT

Try in lower case!

Re: Max blocking factor problem

former_member595195 — Tue, 21 Jun 2016 16:53:08 GMT

I did it but still it does not appear in st12. Mayb it works but st12 doesn't show that. idk, thx anyway.

Re: Max blocking factor problem

Sandra_Rossi — Tue, 21 Jun 2016 21:23:53 GMT

Sorry but I just realized, my answer was just out of context!

If you have only one line in I_DETALLE_AUX, then your trace is okay. Note also that it's normal to not see %_HINTS for these SAP specific hints in the SQL trace, because it's executed at SAP side only.

In fact, the question is: what did you expect?

And what do you mean "it's not calling it"?

Re: Max blocking factor problem

former_member595195 — Wed, 22 Jun 2016 03:06:03 GMT

I mean for example, when I use an index, i go to st12 and I can see the index i'm using in that query, but when I use the max blocking factor I can't see it, so idk if i'm doing it wrong or mayb it's working but st12 doesn't show me that. If u want I can show u some images. ty.

Re: Max blocking factor problem

Sandra_Rossi — Wed, 22 Jun 2016 06:59:04 GMT

Okay, so your question is more about how it works.

Read the SAP note 634263 (Selects with FOR ALL ENTRIES -> https://service.sap.com/sap/support/notes/634263 ), and the SAP note 48230 (Parameters for the SELECT ... FOR ALL ENTRIES statement -> https://service.sap.com/sap/support/notes/48230 ). The note 634263 explains problems of performance of for all entries, optimization, change of max blocking factor to 1000 since 6.10, and there is an attached PDF document.

In the trace, you'll see in the WHERE of the OPEN operation, a repetition of conditions separated by OR, with a maximum of max_blocking_factor; the number of subsequent FETCH operations corresponds to (approximately) the number of internal table lines divided by the max_blocking_factor.

There are probably a few blog posts too, about the topic.