Error

[satheesh_color]

Hai,

I got the following error.Kindly give me your valuable suggestions.

LoadStgSaleForecastSGPzWeek..SrcStgSaleForecastNigWeekOra: ORA-12801: error signaled in parallel query server P007
ORA-01652: unable to extend temp segment by 1024 in tablespace TEMP
Attempting to Cleanup after ABORT raised in stage LoadStgSaleForecastSGPzWeek



Regards,
Satheesh.Rajan

[sb_akarmarkar]

Error is related to oracle database please check with your DBA.


Thanks,
Anupam

[loveojha2]

What is your commit frequency? Are you using zero as the commit frequency?
Try with a small number say 1. and let us know the results.

[chulett]

To me, the error looks more like it's a parallel query coming from the source rather than the target side. In any case, talk to your DBA. They will berate you for whatever you are doing - something that needs to do alot of sorting, possibly - but then may increase the TEMP tablespace for you.

Either that or rethink your query so it doesn't need that much in the way of disk resources.

[satheesh_color]

Hai Guys,

The job is completed while reducing "The parallel degree for the sql statement from 8 to 1".

why parallel degree 8 works sometimes and not others?






Regards,
Satheesh.R

[DSguru2B]

Whenever such database errors pop up, always pay close attention to their codes.
ORA-01652 usually happens when a sort operation cannot be finished in memory alone. Thats when the disk space comes into play but even that is falling short for your case. This error is begging you to get more room in the table space.
So you have two options now, either increase your memory size , something like

Code: Select all

ALTER SESSION SET sort_area_size=1000000

to get a million bytes for the sort area or you can request your dba to increase the table space.

The first error maybe due to a parallel query. For that you definately need your dba's assistance.

[chulett]

The job is completed while reducing "The parallel degree for the sql statement from 8 to 1". Why parallel degree 8 works sometimes and not others?

That would depend on exactly what else is going on at the same time.

And I doubt you needed to drop it to 1... why not try a smaller number, something that will play nicer with others? If everyone always requested maximum parallelism...

[satheesh_color]

Hai Guy's,

Here is the Query:

select /*+ full(s) parallel(s,#PAR_DEG#) full(n) parallel(n,#PAR_DEG#) full(l) parallel(l,#PAR_DEG#) ordered */
s.PERIOD_ID,
s.DIVISION_CODE,
nvl(n.ITEM_GROUP_CODE,0),
s.NTNL_ITEM_GROUP_CODE,
nvl(n.PRICE_ZONE_CODE,0),
n.PRICE_ZONE_TYPE_CODE,
round(nvl(AVG(s.AVERAGE_FORECAST_PRICE),0.0),5),
round(SUM(decode(nvl(s.store_count_price_zone,0),0,0,
s.AVERAGE_EXTENDED_COST*SUM_FORECAST_UNIT/s.store_count_price_zone)),5),
nvl(count(distinct n.LOCATION_ID),0),
round(nvl(SUM(decode(nvl(s.store_count_price_zone,0),0,0,s.SUM_FORECAST_UNIT/s.store_count_price_zone)),0.0),5) ,
round(nvl(SUM(decode(nvl(s.store_count_price_zone,0),0,0,s.SUM_FORECAST_SALE_AMOUNT/s.store_count_price_zone)),0.0),5)
from stg_sale_forecast_nig_week s,
PDM_LOCATION_PRICE_ZONE_WEEK l,
stg_sale_forecast_nig_store n




#PAR_DEG# is the parameter we are passing from job level. As previously stated if we pass PAR_DEG=8 in the sense the job get's aborted else if we set PAR_DEG=1 in the sense the job get's completed.I just want to know the solution to resolve this issue.Kindly give me your valuable suggestions.


Regards,
Satheesh.R

[chulett]

You've already gotten them. Drop the degree of parallelism down or be prepared for it to occasionally crater if too many other parallel queries have been requested by other folks/jobs at the same time.

Discuss that with your DBA. It's not a DataStage issue.