BSIS Secondary index

[Former Member]

Hello,

We created a secondary index on BSIS as

BUKRS

HKONT

BUDAT

XOPVW

ZUONR

Users started complaing about the speed of posting transactions. Is it because of this index?

Thanks in advance

[Former Member]

Answered

[Former Member]

Hi Sameer,

Adding indexes is not a quick solution to all performance issues. Indexes have to be weighed well before implementing them into production as there is always overhead on the database / tables with indexes, additional space requirements and extra structures to be updated during DML operations. Are any of the indexes you added BITMAP indexes? If yes then change them to normal B*Tree index and check if the performance improves. Generating DB stats will also help.

Cheers,

Nisch

[Former Member]

Thanks all for your help. Finally we had to drop the index as the system almost was in hung up position. But just would like to know does anybody have created secondary index on BSIS?

[Former Member]

Sameer,

What's BSIS table size?

Did the hang during index creation or after?

[Former Member]

Can you please post the SQL statement explain that you have found used during your system analysis? (ST04 - Oracle sessions)

[Former Member]

The hung up was after index moved to production and all the batch jobs were running very slow as the load on the system increased. I will try to get the sql statement.

[Former Member]

hi!

You can schedule report RSANAORA to update INDEX statistics. When we schedule a transport with an index creation or change, we also schedule a RSANAORA to update that index.

Cheers

[fidel_vales]

Hi,

with the mentioned information any answer will be a wild guess.

You need to trace a posting, look for the long running queries (assuming that the issue is the DB) and check the query:

what index uses?

is a good index?

Does it has statistics? -> Note, are you in oracle 9i, if so you must calculate statistics for the new index.

etc, etc, etc