These guidelines help you set up the data warehouse physical database and explain why a separate database is necessary for Oracle BI Applications.
These guidelines help you set up the data warehouse physical database for performance and growth.
Allocate around 50 to 70 percent of the total available server memory to the database, assuming no other application is running on the same server.
At a minimum, separate the data and index tablespaces. Create more tablespaces to separate heavily used tables and their indexes.
Oracle recommends using 8k block size for Oracle warehouses. For more information about block size and Oracle databases, see the Oracle Database 11g Documentation Library on Oracle Technology Network.
If you are using multiple disk storage systems, stripe the tablespace containers and files across as many disks as possible.
Raw devices for tablespaces provide better performance as compared to cooked file systems.
RAID-5 is known to give a good balance of performance and availability.
Although it is technically possible to put the Oracle Business Analytics Warehouse in the same database as the transactional database, it is not recommended for performance reasons. The transactional database is structured as an online transaction processing (OLTP) database, whereas the Oracle Business Analytics Warehouse is structured as an online analytical processing (OLAP) database, each optimized for its own purpose.
The reasons for not combining the two databases are:
ETL is configured to maximize hardware resources; and, therefore, the warehouse should not share any resources with any other projects.
The analytical queries interfere with normal use of the transactional database, which is entering and managing individual transactions.
The data in a transactional database is normalized for update efficiency. Transactional queries join several normalized tables and will be slow (as opposed to pre-joined, de-normalized analytical tables).
Historical data cannot be purged from a transactional database, even if not required for current transaction processing, because you need it for analysis. (By contrast, the analytical database is the warehouse for historical as well as current data.) This causes the transactional database to further slow down.
Transactional databases are tuned for one specific application, and it is not productive to use these separate transactional databases for analytical queries that usually span more than one functional application.
The analytical database can be specifically tuned for the analytical queries and Extract-Transform-Load (ETL) processing. These are quite different from transactional database requirements.