In the scenario of NO MORE SPOOL SPACE, can this be attributed to any one of the AMPs (similar to the permanent space, on which one of the amp has high peak perm space). I guess the blank cylinders are used for the spool space (also does reserve database for spool may come in handy on this situations).
Is the spool space utilized of equal size over all amps or one amp can use relatively more space than the other one (i.e, spool has any relationship directly to the AMPS)
Can any resource intensive operations like substring(), other functions may lead to spool space issued if used. Same SQL with out functions if used, can it run to success.
Also, can a bad join on column affect the spool space (redistribution or duplication happening on this one)
I think your main question is can a skewed query run out of spool space on one amp.
Yes it can. Spool limit is specified for a user when created, usually via a profile.
If your spool limit is 100 G and your system has 100 Amps, you will have 1 GB/ Amp to run queries, If any amp exceeds 1 G spool your query will fail with NO MORE SPOOL SPACE error. This is usually a result of SKEW or your spool limit is set to low.