How to do parallel loading in Qlik replicate

[Antony_05]

Hi,

One of the table in my replication is not getting loaded since its a big table, so please help me with the procedure or idea for parallel loading of a table where the source and the target are Oracle.

[Antony_05]

Hi Team,

I just tried loading the big table in parallel load using segment but I'm getting the below mentioned error.

"ORA-00604: error occurred at recursive SQL level 1 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired "

Can anyone please help on this error.?

[IanM]

Hi Antony_05,

This is how I do it. I'm no expert but it is working well for me.

My source isn't Partitioned so I use the data ranges option.

First select the number of processes in Task Settings > Full Load > Full Load Tuning > Maximum number of tables. I run 8, but speak to your DBA. - You don't want to bring down his/her database.

Setting up each table: for each table you want to run in parallel use table settings > Parallel Load, select 'Use Data Ranges'.

'Select Segment Columns' to choose a field to split your data.

It will present you with the indexed fields. I ignore those and select my own.

I run 8 processes in parallel, so I select 7 segment boundaries (Replicate works out from the last boundary upwards), but I guess you can choose as many boundaries as you want.

Initially I tried to use a single field high up in one of the source table indexes and picked values covering roughly 1/8 of the records in the table. Seemed to run well.

My DBA disagreed with my method and said I should choose a field that is NOT indexed and forces a full table scan. Oracle will then cache the whole table when the first process runs, and provide the data to the other processes from the cache (less IO and CPU he says).

He also says I should not choose a field in the table that is incremental (such as dates, or incremental IDs) as the data will not be spread evenly across the table, so the first process will pick up the start of the table. The second processes will have to scan across all of those records before it starts outputting its data. the third has to scan across the previous 2, etc. etc. Using something like a timestamp field gives a more even spread where processes will all output evenly. As I said, you will need to discuss it with your DBA and try some combinations.

Parallel only works on the full load, not CDC.

On the monitor screen [v7.0], the 'current throughput' shows as zero (I guess it can't do the maths ), but the transferred count does show. 

Hope this helps.

Ian

[Antony_05]

Hi,

Thanks for your solution, Let me try this 🙂

[john_wang · Accepted solution]

>>  "ORA-00604: error occurred at recursive SQL level 1 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired "

>> Can anyone please help on this error.?

Well, this error means a LOCK occurs at the target table. 

For Oracle target endpoint, Replicate utilizes the LOAD method to gain the best performance. By default, Replicate uses "direct path full load" (see picture below). However,  in "direct path full load" there is a chance of table lock which caused the above errors. You may turn off the "direct path full load" option, then Replicate will use conventional load method:

Certainly, the Direct Path Loads has better performance than Conventional Loads, if the Conventional Loads cannot meet your performance, you can adjust the number of segments that can be loaded in parallel .

Hope this helps.

Regards,

John.

[john_wang]

Hello @Antony_05 ,

It has been several days since we heard from you. We would like to ensure that we move this case forward to help you with your issue. Would you please let us know your current status with this issue?

Thank you,

John.

[Antony_05]

Hi @john_wang ,

As you have mentioned I changed it to conventional loading and the parallel loading was successful.

Regards,

Antony S

[john_wang]

Hello @Antony_05 ,

Thanks for your update. if the problem was solved ,please press "Accept as Solution", it's helpful for others as well.

thank you,

John.