Please let me tell you about my database config. Total space(Perm) is 50 TB. Current perm is 32 TB. There are around 350 amps in the system. Daily load is around 80-100 GB. Daily users are around 350-400.
The problem is users often go out of spool space, it also effect the loading jobs.
Can you please advise how I can overcome this problem.
What should be the ratio of occupied and free space in the system. I checked the skewness, data is evenly distributed among all amps.
what parameter i need to check to find the highly over loaded user i.e. the user taking the maximum resources. I can check few things through PMON like spool,i/o etc.
Each user has a spool space limit, stated as a system-wide limit but enforced as a proportional limit on a per-AMP basis. "No more spool space" does not mean that there is no free space left on the system, it means that username has exceeded its limit on at least one AMP. Spool space in use by all sessions with the same username is charged against that user's limit.
Perhaps your limit is just too small. That would be a simple fix. Or perhaps data distribution is skewed during execution, which could be improved by collecting statistics (so the optimizer makes better choices) or by changing primary indexes on some tables to reduce the need to redistribute before joining, or doing some query tuning. There is no "one size fits all" answer.
Analyzing PeakSpool (see ClearPeakDisk macro in documentation) may help. Or depending on your DBMS release, DBQL logging may be useful to pinpoint the problem.