Andrew Holdsworth曲卓董志平_自治数据库时代的 SQL 优化

2020-02-27 58浏览

  • 1.
  • 2.Safe Harbor Statement The following is intended to outline our general product direcGon. It is intended for informaGon purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or funcGonality, and should not be relied upon in making purchasing decisions. The development, release, and Gming of any features or funcGonality described for Oracle’s products remains at the sole discreGon of Oracle. Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 2
  • 3.SQL Performance Tuning in the Autonomous Database Era Andrew Holdsworth Chris0ne Qu Cary Dong Real-World Performance November 17, 2017 Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
  • 4.Introduc0ons Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
  • 5.IntroducGons Andrew Holdsworth •  28 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 not good enough. Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
  • 6.IntroducGons 曲卓 (Chris0ne Qu) •  12 Years at Oracle •  Manage Real-World Performance educaGon in China •  Learn to analysis from top down, make sure you are on the right direcGon •  Be open and posiGve, aim high Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
  • 7.IntroducGons 董志平(Cary Dong) •  15 years of Oracle experience •  9 years in RWP •  Manage Real-World Performance projects in China 17/11/16 Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
  • 8.Real-World Performance Who We Are •  Part of the Database Development OrganizaGon •  Global Team located in USA, Europe, Asia •  350+ combined years of Oracle database experience •  Innovate to achieve excepGonal Database Performance •  Ourmethods:•  Use the product as it was designed to be used •  Numerical and logical debugging techniques •  Educate others about the best performance methods and techniques •  Avoid and eliminate “tuning” by hacking/guessing/luck Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
  • 9.The Real World Performance PercepGon Problem Where database user look for performance improvements Percep0on The best place to look for performance Improvements Reality ApplicaGon Algorithmns and Correct Product Usage Database Plagorm ApplicaGon Algorithmns and Correct Product Usage Database Plagorm 17/11/16 Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
  • 10.Program Agenda 1 Why Autonomous Database? 2 SQL Performance Tuning Strategy 3 Makes Tuning Smarter 4 Panel Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 10
  • 11.Program Agenda 1 Why Autonomous Database? 2 SQL Performance Tuning Strategy 3 Makes Tuning Smarter 4 Panel Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 11
  • 12.Oracle Vision for Autonomous Database Goal - Eliminate all human labor No human labor means lower cost No human error means beier reliability and security Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 12
  • 13.Oracle’s Vision for Autonomous Database How we do it •  Self-Driving – User defines workloads and policies, database makes them happen •  Self-Securing – ProtecGon from both external aiacks and internal users •  Self-Repairing – Automated protecGon from all downGme Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 13
  • 14.Automated vs. Autonomous Automated Autonomous •  The car simplifies operaGons by automaGngtasks:– Cruise control – Emergency stopping – Warnings for lane changes •  The database simplifiesoperaGons:–  AutomaGc storage management, automaGc storage management, … –  Dozens of other features •  The car drives itself – No need to use the steering wheel or brake. – Simply tell the car where you are going. •  The database manages itself – All features automaGcally implemented – Simply tell the database your goals Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 14
  • 15.AutomaGcally Diagnoses Performance •  Autonomous Database includes Oracle’s industry leading diagnosGcs automaGon •  AutomaGc Database DiagnosGc Monitor (ADDM) – AutomaGcally diagnoses root cause of performance issues – A.I. (Expert System) •  AcGve Workload Repository (AWR) – AutomaGcally keeps detailed performance and resource uGlizaGon history •  Real-Time SQL Monitoring – AutomaGcally diagnoses how resources are used in SQL statements Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 15
  • 16.AutomaGcally OpGmizes Itself •  Autonomous Database includes Oracle’s industry leading database tuning automaGon •  Many database algorithms self opGmize – caching, locking, storage indexes, offload, etc. •  OpGmizer is now further automated by gathering staGsGcs as new data is loaded •  AutomaGc SQL (Re)Tuning –  Machine learning technology that is constantly re-evaluaGng SQL plans based on the latest staGsGcs and recommending/implemenGng beier plans •  Tuning is workload dependent – e.g. OLTP vs analyGcs so we specialize services •  Tuning is an extremely difficult problem –  Even scheduling a fleet of trucks to opGmally make deliveries is incredibly complex –  Database has many degrees of freedom and tradeoffs that must be considered Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 16
  • 17.Database Administrator QuesGons and Fears • Will my job go away? • Will my job change? • Will I lose control? Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 17
  • 18.What does Autonomous Database mean for the DBA? Less 0me on Administra0on •  Less Gme on infrastructure •  Less Gme on patching, upgrades •  Less Gme on ensuring availability •  Less Gme on tuning More 0me on Innova0on •  More Gme on database design •  More Gme on developing new apps •  More Gme on data analyGcs •  More Gme on securing dataChallenge:There are more data management tasks than humans to do the work Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 18
  • 19.Reality vs. Fears •  Your job will not go away - there is a shortage of skilled database experts – Database automaGon has been improving for decades •  But your job will change, so you must change •  You will spend less Gme on generic maintenance, more Gme innova0ng •  More Gme with the business – ExecuGng more projects, reducing backlog, gerng more value from data – Cloud’s fast provisioning and pay-as-you-go enables rapid experimentaGon •  More Gme with developers – OpGmizing data access, improving end-user experience •  More Gme on new techniques like Machine Learning Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 19
  • 20.Database Administrator Must Ensure End-to-End Service Levels •  Focus on opGmizing how applicaGons and databases worktogether:– Define good data model, and good SQL – Avoid row at a Gme processing, and repeated logins/parsing – Understand where Gme is being spent – Understand tradeoffs in parallelism, plans, indexes, parGGons, etc. – Ensure that sensi0ve data is kept secure end-to-end – Beware what an applicaGon asks for – the database will do exactly that •  Need new skills in Cloud Systems, Cloud Networking, Cloud Storage – DBA sizes, monitors, ensures full stack works as expected •  Database Administrator will gain more control – Will be in charge of, and in control of, end-to-end service levels Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 20
  • 21.Program Agenda 1 Why Autonomous Database? 2 SQL Performance Tuning Strategy 3 Makes Tuning Smarter 4 Panel Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 21
  • 22.SQL Performance •  ElapsedGme:1 hour à 1 min -  Will you stop working? •  Could it be possible to make it down to 1 sec? Aim High 17/11/16 Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
  • 23.SQL Performance •  Is it a valid SQL? •  Do you know the business logic the SQL represents for? •  Is it a well constructed SQL? •  Any mistakes in the SQL? -  N-1 join condiGons for a N table join? -  Implicit data type conversion? Aim High Work on Good SQL 17/11/16 Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
  • 24.SQL Performance •  Is your database sosware correctly patched? •  Are you running with default init.ora parameter serngs? If not, why? Aim High Work on Good SQL Environment serngs 17/11/16 Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
  • 25.SQL Performance •  Stats -  Stats on raw data -  System stats •  Constraints -  NOT NULL, PK, FK, UK •  Schema design -  Index -  ParGGoning -  Compression -  Clustering Aim High Work on Good SQL Environment serngs OpGmizaGon 17/11/16 Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
  • 26.SQL Performance •  Access method •  Join method •  Join order •  DistribuGon method •  Skew? Aim High Work on Good SQL Environment serngs OpGmizaGon ExecuGon 17/11/16 Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
  • 27.SQL Performance •  Diagnosing -  SQL Monitor Report -  Find the leverage Aim High Work on Good SQL Environment serngs OpGmizaGon ExecuGon 17/11/16 Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
  • 28.Program Agenda 1 Why Autonomous Database? 2 SQL Performance Tuning Strategy 3 Makes Tuning Smarter 4 Panel Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 28
  • 29.IntroducGon to SQL Monitor •  Released in Oracle Database 11g •  Enables in depth performance monitoring of a SQL statement – Always on, enabled out of the box – Single execuGon of the SQL statement – Includes currently execuGng statements •  Monitored statements – Serial statements with 5 seconds of total CPU/IO Gme – All parallel statements – /*+ monitor */ hint – Queries / DML / DDL Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 29
  • 30.IntroducGon to SQL Monitor •  Formats available – Text – HTML – AcGve (use this one!) •  Available from – Monitored SQL screen – command line – EM, EM Express – PerfHub Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 30
  • 31.How we use the SQL Monitor report •  Top down – Where is Gme spent? – Which row sources? – EsGmated vs actual row cardinaliGes? – ExecuGons •  Parallel server execuGons •  Nested loop iteraGons •  ParGGon wise operaGons – Skew Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 31
  • 32.Example 1 Observa0ons •  Query currently runs for 40 seconds •  Needs to run under 5 seconds •  ExaminaGon of the SQL Monitor report shows – 67% CPU – 33% IO •  The most expensive row source is HASH JOIN RIGHT OUTER (line 5) – 45% of the CPU – Large amount of read/write from TEMP Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 32
  • 33.Example 1: ObservaGons DuraGon 40s 67% CPU, 33% IO Majority of Gme spent in HASH JOIN Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 33
  • 34.Example 1 Diagnosis •  However… – Is the HASH JOIN itself the problem? •  ExaminaGon of the Cardinality esGmates – The scan of the CARGUYS table at line 11 •  esGmate of 42M rows vs actual 40M rows •  esGmate is very accurate – The scan of the CARGUYS table at line 9 •  esGmate of 186K rows vs actual 40M •  more that 200x underesGmated Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 34
  • 35.Example 1: Diagnosis EsGmatesrows:'>rows: