cancel
Showing results for 
Search instead for 
Did you mean: 

Static SQL variable binding in SEM-BCS / BW applications

Former Member
0 Kudos

Hello,

this is not neccesarily critical but strikes me as rather odd for SAP applications using the Oracle RDBMS.

"BW" seems to issue SQL-Statements without dynamic variable binding, that is I can see SQL-Statement with literals:

SELECT "S27"."/BIC/ZBCS_VER1" AS "ZBCS_VER1" , ...

AND ( ( "D1"."SID_ZBCS_VER1" = 2 ) ) AND ( ( "D1"."SID_ZBCS_VER2" = 6 ) ) AND (

( "D1"."SID_ZBCS_VER3" = 3 ) ) ) )

Usually Statements shouldn't use literals, so that the query looks like this:

"D1"."SID_ZBCS_VER1" = :1

The negative effects of this are among others higher memory consumtion and higher parse rates.

These statements stem from program UCUWB000 in our SEM-BCS system.

I've not come accross this before when analysing SQL-Statements issued by SAP applications, so this seems to be something specific to BW or SEM-BCS.

Can anyone share his / her knowlede on this?

Thanks a lot,

Thorsten

Accepted Solutions (0)

Answers (3)

Answers (3)

stefan_koehler
Active Contributor
0 Kudos

Hello Thorsten,

pretty funny, because of i wrote a similar blog about that topic yesterday (). It's not completely about your question in a BW scenario, but you can see the impact of using bind variables in a SAP environment pretty well.

http://scn.sap.com/community/oracle/blog/2012/08/03/oracle-db-optimizer-part-i--understanding-in-lis...

Usually Statements shouldn't use literals, so that the query looks like this:

"D1"."SID_ZBCS_VER1" = :1

The negative effects of this are among others higher memory consumtion and higher parse rates.

Well in general (for OLTP systems) your statement about literals maybe true, but you are talking about an OLAP system right now. However there are a lot of cases where you should use literals in an OLTP environment too, but this is not the topic right here.

In OLAP systems the same queries are not executed frequently, so the "hard parsing" and "sharing SQL statements / execution plans" does not count that much. It is even more important to get a good/exact calculation of cardinality and costs (with usage of possible query transformation on database level).

The main point for using literals in BW queries is the usage of features of the Oracle CBO (cost based optimizer). Just check my blog and you can see, how easily wrong estimations are calculated by the CBO, if bind variables are used in a SAP environment (because of special suggested database settings).

If you have a performance issue or any further questions, please feel free to contact me (i don't bite ). Contact details are on my SDN profile.

Regards

Stefan

Former Member
0 Kudos

Hi Thorsten,

BCS is not using standard transparent table nomenclature, but rather generates new table names as a result of master data configuration and generation of programs. those generated table names are unique to each instance and cannot be predetermined. it's like creating a new BW database structure with a query from OLTP if that makes any sense.

you can start by running transaction UCWB in ECC and then see how it connects to a BW backend. it is also closely related to shared memory (transaction SHMM).

the good news is that it's one of the earliest applications supported on HANA (since Nov 2011).

as Ethan is saying working closely with SAP support won't hurt you.

have fun,

gm

esjewett
Active Contributor
0 Kudos

I think most BW-generated SQL uses dynamic binding, IIRC from looking at lots of SQL traces of BEx queries But BCS is a different program with different generated SQL. You'd probably get the best results by opening a customer message if you feel that this SQL is causing performance problems for you.

Good luck!

Ethan