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
Printable View
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
too many extents?
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.
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.
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!!
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