safe harbor statement - huodongjia.com does autonomous database mean for the dba? less me on...

Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.|

SafeHarborStatementThefollowingisintendedtooutlineourgeneralproductdirecGon.ItisintendedforinformaGonpurposesonly,andmaynotbeincorporatedintoanycontract.Itisnotacommitmenttodeliveranymaterial,code,orfuncGonality,andshouldnotberelieduponinmakingpurchasingdecisions.Thedevelopment,release,andGmingofanyfeaturesorfuncGonalitydescribedforOracle’sproductsremainsatthesolediscreGonofOracle.

2


SQLPerformanceTuningintheAutonomousDatabaseEraAndrewHoldsworthChris0neQuCaryDongReal-WorldPerformanceNovember17,2017


Introduc0ons


IntroducGons

•  28YearsatOracle• VicePresidentRealWorldPerformance

– Goodperformanceisrarelyanaccident– Mostpeoplegetthesystemstheydeserve– Goodenoughrarelyis,aspireforexcellencenotgoodenough.

AndrewHoldsworth


IntroducGons

•  12YearsatOracle• ManageReal-WorldPerformanceeducaGoninChina•  Learntoanalysisfromtopdown,makesureyouareontherightdirecGon• BeopenandposiGve,aimhigh

曲卓 (Chris0neQu)


IntroducGons

•  15yearsofOracleexperience•  9yearsinRWP• ManageReal-WorldPerformanceprojectsinChina

17/11/16

董志平(CaryDong)


Real-WorldPerformance

• PartoftheDatabaseDevelopmentOrganizaGon• GlobalTeamlocatedinUSA,Europe,Asia•  350+combinedyearsofOracledatabaseexperience•  InnovatetoachieveexcepGonalDatabasePerformance• Ourmethods:

•  Usetheproductasitwasdesignedtobeused•  Numericalandlogicaldebuggingtechniques•  Educateothersaboutthebestperformancemethodsandtechniques•  Avoidandeliminate“tuning”byhacking/guessing/luck

WhoWeAre


Wheredatabaseuserlookforperformanceimprovements

Percep0on

ApplicaGonAlgorithmnsandCorrectProductUsage

DatabasePlagorm

ThebestplacetolookforperformanceImprovements

17/11/16

TheRealWorldPerformancePercepGonProblem

Reality

ApplicaGonAlgorithmnsandCorrectProductUsage

DatabasePlagorm


ProgramAgenda

WhyAutonomousDatabase?

SQLPerformanceTuningStrategy

MakesTuningSmarter

Panel

1

2

3

4

10


ProgramAgenda



MakesTuningSmarter

Panel

1

2

3

4

11


OracleVisionforAutonomousDatabaseGoal-EliminateallhumanlaborNohumanlabormeanslowercostNohumanerrormeansbeierreliabilityandsecurity

12


Oracle’sVisionforAutonomousDatabase

• Self-Driving– Userdefinesworkloadsandpolicies,databasemakesthemhappen

• Self-Securing– ProtecGonfrombothexternalaiacksandinternalusers

• Self-Repairing– AutomatedprotecGonfromalldownGme

13

Howwedoit


Automated

•  ThecarsimplifiesoperaGonsbyautomaGngtasks:– Cruisecontrol– Emergencystopping– Warningsforlanechanges

•  ThedatabasesimplifiesoperaGons:–  AutomaGcstoragemanagement,automaGcstoragemanagement,…

–  Dozensofotherfeatures

Autonomous

•  Thecardrivesitself– Noneedtousethesteeringwheelorbrake.

– Simplytellthecarwhereyouaregoing.

•  Thedatabasemanagesitself– AllfeaturesautomaGcallyimplemented– Simplytellthedatabaseyourgoals

14

Automatedvs.Autonomous


AutomaGcallyDiagnosesPerformance

• AutonomousDatabaseincludesOracle’sindustryleadingdiagnosGcsautomaGon

• AutomaGcDatabaseDiagnosGcMonitor(ADDM)– AutomaGcallydiagnosesrootcauseofperformanceissues– A.I.(ExpertSystem)

• AcGveWorkloadRepository(AWR)– AutomaGcallykeepsdetailedperformanceandresourceuGlizaGonhistory

• Real-TimeSQLMonitoring– AutomaGcallydiagnoseshowresourcesareusedinSQLstatements

15


AutomaGcallyOpGmizesItself

•  AutonomousDatabaseincludesOracle’sindustryleadingdatabasetuningautomaGon•  ManydatabasealgorithmsselfopGmize–caching,locking,storageindexes,offload,etc.

•  OpGmizerisnowfurtherautomatedbygatheringstaGsGcsasnewdataisloaded

•  AutomaGcSQL(Re)Tuning– Machinelearningtechnologythatisconstantlyre-evaluaGngSQLplansbasedonthelateststaGsGcsandrecommending/implemenGngbeierplans

•  Tuningisworkloaddependent–e.g.OLTPvsanalyGcssowespecializeservices•  Tuningisanextremelydifficultproblem

– EvenschedulingafleetoftruckstoopGmallymakedeliveriesisincrediblycomplex– Databasehasmanydegreesoffreedomandtradeoffsthatmustbeconsidered

16


DatabaseAdministratorQuesGonsandFears

• Willmyjobgoaway?

• Willmyjobchange?

• WillIlosecontrol?

17


WhatdoesAutonomousDatabasemeanfortheDBA?

Less0meonAdministra0on

•  LessGmeoninfrastructure•  LessGmeonpatching,upgrades•  LessGmeonensuringavailability•  LessGmeontuning

18

More0meonInnova0on

• MoreGmeondatabasedesign• MoreGmeondevelopingnewapps• MoreGmeondataanalyGcs• MoreGmeonsecuringdata

Challenge:Therearemoredatamanagementtasksthanhumanstodothework


Realityvs.Fears

•  Yourjobwillnotgoaway-thereisashortageofskilleddatabaseexperts– DatabaseautomaGonhasbeenimprovingfordecades

• Butyourjobwillchange,soyoumustchange•  YouwillspendlessGmeongenericmaintenance,moreGmeinnova0ng• MoreGmewiththebusiness

– ExecuGngmoreprojects,reducingbacklog,gerngmorevaluefromdata– Cloud’sfastprovisioningandpay-as-you-goenablesrapidexperimentaGon

• MoreGmewithdevelopers– OpGmizingdataaccess,improvingend-userexperience

• MoreGmeonnewtechniqueslikeMachineLearning19


DatabaseAdministratorMustEnsureEnd-to-EndServiceLevels•  FocusonopGmizinghowapplicaGonsanddatabasesworktogether:

– Definegooddatamodel,andgoodSQL– AvoidrowataGmeprocessing,andrepeatedlogins/parsing– UnderstandwhereGmeisbeingspent– Understandtradeoffsinparallelism,plans,indexes,parGGons,etc.– Ensurethatsensi0vedataiskeptsecureend-to-end– BewarewhatanapplicaGonasksfor–thedatabasewilldoexactlythat

• NeednewskillsinCloudSystems,CloudNetworking,CloudStorage– DBAsizes,monitors,ensuresfullstackworksasexpected

• DatabaseAdministratorwillgainmorecontrol– Willbeinchargeof,andincontrolof,end-to-endservicelevels

20


ProgramAgenda



MakesTuningSmarter

Panel

1

2

3

4

21


•  ElapsedGme:1hourà1min- Willyoustopworking?

•  Coulditbepossibletomakeitdownto1sec?

17/11/16

SQLPerformance

AimHigh


•  IsitavalidSQL?•  DoyouknowthebusinesslogictheSQLrepresentsfor?

•  IsitawellconstructedSQL?•  AnymistakesintheSQL?

-  N-1joincondiGonsforaNtablejoin?-  Implicitdatatypeconversion?

17/11/16

SQLPerformance

AimHigh

WorkonGoodSQL


•  Isyourdatabasesoswarecorrectlypatched?•  Areyourunningwithdefaultinit.oraparameterserngs?Ifnot,why?

17/11/16

SQLPerformance

AimHigh

WorkonGoodSQL

Environmentserngs


•  Stats-  Statsonrawdata-  Systemstats

•  Constraints-  NOTNULL,PK,FK,UK

•  Schemadesign-  Index-  ParGGoning-  Compression-  Clustering

17/11/16

SQLPerformance

AimHigh

WorkonGoodSQL

Environmentserngs

OpGmizaGon


•  Accessmethod•  Joinmethod•  Joinorder•  DistribuGonmethod•  Skew?

17/11/16

SQLPerformance

AimHigh

WorkonGoodSQL

Environmentserngs

OpGmizaGon

ExecuGon


•  Diagnosing-  SQLMonitorReport-  Findtheleverage

17/11/16

SQLPerformance

AimHigh

WorkonGoodSQL

Environmentserngs

OpGmizaGon

ExecuGon


ProgramAgenda



MakesTuningSmarter

Panel

1

2

3

4

28


IntroducGontoSQLMonitor• ReleasedinOracleDatabase11g•  EnablesindepthperformancemonitoringofaSQLstatement

– Alwayson,enabledoutofthebox– SingleexecuGonoftheSQLstatement– IncludescurrentlyexecuGngstatements

• Monitoredstatements– Serialstatementswith5secondsoftotalCPU/IOGme– Allparallelstatements– /*+monitor*/hint– Queries/DML/DDL

29


IntroducGontoSQLMonitor•  Formatsavailable

– Text– HTML– AcGve(usethisone!)

• Availablefrom– MonitoredSQLscreen– commandline– EM,EMExpress– PerfHub

30


HowweusetheSQLMonitorreport

•  Topdown– WhereisGmespent?– Whichrowsources?– EsGmatedvsactualrowcardinaliGes?– ExecuGons

•  ParallelserverexecuGons•  NestedloopiteraGons•  ParGGonwiseoperaGons

– Skew

31


Example1

• Querycurrentlyrunsfor40seconds• Needstorununder5seconds•  ExaminaGonoftheSQLMonitorreportshows

– 67%CPU– 33%IO

•  ThemostexpensiverowsourceisHASHJOINRIGHTOUTER(line5)– 45%oftheCPU– Largeamountofread/writefromTEMP

Observa0ons

32


Example1:ObservaGons

33

DuraGon40s

67%CPU,33%IO

MajorityofGmespentinHASHJOIN


Example1

• However…– IstheHASHJOINitselftheproblem?

•  ExaminaGonoftheCardinalityesGmates– ThescanoftheCARGUYStableatline11

•  esGmateof42Mrowsvsactual40Mrows•  esGmateisveryaccurate

– ThescanoftheCARGUYStableatline9•  esGmateof186Krowsvsactual40M• morethat200xunderesGmated

Diagnosis

34


Example1:Diagnosis

35

EsGmatesrows:186K Actualrows:40M


Example1

• Whatisthecauseofthebigmis-esGmateincardinality?– ExaminingthepredicateinformaGon:

– Allthreefilterpredicatesarehighlycorrelated•  Ferrari’sareonlymadeinItaly•  OnlyFerrarimakesamodelcalledthe‘458Italia’

– TheopGmizerhasmulGpliedtheselecGvityoftheindividualpredicatesandunderesGmatedthenumberofrowsthatwillberetrieved

Diagnosis

36


Example1:Diagnosis

37

Highlycorrelatedpredicates


Example1

•  SoluGon– UseextendedstaGsGcs

•  BuildacolumngrouponMAKE,MODEL,COUNTRY

– TheopGmizercannowdeterminetheselecGvityofthethreepredicatescombined

• Result– ThecardinalityesGmatehasnowchangedto40Mrows.– ChangesdistribuGonmethodfromBROADCASTtoHASH-HASH– ThisresultsinamoreefficientHASHJOIN,whichdoesnotspilltoTEMP– Querynowrunsin3seconds.

38


Example1:SoluGon

39

EsGmatesrows:40M Actualrows:40M

DistribuGonmethodchangedtoHASH-HASH

DistribuGonmethodchangedtoHASH-HASH HashjoinnolongerspillstoTEMP


Example2

• Querytakes42minutes– AlmostallCPU

• ComplexSQLstatement• ParallelexecuGonrequested•  Timespentinnestedfulltablescans• OpGmizeresGmateditwouldperformonetablescan

– Actuallyperformed5776tablescans

Observa0ons

40


Example2:Overview

41

DuraGon41.2minutesAllCPU

RequestedParallel

Executedserially


Example2:SQL

42

ComplexSQLstatement


Example2:PlanstaGsGcsshowswhereGmespent

43

Timemainlyinthesetworow

sourcesMaterializedview

scanExecuted5,776

GmesTimemainlyinthesetworow

sources


Example2:PlanstaGsGcsleadstosourceofscaniteraGons

44

OpGmizerexpectsonerow,not5,776

Thislinedrivesnestedloop


Example2:PlanStaGsGcsscreenshowsesGmateandactual

45

OpGmizeresGmateforindexscanisclose

Butonlyexpects1rowasertablefilters


Example2

• PoorcardinalityesGmatecausedbySUBSTR()funcGon

46

Diagnosis


Example2:SQLMonitorPlanScreenShowsPredicates

47

OpGmizeresGmatesfiltersarehighlyselecGve

Butsubstringfilteractuallyremovesfewrows


Example2

•  ExtendedstaGsGcsonSUBSTR()funcGon• OpGmizernowesGmated10rowsratherthan1• OpGmizeruseshashjoinratherthannestedloop• Accessestablewithsinglescan•  ElapsedGme:6seconds

Solu0on

48


Example2:SQLMonitorAserExpressionStaGsGcsAdded

49

Queryexecutesinsixseconds.

Requeststorunparallel

Actuallyrunsparallel

DBTimeismixofCPU,IOandclusterwaits


Example2:SQLMonitorAserExpressionStaGsGcsAdded

50

ScanofnamestablenowhasesGmateof10

Nowdoessingleparallelscanofmaterializedview


Example3

• Querytakes56minutes•  TimespentinHASHJOINBUFFERED• CardinalityesGmatesareaccurate•  LargeamountofTEMPIO•  TableScanof1TBtableranfor1800’s.

– Scanratefortheplagormismuchhigherthanthat•  ScanisconstrainedbythebufferingoftheHASHJOIN

51

Observa0ons



52

DuraGonalmost1hour



53

CardinalityEsGmatespreiyaccurate

CardinalityEsGmatespreiyaccurate



54

TimespentinHASHJOIN


Example3

•  ExaminingtheParallelTab– DatabaseGmeforPXserversoninstance6ismuchhigher– ExpandingtheInstance6nodeandexpandingParallelSet1,weobserveonePXserverwithaDBTimeof53mins

– AlargepartofthequerywasexecutedbyasinglePXserver.

55

Diagnosis


Example3:Diagnosis

56

NavigatetoParalleltab

SkewindatabaseGme


Example3:Diagnosis

57

ExpandtheParallelSet

OnePXserverconsumingalltheGme:53minutesvs5.9minutes


Example3

•  Somedomainknowledgewasappliedtothisproblem– TheapplicaGonsupportsloyaltycards– MajorityofthecustomersidsareunknownornotidenGfiable

•  ImplementedasauserID=-1

– ThisvaluefromtheCUST_IDENTIFIER_DIMtablehashestoasinglehashbucketandsotheHASHJOINforthatvalueisexecutedbyasinglePXslave

58

Diagnosis


Example3

•  Somedomainknowledgewasappliedtothisproblem– TheapplicaGonsupportsloyaltycards– MajorityofthecustomersidsareunknownornotidenGfiable

•  ImplementedasaUserID=-1

– UserID=-1hashestoasinglehashbucket– HASHJOINforUserID=-1isexecutedbyasinglePXslave

59

Diagnosis


Example3

– HavetheunknowncustomersbeidenGfiedbyanegaGvesequencenumber– Runtwoseparatequeries

•  Aqueryforknowncustomers,ieCUST_IDENTIFIER_ID>0– ThisqueryexecuteswithHASHHASHdistribuGon,liketheoriginal

•  AqueryforunknowncustomersCUST_IDENTIFIER_ID=-1– ThisqueryexecuteswithaBROADCASTdistribuGon

– Similartoabove• WritethequeryasaUNION-ALLoftheabove

– In12c,SKEWdetecGonmaygenerateaHYBRIDplanthatwillautomaGcallybroadcastpopularvalues,andHashdistributenon-popularvalues

60

Solu0on(s)


SQLMonitorgotcha’s

•  BewareofcardinalityesGmatesof1exceptforuniqueindexlookup•  ExecuGoncount

–  ParalleloperaGons–  ParGGonedoperaGons–  Nestedloop–  OrcombinaGon!

•  ForNestedloopplan–  EsGmatedrowsisforasingleexecuGon–  ActualrowsisforallexecuGons(sofar)

•  ParallelserverelapsedGmebarsnotagoodindicaGonofGme•  Fixedsizeinmemorybuffermeansstatementsageout•  Planlineslimitdefaultsto300

–  _sqlmon_max_planlinestochange

61


SQLMonitorConclusions

• Alwayson• DetaileddataonwheretheGmeisgoing•  ShowscardinalityesGmatesvsactuals•  TopSQLcapturedintoAWRin12c

SQLMonitoristhebesttooltounderstandwhySQLisrunningslowly

62


ProgramAgenda



MakesTuningSmarter

Panel

1

2

3

4

63


SafeHarborStatementTheprecedingisintendedtooutlineourgeneralproductdirecGon.ItisintendedforinformaGonpurposesonly,andmaynotbeincorporatedintoanycontract.Itisnotacommitmenttodeliveranymaterial,code,orfuncGonality,andshouldnotberelieduponinmakingpurchasingdecisions.Thedevelopment,release,andGmingofanyfeaturesorfuncGonalitydescribedforOracle’sproductsremainsatthesolediscreGonofOracle.

65

safe harbor statement - huodongjia.com does autonomous database mean for the dba? less me on...

Documents