Friday, March 28, 2008

Your trash will drag you

If you think that Oracle recycle bin (feature available since 10g1) has no direct impact on your performance, you better think deep about it... seriously.

Try the following (if possible) : turn on statement timing, then issue a simple SELECT on DBA_FREE_SPACE and keep record of elapsed time; create a couple of hundreds of thousands tables in a 10g database, and then delete it (the DB recycle bin feature must be turned on) ... now you have a full-to-the-top recycle bin, then repeat the SELECT on DBA_FREE_SPACE, you'll see it will take a longer time to complete, and finally will get a huge number of rows. You may think that suddenly the free space has been multiplied... but your response time divided.

I don't know ... I'll let you think about this puzzling situation here and come back later to see how you doing.

Question: Can you figure out what is happening?

Subscribe to Oracle Database Disected by Email

No comments:

Custom Search