Someone has already mentioned this, and if you look at the rowlength of 4958 bytes compared to blocksize, you can only fit in 1 row in an 8k block, leading to about 40% wastage. With 16k blocksizes, things are better, but rowsize is still a significant percentage of blocksize. Variance in rowsizes can only help to make things worse.
As rows are inserted/updated/deleted, there is a good chance of blocks coming on and off the freelist. I believe there is something in the kernel that says if the same block goes on/off too frequently, ie 5 times (5 seems to be a favourite number), it is banished off the freelist. Whichever way, the rowsize is a factor in your case. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, October 02, 2003 5:49 PM > Here are the stats if you're interested. I can't run dbms_space on the table because it will lock up the application. This table is accessed ALL the time. It grabbed another 100M today! Extent management is LOCAL with UNIFORM SIZE of 100M. > > > 24th Sep 2003 > > OWNE SEGMENT_NAME TABLESPACE_NAME KB NEXTKB EXT[MAX] % Inc > ---- --------------------------------- -------------------- ------------ - ----------- -------------------- ---------- > CCA CONNECT_TASK[T] CC_TASK_TABS 3,891,200 102,400 38[2147483645] 0 > CCA CONNECT_TASK_CONNECTID[I] CC_TASK_IDXS 102,400 102,400 1[2147483645] 0 > CCA CONNECT_TASK_CRN[I] CC_TASK_IDXS 102,400 102,400 1[2147483645] 0 > CCA PK_CONNECT_TASK[I] CC_TASK_IDXS 102,400 102,400 1[2147483645] 0 > > > OWNE TABLE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS EMPTY_BLOCKS CHAIN_CNT > ---- ------------------------------ ---------- ----------- ---------- ---- -------- ---------- > CCA CONNECT_TASK 185583 4898 484189 2210 1445 > > > > 2nd Oct 2003 > > OWNER SEGMENT_NAME TABLESPACE_NAME KB NEXTKB EXT[MAX] > -------------------- --------------------------------- ------------------- - ------------ ------------ -------------------- > CCA CONNECT_TASK[T] CC_TASK_TABS 4,198,400 102,400 41[2147483645] > CCA CONNECT_TASK_CONNECTID[I] CC_TASK_IDXS 102,400 102,400 1[2147483645] > CCA CONNECT_TASK_CRN[I] CC_TASK_IDXS 102,400 102,400 1[2147483645] > CCA PK_CONNECT_TASK[I] CC_TASK_IDXS 102,400 102,400 1[2147483645] > > > OWNE TABLE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS EMPTY_BLOCKS CHAIN_CNT > ---- ------------------------------ ---------- ----------- ---------- ---- -------- ---------- > CCA CONNECT_TASK 184113 4958 512699 12100 1528 > > > > -------------------------------------------------------------------------- ------ > From: "Sinardy Xing" <[EMAIL PROTECTED]> > Date: Wed, 1 Oct 2003 14:51:40 +0800 > Subject: RE: Table not reusing deleted space > > Hi Kaing, > > Have you check the degree of fragmentation? > > have you check your extent size? > > > Sinardy > > > ---------------------------------------------------------- > Leng Kaing > Email: [EMAIL PROTECTED] > Phone: +61-3-9203-7589 > Mobile: +61-417-371-348 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Kaing, Leng > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
