(Oracle)

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. Therefore, it is crucial that the database is tuned for our implementation. If we are using Oracle database as the WebSphere Commerce database, consider the following performance tuning recommendations:

• Keep the database statistics up-to-date:

  Gathering statistics on the WebSphere Commerce schema object helps the database choose the best execution plan for SQL queries. When you run an 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 an SQL query, it relies on these statistics information about the tables and indexes in the query. Choosing the best execution plan for our SQL queries helps improve the performance of the database, improving WebSphere Commerce performance. IBM recommends 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 

  1. The first command line gathers statistics for the entire database.

  2. The second command line gathers statics for a schema; where schema_name is the name of the WebSphere Commerce schema.

  For more information about using the DBMS_STATS package, see the Oracle documentation.

• Review and verify your need for indexes on order-processing related database tables and other tables where block contention occurs during high-peak workloads.

  For an Oracle database, high-peak workloads might adversely affect performance during order processing. This effect can occur due to block contention on an index that is defined for the ORDERS database table or other tables that are queried or updated frequently during high-peak workloads. For instance, contention can occur on an index that is defined for the STORE_ID column of the ORDERS table. If the index where block contention occurs does not provide significant performance benefits for the site, such as to improve queries against the associated table, consider dropping the index.

  Before you drop the index, verify through performance reports, such as Automatic Workload Repository (AWR) reports, that the benefits for the site from keeping the index are insignificant. If you have verified that the benefits for keeping the index are not significant, we can drop the index.