Tuning and Statistics

Here are some recommendations to consider:

In the init<SID>.ora file, increase the DB_BLOCK_BUFFERS setting to the size appropriate for the Oracle server system.
Set the Oracle optimizer mode to "choose" by adding the following line to the init<SID>.ora file:
optimizer_mode = CHOOSE

Choose mode allows Oracle to decide between rule-based or cost-based optimizing depending on whether or not statistics are calculated on the tables used. With this setting in place, you should calculate statistics regularly. (Statistics are required and used for cost-based optimizing.) Ensure that other optimizer settings are also correct. Refer to optimizer_index_caching and optimizer_index_cost_adj. Refer to Oracle documentation for more information on optimizer modes.
Calculate statistics on all ENOVIA Live Collaboration tables by executing the following MQL command <mql> validate level 4;.
This forces the mode to be cost-based, since statistics will be available for every table, and in most cases, performance is optimal. However, the validate level 4 command should be run after any substantial data load operation, and periodically, thereafter, to update the statistics.

Depending on the nature of the data, some large databases perform better using the rule-based optimizer. If the performance degrades noticeably after you tune the database as described above, try removing all Oracle table statistics by running an MQL validate level 2 command, which forces the use of the rule-based optimizer. Once you are running with the rule-based optimizer, you should not calculate statistics again.

A Note About Scability and Performance

The issue of scalability comes down to the specific schema and functionality of the implementation. An implementation may be able to support 4,000 concurrent users on a certain hardware/software configuration, but on that same configuration, they might not be able to support a different custom application with only 500 concurrent users. Using ENOVIA Matrix Web Navigator means that the schema, the triggers, etc. are all custom and will scale at a different rate from any other ENOVIA product implementation.

To improve performance, you should find out what in the specific application uses a lot of resources and then either re-architect it to run more efficiently or offload it to a background process on another server. If 500 users are using 100% of the CPU, then either more CPU is needed or the application needs to be more scalable. Common sources of scalability issues are: inefficient use of APIs, complex triggers that need to be executed frequently, and inefficient data modeling. By tuning your database and possibly adding additional indices, you can improve both performance and scalability simultaneously. We also recommend reviewing the design and code for your application specifically with an eye toward scalability.