-
Weird behavoir in Oracle 9i
Hi everyone,
I have kinda strange situation that I need an explaination, I hope some of you can me a good hint. We have a large table that has alot of inserts and the table is growing at a fast pace. To minimize this growth, we implement a purge job that essentially delete a bunch of old records from this table. The strangest thing is that even after the purge job ran, the table keep growing, even though the number of records within that tables has been reduced significantly. From what I can see, the table is grabbing free blocks beyond the HWM. I thought Oracle would recycle the used blocks before attempting to use the free blocks above the HWM. Is there something I can do to rectify this issue. We're using 9iR2 with LMT and auto segment space mgmt. Thanks in advance!!
-
How you are inserting the data?
-
Consider table reorganization. After you have deleted the rows, export the table, then drop it and import the table again. That should solve your problem I suppose.
-
Hi,
Could you let us know the way you measure the size of the table/segment?
Thanks
-
Hi,
You can also use alter table sch.tab_name move;
But remember that u need to rebuild all the indexes involved with this table. If that is feasible for u then u can proceed and measure the size of the segment.
-
apart from what Thomasps said, what is your PCT_FREE settings? (if your not using segment space management AUTO)
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Do you use /*+ append */ hint for your table inserts by any chance? This is exactly what this hint was designed for.
-
Originally Posted by newbie
Hi everyone,
I have kinda strange situation that I need an explaination, I hope some of you can me a good hint. We have a large table that has alot of inserts and the table is growing at a fast pace. To minimize this growth, we implement a purge job that essentially delete a bunch of old records from this table. The strangest thing is that even after the purge job ran, the table keep growing, even though the number of records within that tables has been reduced significantly. From what I can see, the table is grabbing free blocks beyond the HWM. I thought Oracle would recycle the used blocks before attempting to use the free blocks above the HWM. Is there something I can do to rectify this issue. We're using 9iR2 with LMT and auto segment space mgmt. Thanks in advance!!
Hi,
can you provide us with some additional answers?
How are you loading the data? Are you using SQL*Loader? This tool can load above the HWM to gain speed.
Why is this behaviour strange, did you not have this with Oracle8?
I don't think rebuilding the table every each delete is a good idea. I myself restrain from rebuilding indexes every evening.
There is an interesting Oracle doc:
"How to stop defragmenting and start living : the definitive word on fragmentation" - doc in PDF *239049.pdf ( I don't remember the original ID)
Regards,
Richard.
"Whenever you want to learn the basics about Oracle - start from the Oracle Concepts Guide."
-
Originally Posted by nuushona
Consider table reorganization. After you have deleted the rows, export the table, then drop it and import the table again. That should solve your problem I suppose.
If you're going to reorganise a table then export/import is about the worst way of doing it. ALTER TABLE ... MOVE is a much better method.
-
Originally Posted by abhaysk
apart from what Thomasps said, what is your PCT_FREE settings? (if your not using segment space management AUTO)
He is.
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
|