Hi,
We are facing an issue when using sparse loookup. We get thousands of files per day and the name includes date. Now if the file already processed comes again, we should not load it. We store the loaded file names in table FileNameControl.
So when we start file processeding, we extract the date from the files to be processed and then send this date to sparse lookup to extract all files matching to that date. So at max on a single run, there could be 2 rows to the sparse lookup.
After this lookup we do join on fileName from sparse and from input data. If the match found, file is ingored.
Now the problem is: On Dev sparse on the similar data runs much faster. However on production, sparse gets the rows and then goes into the wait mode for 2/3 hours. Due to this issue, for the time being we have converted it to normal lookup. However normal lookup would fetch crores of records as we are going to store months of data froim the files and 50K per day into these many months.
When we checked on Oracle, it shows OSH session and it remained in wait state for a very long time. The message shown is "SQL Net Message From Client".
Oracle Wait on Sparse Lookup
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
Oracle Wait on Sparse Lookup
Regards,
S. Kirtikumar.
S. Kirtikumar.
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
After doing a debugging for almost a day, we have found the issue was not with oracle. In the job we run a for loop to pick the files that match a pattern and External source stage. The cut command is being used and that command gives required fields.
When the folder where we run the for loop has a lot of files, the for loop runs very slowly. During this time, though the sparse was complete Oracle connection was not released.
After that we fixed the issue by replacing the for loop with xargs in the External source stage and now the performance of the job is better.
Now the oracle connection is also released asap. The thing to note here is, though the sparse is complete, the connection is not released till the time Job is not complete.
When the folder where we run the for loop has a lot of files, the for loop runs very slowly. During this time, though the sparse was complete Oracle connection was not released.
After that we fixed the issue by replacing the for loop with xargs in the External source stage and now the performance of the job is better.
Now the oracle connection is also released asap. The thing to note here is, though the sparse is complete, the connection is not released till the time Job is not complete.
Regards,
S. Kirtikumar.
S. Kirtikumar.