| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Table not reusing deleted space
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.
> 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
- ------------ ------------ --------------------
> 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]
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim INET: Binley.Lim_at_xtra.co.nz 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: 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 Thu Oct 02 2003 - 09:19:32 CDT
 |
 |