Need guidance

Database
Enthusiast

Need guidance

Hi,

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.

4 REPLIES
Enthusiast

Re: Need guidance

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.

Cheers,

Raja

Senior Apprentice

Re: Need guidance

Can you share the definition of both fact tabe and JI, the MERGE and explain?

What's the spool size assigned to your load user?

Enthusiast

Re: Need guidance

You can log the merge errors into an error table. But I dont know from which version, this is available. But 14 I see this option.

Cheers,

Raja

Enthusiast

Re: Need guidance

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.