Page 1 of 1
strange Oracle OCI performance problem
Posted: Tue Jul 19, 2005 10:42 am
by chanthou_2000
Hello everyone !!
I have a very weird problem with an Oracle OCI SQL Query.
I have compared the performance of 2 queries (which do the same thing) into an Oracle OCI
the first seems to be very slow (cos i executed an explain plan on it, an it returned me very bad results)
the second is normaly very faster than the first.
The problem is : when i execute the first job, it's quiet fast (20 000 row/s)
but when i execute the second one, the performance is ... 1500 rows/s !!
It is very strange !!!
Can anyone tell me what's going on ?
Thank you very much for your anwer
Chanthou
Re: strange Oracle OCI performance problem
Posted: Tue Jul 19, 2005 10:56 am
by bibhudc
Chanthou,
After the OCI stage, are both jobs doing a similar kind of processing ? i.e. is one writing to a text file while the other is going through transformations ?
One way to find out which query is really faster (outside of datastage) is to do a "create table select * from"... or do a
"select count(*) from (select * from ...."
for both queries.
Re: strange Oracle OCI performance problem
Posted: Tue Jul 19, 2005 12:50 pm
by chanthou_2000
the processing after the OCI is exactly the same.
as i mentionned, i execute an explain plan and i tested the 2 queries oustide of Datastage, and i have the same conclusion.
I think, may be, that PX execute the SQL queries with parallel oracle option and it may create some weird results. What do you think about it ?
i wonder myself if the array size parameter had some influence on the performance in that case. May be the fastest query need more array size thant the slowest ? i don't know ....
Thanks
bibhudc wrote:Chanthou,
After the OCI stage, are both jobs doing a similar kind of processing ? i.e. is one writing to a text file while the other is going through transformations ?
One way to find out which query is really faster (outside of datastage) is to do a "create table select * from"... or do a
"select count(*) from (select * from ...."
for both queries.
Re: strange Oracle OCI performance problem
Posted: Tue Jul 19, 2005 1:49 pm
by bibhudc
[quote="chanthou_2000"]the processing after the OCI is exactly the same.
as i mentionned, i execute an explain plan and i tested the 2 queries oustide of Datastage, and i have the same conclusion.
If you tested both queries outside of datastage -- and if you returned ALL (not just the FIRST few) records from both queries and the 1st sql was slower than the 2nd, then yes, I would be surprised to see that the behaviour changed in Datastage.