Oracle Data Integrator performance Issue

User_HGBGR

Hi,

Can someone please explain what below log from ODI operator means

Execution details summary:-

ODI-1026: Execution Summary Environment: Available Processors=8: Available Memory=68719476736:

Threadpool: Current Max Threads per Session=1000: Current Agent Max Threads=-1:

Connection Metrics: Array Fetch Size=100000: Batch Update Size=1000: Target DOP=20 Data Server Configuration.

Source OdiDMSource-335-50-0: Started On 15:12:59.790: Provided 87069739 rows in 13780.337 secs. Wait=0.000 secs.

Target OdiDMTarget-335-50-0: Started On 15:12:59.805: Processed 4370128 rows in 13779.685 secs. Execute=2490.587 secs. Wait=10909.488 secs. 

Target OdiDMTarget-335-50-1: Started On 15:12:59.936: Processed 4380165 rows in 13779.550 secs. Execute=2489.975 secs. Wait=10907.318 secs. 

.....(removed other 18 entries)

Questions:- 

I don't understand what's happening here

1) What is Wait =0.00 secs means on source connection ?

2) What are execute and wait times means on target?

3) Does the JDBC agent's memory full and that's why it took so long 13780.337 secs to process 86 millions records ?Do I need to increase agent memory?

memory assigned currently=64g(MEM_USR_ARGS)

4) Do i need to increase database memory or agent memory(MEM_USR_ARGS) to increase performance?

5) How do we check or know agent's memory is full?

Rodrigo Radtke Souza

Please take a look here:

https://devepm.com/2019/07/05/odi-hidden-gems-degree-of-parallelism-for-target-dop/

The information that you are seeing is related to DOP parameter. You may change those parameters in ODI Topology and get great improvement in some data loads. Give it a try and see what happens.

Thanks!

User_HGBGR

Thanks Rodrigo for quick reply.

I have gone through this document few days before and did some runs giving random values for those ODI parameters .

In all my executions ,I can see waiting times either on source connection or on target connection(c$) as below

case1) Source OdiDMSource-55941-50-0: Started On 08:29:35.716: Provided 88055021 rows in 10571.564 secs. Wait=1987.376 secs.

case2) Target OdiDMTarget-1109-50-0: Started On 15:49:00.187: Processed 8552168 rows in 14316.825 secs. Execute=4539.753 secs. Wait=8828.882 secs.

1) I want difference between both waiting times?

2) How we can execute jobs without waiting times on both connections like increasing memory allocation for ODI MEM_USR_ARGS and any other suggestions?

Rodrigo Radtke Souza

I believe it will be very unlikelly that you will be able to get to a perfect situation where you have 0 wait times in both ends. I say that because there are a lot of factors that are present in this process that is hard to estimate. Imagine the following: you have 10 target workers and your source query starts to run. Lets say it takes 100 secods for it to process in your source DB before it even gets a chance to start the buffering. In this case, the target wait time will start at 100 seconds at least, because you have target workers wainting idle there while your DB is trying to figure out what is the result of your source query. This particular time will only be lower if your DB is powerfull enought to return rows before 100 seconds.

Imagine another scenario: your DB was able to send the data and ODI agent is buffering the rows to send to target workers. If the agent has more data in the the buffers and the target workers already consumed it all, the source will wait until the target workers go throught the internet, write the data in the target DB and come back. In this case you are dependent on the number of target workers, the speed of your wire and maybe how much data each target worker is transfering.

You can check this post below where they do a lot of tests with different fetch sizes. It may give you a better perspective of the entire process.

https://sonra.io/2011/03/22/odi-jdbc-variable-binding-500-performance-gains-array-fetch-size-batch-update-size-row-prefetching-and-the-array-interface-and-an-issue-when-running-the-agent-in-weblogic/

Hope it helps!