MustGather: WebSphere Commerce V6.0 performance

problems with Oracle databases
 Technote (troubleshooting)
This document applies only to the following language version(s):
English
Problem(Abstract)
MustGather for problems with IBM WebSphere Commerce servers experiencing performance problems with Oracle databases such as database deadlocks, timeouts, and slow queries. Gathering this information before calling IBM support will help familiarize you with the troubleshooting process and save you time.
Cause
Before collecting the information described below, have you considered the following procedures:
1) Performing regular database maintenance such as updating the table and index statistics. Please refer to Oracle documentaton for details.

2) Upgrading your software (Oracle, Commerce, Application Server) to the latest service level to take advantage of the most up-to-date performance enhancements and fixes.

3) Reviewing WebSphere Commerce customizations and extensions. Many performance problems reported by WebSphere Commerce customers result in identifying defects in customization. Reviewing customization and understanding how it works is critical to the problem determination process when working with IBM support.

4) Requesting assistance from Oracle. Many Oracle performance problems can be resolved by properly maintaining and tuning your Oracle database. Having Oracle support or your Oracle DBA review the problem first will save you time in narrowing down the issue.

5) Conducting a performance tuning assessment. Database performance depends largely on hardware and the types of traffic accessing it. Unfortunately, all WebSphere Commerce implementations are unique in this regard, so identifying performance issues typically require a customized approach to your WebSphere Commerce environment. Most performance problems can be identified in a controlled environment with simulated load. It is strongly recommended that you conduct such an assessment to ensure that your environment is running smoothly prior to going into production or after major changes.
Resolving the problem
The following instructions will describe how to prepare your environment for troubleshooting, collect the necessary data, and gather the required information for IBM support.
Part A: Configure environment for data collection

Follow the instructions below to set up the problematic system for capturing required information:

1. Enable WebSphere Commerce WC_SERVER trace.

Follow instructions here to enable the following trace specification:

com.ibm.websphere.commerce.WC_SERVER=all

2. Enable Oracle StatsPack.

StatsPack is a diagnosis tool for troubleshooting instance-wide Oracle database performance problems. It also supports application tuning activities by providing data that identifies high-load SQL statements. StatsPack consists of a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data.


a) Enable timed_statistics:

Alter system set timed_statistics=true;

NOTE: Enabling timed_statistics will increase load on the system. Please remember to disable it when the data collection is finished.

b) Create a tablespace for StatsPack:

create tablespace perfstat datafile '/path/to/my/tablespaces/perfstat.dbf'
size 500M autoextend off
extent management local autoallocate
logging
online
segment space management auto;

c) Install StatsPack:

cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba"
@spcreate
exit


3. Enable Oracle SQL trace

NOTE: This is very intrusive to your system. Extra caution need to be taken before you decide to enable the trace.

Alter system set sql_trace=true;

NOTE: This change does not apply to existing database connections. Your WebSphere Commerce Server and Oracle database will need to be restarted for changes to take effect. If you are unable to restart the server, please refer to Oracle for details on how to enable this without restarting. Also note that sql_trace will affect the performance of your site. It should be enabled only for the period of time that you need in order to capture the problem.

For oracle 10gR1 and above, DBMS.MONITOR is a useful package to enable SQL tracing by client id, session id, module level or instance level. --- More assistant on DBMS.MONITOR, please talk to your DBA or oracle support.



Part B: Reproduce the problem

In order to get meaningful performance data from Oracle StatsPack, we need multiple snapshots before, during, and after the problem. Use the following command to generate an Oracle snapshot:

sqlplus perfstat/perfstat
execute statspack.snap; -- or statspack.snap(i_snap_level=>10) to get maximium stats

Overall...

  1. Enable necessary tracing as described above.

  2. Capture an Oracle snapshot before the problem occurs.

  3. Reproduce the problem or wait for problem to recur.

  4. Capture Oracle snapshots periodically while you wait for the problem to recur or while the problem is occurring.

  5. Capture another Oracle snapshot after the problem stops.

  6. Disable timed_statistics:

Alter system set timed_statistics=false;

  1. Disable the SQL trace:

Alter system set sql_trace=false;




Part C: Collect all necessary data


1. Generate Oracle StatsPack report and save it to a file.

sqlplus perfstat/pwd
@spreport


2. Format Oracle SQL trace.

Trace files with SQL information are created in the $ORACLE_BASE/admin/<sid>/udump/ directory. Whether you use DBMS_MONITOR or Oracle Enterprise Manager to set up tracing, you'll use the trcsess command line tool to consolidate trace files.

Looking for wait events in trace file to find out what resource it's waiting for and why it's waiting


You can format these files with the tkprof utility as follows:

tkprof <tracefile> <outputfile>

NOTE: repeat this step if you have multiple tracefiles.


3. Gather all necessary files

Commerce trace files

For Websphere Commerce 6.0:
<WAS_HOME>\profiles\<profile>\logs\server1\trace.log
<WAS_HOME>\profiles\<profile>\logs\server1\SystemOut.log
<WAS_HOME>\profiles\<profile>\logs\server1\SystemErr.log

For Websphere Commerce 5.6.1, the log files are located in directory <WAS_HOME>\logs\<WC_instance>

Oracle trace files
alert.log
statspack output
sql trace output



Part D: Submit files to IBM Support

Include the information collected in Part C in the package that you send to IBM. Refer to the section entitled Submitting information to IBM Suport in the MustGather: Read first for all WebSphere Commerce products technote for details on how to submit data to IBM

Part E: Reference

1. Generate Explain Plan for specific SQL statement.

If one specific query is found suspicious from Oracle sql trace file or statspack. If you want to run the SQL statements from oracle database directly for testing purpose, you can generate explain plan by following steps below. There are a few ways to generate execution plan, only one of them is listed here.

Use the SQL script UTLXPLAN.SQL to create a sample output table called PLAN_TABLE in your schema. .

Include the EXPLAIN PLAN FOR clause prior to the SQL statement.

After issuing the EXPLAIN PLAN statement, use one of the scripts or package provided by Oracle to display the most recent plan table output.

The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is normally executed first.

Run the query in different time to see whether same plan is generated.

2. Use oracle SQL tuning advisor to tune the SQL statement.

Cross Reference information
Segment Product Component Platform Version Edition
Commerce WebSphere Commerce Professional Edition Performance AIX, Linux, Linux xSeries, Solaris, Windows 6.0 Professional Edition
Commerce WebSphere Commerce - Express Performance Linux, Linux xSeries, Windows 6.0 Express
   

Document Information

Current web document: http://www.ibm.com/support/docview.wss?uid=swg21260710