topic Re: %_hints oracle '&SUBSTITUTE VALUES&' in Application Development and Automation Discussions

%_hints oracle '&SUBSTITUTE VALUES&'

Former Member — Tue, 07 Jul 2009 05:28:39 GMT

Hello Experts,

While fetching data from MSEG & MKPF.

Issue is if in my selection-screen I input Plant & Posting Date, the performance is OK, but if I enter PlAnt , posting date & movement type, the report is not able to execute in foreground.

While debugging standard report MB51 Program RM07DOCS,include RM07DOCS_GENERATED there is query like

select (g_t_fields)

into corresponding fields of table itab

from mkpf inner join mseg

on mkpf_{mandt = mseg}mandt

and mkpf_{mblnr = mseg}mblnr

and mkpf_{mjahr = mseg}mjahr

for all entries in matnr

where matnr = matnr-low

and mkpf~budat in budat

and mseg~bwart in bwart

and mseg~charg in charg

and mseg~kunnr in kunnr

and mseg~lgort in lgort

and mseg~lifnr in lifnr

and mseg~sobkz in sobkz

and mkpf~usnam in usnam

and mkpf~vgart in vgart

and mseg~werks in werks

and mkpf~xblnr in xblnr

%_hints

oracle '&SUBSTITUTE VALUES&'

What does last line mean

Is this prove to b useful .

PLz suggest

Ravi

Re: %_hints oracle '&SUBSTITUTE VALUES&'

HermannGahm — Tue, 07 Jul 2009 06:42:06 GMT

Hi Ravi,

this line is a hint on ORACLE DB plattforms.


%_hints
oracle '&SUBSTITUTE VALUES&'

However the hint itself is a hint to the database interface, not to the db.

The hint makes the database interface to use the actual values of a query

in the sql statement and sent these to the database. No bind variables are used.

e.g. if your plant/werks is '101'

instead of

WHERE werks = :a1

you get

WHERE werks = '101'

The hint is useful in combination with histograms (frequency statistics) on ORACLE.

With histograms and actual variables instead of bind variables the optimizer may

came up with a better execution plan because the data distribution is considered as

well which is normally (without this hint and the histograms) not the case.

In oder to find out if it is useful you have to check if you have histograms on the columns

you use.

To do so you can use the following select in the ABAP report RSORADJV:

SELECT COLUMN_NAME, HISTOGRAM, NUM_BUCKETS FROM

DBA_TAB_COLUMNS WHERE

TABLE_NAME = '<table_name>';

Kind regards,

Hermann

Re: %_hints oracle '&SUBSTITUTE VALUES&'

Former Member — Tue, 07 Jul 2009 10:08:26 GMT

Hi,

add to Hermanns comments:

The replacement of bind variables with plain values makes sense for long running queries only:

small number of users  starting long running queries.

The time to parse and validate the SQL statement against the data dictionary compared to the runtime of the statement is negligible: i.e. SAP BW uses this kind of substitution in it's SELECTs on the big cube tables (or in your case).

In all other cases binds are recommended because the SQL statement can be reused from the shared cursor cache.

The similarity of bind variable names in every statement leads to 1 parse / validation only:

A lot of users fire short running queries at the database.

 a big LOOP/ENDLOOP firing short running queries at the database.

The time to parse and validate the SQL statement against the data dictionary compared to the runtime of the statement can take a considerable amount of the runtime if you would substitute binds with different plain values.

that said it can be a problem if the hint is not used properly.

bye

Re: %_hints oracle '&SUBSTITUTE VALUES&'

Rui_Dantas — Tue, 07 Jul 2009 13:19:55 GMT

Hi Hermann,

Correct me if I'm wrong, but even if there are no histograms there is one bucket, with the minimum and maximum value for the whole table. This means that oracle knows the minimum and maximum value for a given column, and that can be useful in some situations (so it might make sense to substitute values even if there are no histograms).

Re: %_hints oracle '&SUBSTITUTE VALUES&'

HermannGahm — Tue, 07 Jul 2009 13:46:40 GMT

Hi Rui,

thanks for the hint... i was thinking about this right before sending the last message.

In order to not make the post not too complex i skipped it.

But:

Yes, you are right, we have min and max values (LOW_VALUE, HIGH_VALUE in dba_tab_columns) in the statistics:

e.g. WERKS MIN '000' MAX '901'.

with the condition:

WHERE werks = :a1

the optimizer assumes that the result set is <nr_of_rows> / <distinct_values_for_that_column> (assuming equal data distribution).

with the condition:

WHERE werks = '920'

the optimizer "sees" that we look for a outlier value that might not be existing in the database and uses a different

calculation and costing e.g. a fixed estimation of 0.75 % as result set. (outlier value according to the statistics!)

This can change execution plans dramatically.

Example:

SELECT

FROM

dd03l

WHERE

as4local = 'Z'

use this in ST05 - explain one sql statement...

one time with as4local = :a1

SELECT STATEMENT ( Estimated Costs = 22.314 , Estimated #Rows = 2.364.281 )

1 TABLE ACCESS FULL DD03L

( Estim. Costs = 22.314 , Estim. #Rows = 2.364.281 )

Estim. CPU-Costs = 2.892.023.174 Estim. IO-Costs = 21.870

Filter Predicates

and another time with as4local = 'Z'.

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

2 TABLE ACCESS BY INDEX ROWID DD03L

( Estim. Costs = 269 , Estim. #Rows = 1 )

Estim. CPU-Costs = 1.916.469 Estim. IO-Costs = 269

1 INDEX SKIP SCAN DD03L~4

( Estim. Costs = 2.693 , Estim. #Rows = 1 )

Search Columns: 1

Estim. CPU-Costs = 19.156.874 Estim. IO-Costs = 2.690

Access Predicates Filter Predicates

Z is an oultier value... according to the statistics (high value is T in my system).

So the optimizer assumes 1 row back... and a cost of 269 with a skip scan (instead of 2.3 million rowas with cost 22314 and a full table scan).

If a skip scan is really the better choice and executing faster... who knows... maybe i'll try it later and post the result here for those who are interested... .

Kind regards,

Hermann

Re: %_hints oracle '&SUBSTITUTE VALUES&'

HermannGahm — Tue, 07 Jul 2009 14:26:01 GMT

Update:

Meanwhile i checked it on different ORACLE versions (9.2.x, 10.2.0.2 and 10.2.0.4), the above ESTIMATED execution plans from ST05 are from 10.2.0.2. However even on this system the ACTUAL USED execution plan is a skip scan as well. (So there is no run time difference (the 2 different statements are using the same execution plan, however the estimated execution plan differs). For 9.2.x and 10.2.0.4 there is no difference in my tests, on 9.2.x fts for both (estimated!) and on 10.2.0.4 a skip scan for both (estimated!). Didn't check the run time behaviour.

But in general you are right, Rui, since we have min and max values, if we search for an outlier value according to the statistics, and the optimizer is informed about the outlier value (substitute values) we might get different costing, different estimated plans and in general we might get different executed plans as well. However i never came accross a situation where we used only substitute values without histograms in order to tune a program so far.

btw. the reason for getting another ACTUAL execution plan, than estimated was bind variabl peeking whih was not switched of (as recommended) on my system.

Edited by: Hermann Gahm on Jul 7, 2009 6:12 PM

Re: %_hints oracle '&SUBSTITUTE VALUES&'

Rui_Dantas — Tue, 07 Jul 2009 18:12:58 GMT


> In order to not make the post not too complex i skipped it.

Yes, I almost not saved my comment either because I also thought it would bring extra complexity with something that is basically only of theoritic use.


>However i never came accross a situation where we used only substitute values without histograms in order to tune a program so far

I had one case in the past (don't remember exactly with which table). The users had a mandatory select-options for document number (which they often filled with 0000000000 to 9999999999) and then an optional parameter for customer (or something like that). With bind variables, "docnr between :A1 and :A2" would use the document number index (which is not good), but with values oracle would know that all documents were being selected and so would use the customer index.

I don't remember the exact details or if substitute values was the solution in the end (probably not) but anyway it's one more theoretic use...

Regards,

Rui Dantas

Re: %_hints oracle '&SUBSTITUTE VALUES&'

Rui_Dantas — Wed, 08 Jul 2009 12:15:14 GMT


>            1 INDEX SKIP SCAN DD03L~4
>              ( Estim. Costs = 2.693 , Estim. #Rows = 1 )
>              Search Columns: 1
>              Estim. CPU-Costs = 19.156.874 Estim. IO-Costs = 2.690
>              Access Predicates Filter Predicates

Hi Hermann,

I had one doubt after seeing your explain plan. the "Access Predicates" / "Filter Predicates" (which I don't see in my system) are a 10g thing, right? They appear as clickable buttons in ST05?

(I googled this and apparently it was introduced in 9.2, but I can't see that in my st05; our system is 9.2.0.7)

Rui Dantas

ps: sorry for the off-topic question

Re: %_hints oracle '&SUBSTITUTE VALUES&'

HermannGahm — Wed, 08 Jul 2009 14:54:59 GMT

Hi Rui Dantas,


> I had one doubt after seeing your explain plan. the "Access Predicates" / "Filter Predicates" (which I don't see in my system) are a 10g thing, right? They appear as clickable buttons in ST05?

yes they are clickable buttons and show the access and filter predicates for the relevant step of th execution plan in a popup.


> (I googled this and apparently it was introduced in 9.2, but I can't see that in my st05; our system is 9.2.0.7)

Which SAP Release are you using on 9.2.0.7? For sure they are available in 7.00 and maybe in 6.40 with a high SPS stack (don't know by heart).

However they are available in v$sql_plan in these columns:

ACCESS_PREDICATES VARCHAR2(4000)

Predicates used to locate rows in an access structure. For example, start or stop predicates for an index range scan.

FILTER_PREDICATES VARCHAR2(4000)

Predicates used to filter rows before producing them

and can be queried with any sql on that view on any SAP release

Kind regards,

Hermann

Re: %_hints oracle '&SUBSTITUTE VALUES&'

Rui_Dantas — Wed, 08 Jul 2009 15:24:24 GMT


> Which SAP Release are you using on 9.2.0.7? For sure they are available in 7.00 and maybe in 6.40 with a high SPS stack (don't know by heart).

Good, thanks.

I assume you are talking about SAP_BASIS, right? I tried with our different systems and the results were:

620 SP51: No buttons

640 SP18: No buttons

640 SP23: They are there!

720 SP12: They are there!

Thanks again,

Rui Dantas

Re: %_hints oracle '&SUBSTITUTE VALUES&'

HermannGahm — Wed, 08 Jul 2009 15:31:25 GMT

Hi Rui Dantas,

ok, thanks for the feedback.

So my "feeling" that they appeared in 6.40 at a later stage was right...

Thanks,

Hermann

Re: %_hints oracle '&SUBSTITUTE VALUES&'

Former Member — Mon, 18 Apr 2016 17:16:39 GMT

Hi Hermann,

The creation of the Oracle Histogram is it a basis task?