Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

In Oracle, where can I see the history of DBFs autoextending? Or get the timestamp that a DBF last autoextended, and by how much?

share|improve this question
1  
I don't think this is recorded. The closest I can think is DBA_EXTENTS which would have info on the changes in extents that may be a byproduct of an autoextend action, but there are no dates associated with the tables that make up the DBA_EXTENTS view. – David Mann Dec 28 '11 at 14:41

2 Answers

up vote 4 down vote

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/

share|improve this answer
I'd like to study this in order to optimize the setting of NEXT empirically... – Gaius Dec 28 '11 at 14:57
1  
Oracle have done a lot of work to make the value of NEXT not that critical. What is the goal of the optimization you are intending? – BillThor Dec 28 '11 at 20:24
1  
Gaius, I'd be amazed if datafile extension had a measurable performance impact to optimize. My preference would be for larger NEXT sizes so as to minimize datafile fragmentation, or even fixed datafile sizes and just add another of the same size when required, but it doesn't sound like that is your target. – Niall Litchfield Jan 3 '12 at 15:48
1  
"how much do you want to delay any individual transaction that triggers a file extension event?" I know it is of academic interest only, but would it be theoretically possible to avoid this delay entirely by having autoextend happen before the datafile is fill - eg when it reaches a kind of 'pctused' threshold? – Jack Douglas♦ Jan 11 '12 at 9:10
1  
Jack, theoretically yes, in practice adding a new datafile rather than autoextending will avoid the impact on the users session (and place it on an admin session ). – Niall Litchfield Jan 13 '12 at 9:47
up vote 1 down vote

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 select sysdate, file#, Blocks, MaxEntend, Inc from sys.file$; every second (processed separately) would give you about as clear a picture as possible. While this does technically answer your question, the advantage of having this information would not be worth the overhead.

I highly recommend NOT doing this.

share|improve this answer
Heh, well, if you have a low-latency trading application that paused to autoextend while the market was open then you might think it was "worth" it (real experience at previous job). In the end, we simply massively oversized it. – Gaius Jan 7 '12 at 16:26
If I had such an application I would certainly not want the overhead of a solution such as I have described. My "not worth it" was with regard to this solution not to the suitable solution to the problem. If I had such an application I wouldn't care as much about space under-utilization and would have taken the exact same approach you did. – Leigh Riffel Jan 9 '12 at 16:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.