number of open indexes reaching 100%

[ninad_ghalsasi]

Hi,

I need help on Sybase Database parameter "number of open indexes".

While analyzing MIRO performance issue, we found that above parameter is reaching its limit.

I would like to know, if my observation is correct and will help in improving MIRO transaction performance.

Before this, we have done following changes for MIRO performance issue as recommended by SAP, but did not helped:

• Created secondary indexes on MSEG, CIN, RBKP and RSEG Tables
• Increased following memory parameters of database according to database size (266 GB)
  • Max Memory - 56 GB
  • Number of open partitions - 200000
  • Procedure cache size - 15 GB
  • Default cache size - 35 GB
• Sybase patch upgrade from 16.0.0.05 to 16.0.02.04

Pls. reply with your expert comments on above and also if any suggestions on improving performance of MIRO transaction as early as possible.

Thanks,

Ninad

P.S: attaching sp_monitorconfig "all" output of production server for your ready reference.

ACB_PRD_DB_MONITOR-CONFIG.txt.zip

[ninad_ghalsasi]

Thanks a ton Mark,

I have updated the parameter as suggested along with following, it made a considerable improvement in MIRO transaction:

• Updated statistics of MSEG tables manually
• Increase of “heap memory per user” to 52000
• Increase of “number of open indexes” 100000

Thanks a lot again for your expert comments...

_________________________________________________________________________________________________________

So, in totality we have made following changes in the system (ECC 6, NetWeaver 7.4, Sybase DB 16.0, Windows) to improve MIRO transaction performance:

• Created secondary indexes on MSEG, CIN, RBKP and RSEG Tables
• Increased following memory parameters of database according to database size (266 GB)
  • Max Memory - 56 GB
  • Number of open partitions - 200000
  • Procedure cache size - 15 GB
  • Default cache size - 35 GB
• Sybase patch upgrade from 16.0.0.05 to 16.0.02.04
• Updated statistics of MSEG tables manually
• Increase of “heap memory per user” to 52000
• Increase of “number of open indexes” to 100000

Best Regards,

Ninad