Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
khchoy
Partner - Creator
Partner - Creator

Is there any way to improve the loading speed of CLOB Table?

Hi All ,

When I try to load large CLOB table, loading speed is around 300 rec/sec . (direct path load)

I checked log, only 2 or 3  rows sent  at one time.

Currently I met a lot of ora-64202 error without direct path load.

Does any  idea for resolving this problem ?

Regards,

KwangHo

Labels (1)
1 Solution

Accepted Solutions
Steve_Nguyen
Support
Support

@khchoy

 

1. on task setting , try to use limited lobs , bc setting unlimited lobs will show down everything, so try to set your lobs limit to 32MB, which is very large.

 

2. try to increase the stream buffer :

https://community.qlik.com/t5/Knowledge/Outgoing-stream-is-full-Forwarding-events-to-target-is-postp...

 

3. have you test a null target to see if this is a source or target slow ?

https://community.qlik.com/t5/Knowledge/Null-File-Target/ta-p/1737871

 

 

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

8 Replies
Steve_Nguyen
Support
Support

@khchoy,

 

1. what is your source and target

2. how large is CLOB ?

3. is this on full load or CDC that you are seeing the speed?

4. You can try running this query to identify the largest lob size:
select max(datalength(columnname)) from table

5. if SQL source, try internal parameter and set forceLobLookup to true

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!
khchoy
Partner - Creator
Partner - Creator
Author

1. oracle us7ascii to oracle al32utf8

2. several hundreds of millions records. almost lob size is around 4k

3.only full load . clob table cannot use cdc mode.

4. around 31mb.

5. does any other internal parameters for memory buffer ?

regards,

kwangho

Steve_Nguyen
Support
Support

@khchoy

 

1. on task setting , try to use limited lobs , bc setting unlimited lobs will show down everything, so try to set your lobs limit to 32MB, which is very large.

 

2. try to increase the stream buffer :

https://community.qlik.com/t5/Knowledge/Outgoing-stream-is-full-Forwarding-events-to-target-is-postp...

 

3. have you test a null target to see if this is a source or target slow ?

https://community.qlik.com/t5/Knowledge/Null-File-Target/ta-p/1737871

 

 

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!
khchoy
Partner - Creator
Partner - Creator
Author

Thanks a lot. 

I will change Stream buffer and again test.

Regards

Kwang ho

khchoy
Partner - Creator
Partner - Creator
Author

Hi ,

I tested with new stream buffer size. I changed to  

 

"stream_buffers_number" : 20,
"stream_buffer_size" : 100,​

 

and  limit lob size 24400

CLOB Table  Row : 1,419,142  row, MAX  CLOB length  24,303 KB     AVG   CLOB length  2305 byte

But loading speed is  around 560~ 580 row/ sec  , it means load duration will be around 2500 sec .

Currently customer have a table it has more than 450 million records and max lob size is  30MB.

Do you have any information about below internal parameters ?

default value
directPathColumnArrayRows:5000,
directPathStreamSize:256000,
directPathParallelLoad:false,
directPathNoLog:false,

This is related direct path loading. 

Regards,

KwangHo

Steve_Nguyen
Support
Support

so if i read this correctly,

before : around 300 rec/sec . (direct path load)

now : 560~ 580 row/ sec  , 

that is almost double the speed .

 

===

did you try to load to all null target to verify is a source issue ?

 

does the table have PK ? if have PK have you try to use parallels load ?

 

===

Oracle target performance enhancement:

There are two new Oracle target advanced properties, similar to the options exposed by the SQL Loader:

  • directPathColumnArrayRows – This is the max number of rows loaded in batch. Default 5000 rows.
  • directPathStreamSize – The is the direct path stream buffer size. Default 256000 bytes.

Increasing those parameters should give better performance.

 

Newer performance params 

 Oracle target Parameters:

bulkUseParallel  - default true – Use Parallel hint for bulk DML statements

bulkArraySize – for direct insert

===

for the directPathParallelLoad:false ,,, this is for more of troubleshooting direct path.

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!
khchoy
Partner - Creator
Partner - Creator
Author

Hi,

Thanks a lot your help.

Currently , General tables  which are does not have  CLOB column , Loading speed is good ( 10~ 30 million records per min) network is 10GB and CDC Server spec. is 40 Physical core (80 logical core) , Zeon gold 6230 2.1GHz  , 256 GB memeory ,  SSD Storage.
When I clob limit change to below 8K , it is not bed  more than 50 thousand . 
Also I use  parallel loading option  in table setting.  (780 segments,  20 maxmum number of tables  in task setting.

I cannot change parameters (directPathColumnArrayRows  and directPathStreamSize ) ,when I change those, I met error. 

Yesterday ,  I tested  a table , max clob size is 30MB and  lob column size is bigger than 8K, loding result  is 42,344,550 records  743,535 MB 12:46:58  duration.  it is 10 % of full table size.  Fortunately ,  snap shot too old error is not occurred. 

Do you have any more tuning points  ?

Regards,

KwangHo

 

Steve_Nguyen
Support
Support

@khchoy,, most tuning are done by our Professional service team, best to open a case and ask for Professional service. 

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!