Oracle Database In-Memory Performance Benefits with MicroStrategy Analytics Platform

1. Oracle Database In-Memory Performance Benefits with MicroStrategy Analytics Platform

2. A Little About Us A global footprint. A proven innovator. A leader in enterprise analytics for 25 years. • A top analytics software vendor by revenue. • Operates in 44 cities in 26 countries worldwide. • Over 4,000 customers across 20 industries. • $576M in revenue in 2013. • $100M+ dedicated annually to R&D. • Innovator in web, mobile, and cloud-based analytics.

3. MicroStrategy and Oracle A longstanding partnership • MicroStrategy is an Oracle Gold certified partner • Significant number of MicroStrategy customers use Oracle as their main database platform • Partner on multiple levels • Participate in all Oracle beta programs • MicroStrategy was invited to participate in the Oracle Database In-Memory Launch

4. From Big Data to Business Value MicroStrategy provides the easiest way to deliver advanced analytics on Big Data to business users Stunning Ease of Use Comprehensive Analytics Optimization Projections Relationship Analysis Benchmarking Trend Analysis Data Summarization Databases Big Data Personal / Departmental Cloud Data Structured Semi-Structured Unstructured Any and All Data

5. Are You Ready for the Age of Analytics? Requirements Driven by new data sources, data volumes are growing exponentially. Leading companies are using advanced analytics to compete and win. Laggards are struggling. Mobile technologyis making analytics ubiquitous and effortless. But it also demands unprecedented sub-second performance and massive user scale. IT can’t keep up as outdated analytics approaches fail. Business users are taking matters into their own hands and disintermediating IT. Data Scale Real-time Analytics Performance

6. Oracle Database 12c In-Memory Option Goals • 100xFaster Queries: Real-Time Analytics • Instantaneous Queries on OLTP Database or Data Warehouse • Faster Mixed Workload OLTP • Transparent: no application changes • Simple to Implement

7. MicroStrategy Analytics + Oracle Database In-Memory A Perfect Match? … we’re going to find out!

8. Joint Benchmarking Hosted in Oracle Solaris Labs under supervision of development teams • MicroStrategy SuperApp • Simulates typical workload • 2TB sized dataset • 14B row fact table • No aggregation • Snowflake schema • 4 dimensions, • 37 attributes • 79 reports and dashboards • 120 different queries • 1 to 149 SQL passes per query (17.5 avg) • Oracle Database In-Memory • SPARC M6 Server • 8 x 3.6GHz 12 core SPARC M6 Processors • 96 Cores, 8 HW threads per core for a total of 768 threads • 4 TB Memory • Solaris 11.1 • Sun ZFS Storage Appliance • 68 x 600GB 15K SAS-2 Drives • 4 x 68GB Write Optimized SSD Drives (Write Cache) • 4 x 477GB Read Optimized SSD Drives (Read Cache) • 2 x 8GBit FC Connection to M6 seconds

9. Interesting Results Implementing Oracle Database In-Memory requires following best practices Tests reveal great performance improvements comparing Oracle Database In-Memory vs. traditional buffer cache BUT: this pales to the performance improvements compared to the first test run which naïvely had not implemented common best practices for analytical workloads

10. Oracle In-Memory Best Practices How to achieve optimal performance for Analytical workloads • Use existing hardware efficiently • Help the Oracle optimizer to make the right decisions • Minimize necessary IO • Give Oracle (In-Memory) sufficient resources • MicroStrategy best practices for Oracle In-Memory

11. Parallel Execution Uses Available HW Resources Analytical queries commonly access large ranges of data Tips: • Enable AutoDoP (*.parallel_degree_policy='AUTO') • Disable Old-style multi-user management (*.parallel_adaptive_multi_user=FALSE) • Avoid starting parallel execution servers dynamically (*.parallel_min_servers=n) Default configuration assumes relatively small data volumes and does not parallelize queries effectively Analytic queries commonly process large data volumes and benefit significantly from parallel processing

12. Verify Parallel Execution Explain plan reports steps executed in parallel (PX for parallel execution)

13. Oracle In-Memory Best Practices How to achieve optimal performance for Analytical workloads • Enable parallel execution • Help the Oracle optimizer to make the right decisions • Minimize necessary IO • Give Oracle (In-Memory) sufficient resources • MicroStrategy best practices for Oracle In-Memory

14. Breakthrough: Dual Format Database Memory Memory BOTHrow and column formats for same table Simultaneously active and transactionally consistent Analytics & reporting use new in-memory Column format OLTP uses proven row format SALES SALES Row Format Column Format

15. Column Store Replaces Analytic Indexes • Fast analytics on any columns • Better for unpredictable analytics • Less tuning & administration • Column Store not persistent so update cost is much lower • OLTP & batch run faster 1 – 3 OLTP Indexes Table In-Memory Column Store

16. Optimizer Decides If Columnar Store is Used Several best practices help the optimizer understand the query • The DBA needs to help the Oracle optimizer to make the right decisions • Use Columnar Store vs. Row based store • Use Bloomfilter, Starschema, Vector optimizations • The good news: existing best practices are sufficient • Simplify: DROP ANALYTIC INDEXES • Use of NOT NULL in DDL • Use PK, FK contrains (“RELY DISABLE NOVALIDATE” is sufficient for this) • Enable the (new in 12.1.0.1) Autostats feature which will capture statistics based on actual queries (will auto tune Oracle over time) • Enable star transformation (*.star_transformation_enabled='TRUE') • References: • Optimizer with Oracle Database 12c (June 2013) • Understanding Optimizer Statistics with Oracle Database 12c (June 2013)

17. Verify Optimizer Decisions Explain shows method of data access and use of advanced features

18. Oracle In-Memory Best Practices How to achieve optimal performance for Analytical workloads • Enable parallel execution • Help the Oracle optimizer to make the right decisions • Minimize necessary IO • Give Oracle (In-Memory) sufficient resources • MicroStrategy best practices for Oracle In-Memory

19. Orders of Magnitude Faster Analytic Data Scans Memory Each CPU core scans local in-memory columns Scans use super fast SIMD vector instructions Originally designed for graphics & science Billions of rows/sec scan rate per CPU core Row format is millions/sec Example: Find all sales in region of CA REGION CPU Vector Register CA Load multiple region values Vector Compare all values an 1 cycle CA CA CA > 100x Faster

20. Analytical Queries Access Both Row And Column Stores Data still needs to be organized on disk according to best practices Tips • Use optimal table partitioning strategy (range vs. hash partition) • Use single BIGFILE tablespace for performance tables and indexes • Use single BIGFILE for temporary tablespace • Enable tablespace compression following best practices (COMPRESS BASIC) References • Best Practices for Implementing a Data Warehouse on the Oracle Exadata Database Machine (March 2014)

21. Verify Partitioning is Effective Optimizer sees an opportunity to parallelize IO operations

22. Oracle In-Memory Best Practices How to achieve optimal performance for Analytical workloads • Enable parallel execution • Help the Oracle optimizer to make the right decisions • Minimize necessary IO • Give Oracle (In-Memory) sufficient resources • MicroStrategy best practices for Oracle In-Memory

23. Column Store Is Part of SGA Optimal configuration needs to balance memory resources Tips • Set appropriate In-memory area of SGA (*.inmemory_size= ???G) • Leave room for buffer cache References: • Best Practices for a Data Warehouse on Oracle Database 11g (Nov 2010)

24. Oracle In-Memory Best Practices How to achieve optimal performance for Analytical workloads • Enable parallel execution • Help the Oracle optimizer to make the right decisions • Minimize necessary IO • Give Oracle (In-Memory) sufficient resources • MicroStrategy best practices for Oracle In-Memory

25. MicroStrategy Best Practices SQL Generation is very flexible • Enable use of temporary tables for intermediate result sets • Create single BIGFILE scratch tablespace for MSTR intermediate tables • Use Oracle join syntax rather than ANSI syntax • Allow Oracle to identify queries coming from MicroStrategy

26. MicroStrategy Provides Two Data Access Options Choose how to access and analyze data Modeled Direct Visual Insight Dashboard Report Dashboard Visual Insight Report • Unified MicroStrategy Metadata • Reusable Data • Reusable Objects • Reusable Design Databases Big Data Personal / Departmental Cloud Data

27. Let Optimizer Manage Intermediate Results Derived Table and Query Factoring syntax generate single query Tends to result in very large queries!

28. MicroStrategy To Generate Script Using Temp Tables Intermediate results managed explicitly in script • Default option for Intermediate Table Type(no change necessary) • Temp tables create lots of activity on temporary tablespace • Create single BIGFILE scratch tablespace for MSTR intermediate tables • Use fastest storage • Oracle join syntax is currently better understood by Optimizer than ANSI syntax • VLDB: Joins -> Join Type: Join 89

29. MicroStrategy Best Practices SQL Generation is very flexible • Enable use of temporary tables for intermediate result sets • Create single BIGFILE scratch tablespace for MSTR intermediate tables • Use Oracle join syntax rather than ANSI syntax • Allow Oracle to identify queries coming from MicroStrategy

30. Comment Adds MicroStrategy User And Report Details Allows identifying origin of SQL syntax in Oracle stats

31. Oracle In-Memory Best Practices How to achieve optimal performance for analytical workloads • Use existing hardware efficiently • Help the Oracle optimizer to make the right decisions • Minimize necessary IO • Give Oracle (In-Memory) sufficient resources • MicroStrategy best practices for Oracle In-Memory

32. Next Steps Review other relevant technical content at this conference Session ID: CON8221Session Title: Real-World Performance of Star and Snowflake Schemas, Part 1: The TheoryVenue / Room: Moscone South - 102Date and Time: 9/30/14, 12:00 - 12:45Session ID: CON8222Session Title: Real-World Performance of Star and Snowflake Schemas, Part 2: The RealityVenue / Room: Moscone South - 102Date and Time: 10/1/14, 11:30 - 12:15

33. Conclusion Oracle In-Memory offers significant improvements for analytical workloads Benefits • Enables Real-time Analytics • Improved query performance • Easy to adopt • Reduced cost Implement Best Practices to achieve the benefits offered by Oracle In-Memory • Use existing hardware efficiently • Help the Oracle optimizer to make the right decisions • Minimize necessary IO • Give Oracle (In-Memory) sufficient resources • Implement MicroStrategy best practices for Oracle In-Memory