At what point do you recommend adding an extent to replace the variable extent?
I have a fixed extent that is full and the variable at 86% of the size of the fixed.
> 25 Index_Txn3 1000000 856440 1856440 1856316 124 186%
Plenty of disk space so not worried there.
You have a decision to make. You are either going to try to stay ahead of things and (try to) add fixed extents before you start writing to the variable extent *or* you might decide that you're not worried about that and you will just let the fixed extents grow.
For many people with small to medium databases the "don't worry about it" option makes a lot of sense.
If you have a larger database then you might want to be more proactive.
Personally I'm not calling anything less than 1TB "large" anymore and it doesn't really bother me to see 64GB extents. Other people have other opinions. You do need to be careful about getting carried away -- there is a very hard limit of 1TB for an extent and there is no recovery if you try to exceed it.
In that case the metric that you probably want to pay attention to is %LastX. That is the percentage full of the last variable extent.
In your case, since you are already writing to the variable extent, so %LastX should be 100%. If so then it is too late to be proactive, you have already overflowed, but that's ok, you just end up with a funky sized extent in the middle of all the others.
If you are going to be actively managing things commercial ProTop has an alert that fires when the last fixed extent reaches a threshold (generally 80%). (Free ProTop does not fire any alerts.)
You have a decision to make. You are either going to try to stay ahead of things and (try to) add fixed extents before you start writing to the variable extent *or* you might decide that you're not worried about that and you will just let the fixed extents grow.
For many people with small to medium databases the "don't worry about it" option makes a lot of sense.
If you have a larger database then you might want to be more proactive.
Personally I'm not calling anything less than 1TB "large" anymore and it doesn't really bother me to see 64GB extents. Other people have other opinions. You do need to be careful about getting carried away -- there is a very hard limit of 1TB for an extent and there is no recovery if you try to exceed it.
In that case the metric that you probably want to pay attention to is %LastX. That is the percentage full of the last variable extent.
In your case, since you are already writing to the variable extent, so %LastX should be 100%. If so then it is too late to be proactive, you have already overflowed, but that's ok, you just end up with a funky sized extent in the middle of all the others.
If you are going to be actively managing things commercial ProTop has an alert that fires when the last fixed extent reaches a threshold (generally 80%). (Free ProTop does not fire any alerts.)
Just want to share with you that in OE 12.1, we have added some new features to managing variable length extents. For example, you can use the "e" attribute in the .st file to extend a variable length extent to certain fixed size, then add a new variable extent (which can use the "x" attribute to pre-extend it to certain size).
By doing this, you can avoid the "funky sized extent in the middle of all the others".
Right. Just because something was making sense 10 years ago does not mean it will make sense now. And what makes sense now will not make in the future, Progress should have the ability to grow an existing extents. It is great that OE 12.1 allows to grow a variable extent to a certain size, but how about fixed extents? It would be nice to grow them too.
10 years ago I set my Oracle DB extents to 5 GB, The database is growing, I changed extents to 10 GB a while back, now I make extents 20 GB each and in 5 years I do not know how much I will make them. Oracle allows me to grow fixed length extents. Nice feature. And having that all the conversation what is the right and what is not right -- does not matter. You make what ever size you want.
BTW in Oracle I can even shrink the fixed extent, provided that it's HWM allows to do that, i.e shrink only Empty blocks. That is probably not that needed, only if accidentally someone makes a too big extent.