Oracle parallel job stuck

juliyas · Post by **juliyas** » Mon Aug 22, 2005 5:01 am

Hi all,

I try to query a very big oracle table (~50M records),
When I use sequential access the job works fine , but very slow.
When I try to use parallel access the job just hangs with no explenation.

I tried to add some APT env variables for debug but still I get ho clue for the reason the job does not progress.
The table is indexed and partitioned so the same sql works ok in sqlplus
My SQL is:

Code: Select all

SELECT CUSTOMER_ID, AGREEMENT_ID,  CYCLE_MONTH,
to_char(LAST_EVENT_DATE,'YYYYMMDDHH24MISS') LAST_EVENT_DATE,
(ORIG_UPLINK_VOLUME+ORIG_DOWNLINK_VOLUME) VOLUME
FROM  #P_OU_OWNER#.OU_PERFORMANCE  
WHERE ROLE='RT' AND CYCLE_MONTH in (#P_CYCLE_MONTH#);

Any idea why it does not work or how can I check it ?

Juliya

ArndW · Post by **ArndW** » Mon Aug 22, 2005 5:28 am

Just for reference, how long does this SELECT take when done from SQLPlus? Also, can you not get your DBA to trace this select when issued from DataStage to see what is happening? Also, if you do a "view data" in the designer, does it correctly resolve all your parameters? Does the "view data" work if you remove the WHERE clause?

juliyas · Post by **juliyas** » Mon Aug 22, 2005 5:49 am

I removed the where clause , but view data doesn't work.
The parameters ok.
but i will try to check DBA trace.

thanks

ArndW · Post by **ArndW** » Mon Aug 22, 2005 8:01 am

I you remove the WHERE clause then the VIEW should work; what is happening in your case?

ray.wurlod · Post by **ray.wurlod** » Mon Aug 22, 2005 4:27 pm

It is a partitioned table? If so, have you specified the Partition Table property in the Oracle Enterprise stage?

Amos.Rosmarin · Post by **Amos.Rosmarin** » Tue Aug 23, 2005 1:02 am

Hi,

Just a question on 'partition table'

From things I read in this forum the 'partition table' property is not related to the actual partitioning of the table, it is an internal DS property.
In this case the pertition table property should be equal to the owner.table name.

Am I right ?

juliyas · Post by **juliyas** » Tue Aug 23, 2005 4:51 am

ray.wurlod wrote:It is a partitioned table? If so, have you specified the Partition Table property in the Oracle Enterprise stage?

Yes, it is a partition table.
I didn't understand if it is a partitioned table i can't define partion in the oracle stage?

Jay · Post by **Jay** » Tue Aug 23, 2005 9:58 am

Need more info. Can you please copy paste the error message when you try to view data?

Thanks
Jay

juliyas · Post by **juliyas** » Thu Aug 25, 2005 1:16 am

Jay wrote:Need more info. Can you please copy paste the error message when you try to view data?

Thanks
Jay

It is very big table, i think this a problem.
When i try to open it it is stuck a long time.

Jay · Post by **Jay** » Fri Aug 26, 2005 2:55 pm

Try to view 10 rows or 1 row at least...

juliyas · Post by **juliyas** » Sun Aug 28, 2005 12:52 am

Jay wrote:Try to view 10 rows or 1 row at least...

View of 10 rows successfully opened

roy · Post by **roy** » Sun Sep 25, 2005 8:01 am

Hi,
Do you have the $DS_ENABLE_RESERVED_CHAR_CONVERT set to true in your job properties?

ray.wurlod · Post by **ray.wurlod** » Sun Sep 25, 2005 2:28 pm

Are there indexes on ROLE and CYCLE_MONTH?

juliyas · Post by **juliyas** » Mon Sep 26, 2005 12:55 am

roy wrote:Hi,
Do you have the $DS_ENABLE_RESERVED_CHAR_CONVERT set to true in your job properties?

Where/How i can found this flag?

juliyas · Post by **juliyas** » Mon Sep 26, 2005 12:56 am

ray.wurlod wrote:Are there indexes on ROLE and CYCLE_MONTH?

I have index on ROLE and oracle partition on CYCLE_MONTH.