Thursday 20 October 2011

Eliminating Fragmentation


What causes database fragmentation?
Essbase database fragmentation occurs when blocks are "re-written" within the database. Not all "re-writes" to a cube will cause fragmentation. Listed below are some behaviors that can cause Essbase fragmentation.

In some cases, fragmentation cannot be reduced completely. Fragmentation is likely to occur with the following:
• Read/write databases that users are constantly updating with data
• Databases that execute calculations around the clock
• Databases that frequently update and recalculate dense members
• Data loads that are poorly designed
• Databases that contain a significant number of Dynamic Calc and Store members
• Databases that use an isolation level of uncommitted access with commit block set to zero


/***********************************
* defrag.mxl - to force dense restructure of a BSO cube
* Input
* $1 - User Name
* $2 - Password
* $3 - Server
* $4 - Application name in form of APPNAME.DBNAME
* $5 - Spool log file name
**************************************/

spool off;

login $1 identified by $2 on $3 ;
spool on to $5 ;
set timestamp on ;
set column_width 40 ;
alter database $4 force restructure ;
logout ;

spool off ;
exit ;

No comments:

Post a Comment