Page 1 of 2
Oracle parallel job stuck
Posted: Mon Aug 22, 2005 5:01 am
by juliyas
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
Posted: Mon Aug 22, 2005 5:28 am
by ArndW
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?
Posted: Mon Aug 22, 2005 5:49 am
by juliyas
I removed the where clause , but view data doesn't work.
The parameters ok.
but i will try to check DBA trace.
thanks
Posted: Mon Aug 22, 2005 8:01 am
by ArndW
I you remove the WHERE clause then the VIEW should work; what is happening in your case?
Posted: Mon Aug 22, 2005 4:27 pm
by ray.wurlod
It is a partitioned table? If so, have you specified the Partition Table property in the Oracle Enterprise stage?
Posted: Tue Aug 23, 2005 1:02 am
by Amos.Rosmarin
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 ?
Posted: Tue Aug 23, 2005 4:51 am
by juliyas
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?
Posted: Tue Aug 23, 2005 9:58 am
by Jay
Need more info. Can you please copy paste the error message when you try to view data?
Thanks
Jay
Posted: Thu Aug 25, 2005 1:16 am
by juliyas
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.
Posted: Fri Aug 26, 2005 2:55 pm
by Jay
Try to view 10 rows or 1 row at least...
Posted: Sun Aug 28, 2005 12:52 am
by juliyas
Jay wrote:Try to view 10 rows or 1 row at least...
View of 10 rows successfully opened
Posted: Sun Sep 25, 2005 8:01 am
by roy
Hi,
Do you have the $DS_ENABLE_RESERVED_CHAR_CONVERT set to true in your job properties?
Posted: Sun Sep 25, 2005 2:28 pm
by ray.wurlod
Are there indexes on ROLE and CYCLE_MONTH?
Posted: Mon Sep 26, 2005 12:55 am
by juliyas
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?
Posted: Mon Sep 26, 2005 12:56 am
by juliyas
ray.wurlod wrote:Are there indexes on ROLE and CYCLE_MONTH?
I have index on ROLE and oracle partition on CYCLE_MONTH.