Oracle Enterprise Execution mode

[reachmexyz]

hello all

I have a job like
OCE ---- > Transformer ---- > dataset.
I wish to execute the OCE stage with the "Parallel" execution mode. But when i tried to check the execution mode it is "default(Sequenctial)" and there is no drop down box to alter the exection mode. Do i need to set anything to get the drop down box highlighted and select parallel execution. Also in my parallel query i am using the hint /*+ Parallel*/.
Is this enough to execute the OCE in parallel or will i get additional benefits if i execute the OCE stage in parallel mode.
Please reply

[chulett]

Your table must be partitioned to get it to run in something other than Sequential mode.

[reachmexyz]

Your table must be partitioned to get it to run in something other than Sequential mode. ...

Thanks for the quick response.
Yes table is partitioned. Using the query i am accessing only one partition and also accessing that partition parallely using Parallel hint. But if i execute the OCE stage in Parallel mode, will i get any additional benefits when compared to executing the OCE stage in "Default(Sequential mode)". Does the "Default(Sequential)" overrides the hints specified in the query. Also i am trying to test 2 parallel jobs one with execution mode sequential and another parallel execution mode. This is the reason why i am willing the execute the stage in Parallel mode. Please reply

[chulett]

Didn't we already have this conversation?

No, the stage cannot 'override hints' so if you are specifying a PARALLEL hint (or it is a table property) then it will run in parallel in the database but that has no effect on what DataStage does with the output.

My undestanding is the only way you'll get the "additional benefits" you're thinking of are if your table is partitioned, you tell it so in the stage and you allow it to select from multiple partitions.

[reachmexyz]

Didn't we already have this conversation?

No, the stage cannot 'override hints' so if you are specifying a PARALLEL hint (or it is a table property) then it will run in parallel in the database but that has no effect on what DataStage does with the output.

My undestanding is the only way you'll get the "additional benefits" you're thinking of are if your table is partitioned, you tell it so in the stage and you allow it to select from multiple partitions.

My Problem is
I have a server job like Oracle---->Sequenctial file and a paralle job like
OCE ---> sequenctial file. Both jobs are completely alike. No differences between two jobs. But the time taken by server job is 3/4 when compared to parallel job. Even after taking all the parallel setup before execution of the job,it looks time consumed is more by parallel job. So i thought there may be any problem with the OCE stage (like any missing settings in Administrator). Have no idea why the query in parallel job is taking more time than the query in server job.

[chulett]

I assume by 'parallel setup' you actually mean 'parallel startup' and have removed that factor from the comparison. After that, you would need to verify that both jobs do in fact run the exact same query by checking both explain plans while they run. You would also need to run the PX job on a single node to get a more 'apples to apples' comparison.

[chulett]

Also, please let us know what kind of volume you're talking about here and how long the actual run times are for each. If we're talking 3 minutes versus 4 minutes, that's not much of a conversation but 3 hours versus 4 hours might be worth digging deeper into.

[John Smith]

Don't think the two jobs are the same. At least internally the parallel job would or may be rehashing the data while the server job wouldn't. If the volume is small then the server job having less repartitioning/rehashing of data will finish quicker.
In order to answer this type of question as chulett said you need to provide more information.