Operational Data Store (ODS) Best Practices on Exadata

Base on my consulting experience, I like to share some best practices running Operational Data Store on Exadata: –

  1. Use direct path load as much as possible.
  2. Maximize parallel data load using different techniques ( Partitioning, Parallel DML, etc.)
  3. User parallel degree based on you application needs, look into AUTO DOP feature.
  4. Make sure to use all Exadata Features (smart scan, offloading, and storage indexes) to get extreme performance.
  5. Use Histograms carefully, they can have an adverse impact on your application.
  6. Test optimizer mode for your application before you start developing you ODS application.
  7. Data compression is very important. Not only it can help you with query performance, it can also save some storage. Watch your CPU utilization.
  8. If you are planning to consolidate many data sources to a single ODS, make sure to implement Exadata consolidation best practices.
  9. Use proper data models 3NF or Star Schema based on your Needs. Most ODS are 3NF.
  10. Make sure to collect stats after every ETL loads, use incremental for huge tables.
  11. User partition smartly to achieve partition-wise join or Star Transformation.
  12. Carefully analyze your partition needs make sure it will help yours with ETL load. Just don’t create partitions randomly, number of partition matters
  13. If you carefully, Range, hash and interval partitioning will help you achieve extreme performance with Exadata.
  14. If you are planning to introduce partitioning to an existing application, please test thoroughly.
  15. Avoid Global Indexes, use local indexes instead.
  16. Test thoroughly before making any index invisible. It can have an adverse impact on other queries.
  17. Data load should be fast as possible with minimum impact on users, end user comes first.
  18. Make sure to achieve partition-wise join or Star Schema transformation.
  19. User summary tables for as needed.
  20. If you are planning have your ODS/Staging/DW within same Exadata machine, implement IORM (Exadata IO Resource Manager)

Implementing ILM for Data Warehouse Database

Information Lifecycle Management (ILM) is the practice of applying policies for the effective management of information throughout its useful life. Implementing ILM become more beneficial in data warehouse environments, where we are required to keep historical data. Data Warehouse databases can grow exponentially and It makes complete sense to move data to different storage tier though its life cycle to save cost and availability.

Benefits of implementing ILM 

  1. Works at the database level with no application changes
  2. Reduces disk space requirements for all types of data
  3. Allows you to automate the movement of data to lower-cost storage based on activity patterns
  4. Lowers query times by keeping only active data on high performance storage
  5. Increases availability of critical information on the most reliable storage

How Implement ILM using Oracle database features  

  1. Use Oracle ADO to create policies for data compression and data movement between different compression level and different tiers of storage.
  2. Use Oracle 12c feature  Heat Map to track data usage
  3. Use Table Partitioning to move historical data to low performance storage tier
  4. Use Advance / HCC compression to compress historical data to same or low cost storage tier

Note : Oracle 12c introduce 2 new feature call Automatic Data Optimization (ADO)  and Heat Map, Both features are part of Oracle Advance Compression Option.

OBIEE Performance Tuning With Exadata

Database Level (Exadata)  

  1. Use Partition-Wise Join or Star transformation
  2. Use Partitioning
  3. Collect stats Regularly
  4. Make sure to use Smart Scan (Exadata )
  5. Avoid using view if there are concurrent users
  6. Set Parallel MAX and MIN parameter properly
  7. Set Optimizer Mode properly
  8. Use Histograms Carefully
  9. Use summary tables when needed
  10. Enable result cache
  11. Use Bitmap Indexes
  12. Cache tables ( Exadata )
  13. Don’t restart Database to often
  14. Enable Parallelism according to your needs
  15. Enable compression (Exadata )



OBIEE Level:  

  1. Remove Snowflakes
  2. Avoid Circular join by using Aliases in Physical Layer
  3. Don’t add unnecessary column to data model
  4. Implement Cache Management
  5. Turn off logging
  6. Add connection pools
  7. Set query limits
  8. Push heavy load to database layer
  9. Limit number of initialization blocks
  10. Use Database hints




Using OBIEE with Oracle RAC Database

What is Oracle RAC

Oracle RAC is a cluster technology which provide database high availability. It’s a hardware configuration where multiple interconnected servers appear as one to end users and applications, Oracle RAC is a complex topic, I will try to keep it simple and focus on OBIEE connection, query execution and High availability.


Query Execution in RAC Database

In this section,  I will explain how Oracle RAC handle queries from OBIEE. If query is running in NOPARALLEL mode, it will only run on one of Oracle RAC instance. But If query is running in parallel mode, it can run 1 or multiple instances. It depend on database parameter setting called PARALLEL_FORCE_LOCAL (true or false).

With 11gr2 Oracle introduces a new feature called In-Memory Parallel Execution, which leverages shared memory cache (SGA) . Oracle uses the aggregated memory cache of all the servers in a Real Application Clusters (RAC) environment to deterministically cache objects distributed across the memory of all nodes. This ensures that subsequent parallel queries will read data out of cache from all nodes that can speed up processing times enormously instead of reading data from storage.

High Availability with Oracle RAC 

It is best practice to use services to connect Oracle RAC databases. Services enable you to group database workloads and route work to the optimal instances that are assigned to offer the service. Services make the work load more manageable, measurable, tunable and recoverable. You services to connect one or more instances to for load balancing. In a nut shell Oracle RAC will distribute OBIEE connection evenly to multiple instances. you can configure services to run on multiple node simultaneously or one instance at time (failover to second instance if node fails).

Example: Creating Services

srvctl add service  -s OBIEE_CONNECTION –a Node1 –r Node2  -d RACDB

srvctl add service  -s OBIEE_CONNECTION –a Node1, Node2      -d RACDB

Example: Connection String