This chapter describes tips for tuning Oracle B2B performance. It contains the following sections:
Oracle B2B (Business to Business) is an e-commerce gateway that enables the secure and reliable exchange of business documents between an enterprise and its trading partners. Oracle B2B supports business-to-business document standards, security, transports, messaging services, and trading partner management. With Oracle B2B used as a binding component within an Oracle SOA Suite composite application, end-to-end business processes can be implemented.
For more information about Oracle SOA Suite, see Oracle Fusion Middleware Developer's Guide for Oracle SOA Suite.
The following sections describe basic tuning configurations that you should also consider while tuning:
Tuning the B2B_DATA_STORAGE table attributes can improve performance in some deployments. Review your use case scenarios and then consider the following to improve performance:
If the B2B_DATA_STORAGE table logging attribute is set to NOLOGGING, consider changing it to LOGGING.
When performing DML operations using NOLOGGING option, the unrecoverable System Change Number (SCN) in the control files are recorded and this can lead to "control file contention" under heavy data load.
Consider migrating LOB columns in the B2B_DATA_STORAGE table to SecureFiles
Partition the table B2B_DATA_STORAGE by HASH(ID) with 128 partitions
Partition the PK index by GLobal HASH(ID) with 64 partitions
Changing the value of the Metadata Service (MDS) instance cache size can improve performance. A ratio of 5:1 is recommended for the xmx-to-mdsCache values. For example, if the xmx size is 1024, maintain mdsCache at 200 MB.
These settings can be modified using Oracle Enterprise Manager Fusion Middleware Control. For more information, see "Configuring Oracle B2B" in the Oracle Fusion Middleware Administrator's Guide for Oracle SOA Suite and Oracle Business Process Management Suite.
B2B is a multithreaded message-processing engine, with a clear segregation of inbound and outbound processing capability.
Changing the value of b2b.inboundThreadCount and b2b.outboundThreadCount can improve Oracle B2B message processing. The recommended value depends on your system. For a 2 GB computer, for example, a setting of 3 to 5 is recommended. The b2b.inboundThreadSleepTime and b2b.outboundThreadSleepTime properties put a thread to sleep after message processing. A setting between 10 and 1000 (milliseconds) is recommended.
These settings can be modified using Oracle Enterprise Manager Fusion Middleware Control. For more information, see "Configuring Oracle B2B" in the Oracle Fusion Middleware Administrator's Guide for Oracle SOA Suite and Oracle Business Process Management Suite.
The JMS Out Queue component is the element that enables B2B to receive data from a JMS queue. To maximize performance, consider enabling the Multiple JMSOUTQUEUES and create the corresponding listening channels in B2B.
You can improve B2B performance by tuning the Oracle HTTP Server (OHS) or by fine-tuning the database. This section includes the following parts:
Oracle HTTP Server (OHS) is the web server component for Oracle Fusion Middleware. It provides a listener for Oracle WebLogic Server and it is used for HTTP load balancing across various B2B instances managed through Web logic servers. You can see Chapter 6, "Oracle HTTP Server Performance Tuning" for more information about this component.
This section describes several OHS configurations that could improve B2B performance.
The configuration file mod_wl_oh.conf helps the end user configure the incoming request pattern. As par of load balancing, the incoming request will be redirected to the servers specified in the cluster.
The match expression will identify the incoming http request in the form of ”b2b/httpReceiver.” Then the request will be routed to any of the servers available in the configured weblogic cluster.
This configuration snippet may resemble the following:
mod_wl_ohs.conf <IfModule weblogic_module> # WebLogicHost <WEBLOGIC_HOST> # WebLogicPort <WEBLOGIC_PORT> # Debug ON # WLLogFile /tmp/weblogic.log # MatchExpression *.jsp WeblogicCluster host.your.site:8000 MatchExpression /b2b/httpReceiver </IfModule>
The httpd.conf configuration file specifies the maximum number of HTTP requests that can be processed simultaneously, logging details, and certain limits and time outs. It is located in ORACLE_INSTANCE/config/OHSComponent/<ohsname>/httpd.conf.
You may want to make the following changes to your httpd.conf to configure the OHS listening port.
Set the OHS Listen Port to Listen 7777.
Set the following connection properties to the described values:
Timeout 600
MaxKeepAliveRequests 1000
KeepAliveTimeout 5
The worker multi-processing module (MPM) uses Multi-Process-Multi-Threads model and is the default MPM on all platforms other than Microsoft Windows platforms. Multi-thread support is more scalable by using fewer system resources, while multi-process support is more stable.
Consider tuning the following the worker MPM parameters.
This parameter specifies the number of child server processes created on startup. If you expect a sudden load after restart, set this value based on the number child servers required. The default value is 12.
This parameter limits the total number of servers running, i.e. the limit on the number of clients who can simultaneously connect. If the number of client connections reaches this limit, then subsequent requests are queued. The default value is 1500.
These two parameters control the server-pool size. The server will periodically check how many server threads are waiting for a request. If there are fewer than MinSpareThreads, the server creates a new spare. If there are more than MaxSpareThreads, some of the spares are removed. The default MinSpareThreads value is 25, and the default MaxSpareThreads value is 100.
This section describes several tuning configurations you can consider to resolve specific problems.
If you are having issues scaling beyond 20 messages/second, consider creating a separate table space for B2B_DATA_STORAGE and changing the block size to 16K.
This solution may resemble the following example:
CREATE TABLE "PS121_SOAINFRA"."B2B_DATA_STORAGE"
( "VALUE_SELECTOR" VARCHAR2(256),
"CLOB_VALUE" CLOB,
"BLOB_VALUE" BLOB,
"DATA_SIZE" NUMBER(10,0),
"ATTRIBUTE1" VARCHAR2(256),
"ATTRIBUTE2" VARCHAR2(256),
"LABEL" VARCHAR2(256),
"DOCUMENT_ID" VARCHAR2(200),
"JOB_ID" VARCHAR2(128),
"ID" VARCHAR2(256) NOT NULL ENABLE,
CONSTRAINT "B2B_DATA_STORAGE_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 50 INITRANS 85 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "B2B_DATA_STORAGE_TABLE" ENABLE,
CONSTRAINT "B2B_DS_DOC_ID_FK" FOREIGN KEY ("DOCUMENT_ID")
REFERENCES "PS121_SOAINFRA"."XML_DOCUMENT" ("DOCUMENT_ID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 75 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "B2B_DATA_STORAGE_TABLE"
LOB ("CLOB_VALUE") STORE AS BASICFILE (
TABLESPACE "B2B_DATA_STORAGE_TABLE" ENABLE STORAGE IN ROW CHUNK 16384
NOCACHE NOLOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
LOB ("BLOB_VALUE") STORE AS BASICFILE "BASICFILE1"(
TABLESPACE "B2B_DATA_STORAGE_TABLE" ENABLE STORAGE IN ROW CHUNK 16384
NOCACHE NOLOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
REM END PS121_SOAINFRA B2B_DATA_STORAGE
REM START PS121_SOAINFRA B2B_IDX_DS_DOC
CREATE INDEX "PS121_SOAINFRA"."B2B_IDX_DS_DOC" ON "PS121_SOAINFRA"."B2B_DATA_STORAGE" ("DOCUMENT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PS121_SOAINFRA"
REM END PS121_SOAINFRA B2B_IDX_DS_DOC
To increase maximum throughput for messages per second with more efficient CPU usage, you can gather stats. To do this, you will need to do the following:
Enable auto stats for all important tables.
Unlock table stats for all important tables.
Collect table stats for all tables you have modified.
Lock table stats for all important tables.
Disable auto stats.
Below is example code:
EXEC DBMS_SCHEDULER.ENABLE('SYS.BSLN_MAINTAIN_STATS_JOB');
exec DBMS_STATS.UNLOCK_Table_Stats(ownname=>'PS121_SOAINFRA',tabname=>'B2B_BUSINESS_MESSAGE'); exec DBMS_STATS.UNLOCK_Table_Stats(ownname=>'PS121_SOAINFRA',tabname=>'B2B_EXT_BUSINESS_MESSAGE'); exec DBMS_STATS.UNLOCK_Table_Stats(ownname=>'PS121_SOAINFRA',tabname=>'B2B_WIRE_MESSAGE'); exec DBMS_STATS.UNLOCK_Table_Stats(ownname=>'PS121_SOAINFRA',tabname=>'B2B_DATA_STORAGE'); exec DBMS_STATS.UNLOCK_Table_Stats(ownname=>'PS121_SOAINFRA',tabname=>'B2B_APP_MESSAGE'); exec DBMS_STATS.UNLOCK_Table_Stats(ownname=>'PS121_SOAINFRA',tabname=>'B2B_BUSINESS_MESSAGE_RETRY'); exec DBMS_STATS.UNLOCK_Table_Stats(ownname=>'PS121_SOAINFRA',tabname=>'B2B_HA_REGISTRY'); exec DBMS_STATS.UNLOCK_Table_Stats(ownname=>'PS121_SOAINFRA',tabname=>'B2B_LIFECYCLE');
Collecting stats from all important tables
exec DBMS_STATS.Gather_Table_Stats(ownname=>'PS121_SOAINFRA', tabname => 'B2B_BUSINESS_MESSAGE', estimate_percent=>100, cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 254', degree =>DBMS_STATS.AUTO_DEGREE); exec DBMS_STATS.Gather_Table_Stats(ownname=>'PS121_SOAINFRA', tabname => 'B2B_EXT_BUSINESS_MESSAGE', estimate_percent=>100, cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 254', degree =>DBMS_STATS.AUTO_DEGREE); exec DBMS_STATS.Gather_Table_Stats(ownname=>'PS121_SOAINFRA', tabname => 'B2B_WIRE_MESSAGE', estimate_percent=>100, cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 254', degree =>DBMS_STATS.AUTO_DEGREE); exec DBMS_STATS.Gather_Table_Stats(ownname=>'PS121_SOAINFRA', tabname => 'B2B_DATA_STORAGE', estimate_percent=>100, cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 254', degree =>DBMS_STATS.AUTO_DEGREE); exec DBMS_STATS.Gather_Table_Stats(ownname=>'PS121_SOAINFRA', tabname => 'B2B_APP_MESSAGE', estimate_percent=>100, cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 254', degree =>DBMS_STATS.AUTO_DEGREE); exec DBMS_STATS.Gather_Table_Stats(ownname=>'PS121_SOAINFRA', tabname => 'B2B_BUSINESS_MESSAGE_RETRY', estimate_percent=>100, cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 254', degree =>DBMS_STATS.AUTO_DEGREE); exec DBMS_STATS.Gather_Table_Stats(ownname=>'PS121_SOAINFRA', tabname => 'B2B_HA_REGISTRY', estimate_percent=>100, cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 254', degree =>DBMS_STATS.AUTO_DEGREE); exec DBMS_STATS.Gather_Table_Stats(ownname=>'PS121_SOAINFRA', tabname => 'B2B_LIFECYCLE', estimate_percent=>100, cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 254', degree =>DBMS_STATS.AUTO_DEGREE);
exec DBMS_STATS.Lock_Table_Stats(ownname=>'PS121_SOAINFRA',tabname=>'B2B_BUSINESS_MESSAGE'); exec DBMS_STATS.Lock_Table_Stats(ownname=>'PS121_SOAINFRA',tabname=>'B2B_EXT_BUSINESS_MESSAGE'); exec DBMS_STATS.Lock_Table_Stats(ownname=>'PS121_SOAINFRA',tabname=>'B2B_WIRE_MESSAGE'); exec DBMS_STATS.Lock_Table_Stats(ownname=>'PS121_SOAINFRA',tabname=>'B2B_DATA_STORAGE'); exec DBMS_STATS.Lock_Table_Stats(ownname=>'PS121_SOAINFRA',tabname=>'B2B_APP_MESSAGE'); exec DBMS_STATS.Lock_Table_Stats(ownname=>'PS121_SOAINFRA',tabname=>'B2B_BUSINESS_MESSAGE_RETRY'); exec DBMS_STATS.Lock_Table_Stats(ownname=>'PS121_SOAINFRA',tabname=>'B2B_HA_REGISTRY'); exec DBMS_STATS.Lock_Table_Stats(ownname=>'PS121_SOAINFRA',tabname=>'B2B_LIFECYCLE');
EXEC DBMS_SCHEDULER.DISABLE('SYS.BSLN_MAINTAIN_STATS_JOB');
If you experience I/O contention due to high number of inserts, you should partition table space.
You will need to create a table, as shown below:
DROP TABLE B2B_DATA_STORAGE;
CREATE TABLE B2B_DATA_STORAGE ( "VALUE_SELECTOR" VARCHAR2(256), "CLOB_VALUE" CLOB, BLOB_VALUE BLOB, DATA_SIZE NUMBER(10,0), ATTRIBUTE1 VARCHAR2(256), ATTRIBUTE2 VARCHAR2(256), LABEL VARCHAR2(256), DOCUMENT_ID VARCHAR2(200), JOB_ID VARCHAR2(128), "CPST_INST_CREATED_TIME" TIMESTAMP (6) DEFAULT systimestamp-30, ID VARCHAR2(256) NOT NULL ) TABLESPACE B2B_DATA_STORAGE_TABLE LOB (CLOB_VALUE) STORE AS SECUREFILE (TABLESPACE B2B_DATA_STORAGE_TABLE) LOB (BLOB_VALUE) STORE AS SECUREFILE BASICFILE1(TABLESPACE B2B_DATA_STORAGE_TABLE) partition by hash(id) partitions 100 store in(B2B_DATA_STORAGE_TABLE); ALTER TABLE B2B_DATA_STORAGE ADD CONSTRAINT B2B_DATA_STORAGE_PK PRIMARY KEY (ID) USING INDEX (CREATE UNIQUE INDEX B2B_DATA_STORAGE_PK ON B2B_DATA_STORAGE (ID) TABLESPACE B2B_DATA_STORAGE_TABLE REVERSE); CREATE INDEX B2B_IDX_DS_DOC ON B2B_DATA_STORAGE (DOCUMENT_ID) TABLESPACE B2B_DATA_STORAGE_TABLE; ALTER TABLE B2B_DATA_STORAGE ADD CONSTRAINT B2B_DS_DOC_ID_FK FOREIGN KEY (DOCUMENT_ID) REFERENCES XML_DOCUMENT (DOCUMENT_ID);
Then you need to add the below constraints to the table:
ALTER TABLE B2B_WIRE_MESSAGE ADD CONSTRAINT B2B_WM_DS_FK FOREIGN KEY(PACKED_MESSAGE) REFERENCES B2B_DATA_STORAGE (ID) ON DELETE CASCADE ENABLE; ALTER TABLE B2B_WIRE_MESSAGE ADD CONSTRAINT B2B_WM_PS_FK FOREIGN KEY(PAYLOAD_STORAGE) REFERENCES B2B_DATA_STORAGE (ID) ON DELETE CASCADE ENABLE; ALTER TABLE B2B_BUSINESS_MESSAGE ADD CONSTRAINT B2B_BM_NDS_FK FOREIGN KEY (NATIVE_DATA_STORAGE) REFERENCES B2B_DATA_STORAGE (ID) ON DELETE CASCADE ENABLE; ALTER TABLE B2B_BUSINESS_MESSAGE ADD CONSTRAINT B2B_BM_TDS_FK FOREIGN KEY (TRANSLATED_DATA_STORAGE) REFERENCES B2B_DATA_STORAGE (ID) ON DELETE CASCADE ENABLE; ALTER TABLE B2B_BUSINESS_MESSAGE ADD CONSTRAINT B2B_BM_AS_FK FOREIGN KEY (ATTACHMENT_STORAGE) REFERENCES B2B_DATA_STORAGE (ID) ON DELETE CASCADE ENABLE; ALTER TABLE B2B_BUSINESS_MESSAGE ADD CONSTRAINT B2B_BM_PS_FK FOREIGN KEY (PAYLOAD_STORAGE) REFERENCES B2B_DATA_STORAGE (ID) ON DELETE CASCADE ENABLE; ALTER TABLE B2B_APP_MESSAGE ADD CONSTRAINT B2B_AM_PAYLOAD_FK FOREIGN KEY (PAYLOAD) REFERENCES B2B_DATA_STORAGE (ID) ON DELETE CASCADE ENABLE; ALTER TABLE B2B_APP_MESSAGE ADD CONSTRAINT B2B_AM_AS_FK FOREIGN KEY (ATTACHMENT_STORAGE) REFERENCES B2B_DATA_STORAGE (ID) ON DELETE CASCADE ENABLE;