Oracle自治數(shù)據(jù)庫SQL優(yōu)化

1、Oracle自治數(shù)據(jù)庫SQL優(yōu)化技術(shù)創(chuàng)新 變革未來Introduc0onsIntroducGonsAndrew Holdsworth28 Years at OracleVice President Real World PerformanceGood performance is rarely an accidentMost people get the systems they deserveGood enough rarely is, aspire for excellence not good enough.IntroducGons曲卓 (Chris0ne Qu)12 Years at

2、OracleManage Real-World Performance educaGon in ChinaLearn to analysis from top down, make sure you are on the right direcGonBe open and posiGve, aim highIntroducGons董志平(Cary Dong)15 years of Oracle experience9 years in RWPManage Real-World Performance projects in China17/11/16Real-World Performance

3、Who We ArePart of the Database Development OrganizaGonGlobal Team located in USA, Europe, Asia350+ combined years of Oracle database experienceInnovate to achieve excepGonal Database PerformanceOur methods:Use the product as it was designed to be usedNumerical and logical debugging techniquesEducate

4、 others about the best performance methods and techniquesAvoid and eliminate “tuning” by hacking/guessing/luckWhere database user look for performance improvementsPercep0onApplicaGon Algorithmns and Correct Product UsageDatabase Plagorm17/11/16The Real World Performance PercepGon ProblemThe best pla

5、ce to look for performance ImprovementsRealityApplicaGon Algorithmns and Correct Product UsageDatabase PlagormProgram AgendaWhy Autonomous Database?SQL Performance Tuning Strategy Makes Tuning SmarterPanel12348Program AgendaWhy Autonomous Database?SQL Performance Tuning Strategy Makes Tuning Smarter

6、Panel12349Oracle Vision for Autonomous Database10Goal - Eliminate all human laborNo human labor means lower costNo human error means beier reliability and securityOracles Vision for Autonomous Database11How we do itSelf-DrivingUser denes workloads and policies, database makes them happenSelf-Securin

7、gProtecGon from both external aiacks and internal usersSelf-RepairingAutomated protecGon from all downGmeAutomatedThe car simplies operaGons by automaGng tasks:Cruise controlEmergency stoppingWarnings for lane changesThe database simplies operaGons:AutomaGc storage management, automaGc storage manag

8、ement, Dozens of other features12AutonomousThe car drives itselfNo need to use the steering wheel or brake.Simply tell the car where you are going.The database manages itselfAll features automaGcally implementedSimply tell the database your goalsAutomated vs. AutonomousAutomaGcally Diagnoses Perform

9、ance13Autonomous Database includes Oracles industry leading diagnosGcs automaGonAutomaGc Database DiagnosGc Monitor (ADDM)AutomaGcally diagnoses root cause of performance issuesA.I.(Expert System)AcGve Workload Repository (AWR)AutomaGcally keeps detailed performance and resource uGlizaGon historyRea

10、l-Time SQL MonitoringAutomaGcally diagnoses how resources are used in SQL statementsAutomaGcally OpGmizes Itself14Autonomous Database includes Oracles industry leading database tuning automaGonMany database algorithms self opGmize caching, locking, storage indexes, ooad, etc.OpGmizer is now further

11、automated by gathering staGsGcs as new data is loadedAutomaGc SQL (Re)TuningMachine learning technology that is constantly re-evaluaGng SQL plans based on the latest staGsGcs and recommending/implemenGng beier plansTuning is workload dependent e.g. OLTP vs analyGcs so we specialize servicesTuning is

12、 an extremely dicult problemEven scheduling a eet of trucks to opGmally make deliveries is incredibly complexDatabase has many degrees of freedom and tradeos that must be consideredDatabase Administrator QuesGons and FearsWill my job go away?Will my job change?Will I lose control?15What does Autonom

13、ous Database mean for the DBA?Less 0me on Administra0onLess Gme on infrastructureLess Gme on patching, upgradesLess Gme on ensuring availabilityLess Gme on tuningMore 0me on Innova0onMore Gme on database designMore Gme on developing new appsMore Gme on data analyGcsMore Gme on securing dataChallenge

14、: There are more data management tasks than humans to do the work16Reality vs. Fears17Your job will not go away - there is a shortage of skilled database experts Database automaGon has been improving for decadesBut your job will change, so you must changeYou will spend less Gme on generic maintenanc

15、e, more Gme innova0ngMore Gme with the business ExecuGng more projects, reducing backlog, gerng more value from data Clouds fast provisioning and pay-as-you-go enables rapid experimentaGonMore Gme with developers OpGmizing data access, improving end-user experienceMore Gme on new techniques like Mac

16、hine LearningDatabase Administrator Must Ensure End-to-End Service Levels18Focus on opGmizing how applicaGons and databases work together:Dene good data model, and good SQLAvoid row at a Gme processing, and repeated logins/parsingUnderstand where Gme is being spentUnderstand tradeos in parallelism,

17、plans, indexes, parGGons, etc.Ensure that sensi0ve data is kept secure end-to-endBeware what an applicaGon asks for the database will do exactly thatNeed new skills in Cloud Systems, Cloud Networking, Cloud StorageDBA sizes, monitors, ensures full stack works as expectedDatabase Administrator will g

18、ain more controlWill be in charge of, and in control of, end-to-end service levelsProgram AgendaWhy Autonomous Database?SQL Performance Tuning Strategy Makes Tuning SmarterPanel123419Elapsed Gme:1 hour 1 min- Will you stop working?Could it be possible to make it down to 1 sec?SQL PerformanceAim High

19、17/11/16Is 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?SQL PerformanceAim HighWork on Good SQL17/11/16Is your database sosware correctly patched?Are you r

20、unning with default init.ora parameter serngs? If not, why?SQL PerformanceAim HighWork on Good SQLEnvironment serngs17/11/16StatsStats on raw dataSystem statsConstraintsNOT NULL, PK, FK, UKSchema designIndexParGGoningCompressionClusteringSQL PerformanceAim HighWork on Good SQL Environment serngs OpG

21、mizaGon17/11/16Access methodJoin methodJoin orderDistribuGon methodSkew?SQL PerformanceAim HighWork on Good SQL Environment serngs OpGmizaGonExecuGon17/11/16DiagnosingSQL Monitor ReportFind the leverageSQL PerformanceAim HighWork on Good SQL Environment serngs OpGmizaGonExecuGon17/11/16Program Agend

22、aWhy Autonomous Database?SQL Performance Tuning Strategy Makes Tuning SmarterPanel123426IntroducGon to SQL Monitor27Released in Oracle Database 11gEnables in depth performance monitoring of a SQL statementAlways on, enabled out of the boxSingle execuGon of the SQL statementIncludes currently execuGn

23、g statementsMonitored statementsSerial statements with 5 seconds of total CPU/IO GmeAll parallel statements/*+ monitor */ hintQueries / DML / DDLIntroducGon to SQL Monitor28Formats availableTextHTMLAcGve(use this one!)Available fromMonitored SQL screencommand lineEM, EM ExpressPerfHubHow we use the

24、SQL Monitor report29Top downWhere is Gme spent?Which row sources?EsGmated vs actual row cardinaliGes?ExecuGonsParallel server execuGonsNested loop iteraGonsParGGon wise operaGonsSkewExample 130Observa0onsQuery currently runs for 40 secondsNeeds to run under 5 secondsExaminaGon of the SQL Monitor rep

25、ort shows67% CPU 33% IOThe most expensive row source is HASH JOIN RIGHT OUTER (line 5)45% of the CPULarge amount of read/write from TEMPExample 1: ObservaGonsDuraGon 40s67% CPU, 33% IOMajority of Gme spent in HASH JOIN31Example 132DiagnosisHoweverIs the HASH JOIN itself the problem?ExaminaGon of the

26、 Cardinality esGmatesThe scan of the CARGUYS table at line 11esGmate of 42M rows vs actual 40M rowsesGmate is very accurateThe scan of the CARGUYS table at line 9esGmate of 186K rows vs actual 40Mmore that 200 x underesGmatedExample 1: DiagnosisEsGmates rows: 186KActual rows: 40M33Example 1All three

27、 lter predicates are highly correlatedFerraris are only made in ItalyOnly Ferrari makes a model called the 458 ItaliaThe opGmizer has mulGplied the selecGvity of the individual predicates and underesGmated the number of rows that will be retrievedDiagnosisWhat is the cause of the big mis-esGmate in

28、cardinality? Examining the predicate informaGon:34Example 1: DiagnosisHighly correlated predicates35Example 136SoluGonUse extended staGsGcsBuild a column group on MAKE, MODEL, COUNTRYThe opGmizer can now determine the selecGvity of the three predicates combinedResultThe cardinality esGmate has now c

29、hanged to 40M rows.Changes distribuGon method from BROADCAST to HASH-HASHThis results in a more ecient HASH JOIN, which does not spill to TEMPQuery now runs in 3 seconds.Example 1: SoluGonEsGmates rows: 40MActual rows: 40MDistribuGon method changed to HASH-HASHHash join no longer spills to TEMP37Exa

30、mple 238Observa0onsQuery takes 42 minutesAlmost all CPUComplex SQL statementParallel execuGon requestedTime spent in nested full table scansOpGmizer esGmated it would perform one table scanActually performed 5776 table scansExample 2: OverviewDuraGon 41.2 minutes All CPURequested ParallelExecuted se

31、rially39Example 2: SQLComplex SQL statement40Example 2: Plan staGsGcs shows where Gme spentTime mainly in these two row sourcesMaterialized view scanExecuted 5,776 Gmes41Example 2: Plan staGsGcs leads to source of scan iteraGonsOpGmizer expects one row, not 5,776This line drives nested loop42Example

32、 2: Plan StaGsGcs screen shows esGmate and actualOpGmizer esGmate for index scan is closeBut only expects 1 row aser table lters43Example 244DiagnosisPoor cardinality esGmate caused by SUBSTR() funcGonExample 2: SQL Monitor Plan Screen Shows PredicatesOpGmizer esGmates lters are highly selecGveBut s

33、ubstring lter actually removes few rows45Example 246Solu0onExtended staGsGcs on SUBSTR() funcGonOpGmizer now esGmated 10 rows rather than 1OpGmizer uses hash join rather than nested loopAccesses table with single scanElapsed Gme: 6 secondsExample 2: SQL Monitor Aser Expression StaGsGcs AddedRequests

34、 to run parallelActually runs parallelQuery executes in six seconds.DB Time is mix of CPU, IO and cluster waits47Example 2: SQL Monitor Aser Expression StaGsGcs AddedScan of names table now has esGmate of 10Now does single parallel scan of materialized view48Example 349Observa0onsQuery takes 56 minu

35、tesTime spent in HASH JOIN BUFFEREDCardinality esGmates are accurateLarge amount of TEMP IOTable Scan of 1TB table ran for 1800s.Scan rate for the plagorm is much higher than thatScan is constrained by the buering of the HASH JOINExample 3: ObservaGonsDuraGon almost 1 hour50Example 3: ObservaGonsCar

36、dinality EsGmates preiy accurate51Example 3: ObservaGonsTime spent in HASH JOIN52Example 353DiagnosisExamining the Parallel TabDatabase Gme for PX servers on instance 6 is much higherExpanding the Instance 6 node and expanding Parallel Set 1, we observe one PX server with a DB Time of 53 minsA large

37、 part of the query was executed by a single PX server.Example 3: DiagnosisNavigate to Parallel tabSkew in database Gme54Example 3: DiagnosisExpand the Parallel SetOne PX server consuming all the Gme: 53 minutes vs 5.9 minutes55Example 356DiagnosisSome domain knowledge was applied to this problemThe

38、applicaGon supports loyalty cardsMajority of the customers ids are unknown or not idenGableImplemented as a userID = -1This value from the CUST_IDENTIFIER_DIM table hashes to a single hash bucket and so the HASH JOIN for that value is executed by a single PX slaveExample 357DiagnosisSome domain knowledge was applied to this problemThe applicaGon supports loyalty cardsMajority of the customers ids are unknown or not idenGableImplemented as a UserID = -1UserID= -1 hashes to a single hash bucketHASH JOIN for UserID= -1 is executed by a s