Re: Table Fragmentation in LMTS - Want to check the numbers

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_at_gmail.com> To: "Oracle-L Freelists" <oracle-l_at_freelists.org> 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

--
http://www.freelists.org/webpage/oracle-l