Oracle Data Warehouse Tuning – 25 Tips


Originally Posted by: Ashok Thiyagarajan 8/16/2009

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 up to 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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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