Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Different exec-plan of sameSQL-Statement in QA-system and productive system

Hello,

we have a database request

SELECT * FROM COSS WHERE "MANDT" = '100' AND "PAROB" = 'PR00042599' AND "OBJNR" LIKE 'KL1100__________E/MD' AND "GJAHR" <= 2006 AND "WRTTP" = '04'

that ist invoked by a customer-developed report. The database request runs relatively fast ( 35 sek) in our quality-assurance system ( Q03) but in our productive system ( P03) it runs into a server timeout ( 1200 sek.).

We created an Index coss~Z1 with fields OBJNR, PAROB, GJAHR and WRTTP for faster access.

In Q03 the cost-based-optimizer uses index cossZ1 correctly but in P03 it uses coss0, which is fairly inefficient, because coss~0 includes all key-fields of table coss and this in a different alignment.

How is such a completely different execution plan explainable between two systems which are fairly equal (same OS-Version, ORACLE-Version, R/3-kernel-version, same data in table coss) .

I write u201Cfairlyu201D because they differ only in hardware aspects ( amount of memory, filesystems etc. ).

Funny thing is, when the abovementioned SQL-Statement is typed into the ORACLE-Console ( SQLPLUS) then the result comes within a few seconds in BOTH systems and thereby it uses a completely different index coss~1.

Is there a way to force a specific execution plan out of the ABAP-Coding ( force a certain index ) ?

We have also tried already several maintenance things like rebuild indices and update statistics, but none of them quite worked.

Thanks.

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question