Fetch XML CLOB data through Oracle Connector faster

apoorvadwi · Post by **apoorvadwi** » Tue Feb 16, 2016 5:10 am

Hi ,

I am trying to read 4 columns from Oracle database using a SELECT query in Oracle connector. One of the columns has CLOB data type in the Oracle database. The query has a WHERE clause to fetch data for a single day.

If I run the query in SQL Developer it finishes in 90 sec for 70,000 records. While in Connector stage it takes 1 hour to read 70,000 records.

Could someone please tell me how I can fetch this data faster? If I remove the XML column the job finishes in 1 minute. If I add the XML column in the select query then the job runs for 1 hour .

Please someone help me asap. Thanks in advance.

ray.wurlod · Post by **ray.wurlod** » Tue Feb 16, 2016 4:20 pm

You could try:

putting the CLOB column last in the SELECT clause

casting the CLOB column as LONG VARCHAR2

consulting with your Oracle DBA

apoorvadwi · Post by **apoorvadwi** » Wed Feb 17, 2016 12:14 am

Thanks for your reply. But my select query already had this CLOB column in last position. And I have kept the datatype as LongVarChar.

Do you have any other suggestions as to what values to set for array size, record count, buffer size etc?

Or please let me know if I could use some other stage to perform this functionality. Please help me with any suggestions you have.

Thanks a lot.

eostic · Post by **eostic** » Wed Feb 17, 2016 7:02 am

Just curious...but what are you doing to make your comparisons? Are you processing all of the xml clob columns in both tools? What is your Job doing downstream from the Oracle Connector? What is the other tool doing with the XML that it retrieves?

Let's see exactly "where" the performance issue is.

Ernie

ray.wurlod · Post by **ray.wurlod** » Wed Feb 17, 2016 4:24 pm

Array size = 1.
Record count = 1.
Buffer size - which buffers are you asking about, virtual data set buffers or transport buffers or some other? Any buffer needs to be large enough to hold at least one record.

apoorvadwi · Post by **apoorvadwi** » Thu Feb 18, 2016 12:38 am

hi Thanks for the reply . I tried to increase the number of partitions and nodes . I am running now with 3 nodes and 7 partitions and my job is finishing in 5 minutes.

ray.wurlod · Post by **ray.wurlod** » Thu Feb 18, 2016 2:03 am

Can you please explain how you get 7 partitions from three nodes? This may, of course, be confusion of terminology.

apoorvadwi · Post by **apoorvadwi** » Thu Feb 18, 2016 2:56 am

We have a parameter set in that we can pass the values for partition and nodes. The maximum no. of nodes allowed is 3 . So I kept 3 for the noces and in the number of partitions I increased to 7. so the job was running on 21 partitions and hence finished in 5 minutes

chulett · Post by **chulett** » Thu Feb 18, 2016 7:52 am

Can you post the parameter names involved, please, the ones controlling your two options? Just so we make sure we're all on the same page, terminology-wise.

kottinaresh · Post by **kottinaresh** » Thu Mar 17, 2016 2:00 am

apoorvadwi: Confused

with "3 nodes & 7 partitions =21" can you please brief on your settings please..