Database (Oracle) performance considerations
The database is typically one of the potential areas for bottlenecks that makes WebSphere Commerce unable to scale and perform well. It is therefore crucial that the database is tuned appropriately for the implementation.
Before attempting to tune the database, it is important to realize that a WebSphere Commerce implementation is considered an On-Line Transaction Processing (OLTP) application, and hence the database should be tuned for an OLTP application. Typical characteristics of an OLTP application are:
- It has frequent insert or update activity.
- There are a high number of users accessing the database.
- SQL executing against the database is quick and relatively simple.
If you are using Oracle database as the WebSphere Commerce database, consider the following performance tuning recommendations:
- Keep the database statistics up-to-date:
Gather statistics on the WebSphere Commerce schema object helps the database choose the best execution plan for SQL queries. When you run a SQL query, the database converts the query into an execution plan, and chooses the best way to retrieve data. For the Oracle database to choose the best execution plan for a SQL query, it relies on these statistics information about the tables and indexes in the query. Choosing the best execution plan for the SQL queries helps improve the performance of the database, improving WebSphere Commerce performance.
You are recommended to use the DBMS_STATS package instead of the ANALYZE command to gather the database statistics. From the SQLPlus prompt, run the following commands:
exec dbms_stats.gather_database_stats; 1 exec dbms_stats.gather_schema_stats( ownname=>'schema_name',granularity=>'ALL',DEGREE=>3, OPTIONS=>'GATHER',CASCADE=>TRUE); 2
- The first command-line gathers statistics for the entire database.
- The second command-line gathers statics for a schema; where schema_name is the name of the WebSphere Commerce schema.
Refer to the Oracle documentation for more information about using the DBMS_STATS package.