Hope you have the partition on your huge fact table and the partition condition exist in your query.
Ensure the stats exist not only on primary but on the aggregated columns, and other important where condtioned columns
In the explain steps find the exact step where it is giving sudden spike of counts, spool or time and concentrate on the columns used in it. If possible see this in viewpoint steps.
Are you using left outer joins anywhere? You could be running out of spool when joining spool files as a result of outer joins. Could also be natural data skew (i.e. sales in NYC vs Fargo N.D). Make sure you don't have any aliases that you are not referencing.
In order to help you, i would like to see the SQL statement.
- How many rows are in each of the tables being joined?
- As you mentioned skew: you should check if the join columns have skewed values
- Do you have statistics on the join columns?
- What kind of join type is used by the Optimizer.
Please show us the EXPLAIN output, otherwise it's quite difficult to help.