Fragmentation occurs in a dynamic memory allocation system when many of the free
blocks are too small to satisfy any request.
“Fragmentation is unused disk space”.
1) When insert, update and delete operation perform
2) Database structure is not proper
3) When database has Dynamic Calc and store members
Example:- You can’t store more than 10kb file in to fragmentized space, because no 10kb free block is available in the fragmentized disk. After defragmentation you have enough space to store then 10kb file size.
Measuring Fragmentation: - There are two ways to measure fragmentation
a. Using the Average Fragmentation Quotient
b. Using the Average Clustering Ratio
Steps for Measuring Fragmentation:-
Step1- Login to Essbase Insert Host Name, User Name and Password
Step2- Select the application
Step3- Get data base stats through GETDBSTATS command
Through GETDBSTATS command we can get average fragmentation quotient.
Reduce the fragmentation the performance of cube will increase.
Average Clustering Ratio Fragmentation
a comprehensive discussion of optimizing data load by grouping sparse members, see
Grouping Sparse Member Combinations. Then load data so that fragmentation will be less.
with CLEARDATA, and reload the export file.
Defragmentation occurs when Essbase queues blocks into the cache for calculation purposes, many passes of a database will result in the cache being filled and emptied depending on what is being calculated. This results in defragmentation as the logical storage order of these blocks is changed by the movement in and out of the cache.
This can sometimes, not always, result in performance degradation (as Essbase seeks out the correct blocks either from the cache or form the index), in the database stats an Average Clustering Ratio of 1 shows no defragmentation. If this value falls to below 0.1 then it would be deemed defragmented. Usually it sits between 1 and 0.5.
Step3:- Once data exported successfully, clear all data of database.
Step4:- Now Application has blank Database, Right click on Database and select “Load Data”.
Step5:- Click on “Find Data File” and browse data file then click ok
Load Data in to Application
Data loaded success fully
Now this application database is defragmented the performance of database will be better than the previous.
Reducing Database Fragmentation
Another way to reducing fragmentation is force a dense restructure of the database.
How to perform a full database restructure?
Step1:- Right click on database and select “Restructure”
This action removes the fragmentation from the database.
Removing Security File Fragmentation
Now if there is update on security files, such as the addition or removal of users, groups, applications, or database, can gradually create security file fragmentation. The same way if fragmentation exists in any of these file the performance of that particular file will decrease.
Automation Database Fragmentation
We can write MAXL script for automation off all tasks.
The script is divided in three parts.
1) Exporting Data
2) Clearing Data
3) Importing Data
spool on to 'H:\Database\Bisplog.txt';
login 'admin' 'password' on 'orgbisp';
alter system load application 'Bisp';
alter application 'Bisp' disable connects;
export database 'Bisp'.'BispBD' level0 data to data_file 'H:\Database\BispBD.txt'; execute calculation 'Bisp'.'BispBD'.'Clear';
import database 'Bisp'.'BispBD' data from data_file 'H:\Database\BispBD.txt'on error write to 'H:\Database\error.txt‘;
This is the Calculation script for clearing data.
Automation Database Fragmentation
When you Execute script two text file will be generated first one is database backup file or Exported file of database.
This is data base log file you can see what actions are performed. If any error comes during automation error message will log into this file.