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
----- Original Message -----
From: "BN" <bnsarma@xxxxxxxxx>
To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, August 02, 2006 4:48 PM
Subject: Table Fragmentation in LMTS - Want to check the numbers
> 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
