Re: sqllder hangs at 99-100% CPU on select of fet$

On Tue, 09 Sep 2003 22:08:48 GMT, Don Seiler <don_at_seiler.us> wrote:

>> SELECT length
>> FROM fet$
>> WHERE file# = :1
>> AND block# = :2
>> AND ts# = :3
>>
>> I'm assuming that sqlldr does that on it's own because my developers
>> have no idea what fet$ is. This process locks fet$ and eventually will
>> block SMON from accessing the table. This whole mess makes the database
>> unusable until I kill the sqlldr's oraclesid process.

This is not a function of sqlldr but a function of Oracle when it needs to extend a table. The used extents are being maintained in uet$, the free extents are maintained in fet$. It means in a few words, you probably have excessive free space fragmentation, and the query needs to run repeatedly to find an appropiate free extent.
In short, what you see now are the dire consequences of years of neglect. (I won't even mention that you wouldn't have had the issue when you would have upgraded to 8i, and would have set up locally managed tablespaces)
You need to reorganize that tablespace, and there is no way you can escape from that. Your problems have NOTHING at all to do with sqlldr, if you would INSERT a few thousand rows, you would probably face the same problem.

You need to investigate the fragmentation of that tablespace, the number of extents of the affected table in relation to it's size. You can't also escape from export/import for that table, as it is the only method in 7.3 to get rid of the chaining issue. You can issue the command
analyze table <table_name> list chained rows into <chain table> provided you run $ORACLE_HOME/rdbms/admin/utlchain.sql to set up a chain table prior to analyzing it.
You can also ANALYZE table <table_name> compute statistics to determine the size of the average record, in comparison to the database block size. These statistics will end up in user_|dba_tables.

Finally you'll need to convince your boss to send you to an administrators course. You seem to be completely clueless, and you are just lucky you have survived so far.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Sep 09 2003 - 17:39:19 CDT