sqlldr locks fet$ and uet$, blocks smon, 100% cpu usage

Using Oracle 7.3.4.5 on HP-UX.

The subject pretty much says it all. We have an application that uses sqlldr to post back a lot of rows to a table. This table holds ~ 65 million rows. This application has worked fine until this week. Now it will hang, and there will be a process on the server (HP-UX) that uses 100% of a CPU. This pretty much makes the rest of the database unusable. I can't even view the sessions to see who owns that process.   I think it is spawned off by the sqlldr, but the parent pid when I do ps -aef says 1, which usually means it's a remote process but the application in question is run locally. However the time run by the process has matched pretty much exactly the time that the sqlldr application was run.

Anyway what we can see is that there will be an extraordinary amount of locks on the system fet$ and uet$ tables by the db user that the sqlldr app runs as. These locks are blocking the SMON process.

My developers tell me this happens after 90,000 rows or so have gone in, then they can't do any more with sqlldr. They can still insert data with sql*plus, but that isn't really an option.

I can't say if it's related, but it shocked me and will probably shock you that this table has over 10 million chained rows. I've read that the two main causes are that the db_block size is too small or the pctfree is wrong. What would be the best way to diagnose the problem and implement the solution. We are planning an upgrade to Oracle 9i (9.2.0.x) this winter so I can make adjustments then I assume. But the locking problem above is of more urgent need. Received on Tue Sep 09 2003 - 14:41:25 CDT