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
strange Oracle OCI performance problem
Moderators: chulett, rschirm, roy
-
chanthou_2000
- Participant
- Posts: 11
- Joined: Mon Jun 16, 2003 2:21 am
Re: strange Oracle OCI performance problem
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.
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.
Bibhu
-
chanthou_2000
- Participant
- Posts: 11
- Joined: Mon Jun 16, 2003 2:21 am
Re: strange Oracle OCI performance problem
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
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
[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.
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.
Bibhu