In Oracle, where can I see the history of DBFs autoextending? Or get the timestamp that a DBF last autoextended, and by how much?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
I'm not aware of a particular method that records this, the information doesn't appear in file$ or x$kccfe . In general though I wouldn't worry about these events so much as how much free space I have in both the tablespace and the file system. There are alternative methods to monitor this
Updated At Leigh's suggestion
In answer to Gaius comment - but too long for a comment itself.
I think there are 2 basic things to consider for "efficiency" and the results will be system and site dependent. Its worth re-iterating though that I think the efficiency or otherwise of autoextend from a performance viewpoint is way down the list of priorities. (it was just interesting at a quiet time).
First is, overall how efficient do you want the allocation of space to be. The best result will be achieved if you never autoextend but size exactly correctly to begin with :) In general the fewer file extension operations you do the better, but its a declining win.
Second, how much do you want to delay any individual transaction that triggers a file extension event? here the opposite applies, the more small file extensions you have the shorter each individual event will be.
I have some figures and a sample script for others to test/critique etc at http://orawin.info/blog/2012/01/04/proof-by-extension/
As Niall said this data is not available.
It would be crude, but you could roll your own solution for this. By periodically querying sys.file$ for size changes, you could determine that one or more auto-extend events have occurred and then based on the size change and the auto-increment value determine how many extensions have taken place since the last check. The more often you check the more closely you could identify the times.
Inserting the results of
I highly recommend NOT doing this.