I have a work table that inserts and deletes thousands of rows per month. This is causing data fragmentation, i.e. 14000+ blocks and only 135 blocks used. What should I set the pctfree and pctused to eliminate this type of fragmentation? Thanks.
Printable View
I have a work table that inserts and deletes thousands of rows per month. This is causing data fragmentation, i.e. 14000+ blocks and only 135 blocks used. What should I set the pctfree and pctused to eliminate this type of fragmentation? Thanks.
I gather somebody told you that you eliminate fragmenation by setting "right value" for PCTFREE and PCTUSED. However, they are not the real fragmentation factors.Quote:
Originally posted by ssmith
I have a work table that inserts and deletes thousands of rows per month. This is causing data fragmentation, i.e. 14000+ blocks and only 135 blocks used. What should I set the pctfree and pctused to eliminate this type of fragmentation? Thanks.
It is the delayed block cleanout which causes most of the fragmentation. I assume you know what a fast commit is?
The only way to eliminate fragmentation is drop and recreate the object. Usually export and import will solve the fragmentation problem.
Hi juliaan
EXP/IMP will remove the fragmentation ok .
but if he will not change the storage setting ,In next few months , will he not face fragmantation again ??
please thru some light .
Fragmentation usually does not come due to bad storage settings. There are things, we as DBAs cannot influence on, things that a major fragmentation factors.
Here is how tables get most oftenly fragmented: say that there is a table or index upon which you are doing large deletes and then inserts very frequently. After deleting lots of rows and immediately afterwards insert rows, well the table will grow. The reason is: delayed block cleanout, which will cause fragmentation in the table.
pctused and pctfree controls block fragmentation, but the fragmentation you are talking about is at segment level!
you can reconstruct your table with alter table xxx move tablespace vvvv
if you want to use your blocks efficiently, set a low pctfree (if there are no updates) and high pctused so the blocks cab be relinked to freelists more easily
[Edited by pando on 03-13-2002 at 05:44 AM]
Thanks Julian/pando,
I got ur point.
Now I have a slightly different question ,
I have a table not having records more then 10000.
But users process are such that they delete ALL the records daily and then next days new records are inserted for processing.
I am not facing fragmantation problem but sometimes performance problem there.
As all the data is daily deleted from the table . It it makes any sense to Rebuild indexs of such a table.
Do they TRUNCATE the table or just DELETE?
They just delete.
In fact rows are processed one by one and then moved to some other table ( i.e. fst insert in to some other table and then delete from the main table)
Why don't you ask them to truncate the table after it's empty?Quote:
Originally posted by sri_sumit123
They just delete.
In fact rows are processed one by one and then moved to some other table ( i.e. fst insert in to some other table and then delete from the main table)
Hi julian,
This is application design, based on some values of columns the rows are deleted after pocessing. Design can not be changed.
Ok , Thanks julian , got your point .
OK but nothing should happen to the design of the application if you truncate an empty table, right?
yes ys !! u r very true.
The general guidelines for setting up PCTFREE and PCTUSED are:
If the table is of
1 Mostly inserts, Set a small PCTFREE and PCTUSED as 40
2 Queue like - rows are inserted and subsequently deleted soon after- Set a small PCTFREE (5) and very small PCTUSED (10).
Example, an audit table (log table). Rows older than a month get deleted every day.
3 Randon Insert/Delete, estimate row size. Then apply the formula given below:
PCTUSED = (100 - PCTFREE - (MAX(10,(MAX_ROW_SIZE/BLOCK_SIZE)*100))
If the PCTUSED ends up -n negative, set it 1.
4 Mostly update. Estimate row expansion in %, and set it to PCTFREE and set PCTUSED as 40.
Tamilselvan
I read in Oracle 8i DBA HandBook that it's better if the sum of pctfree with pctused results 85, according to pctfree value. For example:
if pctfree is 10%, pctused should be 75%
if pctfree is 5%, pctused should be 80%
if pctfree is 50%, pctused should be 35%
this grants that a block will use most of its space for data storage.
How far is this true?
F.
Oracle DBA HandBook talked about PCTFREE and PCTUSED are in general.
It does not know the nature of table that undergoes changes.
When you switch to 9i you can forget about PCTUSED (and FREELIST stuff, which PCTUSED is all about). You can simply specify your localy managed tablespace as "SEGMENT SPACE MANAGEMENT AUTO" and let Oracle doo all the decisions about which blocks should be available for additional inserts. And no more freelist-contention problems...
however with this new feature there are already performance problems being reported on metalink....
Very far!!!Quote:
Originally posted by Mnemonical
For example:
if pctfree is 10%, pctused should be 75%
if pctfree is 5%, pctused should be 80%
if pctfree is 50%, pctused should be 35%
How far is this true?
F.
Example :
DB_BLOCK_SIZE=4K
Table TTTT has avg_row_len=400 byte
Row_per_block ~9 max
Every day insert ~ 1000 new records.
Avg delete ~5% rows
Business procedure has 3 steps:
1) insert row (with initial information) ~ 100 bytes avg len
2) 1 week later each row should be upbate and add +50 bytes to avg len
3) 2 weeks later each row should be upbate and add last +250 bytes to avg len
How we should calculate PCT_FREE, PCT_USED
If we set :
if pctfree is 10%, pctused should be 75% ~= 90% rows will be chained (and near 100% of db_blocks)
if pctfree is 50%, pctused should be 35% ~= 25% rows will be chained
base on information about 1step ~ 35% alg len of record (then PCT_FREE ~ 30-35%)
base on information about 5% deleted rows:
PCT_USED ~ 35-( 9*0.05*400)/4000 ~= 25(or 30%)
Conclusion : use LMT
Set PCTFREE to 75 % and PCTUSED 15 %.
[Edited by tamilselvan on 03-14-2002 at 05:02 PM]
I'm sorry U rignt.Quote:
Originally posted by tamilselvan
Set PCTFREE to 60 % and PCTUSED 30 %.
PCT_FREE = 100-35%(not 35)
Never set PCTFREE+PCTUSED= 100.
This will cause over head on extent management.
For a OLTP system,
you can set PCTFREE to 75 % and PCTUSED 15 %.
I know it. We can disscass this question more detail. Becase we are neighbors. I live in Highland park and we can meet somewhere in "Somerset Diner"Quote:
Originally posted by tamilselvan
Never set PCTFREE+PCTUSED= 100.
This will cause over head on extent management.
For a OLTP system,
you can set PCTFREE to 75 % and PCTUSED 15 %.