Fact table with over 250M rows.

Data in question is around 100M. I am running a quarterly aggregate to calculate simple weighted averages at different grains.

Query I am using is pretty simple and straightforward.
However, when executed against the entire data set, I get the below error:


ORA-00039: error during periodic action
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
00039. 00000 - "error during periodic action"
*Cause: An unexpected error occurred while executing a periodically invoked
action.
*Action: Check the error stack for detailed error information.

In the actual environment, the datasource is a view but no parallelism defined.

Question: Can you please explain in what conditions this error would come about?

For running aggregates against 100M rows, is this the right approach? (Using Union?)
Is there a better way to do this kind of aggregation?

Please see the query below and the Live SQL Link attached:

select 'All' as Grain,'ALL' as Location,'ALL' as Department
,sum(salary*weight)/sum(weight) as Salary_Avg
,sum(bonus*weight)/sum(weight) as Bonus_Avg
,sum(commission*weight)/sum(weight) as Commission_Avg
,sum(term*weight)/sum(weight) as Term_Avg
from employee
union
select 'Location' as Grain,location,'ALL' as Department
,sum(salary*weight)/sum(weight) as Salary_Avg
,sum(bonus*weight)/sum(weight) as Bonus_Avg
,sum(commission*weight)/sum(weight) as Commission_Avg
,sum(term*weight)/sum(weight) as Term_Avg
from employee
group by location
union
select 'Department' as Grain,'ALL' as Location,department
,sum(salary*weight)/sum(weight) as Salary_Avg
,sum(bonus*weight)/sum(weight) as Bonus_Avg
,sum(commission*weight)/sum(weight) as Commission_Avg
,sum(term*weight)/sum(weight) as Term_Avg
from employee
group by department