Size of extents - OE12

Posted by goo on 16-Jul-2019 15:42

One of the databases is around 100Gb and I can see that they have added 2Gb extents and since we are talking 100Gb there is 50 extents. Is it normal to do this now? I understand back in time there was a 2Gb issue, but now?

//Geir Otto

All Replies

Posted by James Palmer on 16-Jul-2019 15:52

So long as you have an Enterprise License, there is no need for this any more, other than to fill up the directory quickly!

You could, if you like, just have one variable extent, although it may make sense to have some nice ordered fixed extents of say 25GB just to make it look pretty... :)

Posted by Richard Banville on 16-Jul-2019 16:43

Max extent size is 1TB.

Max # extents per area is 1024.

Not that you appear to be at rick of hitting the max [tag:extent]s per area, just a data point.

Posted by ChUIMonster on 16-Jul-2019 17:06

IMHO having extents that are limited to 2GB is kind of silly.  You end up with a lots and lots of tiny little extents.

How many storage areas do you have?

You should have at least a few -- no end user data should be in the schema area.  If you have end user data in the schema area fix that!

At a minimum, you should have "data", "indexes", and "lobs" (if you have any lobs in your database).  You say that the db is 100GB so that's big enough to have made it worth splitting out your larger and more active tables and their associated indexes.  Usually a small number of tables are the bulk of the storage -- perhaps 5 to 10 tables are probably consuming 80% of the disk space (there are always exceptions but that is a general pattern that holds for a lot of people).

That would mean that you might have 15 to 20 storage areas and that the larger ones might be 10GB or so.  In that case I, personally, would be quite happy to use an "all variable" approach.

I don't much like seeing 64GB variable extents but these days I also don't usually start creating fixed extents until I have at least 32GB of data in a storage area.

For the record: the largest variable extent that I know of is just under 800GB.  I'm not really very happy about that, it would be a pain in the neck to work with if it ever needs "dbrpr" style surgery.  But getting permission to take care of it has proven "difficult" (it is the sort of customer where you *really* need permission).  I've got alerts set to trigger as it grows -- I hear that hitting that 1TB limit is unrecoverably ugly.

Posted by goo on 17-Jul-2019 05:24

Thanks, the db in play here is an archive db, so it does not have many tables. It seems like it is only three areas, DokArkiv (Tables), DokArkiv_index (index) and Dokument (BLOB/CLOB). They have also used AI, and that seems kind of stranged as well. So I will use a "ALL variable extents" approach, also for AI. The db is not growing fast...

d "Schema Area":6,32;1 .\arkiv.d1

#

d "DokArkiv":7,32;8 .\arkiv_7.d1 f 32768

d "DokArkiv":7,32;8 .\arkiv_7.d2 f 12096

d "DokArkiv":7,32;8 .\arkiv_7.d3 f 32768

d "DokArkiv":7,32;8 .\arkiv_7.d4 f 32768

d "DokArkiv":7,32;8 .\arkiv_7.d5 f 32768

d "DokArkiv":7,32;8 .\arkiv_7.d6 f 32768

d "DokArkiv":7,32;8 .\arkiv_7.d7

#

d "DokArkiv_indx":8,1;8 .\arkiv_8.d1 f 32768

d "DokArkiv_indx":8,1;8 .\arkiv_8.d2 f 32768

d "DokArkiv_indx":8,1;8 .\arkiv_8.d3 f 32768

d "DokArkiv_indx":8,1;8 .\arkiv_8.d4

#

d "Dokument":9,1;512 .\arkiv_9.d1 f 2097088

d "Dokument":9,1;512 .\arkiv_9.d2 f 2097088

:

:

d "Dokument":9,1;512 .\arkiv_9.d46

a e:\database\AI\arkiv.a1 f 524288

a e:\database\AI\arkiv.a2 f 524288

a e:\database\AI\arkiv.a3 f 524288

a e:\database\AI\arkiv.a4 f 524288

This thread is closed