Re: Table Fragmentation in LMTS - Want to check the numbers
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 3 Aug 2006 15:48:28 +0100
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
--
http://www.freelists.org/webpage/oracle-l
Other related posts:
Table Fragmentation in LMTS - Want to check the numbers Re: Table Fragmentation in LMTS - Want to check the numbers Re: Table Fragmentation in LMTS - Want to check the numbers
