• 1. Oracle Database In-Memory Performance Benefits with MicroStrategy Analytics Platform
  • 2. A Little About Us2A 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.A global footprint. A proven innovator. A leader in enterprise analytics for 25 years.
  • 3. MicroStrategy and OracleA longstanding partnershipMicroStrategy 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. Stunning Ease of UseMicroStrategy provides the easiest way to deliver advanced analytics on Big Data to business usersFrom Big Data to Business Value4Any and All DataStructured Semi-Structured Unstructured Databases Big Data Personal / Departmental Cloud DataComprehensive AnalyticsBenchmarkingProjectionsTrend AnalysisData SummarizationRelationship AnalysisOptimization
  • 5. Driven by new data sources, data volumes are growing exponentially. Leading companies are using advanced analytics to compete and win. Laggards are struggling. Mobile technology is 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. Are You Ready for the Age of Analytics?Real-time AnalyticsPerformanceData ScaleRequirements
  • 6. Oracle Database 12c In-Memory Option Goals100x Faster Queries: Real-Time Analytics Instantaneous Queries on OLTP Database or Data Warehouse Faster Mixed Workload OLTP Transparent: no application changes Simple to Implement6
  • 7. MicroStrategy Analytics + Oracle Database In-MemoryA Perfect Match?… we’re going to find out!Enables Real-time BIOperational and analytic workloads against the same tablesEliminates data movement into EDWImproved query performanceColumnar data store in memory enables improved analytical query performanceEasy to adopt No application changes requiredOptimizer decides when to use columnar or row-based data structuresReduced cost Less data movementLess performance optimizations required
  • 8. 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 Joint BenchmarkingHosted in Oracle Solaris Labs under supervision of development teamsseconds
  • 9. Interesting ResultsImplementing Oracle Database In-Memory requires following best practicesTests reveal great performance improvements comparing Oracle Database In-Memory vs. traditional buffer cacheBUT: this pales to the performance improvements compared to the first test run which naïvely had not implemented common best practices for analytical workloadsTest scenarioIn-MemoryBuffer CacheImprovementSingle user, final0:37:071:03:5373%Test scenarioIn-MemoryBuffer CacheImprovementSingle user, first run18:16:5211:32:56- 35%
  • 10. Oracle In-Memory Best Practices How to achieve optimal performance for Analytical workloadsUse 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 ResourcesAnalytical 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 effectivelyAnalytic queries commonly process large data volumes and benefit significantly from parallel processing
  • 12. Verify Parallel ExecutionExplain plan reports steps executed in parallel (PX for parallel execution)
  • 13. Oracle In-Memory Best Practices How to achieve optimal performance for Analytical workloadsEnable 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 DatabaseBOTH row and column formats for same table Simultaneously active and transactionally consistent Analytics & reporting use new in-memory Column format OLTP uses proven row format14MemoryMemorySALESSALESRow FormatColumn Format
  • 15. Column Store Replaces Analytic IndexesFast 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 Table1 – 3 OLTP IndexesIn-Memory Column Store15
  • 16. Optimizer Decides If Columnar Store is UsedSeveral best practices help the optimizer understand the queryThe 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 DecisionsExplain shows method of data access and use of advanced features
  • 18. Oracle In-Memory Best Practices How to achieve optimal performance for Analytical workloadsEnable 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 ScansEach 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 19Vector RegisterLoad multiple region valuesVector Compare all values an 1 cycleCPUMemoryREGIONCACACACAExample: Find all sales in region of CA> 100x Faster
  • 20. Joining and Combining Data Also Dramatically FasterConverts joins of data in multiple tables into fast column scans Joins tables 10x faster 20Example: Find total sales in outlet storesSalesStoresStore IDAmountType=OutletStoreID in 15, 38, 64Store IDTypeSum
  • 21. Analytical Queries Access Both Row And Column StoresData still needs to be organized on disk according to best practicesTips 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)
  • 22. Verify Partitioning is EffectiveOptimizer sees an opportunity to parallelize IO operations
  • 23. Oracle In-Memory Best Practices How to achieve optimal performance for Analytical workloadsEnable 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
  • 24. Column Store Is Part of SGAOptimal configuration needs to balance memory resourcesTips 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)
  • 25. Oracle In-Memory Best Practices How to achieve optimal performance for Analytical workloadsEnable 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
  • 26. MicroStrategy Best Practices SQL Generation is very flexibleEnable 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
  • 27. Choose how to access and analyze dataMicroStrategy Provides Two Data Access OptionsDirectUnified MicroStrategy Metadata Reusable Data Reusable Objects Reusable Design ReportModeled Visual Insight Dashboard Databases Big Data Personal / Departmental Cloud Data Report Visual Insight Dashboard
  • 28. Let Optimizer Manage Intermediate ResultsDerived Table and Query Factoring syntax generate single queryTends to result in very large queries!
  • 29. MicroStrategy To Generate Script Using Temp TablesIntermediate results managed explicitly in scriptDefault 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
  • 30. MicroStrategy Best Practices SQL Generation is very flexibleEnable 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
  • 31. Comment Adds MicroStrategy User And Report DetailsAllows identifying origin of SQL syntax in Oracle statsVLDB CategoryVLDB Property SettingValue Select/InsertSQL Hint/* MSTRUser=!u;Report=!r;*/
  • 32. Oracle In-Memory Best Practices How to achieve optimal performance for analytical workloadsUse 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
  • 33. Next StepsReview other relevant technical content at this conferenceSession ID: CON8221 Session Title: Real-World Performance of Star and Snowflake Schemas, Part 1: The Theory Venue / Room: Moscone South - 102 Date and Time: 9/30/14, 12:00 - 12:45 Session ID: CON8222 Session Title: Real-World Performance of Star and Snowflake Schemas, Part 2: The Reality Venue / Room: Moscone South - 102 Date and Time: 10/1/14, 11:30 - 12:15
  • 34. ConclusionOracle In-Memory offers significant improvements for analytical workloadsBenefits 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