Oracle OCI problem
Posted: Mon Feb 18, 2002 4:58 pm
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?
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?