Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sqlldr locks fet$ and uet$, blocks smon, 100% cpu usage

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

From: Don Seiler <don_at_seiler.us>
Date: Wed, 10 Sep 2003 00:35:28 GMT
Message-ID: <pan.2003.09.10.00.32.51.819498@seiler.us>


All the fields in this table are type varchar2 and number, no long or long raw. Thanks!

Don.

On Wed, 10 Sep 2003 09:27:40 +1000, Howard J. Rogers wrote:

> You need to determine whether it is row chaining or row migration (the
> relevant column in DBA_TABLES is CHAIN_CNT... which covers both
> eventualities, despite its name).
>
> The way to tell the difference is to look at the table definition. If you
> "desc blah', and see:
>
> COL1 CHAR(2)
> COL2 NUMBER(8,2)
> COL3 VARCHAR2(30)
>
> ....then the row-length in this table is about 40 bytes long, give or take,
> and there is no way this can be row chaining (which is, as you say, when the
> row length exceeds the database block size). It must be row migration.
>
> On the other hand, if you saw:
>
> COL1 LONG
> COL2 LONG RAW
>
> etc etc etc, then you are dealing with row lengths potentially in the
> gigabytes, and that's definitely going to be row chaining. About which you
> can do sod-all, short of re-creating your database with a bigger block
> size... but last time I checked, Oracle did not permit block sizes to be in
> the gigabyte range!
>
> If it's migration, then 'alter table blah pctfree X' (where X is bigger than
> it is now) will help prevent future migrations. But it won't fix up anything
> that's already migrated. The only way to do that is to somehow arrange for
> the affected rows to be re-inserted into the table, and the easiest way of
> doing that is 'create table blahcopy as select * from blah', 'truncate table
> blah', insert into blah select * from blahcopy', 'drop table blahcopy'.
>
> Which happens to be one of the same solutions I mentioned in my reply to
> your other post. So if you are fixing up tablespace fragmentation, you can
> fix up row migration at exactly the same time, for free.
>
> Regards
> HJR
Received on Tue Sep 09 2003 - 19:35:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US