There are couple of way to improve the performance of OBIEE request based on the underlying database servers and its object configurations :
Reorganizing Indexes : Multiple Upsert (Update/Insert) and Desert (Delete / Insert) operations on database object invalidate the existing index and index need to be rebuild after successive load to ensure that the query hitting the index should return optimal performance .
Drop Index : Since Index occupies the database storages ,so any unnecessary index will slow down the query performance and degrade update operations .
Creating B-Tree Index : Create indexes to speed-up access times and improve join operations . Create B-tree indexes on dimension table columns that have a large number of distinct values. This will speed-up access time .Typically this kind of index needed on top of which OBIEE prompt has been built . However join operations have been speeded up by creating multicolumn B-tree indexes on foreign key reference columns of fact tables, and multicolumn B-tree indexes including the primary key and selected columns from the dimension tables. Indexes should be designed sensibly and ideally should not contain more than 5 columns.
Creating Bitmap Index : Creating Bitmap indexes on dimension columns that have few distinct values will speed up the access times . These are most effective when queries constrain multiple columns which have bitmap indexes . However the join operations could be accelerated by Creating bitmap indexes on foreign key reference columns where the index value has a low degree of selectivity (more than 3 – 5% of the table values on average per value) .
Degree of Parallelism : If there is sufficient computing resources, database server need to be configured such that optimal degree of parallel processing could be achieved .Given sufficient resources, this can greatly improve query response time.
Disk I/O Traffic : Considering the network traffic and bandwidth the optimal performance could be achieved if the Disk has been put into RAID , LVM and the database also put into Logical Cluster like Real Application Clustering (RAC) from Oracle front
Posts
