You have your formula a bit messed up:
If you calculate num_rows * (avg_row_len + 5) / 3900 That will tell you (approximately) the number of blocks you would need to store the table with PCTFREE set to 0.
avg_row_len + 5 because dbms_stats does not allow for the 5 byte row overhead.
3900 because with a 4K block size, that allows for the block header requirements.
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS NFB AVG_ROW_LEN -------------------- ---------- ---------- ------------ ------ ----------- BIGTABLE_XYZ 1026304 11390 0 0 14
1,026,304 * 19 / 3900 = 4,999
So when you took the stats, your blocks were averaging about 57 percent empty. Now that you're down to 122,420 rows, you'll be nearer an average of 95% empty.
A first thought is that you may need to set PCTUSED to something quite large (say 90 - with pctfree 10) so that blocks can be re-used as soon as you delete a few rows. On the other hand, your pattern of delete/insert may be so extreme that you need to do something more subtle if this is causing an obvious performance problem.
You might find that ASSM might help - provided your usage isn't sufficiently odd to hit a bug/anomaly in its algorithms.
Regards
Jonathan Lewis http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Greetings
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production PL/SQL Release 9.2.0.6.0 - Production CORE 9.2.0.6.0 Production TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production NLSRTL Version 9.2.0.6.0 - Production
Please see the following suggestion we got from our Vender and Oracle regarding Table Re-org in LMTS, Want to verify this with the experts here:
Before we move the table to the keep pool, we saw the table is fragmented. When we query BIGTABLE_XYZ info in the dba_tables, the num_rows * avg_row_len * 4k (block size) is only 1/6 of the blocks taken by the table. We did the "alter table .. move" statements and found that the table takes much less blocks after that.
1) is that the correct way to determine if the table is fragmented?
Oracle Reply:
1. Yes, that is a fair way. Other ways are - - Use the following script : Note.1019716.6 Script to Report Table Fragmentation - collect stats using analyze and check avg_space column in dba_tables. Unfortunately this column d oesnt get populated if we use dbms_stats instead of analyze, so you are forced to use analyze.
Some info about the table:
Free Blocks............................. Total Blocks............................12288 Total Bytes.............................50331648 Total MBytes............................48 Unused Blocks...........................704 Unused Bytes............................2883584 Last Used Ext FileId....................4 Last Used Ext BlockId...................246800 Last Used Block.........................320
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS NFB AVG_ROW_LEN -------------------- ---------- ---------- ------------ ------ ----------- BIGTABLE_XYZ 1026304 11390 0 0 14
Thie table above was last analyzed a week back.
Right now the number of rows is : 122420
so 122420*14*4K=6855520/6 = 1142533.3 and 1026304*14*4=57473024/6= 9578837.33
Can some body throw more light on this ? -- Regards & Thanks BN
--------------------------------------------------------------------------------
No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.5/405 - Release Date: 01/08/2006
-- //www.freelists.org/webpage/oracle-l