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

Greetings Jonathan,

Thank you.

My doubts are cleared now.

Regards & Thanks

On 8/3/06, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>
> 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
>
>
>

-- 
Regards & Thanks
BN

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