1 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real-World Performance State of the Art 2014 Andrew Holdsworth Vice President Real-World Performance Group Database Development 2 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Introductions Andrew Holdsworth §  25 Years at Oracle §  Vice President Real World Performance –  Good performance is rarely an accident –  Most people get the systems they deserve –  “Good Enough” rarely is, aspire for Excellence never “Good Enough” 3 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. What is Real World Performance ? Bridging the Chasm from Today’s Performance to What is Possible And NOT Good Enough 4 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real World Performance §  Innovations Past and Present §  Real World Performance Challenges §  Evolving Your Performance §  Meeting the Real World Performance Group 5 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Innovations Past and Present Elimination of I/O as a bottleneck §  HW innovation –  Seekless devices e.g. flash/SSD –  High bandwidth networks §  SW innovation –  Allows HW all HW to run at HW speeds –  Intelligent Storage Devices §  Balanced configurations –  Storage space not the only metric of success §  Bandwidth, IOPS, Redundancy 6 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Innovations Past and Present Elimination of I/O as a bottleneck §  Exadata introduced in 2009 as a recognition that conventional storage was not going to keep modern CPUs busy:–  HBAs –  Switches –  Fiber –  Disk Arrays §  It did allow considerable scope for innovation/optimization/engineering of the database processing process. §  The rest is History ! 7 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Innovations Past and Present Elimination of disk based structures as a bottleneck §  Disk based structures do not allow database operations to run and exploit modern CPUS –  To much stalling on cache misses or I/O –  Unable to exploit vector operations where 1000’s of evaluations can be done in a single instruction §  Introduction of in Memory structures to allow data intensive operations to run at true CPU speeds –  Columnar storage techniques –  Encoding and compression techniques 8 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Innovations Past and Present Elimination of disk based structures as a bottleneck §  In Memory Columnar Stores are included in 12.1.0.2 and include –  In Memory Columnar representation of on disk structures –  Synchronization between on disk structures and In memory structures –  Query optimization techniques for in memory structures 9 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Performance Improvement Block Access Speeds Elapsed Time NanoSec 1000000 800000 600000 400000 200000 0 Elapsed Time NanoSec Disk Access 10 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. DRAM Access CPU Cache Access Performance Improvement Block Access Speeds ( Log Scale ) Elapsed Time NanoSec 1,000,000 100,000 10,000 1,000 100 10 1 Elapsed Time NanoSec Disk Access 11 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. DRAM Access CPU Cache Access Performance Potential Dimensional Schema or Start/Snowflake Queries 12 Optimization Technique Primary Fact Table Access method Performance Metric Bit Mapped Index on conventional Storage Rowid from Index BitMap Merge 1 Exadata Offloaded Scan with Bloom Filters 20 IMC IMC Column Scans with Bloom filters 8,000 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real World Performance The Big Challenges §  CPU over subscription –  Architecture and resource management §  Ever Increasing Data Sets –  Algorithmic gains from set based processing §  A reluctance to change code –  HW is the new SW 13 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real World Performance CPU Over Subscription §  With Caching, Non-Blocking and fast I/O it is easy to run a CPU at 99% §  It is easy to over subscribe the CPU by running more Oracle processes than CPU Cores §  Historically not an issue as slow I/O prevented high CPU utilization 14 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real World Performance Ever Increasing Data Sets §  Row by Row Processing §  The need to adopt the economy and efficiency of set and bulk processing §  Utilization of Core Database processing allows you to exploit Innovations Database HW and SW effectively 15 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Small Numbers and Large Multipliers How long does it take when processing one row per millisecond? Elapsed Time in Seconds Wall Clock Time 1 1 second Million 1,000 17 minutes Billion 1,000,000 12 days Trillion 1,000,000,000 32 years Thousand 16 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real World Performance Reluctance to Change Code §  Has HW become SW and SW become HW ? §  Challenges are often Organizational, Political, Ego Driven §  Good Real World Performance Engineering techniques allow you to exploit the innovations in technology and run orders of magnitude faster 17 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real World Performance The Order of Magnitude Challenge Name Sopwith Camel Bell X-1 Partial Upgrade Date 1916 1947 2014 Maximum Speed 115 MPH 957 MPH ? 18 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real World Performance The Big Mistakes §  Incorrect Use of Tools/Products §  Not understanding the big/little data design challenge §  Moving to a solution before understanding the problem 19 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real-World Performance Education Choose your participation level §  The Real-World Performance Class §  The Real-World Performance Day §  The Real-World Performance Learning Library 20 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real-World Performance Class 21 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real-World Performance Class §  4 Day Class of Intensive Performance Training –  Topics: SQL, OLTP, DW/BI –  Classroom, Demos, Hands On, Test and Quizzes –  Training given by Real World Performance Engineers –  Designed for Architects, Developers and DBAs –  4 months training in 4 days §  Contact RWP and you local Oracle team to apply 22 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real-World Performance Class Quotes “Fantastic – such a good course. Why can’t all training be like this?” “The demos schooled me” “Content matches the real world—totally different than courses offered by Oracle University” “Your understanding of oracle real life problems, familiarity with computer science basics and procedures, teaching excellence, and interpersonal skills all contributed to a successful training”—Venkat Dulla, Qualcomm “Anyone out there claiming that he/she is an Oracle DBA must attend RWP classes. We are highly experienced team of DBAs and we thought we’ve seen it all until this class ! Real problems that we are dealing day-to-day, like connection storms, was turned into training scenarios and explained so clearly with cause and solutions along with a diagnostic methodology…it was also an eye-opener for OLTP DBAs that I/O is no longer a bottleneck with exadata and it is time to start using set operations to achieve order of magnitude performance gains” –Metin Ylimaz, Turkcell 23 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real World Performance Day 24 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real-World Performance Day §  1 Day Seminar covering the latest Hot topics in Real-World Performance §  Speakers –  Tom Kyte –  Graham Wood –  Andrew Holdsworth §  Organize with you local User Group or Directly with RWP §  Requires 200+ delegates and theatre style facilities 25 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real-World Performance Day Next Performances §  March 26th UK, London §  March 28th Latvia, Riga §  Taking bookings for Nov/Dec 2014 Onwards 26 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real-World Performance Learning Library §  Today –  The Demos from the RWP Class and Day have been recorded for your Reference –  Use the them for study –  Send them to your boss –  Win arguments and increase the performance of your system by orders of magnitude 27 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real-World Performance Learning Library 28 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real-World Performance Education Goals §  Raise the quality of Oracle Database Usage –  Realize the true potential of the Database and HW –  Learn how to use the products in a manner they were meant to be used –  Provide data to help win arguments –  Change performance techniques from the hack and hope mentality to that of a professional engineer 29 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real-World Performance Hall of Fame §  We are collecting Real World Performance Stories –  Can you document the performance prior to improvement –  Can you document what is wrong with a system, SQL statement or Application module algorithm –  Can you articulate Real World Performance Solutions –  Can you document the predicted performance gains –  Did you see them in production and can you tell us the story §  We are looking for the best Real World Performance Engineer in the Real World 30 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Real-World Performance Places to Bookmark §  http://www.oracle.com/goto/oll/rwp §  email [email protected] 31 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 32 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 33 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.