-
how to improve TRUNCATE process
Truncate process is taking more than expected time. I can not use 'REUSE STORAGE' option. Which are the other areas or parameters I must look into?
Regards
Nwcomer
Student
-
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"
-
Let me give u guys the details
In production these tables(which I am truncating) is spread over 4 tablespace segments with total 4768 extents.
Where as in my test database these tables are associated with only one TS with 4896 extents. And in Test DB it is taking hardly 1 minutes with more or less same volume od records.
Nwcomer
Student
-
It sounds like you're using dictionary managed TS, not Locally Managed TS, right?
-
how does that matter in such scenario? If it does, then it is same in 2 databases.
Nwcomer
Student
-
If you are using Oracle Rel 9.1.0.3 and LMT, then truncating table that has a large number of extents will take long time.
If you are using 8.1.6 and DMT, then also truncate will take long time.
Bugs are very common in Oracle. Test your Oracle Rel before you choose LMT or DMT.
Tamil
-
... but in general LMT's are much faster for this kind of operation, due to lower overhead in extent management.
So how long is it taking in production? Got any indexes on there?
-
Yes there is. But the setup is same in test database too. But it is taking hardly 1 minute in the test server.
What can be the other reasons!!
Nwcomer
Student
-
Well, there's obviously the potential for a bug -- is this partitioned by the way, since it's on multiple TS's?
-
Clone the prod into test and do the test again.
Did you analyze sys schema by any chance?
Tamil
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
|