Performance Tuning Guide for DCC on Oracle DB

Introduction

We have one escalation from customer that reported the DCC has serious performance issue. I setup one test environment and reproduced the problem. From my test results shown, the performance of DCC on Oracle is about 10 times slower than that on DB2. By the performance tuning and bug fix, the performance of DCC on Oracle improved about 10 times.

We should do some tuning on Oracle DB for the typical application of ETL load. ETL load is one typical behavior that delivers huge data into data base without any interrupt. We can call that as bulk loads. The large bulk load brings many challenges to the maintenance of DB and the big I/O contention.

And the DCC also consider how to improve the query to do surrogate key lookups. The most significant problem is the improper SQL where condition ‘is null’ checking is used in the lookup checking. By minimizing the ‘is null’ SQL checking, the performance will be improved significantly.

The DCC ETL is a typical bulk loads (A.K.A batch loads), which deliver big number of rows into the tables and indexes, that will impact the statistics info of the table and index significantly. The Statistics info is the key information to help DB to do the optimized operations. Gathering the statistics is a key tuning factor for the typical bulk loads. So we should determine what frequency of the statistics gathering is the best timeslot to reach the optimal performance.

Methodology

The testing is based on 3 CLM repositories with the same data volume which are generated using data population tool and use DCC server. The testing is target to measure the ETL duration on the specific configuration. The ETL Performance improvement means the ETL duration reduce. By tuning some configuration of OS, Oracle db and DCC configuration, we try to improve the performance of DCC on Oracle Server.

Tuning Guide

OS Part

Oracle has many prerequisites for the OS kernel. Before setup Oracle Server, I have done the responding setting on the RHLE kernel according to the Oracle installation document http://docs.oracle.com/cd/E11882_01/install.112/e24325/toc.htm#CEGHFFGG

Oracle Part

• Avoid I/O Contention between Log File and Data file

So we should separate the Redo Log and data file on to different storage devices. From the performance tuning, the following steps have been done:

1. Put the REDO log files and UNDO table space files onto separated disk. I put the redo log and undo table space files onto one high performance disks (RAID5(8disks), Write back policy).

2. Create more REDO LOG Group so that the redo log can get one inactive log group without waiting. From my test env, I created 6 redo log group, 200M per log file. And then I increase the LOG_BUFFER size to 50M . Make sure there is at least one REDO FILE which status is inactive anytime. The sample command:

> ALTER DATABASE ADD LOGFILE GROUP 4 ('/mnt/fileServer/OraFileLog/redo04.log') SIZE 200M;

3. Increate the Undo LOG table space size. From my test env, I created more files in the table space and increased the table space to 2G.

• Asynchronous I/O

With synchronous I/O, when an I/O request is submitted to the operating system, the writing process is blocked until the write is confirmed as complete noticed by the OS. Then it can be continue. With asynchronous I/O, processing continues while the I/O request is submitted and processed. Use asynchronous I/O when possible to avoid bottleneck. DB application always is less response time better. Asynchronous I/O can help improve the CPU parallelism of disk write and computing.

To enable the asynchronous I/O, you should check if the host OS supports the asynchronous I/O. In this performance tuning, the host OS is RHEL6U2, you can check the RHEL install the aio packages:

1. Check RPM package :

> rpm -qa | grep aio

2. Check Linux:

> cat /proc/slabinfo | grep kio

The 2nd and 3rd column return non-zero value.

3. Check if Oracle support AIO:

> /usr/bin/ldd $ORACLE_HOME/bin/oracle | grep libaio

libaio.so.1 => /usr/lib64/libaio.so.1 (0x00002aaaac4a9000)

> /usr/bin/nm $ORACLE_HOME/bin/oracle | grep io_getevent

Thus, you should enable the ORACLE parameter to support asynchronous I/O:

>alter system set filesystemio_options=setall scope=spfile;

>alter system set disk_asynch_io=TRUE scope=spfile;

Data Warehouse Part

• Avoid the ‘is Null’ or ‘Is not Null’ where condition SQL Checking

In 5.0.2 release, the problem has been fixed. From the thumb of rules, the SQL statements of ‘Select * from table A where A.column_a is null’ should not happen on the data processing, even though there is one index Index_A on the column_a. In general, index won’t include the key ‘null’ . That situation is much worse when the column_a has little rows which value is null. When the select SQL statement executing, the DB will go through the index Index_A and try to get the null value. The explain plan of the SQL statement must be INDEX FULL SCAN. The INDEX Full Scan is very time consuming compared with Index Range scan. In our original implementation, there are huge select SQL executions, just like :

SELECT "EXECUTION_RESULT_ID" FROM RIODS."EXECUTION_RESULT" WHERE "EXTERNAL_ID"=:1 OR ("EXTERNAL_ID" IS NULL AND :2 IS NULL )

Here is the performance comparison base on my test result about the SQL without ‘is null’ and SQL with ‘is null’.

• Schedule Gather Statistics Job

Automatic statistics gathering should be sufficient for most database objects which are being modified at a moderate speed. However, there are cases where automatic statistics gathering may not be adequate. Because the automatic statistics gathering runs during an overnight batch window (10:00 pm-6:00 am), the statistics on tables which are significantly modified during the day may become stale. ETL is a typical bulk loading which insert/update the table significantly in a short time. DCC has the capability to insert more than 100k per hour to the data warehouse. The automatic statistics is far enough to help the DB do the optimized operations. The performance will be degrading as time goes on.

In order to update the statistics info to support DB do the optimized operations, we have to take the frequency of statistics gathering into consideration as an important plan at the beginning.

For the initial load of DCC, the data volume is huge. And the total duration will be more than 10 hours if your data set is big enough. If there is no proactive statistics gathering, the performance would be degraded significantly. You are encouraged to estimate the duration of your DCC (Initial Load and Delta Load). And set the interval of statistics gathering as 10% of total duration. From my initial load testing with huge data, the total duration of CCM part is 1.5 hours, so I set the interval as 10 minutes. Here is one sample performance comparison for the different statistics gathering interval. From the chart shown, the performance of CCM has big impact for the two different statistics gathering. The reason is the 1 hour interval is too big for the total duration of CCM data loading.

Sample steps:

1. Log on the enterprise console using sysdba

2. Open the Server tab page, and find the Jobs.

3. Create one job to run SQL Scripts:

exec dbms_stats.gather_schema_stats(ownname => 'RICALM', options=>'GATHER' ,estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE ,degree => DBMS_STATS.AUTO_DEGREE,cascade =>TRUE );

exec dbms_stats.gather_schema_stats(ownname => 'RIODS', options=>'GATHER' ,estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE ,degree => DBMS_STATS.AUTO_DEGREE,cascade =>TRUE );

4. Schedule the execution as repeating per each 10 minutes.

Topology

The topology under test is based on Standard Topology (E1) Enterprise - Distributed / Linux / DB2.

The specifications of machines under test are listed in the table below. Server tuning details are listed in Appendix A

Test data was generated using automation. The test environment for the latest release was upgraded from the earlier one by using the CLM upgrade process. Please see the system configuration in the under table. And refer to the server tuning details listed in Appendix A

IBM Tivoli Directory Server was used for managing user authentication.

Data volume and shape

The data volume listed in Appendix B

Network connectivity

All server machines and test clients are located on the same subnet. The LAN has 1000 Mbps of maximum bandwidth and less than 0.3 ms latency in ping.

Results

After tuning and bug fix, the performance of DCC on Oracle Server has been improved about 10 times.

Appendix A - Server Tuning Details

-verbose:gc -XX:+PrintGCDetails -Xverbosegclog:gc.log -Xgcpolicy:gencon 
-Xmx8g -Xms8g -Xmn1g -Xcompressedrefs -Xgc:preferredHeapBase=0x100000000
-XX:MaxDirectMemorySize=1g

Appendix B - Data Volume and Shape

N/A: Not applicable.

For more information

• Collaborative Lifecycle Management 2012 Sizing Report (Standard Topology E1)

About the authors

PengPengWang

Questions and comments:

• What other performance information would you like to see here?
• Do you have performance scenarios to share?
• Do you have scenarios that are not addressed in documentation?
• Where are you having problems in performance?

Warning: Can't find topic Deployment.PerformanceDatasheetReaderComments