Regarding select statement in a standard report

[Former Member]

Hi all,

Please see the following select statement from standard report RM07DOCS.

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

WHERE 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~MATNR in MATNR

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&'.

Can anybody tell me the purpose of writing the last two lines of the above statement and let me know whether it helps in improving the performance of the select statement.

please explain.

Thanks in advance.

[ThomasZloch]

Values in the WHERE-condition are passed directly to the database without usage of so-called bind variables. I do not know however how this would affect overall performance.

Thomas

Edit: just found out that this allows usage of histograms (sort of advanced table statistics), when searching the optimal access path for the statement (CBO), see SAP note 797629

[Former Member]

Hi Thomas,

thanks for replying. I am new to those terms like Histograms and bind variables. Let me check it and revert.

Thank u.

[ThomasZloch]

> I am new to those terms like Histograms and bind variables.

Same here, this is really advanced stuff (for an ABAP developer). I'm sure there will be further comments by the usual suspects

[Former Member]

jayashri

SUBSTITUTE VALUES are used only when the SQL statement is executed with same values.for ex.see notes below.

RSDD_TMPNM_ADM (Note 802299)

LTAP / LTBP (Note 811852)

MSEG / MKPF (Note 902157, 902675)

PPC_HEAD

BDCP / BDCPS (Note 706836)

F4 search help (Note 1008433)

dont replace any value with a bind variable.

when determining the optimum access path (index access, full table scan...) (see Note 588668). histograms r mainly used

[Former Member]

Hi,

the hint you specify allow using the plain values instead of bind variables.

It is ONLY safe to to this if

1) you execute the query only a few times:

otherwise your shared pool where the executed SQL is stored for reuse will be filled up

• i.e. SAP BW queries default is substituting values because in DW environments you have the ad-hoc query type: few users executing a long running query with only slightly changing values.

2) you have histogram statistics in place to make use of the substitution

I.e. you have a skewed data distribution in the WHERE columns and the histograms allow the CBO to take this into account when suggesting an access plan

in all other cases

use the statement with it's intended use of bind variables.

• i.e. in OLTP you have many users executing a fast running query with different values . The shared pool only stores the 1st executed statement , all the other executions can benefit from the already stored SQL.

bye

yk

[Former Member]

Since this is a standard SAP report, you should search for notes rather than posting to the forum.

In this case, there are a number of performance related notes. See particularly 921164.

Also 902675 along with others.

Rob

Edited by: Rob Burbank on Dec 10, 2008 10:53 AM

[former_member194613]

here you got a rather complicated example, but I guess the question is more understanding than really doing something:

The ABAP Statement is two times processed before it is executed on the DB

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
WHERE 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~MATNR in MATNR
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&'.

First in ABAP, the actuall values in the ranges are inserted, all empty ranges disappear:

I am sorry, but I will not check the actually allowed values, I simply use '12345' for a real input.

Can be like that:

select ...
from mkpf inner join mseg
on mkpf~mandt = mseg~mandt
and mkpf~mblnr = mseg~mblnr
and mkpf~mjahr = mseg~mjahr
WHERE MKPF~BUDAT in BUDAT
and MSEG~BWART = '12345'
and MSEG~CHARG = '12345'
and MSEG~KUNNR = '12345'
and MSEG~LGORT = '12345'
and MSEG~LIFNR   = '12345'
%_HINTS
ORACLE '&SUBSTITUTE VALUES&'.

or that

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
WHERE MSEG~LIFNR = '12345'
and MSEG~SOBKZ = '12345'
and MSEG~WERKS = '12345'
%_HINTS
ORACLE '&SUBSTITUTE VALUES&'.

or lots of other combinations. In the SQL trace you can see which statement is going to the database, see details.

Usually, there something else done:

select ...
from mkpf inner join mseg
on mkpf~mandt = mseg~mandt
and mkpf~mblnr = mseg~mblnr
and mkpf~mjahr = mseg~mjahr
WHERE MKPF~BUDAT in BUDAT
and MSEG~BWART = A0
and MSEG~CHARG = A1
and MSEG~KUNNR = A2
and MSEG~LGORT = A3
and MSEG~LIFNR   = A4

and A0 = '12345', A1 = ... kept somewhere ...

The statement is the processed by the optimizer and the result is kept in the cursor cache.

Advantage:

+ Optimizer processing is needed only once, because statement with other literals look identical if bind variables are used. Optimizer processing can need longer than a fast SELECT SINGLE.

+ Much simpler statistics can be used, no histogramms

Disadvantage:

+ if some some literals are very selective, then it will not be seen.

Your hint overrides the replacement of the literals.

%_HINTS ORACLE '&SUBSTITUTE VALUES&'.

can only work if the statistics with histograms are there. As there is a note, it will probably help for performance. of course depending on the actuall selection in the ranges.

Siegfried

[former_member192616]

Hi,

this is DBI (Database Interface) Hint. Unlike real database hints it is

evaluated in the DBI not in the database. This hint send the actual values

instead of parameter markers to the database.

In ORACLE in the basic statistics some basic values

for inexed columns like min-value, max-value, #distinct values are stored.

With this kind of statistic the optimizer is not aware of how often a specific

value exists in a table, therefore it assumes equal distribution. Imagine

a column called 'STATUS' with 2 distinct values ('open', 'closed'). The optimizer

assumes 50% 'open' and 50 % 'closed'.

With histograms more statistics are collected. Statistics that describe how often

a specific value exists in the table. Imagine 95 % of the records have status 'closed'

and only 5 % status 'open'.

Now, both things together:

1st what are you searching for? open or closed? (provided by the DBI Hint)

2nd how often do the values exist? (provided by the histograms)

much more information is available to the optimizer.

Therefore it can come up with a much better execution plan.

E.g. use an index if you search for 'open'.

Without histograms and hints the optimizer would do a full table scan in all cases.

For example for 'open' it assumes that 50 % of the table's entries will match. If 50%

of a table are needed a full table scan is probably the most efficient execution plan.

For your example with 2 tables it is important to start with the table that has

the smallest resultset and join these entries to the second table. The entries

in MKPF / MSEG are normally not equally distributed. So there is a high chance

of picking the wrong table as a starting point if we assume an equal distribution

of the data.

With this hint and histograms the optimizer gets the information it needs to find out

which table will probaly have the smaller resultset and can come up with a more

efficient execution plan for the join.

Hth,

Hermann

[Former Member]

When you write

SELECT col1 from tab1 where col1 = 'ABC'

the 'ABC' literal is replaced by a bind variable before it is sent to Oracle. The bind variable is a placeholder that allows Oracle to re-use a SQL statement over and over without re-parsing, because parsing can be very expensive if the same statement is executed many times.

However, what is lost is the actual value of 'ABC', so Oracle cannot use histograms (the data distribution) in order to come up with the best execution plan.

By using ORACLE '&SUBSTITUTE VALUES, you're telling the DBI to actually pass the values to Oracle, not the bind variables. The cost, of course, is that Oracle will need to re-parse the statement every time the literal changes, but it will be able to take full advantage of data distribution stats.

Chances are, the report is not run over and over thousands of times during the day, so the re-parsing is not an issue here, and the hint may be helping.