The default value for NUMBLOCKSTOEXTEND is 2048. I've set it to 1 for the purpose of testing how the cube will respond to various operations.
I loaded data to one block in my cube and the PAG file size is just a little larger than the 1 block size. However, when I restructure the database, the PAG file size grows to accommodate 2048 blocks. So it seems that the NUMBLOCKSTOEXTEND is being ignored by the explicit dense restructure.
Can anyone confirm that this is expected behavior and whether or not it's intended to be so?
I'm using version 184.108.40.206
Interesting, thanks for posting that result. Whether it's intentional or not... ...well, I guess you could try to argue that this contradicts the documentation. I think the Technical Reference entry and a mention in the 220.127.116.11 New Features guide (which doesn't say anything not contained in the Technical Reference) are all there is for documentation on this one! If you Google the setting you'll see it has been discussed very little. I also searched support.oracle.com and came up with nothing.
I think the clue is in the Tech reference there is a note at the bottom that says
After setting NUMBLOCKSTOEXTEND to a value higher than the default value, there is an increase in the amount of disk space pre-allocated for page files."
The key phrase is "setting NUMBLOCKSTOEXTEND to a value higher than the default value"
I'm guessing that 2048 is both the sefault AND the minimum size
In 18.104.22.168 the note changes again "Note:Upon first upgrading to this release, there is an increase in the amount of disk space pre-allocated for page files unless you set NUMBLOCKSTOEXTEND to 1."
Maybe the setting the value to 1 is not available in 22.214.171.124 and is new in 126.96.36.199
Hmmm... ...the OP reported that setting to 1 produces expected results when loading (in 188.8.131.52) but not when restructuring. So load one block with NUMBLOCKSTOEXTEND 1 and you get a .pag file exactly the size of one block, but force a dense restructure on that single block and the .pag file immediately inflates to the size of 2048 blocks. I think it's that discrepancy that bothering him/her.
So I ran two more tests. The first one I used a value of 2 for NUMBLOCKSTOEXTEND. I then loaded data into 1 block and the PAG file size was 2 blocks. I restructured and we were back up to 2048 blocks.
Then I ran a test using a value of 1024. I loaded data into 1 block and the PAG file size was 1024 blocks. I restructured and we were back up to 2048 blocks.
So it appears that Glenn was correct in his assessment with at least respect to database restructures. However, on data loads, the database grows by NUMBLOCKSTOEXTEND.
Tim is correct: the discrepancy really bothers me.