# Bad performance while writing to Oracle from Kafka using Kafka Connect

**URL:** <https://forum.confluent.io/t/bad-performance-while-writing-to-oracle-from-kafka-using-kafka-connect/6024>\
**Category:** Kafka Connect\
**Created:** [12 September 2022 08:38 UTC](https://forum.confluent.io/t/bad-performance-while-writing-to-oracle-from-kafka-using-kafka-connect/6024 "2022-09-12T08:38:37Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![oaashish](https://avatars.discourse-cdn.com/v4/letter/o/e0b2c6/32.png) [@oaashish](https://forum.confluent.io/u/oaashish)\
**Post date:** [12 September 2022 08:38 UTC](https://forum.confluent.io/t/bad-performance-while-writing-to-oracle-from-kafka-using-kafka-connect/6024/1 "2022-09-12T08:38:37Z")

</div>

We are using Kafka Connect to write to Oracle and having performance issues while writing to Oracle and based on our understanding so far, It is actually dependent of data and the data type we have defined in Kafka Connect and Oracle.

Test 1:  
We got good write performance with sample data set (Dataset 1) in this test.

Test 2:  
With another sample dataset (Dataset2), write performance was almost half of what it was in first test.

Upon diving deep, we found that the difference between two data sets was that one of the field (STUDENT\_ID) was having all not null values populated in dataset1 whereas in dataset2, it was a mix of NULL and NOT NULL values.

We found that STUDENT\_ID field had a data type mismatch between kafka connect and Oracle. Kafka connect had INT64 whereas oracle had VARCHAR(50). so we changed kafka connect data type to STRING and with this change, we were able to get good write performance even with dataset2

Test 3:  
During production run, we again ended up with poor write performance and this time we have no clue which field (and its values) are causing this.  
Below is the mapping of data type between Kafka connect and Oracle.

```auto
Oracle | Kafka Connect
NUMBER(19,0) | int64
NUMBER | bytes (org.apache.kafka.connect.data.Decimal)
VARCHAR2(10) | string
VARCHAR2(1) | string
CHAR(4) | string
VARCHAR2(10) | string
TIMESTAMP | int64 (org.apache.kafka.connect.data.Timestamp)
CLOB | string

```

Connector configuration below

```auto
{
    "tasks.max": 50,
    "insert.mode" : "insert",
    "value.converter.schemas.enable" : "true",
    "value.converter" : "org.apache.kafka.connect.json.JsonConverter",
    "key.converter" : "org.apache.kafka.connect.storage.StringConverter",
    "consumer.auto.offset.reset" : "latest",
    "auto.offset.reset" : "latest",
    "consumer.override.max.poll.records":5000,
    "consumer.override.max.partition.fetch.bytes": "3145728",
    "consumer.override.group.id":"xy.toto.prim",
    "batch.size":5000                
}

```

Please let me know if you have any suggestions on how to find the root cause for slow performance in Test 3.

**Note 1** : While comparing the Test1 and Test2, we suspected that CLOB field might be the cause for poor write performance but it wasn’t the case. Even after removing CLOB field, we got bad performance and it was only once we corrected the data type for STUDENT\_ID field on Kafka side, we got the good performance.

**Note 2** : We are running this connector from last two years and haven’t faced issue in last two years and its the first time we are getting issues and had to change the data type of STUDENT\_ID to get good performance

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex019/uploads/confluentcommunity/original/1X/c49438c90c9df282e9996fdf6971be890c71b65a.svg) [@system](https://forum.confluent.io/u/system)\
**Post date:** [12 October 2022 08:39 UTC](https://forum.confluent.io/t/bad-performance-while-writing-to-oracle-from-kafka-using-kafka-connect/6024/2 "2022-10-12T08:39:01Z")

</div>

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.