Oracle OCI problem

[vmcburney]

I have a problem where an Oracle OCI select statement is taking far too long when executed from a DataStage job.

We have a job that performs a simple select from a table based on a date:
SELECT {list of fields} FROM wp_caption
WHERE last_update_date = TO_DATE('13/02/2002', 'DD/MM/YYYY')

The problem is that when this job starts it takes between 21 to 28 minutes to run the SQL statement. When the same SQL statement is run in a query window it returns data instantly, when I do a "View Data" in the DataStage Designer it also returns data instantly. I can scroll through the full set of results in under a minute.

The table has over 3 million rows, the SQL usually returns about 20,000 rows. I am using DS 5.1. Array size is set to 1000. There is very little load on the machine or database. Similar DS jobs using the same SQL on other tables of the same size or larger are running without the delay.

Does anyone know why this SQL would take 21 minutes in a running DataStage job when it should be returning the full recordset in a matter of seconds?

[datastage]

I'm wondering if maybe it's just an illusion that the query is taking that long but instead the job is performing another task before starting the query. One thing to check is if the job has another database stage that it's writing to and the update action is doing a clear of the table first. DataStage will then clear this target table before beginning the input stage's SELECT. And of course if it is doing a delete and writing log entries as opposed the much faster truncate then you may have a 20 minute wait to clear the table. On the job monitor, if a table is being cleared at the beginning, the status of the first stage will be shown as 'starting' and not change to running until this initial task is complete.

[vmcburney]

Thanks for the reply "datastage", I've tried to find something other than the SQL but there are no before job routines, there are no delete or truncate commands in the job and there are no other jobs running.

The job is simple with just three stages. An OracleOCI select, a transform, and an OracleOCI insert/update.

[hkotze]

One thing you can try is to insert a sequential stage after the Oracle OCI stage to dump the data in their and read of the flat file ( if you have enough space on the machine), we had the same problem in an earlier version and this help as the dataset was returned the moment oracle was finished preparing it.

I have a problem where an Oracle OCI select statement is taking far too long when executed from a DataStage job.

We have a job that performs a simple select from a table based on a date:
SELECT {list of fields} FROM wp_caption
WHERE last_update_date = TO_DATE('13/02/2002', 'DD/MM/YYYY')

The problem is that when this job starts it takes between 21 to 28 minutes to run the SQL statement. When the same SQL statement is run in a query window it returns data instantly, when I do a "View Data" in the DataStage Designer it also returns data instantly. I can scroll through the full set of results in under a minute.

The table has over 3 million rows, the SQL usually returns about 20,000 rows. I am using DS 5.1. Array size is set to 1000. There is very little load on the machine or database. Similar DS jobs using the same SQL on other tables of the same size or larger are running without the delay.

Does anyone know why this SQL would take 21 minutes in a running DataStage job when it should be returning the full recordset in a matter of seconds?