Hello All,
1) I have a oracle table that was partitioned
2) I have a simple parallel job with the following stages
a) Oracle connector reading a query which has just one table where it was partitioned. I haven't enabled any partition type in the oracle connector stage.
b) Transformer stage - just only one type conversion
c) DataSet Stage
Problem: It is taking almost 1 hr to read the data from oracle connector stage whereas the same query has finished in 4 mins in Oracle client. In the job log, it says it is running in sequential mode.
Note: I can't use Oralce connector State with partitions - Yes because the rowcount is mismatching with actual source query. As I want the data to be selected from all partitions, I don't want to enable the partitions again on the result set.
Thanks in advance for your valuable thoughts.
Oracle Connector Stage Executing in Sequential Mode
Moderators: chulett, rschirm, roy
-
pradeepphulari
- Participant
- Posts: 2
- Joined: Mon Jun 18, 2007 12:34 am
Oracle Connector Stage Executing in Sequential Mode
Regards
Pradeep Phulari
HP
Pradeep Phulari
HP
-
priyadarshikunal
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Since you mentioned you have a query, a user defined query will execute in sequential mode only. You have to select entire table to make it run in parallel mode.
When you say you get the result in 4 mins from oracle client, did you fetch entire 45 million records there, if not, that 4 mins are irrelevant. Even the oracle client will run in sequential mode.
Yes 12500 records per second may sound slow with very less transformations but cant comment on that as I do not know the query in oracle connector stage, table structure, indexes, server configuration, utilization, network, network utilization etc. For that you have to give us a little extra details like what kind of aggregation, filtering are you doing in your query, how many records in the table, whether indexes are maintained on the keys, filter conditions, was it analyzed recently, do you see high CPU I/O utilization when this job is running etc.
When you say you get the result in 4 mins from oracle client, did you fetch entire 45 million records there, if not, that 4 mins are irrelevant. Even the oracle client will run in sequential mode.
Yes 12500 records per second may sound slow with very less transformations but cant comment on that as I do not know the query in oracle connector stage, table structure, indexes, server configuration, utilization, network, network utilization etc. For that you have to give us a little extra details like what kind of aggregation, filtering are you doing in your query, how many records in the table, whether indexes are maintained on the keys, filter conditions, was it analyzed recently, do you see high CPU I/O utilization when this job is running etc.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
Also do a describe on the table
sometimes you have a no parallel clause in your table's DDL
that will prevent a parallel read from that table.
IHTH,
sometimes you have a no parallel clause in your table's DDL
that will prevent a parallel read from that table.
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org