Oracle Wait on Sparse Lookup

Kirtikumar · Post by **Kirtikumar** » Wed Jul 27, 2011 11:19 pm

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".

Kirtikumar · Post by **Kirtikumar** » Wed Jul 27, 2011 11:26 pm

Forgot to add one more thing - it runs fine on Dev with same data. On prod though it is having these kind of issues. The Session monitor shows the wait is in "SQL*Net message from client". And the sparse lookup returns rows and still stays in wait.

chulett · Post by **chulett** » Thu Jul 28, 2011 6:51 am

Have your DBA check the plan for the query, sounds like there's an index being used in Dev with Prod doing a full table scan.

Kirtikumar · Post by **Kirtikumar** » Wed Aug 03, 2011 8:08 am

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.