Yes, I did tried those configurations. But still same slow down. My test case works fine for 11g Oracle drivers.
jdbc.pool.maxTotal jdbc.pool.minIdle jdbc.pool.testOnBorrow InitialSize too. On Thu, May 23, 2019 at 2:20 PM FabryProg <fabryp...@gmail.com> wrote: > Hi guy > > Perhaps the error is another: do you tried to change initial and max pool > size ? Or connectionharvest settings? > > > King regards > > > Il Gio 23 Mag 2019, 23:12 Saiteja Parna <saitejap.j...@gmail.com> ha > scritto: > > > This issue is resolved. My post might help others. > > Batch Update/Insert/Delete has issues with Oracle_Jdbc drivers 12c and > > above. > > If any one using Camel-sql or any other Camel database components with > > Oracle_jdbc drivers (12c and latest) will see very slow performance issue > > with batch insert/update/delete with very big records(like a record with > 90 > > columns). > > Oracle team used my test case(Spring JDBC with Oracle Driver) and > confirmed > > the bug. > > > > > > > > > > > > > > On Mon, Apr 15, 2019 at 10:36 AM Saiteja Parna <saitejap.j...@gmail.com> > > wrote: > > > > > Hello, > > > > > > Batch update/insert are deprecated in the Oracle drivers 12c. Please > > Check > > > the description below. > > > > > > *Oracle*: > > > Oracle update batching was deprecated in Oracle Database 12c Release 1 > > > (12.1). Starting in Oracle Database 12c Release 2 (12.2), Oracle update > > > batching is a no operation code (no-op). This means that if you > implement > > > Oracle update batching in your application, using the Oracle Database > 12c > > > Release 2 (12.2) JDBC driver, then the specified batch size is not set > > and > > > results in a batch size of 1. With this batch setting, your application > > > processes one row at a time. Oracle strongly recommends that you use > the > > > standard JDBC batching if you are using the Oracle Database 12c > Release 2 > > > (12.2) JDBC driver. > > > > > > > > > *Question*: As per the above description, It is recommended to set > Batch > > > Size. But i don't see BatchSize property for Camel-SQL document. > > > I tried *template.batchSize=500* on camel-sql endpoint but it didn't > > > work. Still it is taking 30 mins to insert 4000 records. > > > > > > If anyone wants to replicate the issue, i copied my blueprint in the > > above > > > email. > > > > > > > > > > > > > > > On Fri, Apr 12, 2019 at 4:04 PM sai [via Camel] < > > > ml+s465427n5833064...@n5.nabble.com> wrote: > > > > > >> I have tested with old driver. I don't see any performance issues. > > >> > > >> I don't think it is environment issue, because i tested my route in > > >> different machines with different oracle databases. > > >> > > >> Following is the blueprint and scenario for new jdbc driver: > > >> Remote Oracle Database: 12.2.0.1 > > >> Fuse : 6.3.0.redhat-317 > > >> Blueprint: > > >> <?xml version="1.0" encoding="UTF-8"?> > > >> <blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0"; > > >> xmlns:camel="http://camel.apache.org/schema/blueprint"; > > >> xmlns:cm=" > > http://aries.apache.org/blueprint/xmlns/blueprint-cm/v1.1.0"; > > >> > > >> xmlns:ext=" > > >> http://aries.apache.org/blueprint/xmlns/blueprint-ext/v1.0.0"; > > >> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; > > >> xsi:schemaLocation="http://www.osgi.org/xmlns/blueprint/v1.0.0 > > >> https://www.osgi.org/xmlns/blueprint/v1.0.0/blueprint.xsd > > >> http://camel.apache.org/schema/blueprint > > >> http://camel.apache.org/schema/blueprint/camel-blueprint.xsd";> > > >> > > >> <cm:property-placeholder id="blueprintProp" > > >> persistent-id="camelSql.Oracle" update-strategy="reload"> > > >> <cm:default-properties> > > >> </cm:default-properties> > > >> </cm:property-placeholder> > > >> > > >> <bean id="shutdown" > > >> class="org.apache.camel.impl.DefaultShutdownStrategy"> > > >> <property name="timeout" value="30"></property> > > >> </bean> > > >> > > >> <bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource"> > > >> <property name="URL" value="jdbc:oracle:thin: > > >> @ldap://remothost:port/database,cn=OracleContext,dc=mydb"/> > > >> <property name="user" value="test"/> > > >> <property name="password" value="*******"/> > > >> </bean> > > >> <bean id="sql" > class="org.apache.camel.component.sql.SqlComponent"> > > >> <property name="dataSource" ref="dataSource"/> > > >> </bean> > > >> > > >> <camelContext id="sqlOracleCC" xmlns=" > > >> http://camel.apache.org/schema/blueprint";> > > >> <route id="timerToSqlRoute"> > > >> <from > > >> uri="timer://test?delay=2000&fixedRate=true&period=20000000" > /> > > >> <log loggingLevel="INFO" message="Route Started" /> > > >> <to uri="sql:select * from table" /> > > >> <log loggingLevel="INFO" > > >> message="${in.header.CamelSqlRowCount} > > >> records are retrieved" /> > > >> <!-- Sql Insert has around 90 columns --> > > >> <toD uri="sql:INSERT INTO > > >> table2(column1,column2,column3,column4) VALUES > > >> (:#column1,:#column2,:#column4,:#column4)?batch=true" /> > > >> <log loggingLevel="INFO" message="Successfully inserted" > /> > > >> </route> > > >> </camelContext> > > >> </blueprint> > > >> > > >> On Fri, Apr 12, 2019 at 11:42 AM Saiteja Parna <[hidden email] > > >> <http:///user/SendEmail.jtp?type=node&node=5833064&i=0>> > > >> wrote: > > >> > > >> > Fuse: 6.3.0.redhat-317 R10 > > >> > My Route in blueprint: Timer to SqlSelect to SqlInsert(batch=true) > > >> > Camel-sql retrieves 4000 records and does batch insertion to Oracle > > db. > > >> > Camel-sql is taking 30 mins to insert 4000 records to Oracle > > >> > database(remote) 12.2.0.1. > > >> > Each record has 90 columns with no indexes, constraints... > > >> > > > >> > We have upgraded jdbc driver to 12.2.0.1 and started having slow > > >> > performance. Previously it was 11.2.0.4, Same transaction takes 1 > > >> second to > > >> > process. > > >> > > > >> > Anyone faced the similar issue? Am i missing any property? > > >> > > > >> > Thank you. > > >> > Sai. > > >> > > > >> > > > >> > > > >> > > > >> > > > >> > > > >> > > >> > > >> ------------------------------ > > >> If you reply to this email, your message will be added to the > discussion > > >> below: > > >> > > >> > > > http://camel.465427.n5.nabble.com/Poor-Performance-of-Camel-Sql-Batch-insertions-with-Oracle-DB-tp5833056p5833064.html > > >> To unsubscribe from Poor Performance of Camel-Sql Batch insertions > with > > >> Oracle DB, click here > > >> < > > > http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5833056&code=c2FpdGVqYXAuamF2YUBnbWFpbC5jb218NTgzMzA1NnwtMTk5ODYwNzMyMA== > > > > > >> . > > >> NAML > > >> < > > > http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml > > > > > >> > > > > > >
