Different exec-plan of sameSQL-Statement in QA-system and productive system
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.