[MicroStrategy][Oracle] best practice on integration of Oracle in MicroStrategy BI platform

Best Practices to integrate Oracle in MicroStrategy (Version 19c with DDA Mode)

Construction Considerations for Project Database Tuning: When initiating a project using MicroStrategy with Oracle as the warehouse (Version 19c in DDA mode), careful construction steps are crucial for optimal performance.

1. 1. VLDB True Temporary Table vs. Derived Table:

   • The default Intermediate Table Type is True temporary table, generating multi-pass queries.
   • Derived table and Common table expression produce single-pass SQL queries.
   • Experiment with different Intermediate Table Type settings, especially for Derived table and Common table expression, to find the optimal configuration for your specific business scenarios.

2. 2. Review and Set Database Parameters:

   • Collaborate with the project DBA to review and set database parameters according to instance resource quotas.
   • Adjust parameters like SGA_MAX_SIZE and SGA_TARGET based on available memory for improved performance.
   • MicroStrategy's in-house model testing showcased a 30% performance improvement by modifying these parameters.

3. 3. Create Index and Optimize SQL Execution Plan:

   • For traditional databases, a well-optimized query execution plan is crucial for performance.
   • Collect database statistics to inform the query execution plan.
   • Use Oracle AWR reports or database monitoring to identify low-performance queries.
   • Analyze low-performance queries and intervene by adding indexes or SQL hints to enhance the execution plan.
   • Modify the SQL execution plan to leverage a better SQL profile.

4. Example of SQL Tuning Adviser:

   • Presented an SQL statement with a 54s execution time before tuning.
   • Executed the SQL Tuning Adviser, resulting in an improved execution time of 2.37s.
   • Demonstrated the acceptance of a recommended new SQL profile to enhance the execution plan.

Additional Recommendations for Performance Enhancement:

1. 1. Table Compression:

   • For large data sizes (>80M) and less transactional tables (e.g., history tables), consider compressing them to free up space on data blocks and reduce table scan size during queries.

2. 2. Table Partitioning:

   • For data models involving querying on parts of large tables, consider using table partitioning based on specific columns (e.g., fiscal year/month or product categories) to decrease table scan costs.

3. 3. Enable Result Cache:

   • Oracle result cache stores query results in memory for reuse.
   • Enable result cache through VLDB settings or the driver connection to improve performance for queries with fixed result returns.
4. 

This comprehensive guide provides practical steps and considerations for tuning performance in MicroStrategy using Oracle 19c in DDA mode.

Regularly refer to MicroStrategy's documentation for the latest updates and version-specific recommendations.

LINK: https://community.microstrategy.com/s/article/KB484932-Best-Practice-for-Performance-Tuning-using-Oracle?language=en_US