Wrong index is being picked up

[Former Member]

Hi Friends,

We are running oracle 11g 11.2.0.3.0 on Linux having CRM 5.0 and ECC 6.

There are oracle RAC having 9 nodes and 28 SAP APPs instances for CRM and 8 for SAP ECC.

We are facing performance challenge  in transaction code FBL5N in ECC and in CRM we are facing same issue for customer and partner not able to enter meter reading.

We have analyzed both issue in found that it is using wrong index. After making wrong index invisible it is using correct index and performance is also fine.

Now we are not able to find how it picked up wrong index in both cases and nothing has changed in programs and database level.

Regards

Ganesh Tiwari

[stefan_koehler]

Hi Ganesh,

please snap and post the CBO trace for both execution plans.

You can do this based on the shared pool cursor with help of PL/SQL procedure DBMS_SQLDIAG.DUMP_TRACE. Please check this blog post for more information about that procedure.

Regards

Stefan

[fidel_vales]

Ganesh Tiwari wrote:

 Now we are not able to find how it picked up wrong index in both cases and nothing has changed in programs and database level.

Hi,

for experience, this is never true. Perhaps you do not realize or do not know what has changed, but something has changed. The minimum that has changed is that you are inserting rows in the table(s) and (probably) calculating statisitcs. This might be the cause (apart of programs that in "theory" have not changed or new indexes or ....)

More help is not possible whatsoever as I do not have a crystal ball, it would be necessary more investigation about what the CBO is doing in both cases and what really has changed and that is difficult in a forum with lack of information