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

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Thu, 11 Sep 2003 06:43:48 +1000
Message-ID: <3f5f8d7a$0$28119$afc38c87@news.optusnet.com.au>

Don Seiler wrote:

> On Wed, 10 Sep 2003 10:48:19 +1000, Howard J. Rogers wrote:
> 

>> If your table definition read:
>>
>> COL1 varchar2(4000)
>> COL2 varchar2(4000)
>> COL3 varchar2(4000)
>>
>> ....then it is still almost certain to be row chaining, and not
>> migration. It's the row length that's the issue, not just whether there
>> are LONGs and/or LONG RAWs present.

> 
> No this table is mostly 16 or 8 varchar2 or number fields.  I added all of
> them up and it totalled 324.  The pctfree value is currently 10.  How much
> larger should I make this?

Oh well, that's fine. You have a row length of 324 bytes and a block size of 4K, so it's definitely not row chaining, but merely row migration. Fixable, as discussed.

PCTFREE is tricky to set, but it would appear that 10% is definitely not doing the job if your CHAIN_CNT really is as bad as you said it was. 10% of the block yields 409-ish bytes of free space into which the other 11-ish records in the block can expand when updated... which is a row-length-and-a-bit in its own right. Add another 'spare row' (ie, another 350ish bytes) for a total free space of 750 bytes, and that would give you a PCTFREE of about 20%. I'd give that a whirl.

Regards
HJR Received on Wed Sep 10 2003 - 15:43:48 CDT