Question Fixed Extents

Rob Fitzpatrick said:
One other point that's always worth mentioning in a D&L, I can't remember if it's already been said; take a tabanalys or dbanalys from your source DB after it is shut down and from your target after the load/rebuild and compare the record counts in all tables to ensure they match exactly. Do this before bringing up the target as new production and committing transactions to it.
Click to expand...
Sure Rob, will make a note of it on my check list. Thanks a lot for your advise.
 
Cringer said:
This is really important. 11.2.1 had a bug in it whereby if you did a multi-threaded binary dump and the table didn't have the indexes necessary for multi-threading it told you it started a single threaded dump but in fact it dumped nothing. We found this out the hard way!
Click to expand...
That's weird - I will keep a tab on it. Thanks Cringer.
 
catch.saravana said:
Just wanted to make sure the extends doesn't get recalculated during the second time load (I understand there may still be few) as its pre-grown.
Click to expand...
I'm not sure what you mean by that.

The extents that make up a storage area form a logical container for the storage objects assigned to that area. As data is added to objects, new clusters are formatted and add to the cluster chain of the object and (if necessary) the high water mark of the area is raised. Think of the HWM as a marker or offset within the area that says "there is no data beyond this point".

Once you pre-grow the database and then procopy empty8 overtop of it, in addition to wiping out the schema it also resets the HWMs in the storage areas so they are logically empty. During the second load, the objects will start with small cluster chains that will grow as clusters of blocks are formatted into RM blocks (in table areas) or IX blocks (in index areas), and the area HWMs will move accordingly. Extends happen only in variable extents, and only when there are no more free blocks available in the file for space allocation and the storage engine needs to request more disk space to increase the size of the file. This happens 512 or 64 blocks at a time, depending on the area blocks/cluster setting.

So you should only see extends when an extent file needs to grow physically.

This is my understanding anyway (and obviously I'm simplifying quite a bit). Someone else please chime in if I have it wrong.
 
Rob Fitzpatrick said:
Extends happen only in variable extents, and only when there are no more free blocks available in the file for space allocation and the storage engine needs to request more disk space to increase the size of the file.
Click to expand...
Sorry for not being clear.

My assumption;
the whole point of doing this exercise is to have a pre-grown db that is logically empty. Let's consider an example, we have an AREA with variable extent that will have a table which is 30 GB (121229599 Records). As part of our exercise;
1. Let's say I ran the ST file and got this area created
2. Execute the DF and got the table created in this area
3. Load the data twice to make the table size close to 50 to 60 GB (though its not advisable, let's think i am doing this for just an understanding purpose)
4. I am applying the empty8 on to the db (which will wipe the schema and resets HWM). My assumption is the file size will still be 50 to 60 GB.
5. Load the DF and get the table created again on this area
6. Load the data file (once this time). HWM will move accordingly. BUT free blocks will be available till 50 to 60 GB and only when it reaches this limit the storage engine is going to request more disk space. This way performance will increase as we are loading the data to a pre-grown db which is logically empty.

I have highlighted my assumptions. Is my understanding right? If not please let me know what is helping out in gaining the performance in this exercise.
 
You must log in or register to reply here.
Back
Top