Table 2. Performance Improvements for Underlying Database Servers
Action
|
Description
|
Update Optimizer Statistics
|
All the database servers which Siebel Analytics supports store statistics used by their optimizers. If these statistics are stale, the optimizer can choose a suboptimal plan.
|
Reorganize Indexes
|
When tables in a database are modified by insert, delete, or load operations, the indexes on the tables become less efficient. The indexes on tables subject to updates should be periodically reorganized.
|
Drop Indexes
|
Indexes occupy storage space, provide alternate access paths which must be evaluated by the optimizers, must be maintained, and degrade update operations. Periodic surveys should be made to determine whether an index is being referenced by user queries. If not, remove the index.
|
Create Indexes
|
After query caching and aggregates, indexes are the most effective way to improve query performance. Create indexes to speed-up access times and improve join operations.
Speedup access times by:
- Creating B-tree indexes on dimension table columns that have a large number of distinct values.
- Creating Bitmap indexes on dimension columns that have few distinct values. These are most effective when queries constrain multiple columns which have bitmap indexes (Oracle only).
Speedup join operations 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 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). (Oracle only).
|
Increase Parallel Query
|
If you have sufficient computing resources, configure the database server for an optimal degree of parallel processing. Given sufficient resources, this can greatly improve query response time.
|
Manage I/O Traffic
|
Manage the input and output accesses to disk storage by striping the disk storage. The best and simplest action is to install disk storage arrays (RAID), the second best is to stripe volumes using a Logical Volume Manager.
|
Review Configuration
|
Database vendor all have suggested configurations for specific kinds of workloads. Evaluate your workload, and configure the database server accordingly.
- Oracle
Oracle9i Data warehousing Guide and the Oracle9i Database Performance Guide and Reference
- IBM
DB2 Administration Guide V7.1, Volume 3: Performance.
- Microsoft SQL Server
RDBMS Performance Tuning Guide for Data Warehousing.
|
