patternMinor
Where can I find autoextend events?
Viewed 0 times
caneventswhereautoextendfind
Problem
In Oracle, where can I see the history of DBFs autoextending? Or get the timestamp that a DBF last autoextended, and by how much?
Solution
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/
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/
Context
StackExchange Database Administrators Q#9802, answer score: 4
Revisions (0)
No revisions yet.