-
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.
-
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.
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.
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.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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.
yeah!!!
-
Do they TRUNCATE the table or just DELETE?
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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)
yeah!!!
-
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)
Why don't you ask them to truncate the table after it's empty?
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Hi julian,
This is application design, based on some values of columns the rows are deleted after pocessing. Design can not be changed.
yeah!!!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|