This article focuses on three database tuning steps developed using real-time performance tuning experiences on IBM DB2 9.7 for the Siebel 8.1 global sales application. Most of the time, the performance of a Siebel application depends largely on the performance of the underlying database. This article will walk through the steps we followed during the performance tuning exercise to achieve an 83 percent performance improvement for Enterprise Integration Manager (EIM) jobs and an approximately 17 percent improvement for user interface (UI) components.
All of the performance tuning exercises were done on IBM AIX 18.104.22.168 TL12 with 48 GB memory running on a PowerPC POWER6 processor, 8 CPUs with 4204 MHz CPU clock rate and DB2 9.7 Fix Pack 5 with a High-Availability Disaster Recovery (HADR) pair between two data centers.
DB2 catalog statistics should be up-to-date so DB2 Optimizer can determine the best data access path. The application team should also be aware of
SIEBSTAT API overheads such as lock-waits when running multiple EIMs in parallel.
Disable SIEBSTAT API for all EIM jobs. Set the UPDATE STATISTICS parameter to FALSE in the EIM configuration file.
Create one big buffer pool for EIM tables:
Use a separate 32 KB automatic storage table space to store all EIM tables with 8 or 16 as extent size and 48 or AUTOMATIC pre-fetch size:
CREATE LARGE TABLESPACE “SIEBEL_EIM_DATA32K” IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE
INITIALSIZE 32 M
NO FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON;
Set all EIM_% tables to VOLATILE to make sure that index scans are preferred over table scans. Let the DBA job manage the statistics update on the EIM tables once a day instead of after every EIM run. For example, to set SIEBEL.EIM_ACCOUNT as volatile use the following command:
|No||Job Name||Timing – Before Change||Timing – After Change||Improvement|
|1||BIP/CNR||7 hours 24 min||1 hour 3 min||85%|
|2||Products – SKU||19 min 11 sec||3 min 23 sec||82%|
|3||Shipment – ADL||9 min 40 sec||2 min 42 sec||72%|
|4||Products – Consumer||10 min 21 sec||1 min 55 sec||80%|
|5||Products – BOM||11 min 50 sec||1 min 18 sec||90%|
|6||Shipment – Daily Aggregations||21 min 56 sec||56 sec||95%|
|7||Customers||16 min 1 sec||34 sec||96%|
DB2 9.7 has many performance tuning features that can help improve application performance significantly.
|No||Parameter Name||Current Value||New Value|
We have had issues with application performance after migrating from DB2 9.1 FP 8 to DB2 9.7 FP 5 with CUR_COMMIT being ON. We observed congestion in HADR during the performance window, along with many log buffer full conditions. After tuning the LOGBUFSZ and using a new DB2 9.7 HADR_SYNCMODE SUPERASYNC, we were able to get the performance back to the normal state. With these changes we were able to achieve a gain of 373.9 minutes in our UI interface jobs. Please be aware that SUPERASYNC will not guarantee 100 percent data availability in case of a primary failure.
|Job Type||Performance Improvement|
DB2 9.7 can make use of operating system I/O completion ports (IOCP) for better I/O capabilities. For instance, page cleaners and pre-fetchers will make use of this asynchronous input/output for faster data access.
We enabled IOCP for AIX 5.3 before migrating the database to DB2 9.7, keeping this new feature in mind.
To check IOCP at the OS layer, execute this command using the instance owner:
lsdev -Cc iocp
iocp0 Available I/O Completion Ports
Unfortunately, IOCP affected the system’s availability. If IOCP is ON at the OS layer, turn the feature off at the DB2 instance layer by using the following command and restart the instance:
December 10, 2013 – 1 PM Eastern
December 11, 2013 – 12 PM Eastern
December 17, 2013 – 11:30 AM Eastern
December 18, 2013 – 1 PM Eastern
December 19, 2013 – 12:30 PM Eastern
January 21, 2014 – 11 AM Eastern