Oracle-L: PCTFREE and No Migration!

From: Bill Buchan <wbuchan_at_uk.intasys.com>
Date: Wed, 17 Apr 2002 02:48:23 -0800
Message-ID: <F001.0044638A.20020417024823@fatcity.com>

Hi All,

I have a large (tens of millions of rows) table into which rows are periodically loaded during the day. Nightly a batch process runs updating the newly loaded rows. I have allowed PCTFREE = 20 for the updates.

Following some development changes, I wanted to check if this value was still accurate.
I ran the following query:

SELECT STATUS,COUNT(*),AVG(rowsize)
FROM (
SELECT STATUS,NVL(VSIZE(column1),0)+NVL(VSIZE(column2),0)+.... rowsize FROM TABLE SAMPLE(x)
)
GROUP BY STATUS For a decent sample size, those whose status was unprocessed had an average size of 68.78. Those whose status of processed had an average size of 91.82. This would suggest to me a growth of 25%, yet with PCTFREE of only 20 no migrated rows are ever detected during an analyze. The system has been running for weeks now.

I'm sure I'm missing something obvious but I can't understand why my 20% PCTFREE is accommodating 25% increases in row sizes without any migration.

Anyone care to help me out?

Many thanks for any info
- Bill.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Bill Buchan
INET: wbuchan_at_uk.intasys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Apr 17 2002 - 05:48:23 CDT