I was at a client and needed to make a copy of their 50 gig production cube onto their test system to do some work with it. I exported the data, copied the outline over to test and re-imported the data. I looked at the file system during the load to see how far along and was surprise when the cube stopped loading the .Dat file was only 9 gig. I ran the materialization of aggregations using a saved aggregation from query tracking and the cube grew to 25 gig. About half of the size of the production cube.
There are a couple of things to know.
- During the year, the client deletes some data out of the cube by deleting the members (old years)
- The cube contains no slices
- The cube gets an incremental update multiple times a month by clearing out the current Month/Year and reloading the data
- The cube has been in production for about a year and the outline gets new members added on a regular basis.
8 comments:
Glenn what about about the ESSCMDQ utility - is its use restricted to structure fragmentation or may it be used for data fagmentation as well?
http://www.oracle.com/technetwork/middleware/bi-foundation/esscmdq-sampleapps-093105.html
Login "svr" "admin" "password";
Select "app" "db";
Openotl "2" 1 "app" "db" "db" "y" "y" 0;
Writeotl 1 "2" 1 "app" "db" "db";
Restructotl 1;
CloseOtl 1;
Unlockobj 1 "app" "db" "db";
LogOut;
I've not checked it for data restructure. I have used the utility for outline compaction. I wrote about it a few months ago
Hi Glenn:
Thanks for the post!
I am curious how does your client incrementally update multiple times a month by clearing out the current Month/Year and reloading the data without using slice in an ASO cube? I thought slice is the way to do it?
Thanks
Sherry
Sherry,
I have them using a MDX statement inside of MAxL to do a physical clear of the data. It is slower than doing a logical clear, but avoids some of the issues of a logical clear.
Hi Glenn,
Can throw some light on how to determine whether a ASO cube is fragmented and needs a defrag?
Regards,
Gyanesh
It this client's case, they use a MaxL statement to clear data from their cube (not slices) and reload data multiple times a month. We think this is causing the fragmentation (but I can't prove it) is is also possible that merging slices could cause the issue.
As for outline fragmentation, changes to the outline itself cause that and ASO outlines continue to grow with every update.
How much did the fragmentation impact retrieval performance?
Glenn
Is it this issue available now ?
No new suggestions ?
Post a Comment