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 > > > >> > > >
