Re: Table not reusing deleted space
Hi Daniel, Thank you so much for solving part of the mystery. This is the best explanation I've got yet. I think you may be right. Our avg. row len. is more than 5K and our database is 8k block. I can't believe that the 1G is spread EVERY single 4G block. So how does Oracle determine which 5 blocks to check first before giving up, or is it just a random sample and it happens be those half filled? We may have to build a special 16K block database just for this table in the end. Now I see why we need 9I :-} But upgrading is probably not an option at the moment. Thank you!! Leng. --- From: Daniel Fink [EMAIL PROTECTED] Date: Wed, 01 Oct 2003 07:53:14 -0600 Subject: Re: Table not reusing deleted space This is a multi-part message in MIME format. --D366F924536517C4833C5849 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Leng, I recall a similar scenario some months ago. It had to do with the average row size (quite large) and the block size. The average row size was just under 1/2 of the block size, so the chances of a new row finding a spot in an existing block was slim. Add in that there is a limit (5 I think) of blocks on the freelist that a transaction will attempt to allocate space in before it says I can't find a block with enough free space so I'm going to allocate a new extent.. I think we looked at dba_tables.avg_row_len and dba_tables.avg_space_freelist_blocks. (?) A quick calculation (1048576k /20) indicates that your average row length is over 5k. If you have 8k blocks, this means an average of 1 row per block (perhaps less depending on the variance in row length). Daniel Fink Kaing, Leng wrote: Hello everyone, Env: 8.1.7.4, SunOs 5.8 64 Bit We seem to hitting bug 1262161. The bug seems to imply that tables with triggers behind them do not reuse blocks on the freelist. We have a table that should only use 1G (num_rows * avg_row_len), but is actually using 4.1G and growing in size!! It is subject to high inserts, deletes and updates. But the resultant number of rows is around 200K rows. The insert is just a normal insert, no APPEND hint is used. Updates do not really expand the rows. We've changed PCTUSED from 40 to 70 to no avail. The table does not seem to reuse the deleted space. In trying to prove this error in our environment I've created 5 test scenarios but was never able to reproduce the problem. It only exists on our production database. I'm stumped. Has anyone encountered this problem? Or can someone explain to me why our production database is not reusing the space deleted and placed back on the free list? I should also add that the table in question is a master table of a snapshot. TIA, Leng. -- 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).
Re: Table not reusing deleted space
Leng, I am going to have to defer to others on the freelist algorithm. I have not done any examination/testing and don't want to make assertions that would cause more problems. Daniel Kaing, Leng wrote: Hi Daniel, Thank you so much for solving part of the mystery. This is the best explanation I've got yet. I think you may be right. Our avg. row len. is more than 5K and our database is 8k block. I can't believe that the 1G is spread EVERY single 4G block. So how does Oracle determine which 5 blocks to check first before giving up, or is it just a random sample and it happens be those half filled? We may have to build a special 16K block database just for this table in the end. Now I see why we need 9I :-} But upgrading is probably not an option at the moment. Thank you!! Leng. --- From: Daniel Fink [EMAIL PROTECTED] Date: Wed, 01 Oct 2003 07:53:14 -0600 Subject: Re: Table not reusing deleted space This is a multi-part message in MIME format. --D366F924536517C4833C5849 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Leng, I recall a similar scenario some months ago. It had to do with the average row size (quite large) and the block size. The average row size was just under 1/2 of the block size, so the chances of a new row finding a spot in an existing block was slim. Add in that there is a limit (5 I think) of blocks on the freelist that a transaction will attempt to allocate space in before it says I can't find a block with enough free space so I'm going to allocate a new extent.. I think we looked at dba_tables.avg_row_len and dba_tables.avg_space_freelist_blocks. (?) A quick calculation (1048576k /20) indicates that your average row length is over 5k. If you have 8k blocks, this means an average of 1 row per block (perhaps less depending on the variance in row length). Daniel Fink Kaing, Leng wrote: Hello everyone, Env: 8.1.7.4, SunOs 5.8 64 Bit We seem to hitting bug 1262161. The bug seems to imply that tables with triggers behind them do not reuse blocks on the freelist. We have a table that should only use 1G (num_rows * avg_row_len), but is actually using 4.1G and growing in size!! It is subject to high inserts, deletes and updates. But the resultant number of rows is around 200K rows. The insert is just a normal insert, no APPEND hint is used. Updates do not really expand the rows. We've changed PCTUSED from 40 to 70 to no avail. The table does not seem to reuse the deleted space. In trying to prove this error in our environment I've created 5 test scenarios but was never able to reproduce the problem. It only exists on our production database. I'm stumped. Has anyone encountered this problem? Or can someone explain to me why our production database is not reusing the space deleted and placed back on the free list? I should also add that the table in question is a master table of a snapshot. TIA, Leng. -- Leng Kaing Email: [EMAIL PROTECTED] Phone: +61-3-9203-7589 Mobile: +61-417-371-348 begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
RE: Table not reusing deleted space
Hi Leng, I had this problem a while ago and it drove me crazy. What is your PCTFREE? I eventually resolved the problem by reducing my PCTFREE to 5 reasoning that this would put more blocks on the freelist. But I have a really really big table now which I'm finally going to be able to truncate at the end of the month (and regain many gigs of space). Jay Miller Sr. Oracle DBA x68355 -Original Message- Sent: Friday, October 03, 2003 4:04 AM To: Multiple recipients of list ORACLE-L Hi Daniel, Thank you so much for solving part of the mystery. This is the best explanation I've got yet. I think you may be right. Our avg. row len. is more than 5K and our database is 8k block. I can't believe that the 1G is spread EVERY single 4G block. So how does Oracle determine which 5 blocks to check first before giving up, or is it just a random sample and it happens be those half filled? We may have to build a special 16K block database just for this table in the end. Now I see why we need 9I :-} But upgrading is probably not an option at the moment. Thank you!! Leng. --- From: Daniel Fink [EMAIL PROTECTED] Date: Wed, 01 Oct 2003 07:53:14 -0600 Subject: Re: Table not reusing deleted space This is a multi-part message in MIME format. --D366F924536517C4833C5849 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Leng, I recall a similar scenario some months ago. It had to do with the average row size (quite large) and the block size. The average row size was just under 1/2 of the block size, so the chances of a new row finding a spot in an existing block was slim. Add in that there is a limit (5 I think) of blocks on the freelist that a transaction will attempt to allocate space in before it says I can't find a block with enough free space so I'm going to allocate a new extent.. I think we looked at dba_tables.avg_row_len and dba_tables.avg_space_freelist_blocks. (?) A quick calculation (1048576k /20) indicates that your average row length is over 5k. If you have 8k blocks, this means an average of 1 row per block (perhaps less depending on the variance in row length). Daniel Fink Kaing, Leng wrote: Hello everyone, Env: 8.1.7.4, SunOs 5.8 64 Bit We seem to hitting bug 1262161. The bug seems to imply that tables with triggers behind them do not reuse blocks on the freelist. We have a table that should only use 1G (num_rows * avg_row_len), but is actually using 4.1G and growing in size!! It is subject to high inserts, deletes and updates. But the resultant number of rows is around 200K rows. The insert is just a normal insert, no APPEND hint is used. Updates do not really expand the rows. We've changed PCTUSED from 40 to 70 to no avail. The table does not seem to reuse the deleted space. In trying to prove this error in our environment I've created 5 test scenarios but was never able to reproduce the problem. It only exists on our production database. I'm stumped. Has anyone encountered this problem? Or can someone explain to me why our production database is not reusing the space deleted and placed back on the free list? I should also add that the table in question is a master table of a snapshot. TIA, Leng. -- 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: 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).
RE: Table not reusing deleted space
Actually, that probably wasn't the reasoning (I'm too tired to recollect it at the moment), but it worked. We have PCTFREE 5 and PCTUSED 40. Good luck! Jay Miller Sr. Oracle DBA x68355 -Original Message- Sent: Friday, October 03, 2003 8:27 PM To: '[EMAIL PROTECTED]' Hi Leng, I had this problem a while ago and it drove me crazy. What is your PCTFREE? I eventually resolved the problem by reducing my PCTFREE to 5 reasoning that this would put more blocks on the freelist. But I have a really really big table now which I'm finally going to be able to truncate at the end of the month (and regain many gigs of space). Jay Miller Sr. Oracle DBA x68355 -Original Message- Sent: Friday, October 03, 2003 4:04 AM To: Multiple recipients of list ORACLE-L Hi Daniel, Thank you so much for solving part of the mystery. This is the best explanation I've got yet. I think you may be right. Our avg. row len. is more than 5K and our database is 8k block. I can't believe that the 1G is spread EVERY single 4G block. So how does Oracle determine which 5 blocks to check first before giving up, or is it just a random sample and it happens be those half filled? We may have to build a special 16K block database just for this table in the end. Now I see why we need 9I :-} But upgrading is probably not an option at the moment. Thank you!! Leng. --- From: Daniel Fink [EMAIL PROTECTED] Date: Wed, 01 Oct 2003 07:53:14 -0600 Subject: Re: Table not reusing deleted space This is a multi-part message in MIME format. --D366F924536517C4833C5849 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Leng, I recall a similar scenario some months ago. It had to do with the average row size (quite large) and the block size. The average row size was just under 1/2 of the block size, so the chances of a new row finding a spot in an existing block was slim. Add in that there is a limit (5 I think) of blocks on the freelist that a transaction will attempt to allocate space in before it says I can't find a block with enough free space so I'm going to allocate a new extent.. I think we looked at dba_tables.avg_row_len and dba_tables.avg_space_freelist_blocks. (?) A quick calculation (1048576k /20) indicates that your average row length is over 5k. If you have 8k blocks, this means an average of 1 row per block (perhaps less depending on the variance in row length). Daniel Fink Kaing, Leng wrote: Hello everyone, Env: 8.1.7.4, SunOs 5.8 64 Bit We seem to hitting bug 1262161. The bug seems to imply that tables with triggers behind them do not reuse blocks on the freelist. We have a table that should only use 1G (num_rows * avg_row_len), but is actually using 4.1G and growing in size!! It is subject to high inserts, deletes and updates. But the resultant number of rows is around 200K rows. The insert is just a normal insert, no APPEND hint is used. Updates do not really expand the rows. We've changed PCTUSED from 40 to 70 to no avail. The table does not seem to reuse the deleted space. In trying to prove this error in our environment I've created 5 test scenarios but was never able to reproduce the problem. It only exists on our production database. I'm stumped. Has anyone encountered this problem? Or can someone explain to me why our production database is not reusing the space deleted and placed back on the free list? I should also add that the table in question is a master table of a snapshot. TIA, Leng. -- 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: 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).
RE: Table not reusing deleted space
Hi Leng, I have suggestion, FOR YOU TO TEST ONLY *CHECK SIZE** 1. EXPORT target tables with COMPRESS = Y parameter 2. DROP target tables *CHECK SIZE** 3. CREATE TABLE (target tables) 4. IMPORT target tables *CHECK SIZE** Good luck Sinardy -Original Message- Sent: 02 October 2003 13:50 To: Multiple recipients of list ORACLE-L 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_NAMEKB NEXTKB EXT[MAX] % Inc - -- CCA CONNECT_TASK[T] CC_TASK_TABS3,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 1855834898 484189 2210 1445 2nd Oct 2003 OWNERSEGMENT_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 1841134958 51269912100 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: Sinardy Xing 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).
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. - 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_NAMEKB NEXTKB EXT[MAX] % Inc - - --- -- CCA CONNECT_TASK[T] CC_TASK_TABS3,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 1855834898 484189 2210 1445 2nd Oct 2003 OWNERSEGMENT_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 1841134958 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).
Re: Table not reusing deleted space
Sinardy, I'm not sure I would perform the export/import. It may temporarily mask the problem, but it will return. I think the real issue is that the table has a very large average row length (4898). If you look at the chain count (roughly 1% of the rows), this indicates that the updates are such that they cannot fit into the original block. If you exp/imp, I think you will find that the allocation problem returns rather quickly. One possible solution that has been mentioned in previous posts is to increase the freelists. The intent is to enable inserts to find a block with sufficient free space. You also need to look at the pctfree/pctused and see if they can be set to enable reuse of the blocks. There is also an undocumented parameter (contact OWS) that increases the number of freelist blocks that a transaction will walk before using a new block/allocating a new extent. Daniel Sinardy Xing wrote: Hi Leng, I have suggestion, FOR YOU TO TEST ONLY *CHECK SIZE** 1. EXPORT target tables with COMPRESS = Y parameter 2. DROP target tables *CHECK SIZE** 3. CREATE TABLE (target tables) 4. IMPORT target tables *CHECK SIZE** Good luck Sinardy -Original Message- Sent: 02 October 2003 13:50 To: Multiple recipients of list ORACLE-L 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_NAMEKB NEXTKB EXT[MAX] % Inc - -- CCA CONNECT_TASK[T] CC_TASK_TABS3,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 1855834898 484189 2210 1445 2nd Oct 2003 OWNERSEGMENT_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 1841134958 51269912100 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: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
RE: Table not reusing deleted space
What is the block size of the database. Also, is the AVG_ROW_LEN of almost 5000 bytes evenly distributed, i.e. are ALL rows more or less 5000 bytes long, or are there many rows that are a lot smaller and then some that are really big? At 11:49 PM 10/1/2003, you wrote: 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_NAMEKB NEXTKB EXT[MAX] % Inc - -- CCA CONNECT_TASK[T] CC_TASK_TABS3,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 1855834898 484189 2210 1445 2nd Oct 2003 OWNERSEGMENT_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 1841134958 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). Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).
RE: Table not reusing deleted space
Hi Kaing, Have you check the degree of fragmentation? have you check your extent size? Sinardy -Original Message- Sent: 01 October 2003 14:20 To: Multiple recipients of list ORACLE-L Hello everyone, Env: 8.1.7.4, SunOs 5.8 64 Bit We seem to hitting bug 1262161. The bug seems to imply that tables with triggers behind them do not reuse blocks on the freelist. We have a table that should only use 1G (num_rows * avg_row_len), but is actually using 4.1G and growing in size!! It is subject to high inserts, deletes and updates. But the resultant number of rows is around 200K rows. The insert is just a normal insert, no APPEND hint is used. Updates do not really expand the rows. We've changed PCTUSED from 40 to 70 to no avail. The table does not seem to reuse the deleted space. In trying to prove this error in our environment I've created 5 test scenarios but was never able to reproduce the problem. It only exists on our production database. I'm stumped. Has anyone encountered this problem? Or can someone explain to me why our production database is not reusing the space deleted and placed back on the free list? I should also add that the table in question is a master table of a snapshot. TIA, Leng. -- 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: Sinardy Xing 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).
Re: Table not reusing deleted space
Leng, I recall a similar scenario some months ago. It had to do with the average row size (quite large) and the block size. The average row size was just under 1/2 of the block size, so the chances of a new row finding a spot in an existing block was slim. Add in that there is a limit (5 I think) of blocks on the freelist that a transaction will attempt to allocate space in before it says I can't find a block with enough free space so I'm going to allocate a new extent.. I think we looked at dba_tables.avg_row_len and dba_tables.avg_space_freelist_blocks. (?) A quick calculation (1048576k /20) indicates that your average row length is over 5k. If you have 8k blocks, this means an average of 1 row per block (perhaps less depending on the variance in row length). Daniel Fink Kaing, Leng wrote: Hello everyone, Env: 8.1.7.4, SunOs 5.8 64 Bit We seem to hitting bug 1262161. The bug seems to imply that tables with triggers behind them do not reuse blocks on the freelist. We have a table that should only use 1G (num_rows * avg_row_len), but is actually using 4.1G and growing in size!! It is subject to high inserts, deletes and updates. But the resultant number of rows is around 200K rows. The insert is just a normal insert, no APPEND hint is used. Updates do not really expand the rows. We've changed PCTUSED from 40 to 70 to no avail. The table does not seem to reuse the deleted space. In trying to prove this error in our environment I've created 5 test scenarios but was never able to reproduce the problem. It only exists on our production database. I'm stumped. Has anyone encountered this problem? Or can someone explain to me why our production database is not reusing the space deleted and placed back on the free list? I should also add that the table in question is a master table of a snapshot. TIA, Leng. -- Leng Kaing Email: [EMAIL PROTECTED] Phone: +61-3-9203-7589 Mobile: +61-417-371-348 begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
Re: Table not reusing deleted space
Hi Leng, You mentioned that the table is subject to high insert, update and delete activity. Is it possible that the high watermark of the table has in fact been growing, even though the steady state size is around 200K rows? Since deletes don't actually shrink the size of a table (only truncates do), if the high watermark of the table keeps growing, the table size will grow too, even if you later on delete many of the rows. If this is the case, you might want to look into partitioning the table (so that you can drop a partition instead of deleting rows) or regularly rebuilding the table. HTH, Dave [EMAIL PROTECTED] wrote: Hello everyone, Env: 8.1.7.4, SunOs 5.8 64 Bit We seem to hitting bug 1262161. The bug seems to imply that tables with triggers behind them do not reuse blocks on the freelist. We have a table that should only use 1G (num_rows * avg_row_len), but is actually using 4.1G and growing in size!! It is subject to high inserts, deletes and updates. But the resultant number of rows is around 200K rows. The insert is just a normal insert, no APPEND hint is used. Updates do not really expand the rows. We've changed PCTUSED from 40 to 70 to no avail. The table does not seem to reuse the deleted space. In trying to prove this error in our environment I've created 5 test scenarios but was never able to reproduce the problem. It only exists on our production database. I'm stumped. Has anyone encountered this problem? Or can someone explain to me why our production database is not reusing the space deleted and placed back on the free list? I should also add that the table in question is a master table of a snapshot. TIA, Leng. -- 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: Dave Hau 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).
RE: Table not reusing deleted space
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_NAMEKB NEXTKB EXT[MAX] % Inc - -- CCA CONNECT_TASK[T] CC_TASK_TABS3,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 1855834898 484189 2210 1445 2nd Oct 2003 OWNERSEGMENT_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 1841134958 51269912100 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).
Table not reusing deleted space
Hello everyone, Env: 8.1.7.4, SunOs 5.8 64 Bit We seem to hitting bug 1262161. The bug seems to imply that tables with triggers behind them do not reuse blocks on the freelist. We have a table that should only use 1G (num_rows * avg_row_len), but is actually using 4.1G and growing in size!! It is subject to high inserts, deletes and updates. But the resultant number of rows is around 200K rows. The insert is just a normal insert, no APPEND hint is used. Updates do not really expand the rows. We've changed PCTUSED from 40 to 70 to no avail. The table does not seem to reuse the deleted space. In trying to prove this error in our environment I've created 5 test scenarios but was never able to reproduce the problem. It only exists on our production database. I'm stumped. Has anyone encountered this problem? Or can someone explain to me why our production database is not reusing the space deleted and placed back on the free list? I should also add that the table in question is a master table of a snapshot. TIA, Leng. -- 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).
