Configure Oracle OBIEE with Standby Database (Active Data Guard)

Because an Oracle Standby database (Active Data Guard) is essentially a read-only database, it can be used as a Business intelligence query server, relieving the workload of the primary database and improving query performance.

How it works

You would think since Oracle Standby database is read only database and Oracle OBIEE only generate sql queries, it should work with default configuration. But it’s not that simple , OBIEE generates some write operations and they need to route to Primary database. Following are the example of OBIEE write operations.

  1. Oracle BI Scheduler job and instance data
  2. Temporary tables for performance enhancements
  3. Writeback scripts for aggregate persistence
  4. Usage tracking data, if usage tracking has been enabled
  5. Event polling table data, if event polling tables are being used

Configuration Steps:

  • Create a single database object for the standby database configuration, with temporary table creation disabled.

Capture database object

  • Configure two connection pools for the database object:

A read-only connection pool that points to the standby database

stadnbyconnection

A second connection pool that points to the primary database for write Operations

primaryconnection

  • Update any connection scripts that write to the database so that they explicitly specify the primary database connection pool.
  • If usage tracking has been enabled, update the usage tracking configuration to use the primary connection.
  • If event polling tables are being used, update the event polling database configuration to use the primary connection.
  • Ensure that Oracle BI Scheduler is not configured to use any standby sources.

Gathering Optimizer Statistics on Exadata

Because the cost-based approach relies on statistics, you should generate statistics for all tables and clusters and all indexes accessed by your SQL statements before using the cost-based approach. If the size and data distribution of the tables change frequently, then regenerate these statistics regularly to ensure the statistics accurately represent the data in the tables.

Collecting optimizer statistics on Exadata is not any different than other systems. I usually recommend my clients for migrate existing gather stats methods from old system. In case you were not collecting stats on existing system , you should gather should at least following types of optimizer statistics.

  • Table stats
  • Index stats
  • System stats

You can gather table / index stats using following procedure at schema level :

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(‘HR’,DBMS_STATS.AUTO_SAMPLE_SIZE);

Gathering Exadata specific system statistics ensure the optimizer is aware of Exadata scan speed. Accurately accounting for the speed of scan operations will ensure the Optimizer chooses an optimal execution plan in a Exadata environment. Lack of Exadata specific stats can lead to less performant optimizer plans.
The following command gathers Exadata specific system statistics

exec dbms_stats.gather_system_stats(‘EXADATA’);

Note this best practice is not a general recommendation to gather system statistics in Exadata mode for all Exadata environments. For existing customers who have acceptable performance with their current execution plans, do not gather system statistics in Exadata mode.

For existing customers whose cardinality estimates are accurate, but suffer from the optimizer over estimating the cost of a full table scan where the full scan performs better, then gather system statistics in Exadata mode.

For new applications where the impact can be assessed from the beginning, and dealt with easily if there is a problem, gather system statistics in Exadata mode.

 

Using Oracle In-memory Parallel Execution with Exadata

Traditional parallel query(PQ) execution, it adopted Direct Path Read to load data which bypassed the database buffer cache(buffer cache) and load directly from the disks. In-Memory parallel execution takes advantage of this large aggregated database buffer cache. By having parallel execution servers access objects using the database buffer cache, they can scan data at least ten times faster than they can on disk. It allows you to cache your hottest tables across buffer caches of multiple RAC nodes

What about Exadata ? In-memory PQ is a great option, only if I/O is your bottleneck. With Exadata you get Terabytes falsh cache and Flash I/O , In-memory PQ is probably not a got idea.

Drop all Indexes on Exadata ?

As I have been a part of many detailed conversations with Oracle Experts regarding “Use of indexes on Exadata”, I decided to share my thoughts and experience on this topic.

I have been working with Exadata since 2011 and have been a part of many implementations and POC’s. As per my experience, Exadata works better without indexes but getting rid of all indexes is not a practical approach. I have implemented / migrated different types of applications (OLTP / OLAP) to Exadata and there were some cases where I was not allowed to make any application changes.  Application changes like dropping an index, partitioning will require testing and will not be easy as it sounds. if you have worked with applications like EBS and SAP, you understand how difficult it will be to make any changes to the environment.

Personally I recommend following balance approach when it comes to use of indexes on Exadata.

  • Don’t drop all the indexes
  • Keep primary key / unique indexes
  • You can drop bit map indexes
  • Use invisible index options when possible
  • Avoid indexes using SQL HINTS
  • Drop and rebuild indexes during ETL load

Forcing Smart Scans on Exadata

Let start with listing Smart Scan requirements here:

  1. Full table scan / Full Index Scan
  2. Direct path read

As you can see that “direct path read” is required for Smart Scan.  Parallel queries automatically use direct path read and serial queries may use direct path read. So if you don’t see Smart Scan happening with “Full Table / Index Scan”, its probably serial query not doing “Direct Path Read”.

Direct Path Read

For serial queries Oracle dynamically decides to use “Direct Path Read” base on following parameters

  • _serial_direct_read = auto
  • _direct_read_decision_statistics_driven = TRUE
  • _small_table_threshold = XXX

 

As you can see that we can force “Direct Path Read” by setting “_serial_direct_read” to “ALWAYS”. It’s a hidden parameter and if you are not is comfortable using it in production, I will suggest making your queries run in parallel and keep db cache size small.

 

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