Precautions for Oracle Data Warehouse deployment (OLAP)

After the Oracle database has been upgraded to 11 GB recently, some problems have occurred. I began to find some things that need to be summarized. Every time I thought: next time, when I build my own data warehouse, be sure to pay attention to these details and do the work well at the initial stage of warehouse creation.

1. redo log Design
1) if it can be put separately, redo and data files can be grouped separately for striping. Physically separated.

2) If redolog can be placed separately, do not set it to too large. Up to MB, because the log is too large, it may lead to a long time for instance recovery. In addition, in extreme bad circumstances, if the instance is down again during the data recovery process, for example, power loss. Then you are miserable. In short, it is not safe to store so much data in logs, so you can rest assured in datafile.

3) if you are as miserable as you are, redo and datafile are in the same disk group, because the storage bottom layer is directly divided into a large pool (based on cost and speed considerations ), you can increase the number of logs (2 GB at present) and increase the number of groups (20 groups at present). If the number of logs is insufficient, add more groups, such as 30. In addition, ASM basically cannot improve IO in the face of such a large pool, because you cannot solve the problem of IO competition between redo and datafile.

2. undo Policy
1) undo_retention Design

Generally, you can set it for three hours to ensure the system is available. Of course, the undo tablespace should be large enough.

Well, if you say it cannot be quantified, I will give you a reference value.

For databases with 3 TB data, set the undo tablespace to 300 GB and the undo_retention time to 10800. The default unit is seconds, that is, 3 hours.

2) guarantee Parameter Selection

I think you should not enable this parameter unless you fully understand your business system and database status. It may cause an exception or disaster.

If the above conditions are not true, you can try to use this parameter to prevent the appearance of the ORA-01555.

3. Enable asynchronous IO at DB and OS levels
Reference: and

4. Others. Add them when you think of them.

5. Different database designs of OLTP and DSS

Oltp database dss database
Oltp = online transaction processing dss = data warehousing
Online Transaction Processing Data Warehouse
For example, flight booking, online transactions, bbs, and other such information inquiry systems for various resources
A large number of online users and few dml operations
A large number of index-based queries a large number of full-Table scans
Index with B-tree and reverse key, regular index reconstruction with bitmap Index
A large number of small rollback segments are required.
Do not use distributed queries
Data Object Storage parameter pctfree 20 or higher Data Object Storage parameter pctfree 0
Sharing Program Code and various variable constant character variables and clues
Start the multi-clue service to use big data blocks, db_file_mutiblock_read_count
Use a large log file and a small log file
Add sort_area_size to open multiple response ports in listener