I have a bteq job which is running MERGE on fact table.
there is a JI bult on my fatc table.
while executing the MERGE suddenly the query started failing with spool space error, even thogh there is no load on server.
also when i monitored the performance of the query in Viewpoint.. i could see that it was running with 99.6 % CPU skew and 99.6% IO skew..
Could you please advice me how to do investigation on this?
any canary query that can help me , please provide.
It is strange. Could you run Explain merge into and you can investigate.
Also,since you run bteq, maybe you can run an os command with say 'select before' before the merge stmt and after the merge stmt redirecting the output to a file, just to test.
As Dieter already mentioned, more details are needed to pin point the exact problem. specially Spool size, spool allocation, table structure, Merge Statement and Number of AMPs.
At a glance, it looks spool skewness problem on a particular amp.