Oracle Data Warehouse Tuning – 25 Tips

Things to know about Tuning Oracle Data Warehouse Databases

1.Dimensions and Fact Tables
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
17.Query Rewrite option
18.AWR reports
20.SQL Tuning Advisor
22.CBO Statistics
23.SQL Hints – This helps a lot in real time
24.Ofcourse some init.ora parameters like CBO related , MTS etc

RAC Features for Data Warehouse Databases

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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s