Question Fixed Extents

[catch.saravana]

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.

Sure Rob, will make a note of it on my check list. Thanks a lot for your advise.

 

[catch.saravana]

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!

That's weird - I will keep a tab on it. Thanks Cringer.

 

[Rob Fitzpatrick]

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.

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.

 

[catch.saravana]

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.

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.

 

[Rob Fitzpatrick]

This way performance will increase

Performance will be improved when compared with a load into small variable extents that have to be repeatedly extended. The stated assumptions are correct.

 

[catch.saravana]

@Rob Fitzpatrick - Thanks for confirming my assumptions.