OK, I am totally confused. Didn't this question already get posted with replies from John G and Celvin? It seems to have disappeared completely now.
For what it's worth, John directed you to this thread: Database Restructure ignoring NUMBLOCKSTOEXTEND Configuration Setting
I would try setting NUMBLOCKSTOEXTEND to 1 and seeing what size you get after your daily build. Not saying this a good default in general (I still don't understand when that setting is supposed to be useful) but would demonstrate whether or not this is the root cause.
Am Sorry John, Tim, I saw John's reply on the other post only after I posted this one. Will test with the recommended settings. Thanks to all of you! Relieved that there are many posts regarding this. I did search but could not arrive at the exact threads. Thanks again.
Tim/John I did test with the recommended setting
NUMBLOCKSTOEXTEND appname dbname 1
with no reduction in disk space. I did restart essbase after making changes to essbase.cfg
Even after following the thread on network54 the OP there did not arrive at a solution. Will be logging it with oracle but is there something else you recommend.
Message was edited by: Teddd
Hi Ted - that's interesting. Could you post the following stats (before the restructure, i.e. at the point at which you are seeing the discrepancy) for the same cube on both boxes?
Total block count
Average Clustering Ratio (although technically I don't think this should matter in isolation)
Total .pag file size
My thought is that it's one of three things - a lot of genuinely unused space in the .pag files (caused by a behaviour change like NUMBLOCKSTOEXTEND), much increased fragmentation, or a calc is now creating a lot of empty blocks which are cleared by the explicit restructure.
I'm not 100% clear, is this a full reset / load / calc every day? And whatever the process, at exactly which point do you notice the size difference?
here are the statistics
Block Size 95648B 95648B
Number Of Existing Blocks: 190496 112535
Potential Number of Blocks: 6944160 6944160
Avg Fragm Quotient:(Using ESSCMD): 28.59524 32.16586
Average Clustering Ratio: 0.38 0.44
Compression Ratio: 0.01 0.02
Block Density: 0.61 1.23
Total Page Size 1.37gb 9.36gb
And to answer your last question, yes we clear and load data everyday, this does not happen with just one application, but all the BSO applications we have. The above stats are from one such cube. The exact point is when we are loading the data(import database appname.dbname data is getting executed).
Thanks for looking into it.
Hi Ted. Thanks. Surprised you have an almost 2x discrepancy in number of existing blocks, compression ratio and block density (although those last two always go together with bitmap compression). Are these cubes supposed to be identical in terms of data and calculation?
Still, doesn't explain what you're seeing. If those stats are to be believed (the fragmentation / compression stats are reputedly sampled) I can't see any reason for the total .pag size to be so much larger on the new cube - except if there's a lot more unused space in them. Which sort of brings us back to NUMBLOCKSTOEXTEND, which you've already eliminated.
Very confusing. Not helping you much either.
They both do have the same data. Some of the people on the business side know that we just upgraded and so they are actually looking at reports from both the versions for any discrepancies and they think the upgrade went great!! They have no complaints whatsoever! I created a report and I couldn't finds any differences either with the data. I am logging this with Oracle.
Why are num exist blocks different????
Dbl chk dese sparse export & reload see if shrinks
it standart issue (in newest essbase ) if u startup from binary backup
1) stop application
2) check compression setting in essbase properties
3) start essbase
4) restructure application