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

[khchoy]

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

[Steve_Nguyen]

@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

[khchoy]

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 · Accepted solution]

@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

[khchoy]

Thanks a lot. 

I will change Stream buffer and again test.

Regards

Kwang ho

[khchoy]

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]

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.

[khchoy]

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]

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