DSXchange

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.

You could try:

• putting the CLOB column last in the SELECT clause
  
  casting the CLOB column as LONG VARCHAR2
  
  consulting with your Oracle DBA

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.

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

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.

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.

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

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

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.

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