Oracle Data Warehouse Tuning - 25 Tips

Things to know about Tuning Oracle Data Warehouse Databases -
-----------------------------------------------------------

1.Dimensions and Fact Tables
2.De-Normalization
3.RAID Levels (Design) - specific to DW applications
4.Big Tablespaces - Where single datafile can grow upto 128TB
5.Block size - 16K or 32K - Depends on the Operating system
6.Partitioning Options - which needs License from Oracle
Range,Hash,List and Composite partitions
7.Partition Operations - DDL specific
8.Bitmap Indexes - Helps a lot
9.Functional base indexes
10.Data Compresssion
11.Direct Data load operations
12.Oracle Joins - Nested , Merge and Hash joins
13.SQL Tuning - Need Good understanding of SQL Tuning
14.Parallel Operations
15.Materilaized Views
16.Dimensions
17.Query Rewrite option
18.AWR reports
19.ADDM
20.SQL Tuning Advisor
21.Reoranization
22.CBO Statistics
23.SQL Hints - This helps a lot in real time
24.Ofcourse some init.ora parameters like CBO related , MTS etc
25.IOT

RAC Features for Data Warehouse Databases
---------------------------------------

1.Automatic Workload Management
2.Parallel Query Options
3.Parallel Instance Groups
4.DOP
5.Be aware of the Inter connect traffic
6.Services
7.Partitions specific to RAC applications
8.SQL tuning
9.Dedicated Temp tablespaces
10.TAF