the best way to tune your parallel statements: real-time ...€¦ · real-time sql monitoring •...
TRANSCRIPT
![Page 1: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/1.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
TheBestWaytoTuneYourParallelStatements:Real-TimeSQLMonitoring
YasinBaskanProductManager,ParallelExecuPonDataWarehouseDevelopmentSep20,[email protected]@yasinbaskan
![Page 2: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/2.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
SafeHarborStatementThefollowingisintendedtooutlineourgeneralproductdirecPon.ItisintendedforinformaPonpurposesonly,andmaynotbeincorporatedintoanycontract.Itisnotacommitmenttodeliveranymaterial,code,orfuncPonality,andshouldnotberelieduponinmakingpurchasingdecisions.Thedevelopment,release,andPmingofanyfeaturesorfuncPonalitydescribedforOracle’sproductsremainsatthesolediscrePonofOracle.
![Page 3: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/3.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
• Availablenow– ExadataExpressCloudService
• Comingsoon– DatabaseCloudServices– ExadataCloudMachine
3
AnnouncingOracleDatabase12cRelease2onOracleCloud
OracleispresenPngfeaturesforOracleDatabase12cRelease2onOracleCloud.WewillannounceavailabilityoftheOn-PremreleasesomePmeaZerOpenWorld.
![Page 4: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/4.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
Agenda
IntroducPontoReal-TimeSQLMonitoring
StepstoanalyzeaSQLMonitorreport
CommoncausesofsubopPmalperformanceinparallelstatements
1
2
3
4
![Page 5: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/5.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
IntroducPontoReal-TimeSQLMonitoring
5
![Page 6: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/6.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
Real-TimeSQLMonitoring• IntroducedinOracleDatabase11gtomakeSQLtuningeasier• ShowsdetailedinformaPonabouttheexecuPonofasingleSQLstatement– BothatrunPmeandaZerthestatementends
• MakesitveryeasytoidenPfycausesofperformanceproblemsbyshowingwherePmeisbeingspentintheexecuPonplan– NoneedtoqueryV$viewsorenableanytracing
• EspeciallyusefulforparallelstatementswheretheexecuPonplanmaybemuchlonger• ForguidanceonhowtogetSQLMonitorreportssee:– hcp://www.oracle.com/technetwork/database/manageability/sqlmonitor-084401.html
6
![Page 7: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/7.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.| 7
Real-TimeSQLMonitoring
![Page 8: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/8.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
StepstoAnalyzeaSQLMonitorReport
8
![Page 9: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/9.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
HowtoanalyzeaSQLMonitorreport• LookatwherethePmeisbeingspent– Time&waitstaPsPcs– FindtheplanstepdoingmostoftheacPvity• “AcPvity%”column
– CheckiftheesPmatedcardinalitymatchestheactualcardinality• “EsPmatedrows”and“actualrows”columns
• GatheringappropriateopPmizerstatsfixesmostoftheproblems– Extendedstats,histograms,etc…
9
![Page 10: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/10.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
CommonCausesofSubopPmalPerformanceinParallelStatements
10
![Page 11: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/11.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
Top3thingstobeawareofinparallelstatements• WorkdistribuPonacrossparallelprocesses• DatadistribuPonbetweenparallelprocesses• Degreeofparallelism(DOP)
11
![Page 12: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/12.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
WorkDistribuPonAcrossParallelProcesses
12
![Page 13: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/13.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
WhyisworkdistribuPonimportant?• MulPpleprocessesworkingforasingleparallelSQLstatement• UnevenworkdistribuPoncausessubopPmalperformanceandlowscalability– e.g.IncreasingtheDOPdoesnotmakethequeryrunfaster
13
![Page 14: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/14.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
WorkdistribuPonacrossprocesses• LookattheParalleltabtoseetheworkdistribuPonacrossprocesses
14
UnevenworkdistribuPon
![Page 15: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/15.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
WorkdistribuPonacrossprocesses• Lookatwhataspecificprocessisdoing• TrytounderstandwhatcausesunevenworkdistribuPon
15
![Page 16: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/16.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
CommoncausesofunevenworkdistribuPon• SubopPmaldatadistribuPonmethodchosenbytheopPmizer• Dataskewinthejoincolumns
16
![Page 17: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/17.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
CommonDataDistribuPonProblems
17
![Page 18: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/18.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
Producer/consumermodelanddatadistribuPon
PX1
PX2
A-L
K-Z
ProducerConsumerDistribute
ProducerConsumerDistribute
QueryCoordinator
SCANJOIN
SELECT*FROMCUSTOMERSORDERBYcust_last_name;
![Page 19: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/19.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
CommondistribuPonmethods• HASH– AppliesahashfuncPononthejoinkeysandsendstherowtoasingleconsumer
• BROADCAST– Sendsallrowstoeveryconsumer– Suitableforsmallerdatasets
![Page 20: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/20.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
HashdistribuPon
PX2
PX1cust_id
3000
1000
2000
4000
c.cust_id
1000
4000
c.cust_id
3000
2000
cust_id
3000
1000
cust_id
2000
4000
PX3
PX4
PX1andPX2scantheleZsidetable
PX1andPX2applyahashfuncPononthekeycolumnsanddistributetherowsbasedontheresulttoPX3andPX4
TableC
selectcount(*)fromc,swherec.cust_id=s.cust_id;
![Page 21: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/21.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
HashdistribuPon
PX2
PX1cust_id
4000
1000
2000
3000
c.cust_id s.cust_id
1000 1000
4000 4000
c.cust_id s.cust_id
3000 3000
2000 2000
cust_id
4000
1000
cust_id
2000
3000
PX3
PX4
PX1andPX2scantherightsidetable
PX1andPX2applyahashfuncPononthekeycolumnsanddistributetherowsbasedontheresulttoPX3andPX4
TableS
selectcount(*)fromc,swherec.cust_id=s.cust_id;
![Page 22: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/22.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
BroadcastdistribuPon
PX2
PX1cust_id
3000
1000
2000
4000
cust_id
3000
1000
cust_id
2000
4000
PX1andPX2scantheleZsidetable c.cust_id
3000
1000
2000
4000
PX3
PX4c.cust_id
3000
1000
2000
4000
PX1andPX2broadcastallrowstoPX3andPX4
TableC
selectcount(*)fromc,swherec.cust_id=s.cust_id;
![Page 23: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/23.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
BroadcastdistribuPon
cust_id
3000
1000
2000
4000
PX3andPX4scantherightsidetablecust_id s.cust_id
3000 3000
1000 1000
2000
4000
PX3
PX4cust_id s.cust_id
3000
1000
2000 2000
4000 4000
TableS
selectcount(*)fromc,swherec.cust_id=s.cust_id;
NodistribuPonfortherightside
![Page 24: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/24.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
CommondistribuPonproblems• OpPmizermaypickasubopPmaldistribuPonmethodbecauseofstale/missingstats– HashdistribuPonwithlowcardinality• Causessomeprocessesdomorework
– BroadcastdistribuPonwithhighcardinality• CauseshighnumberofrowstobesentacrossRACnodesandprocesses• MaycausehighPGAandtemporarytablespaceusage
![Page 25: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/25.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
HashdistribuPonwithlowcardinalityWorkdistribu2on
UnevenworkdistribuPon
![Page 26: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/26.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
HashdistribuPonwithlowcardinalityWaitac2vityfordistribu2on
EsPmatedrowsvs.ActualRows HighwaitPmefordistribuPonHashdistribuPonasaresultofwrongcardinalityesPmates
![Page 27: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/27.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
HashdistribuPonwithlowcardinality• OpPmizermaypickhashdistribuPonforsmalltablesbecauseofstalestats• Causessomeprocessestodomoreworkthanothersdegradingscalability• MakesureopPmizerstatsareup-to-datetogettheopPmaldistribuPonmethod• OracleDatabase12cprovidesadapPvedistribuPonmethodstopicktheopPmaldistribuPonmethodatrunPme
27
![Page 28: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/28.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
HashdistribuPonwithlowcardinalityA9ergatheringop2mizerstatsinOracleDatabase11g
BroadcastdistribuPon
![Page 29: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/29.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
HashdistribuPonwithlowcardinality
29
Adap2vedistribu2onwithstalestatsinOracleDatabase12c
AdapPvedistribuPon
![Page 30: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/30.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
AdapPvedistribuPoninOracleDatabase12c
• DistribuPonmethoddecisionbasedonactualnumberofrowsratherthanopPmizerstats
• NewadapPvedistribuPonmethodhybridhash– StaPsPccollectorsusedontheleZsideofthejointocounttheactualrowsatrunPme– IfnumberofrowsislessthantheopPmizerthresholdusebroadcast,elseusehash
Hybridhashdistribu2on
![Page 31: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/31.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
AdapPvedistribuPoninOracleDatabase12c
31
Howtofindtherun2medistribu2onmethod
6=>Broadcast16=>Hash
![Page 32: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/32.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
CommondistribuPonproblems• OpPmizermaypickasubopPmaldistribuPonmethodbecauseofstale/missingstats– BroadcastdistribuPonwithhighcardinality• CauseshighnumberofrowstobesentacrossRACnodesandprocesses• Maycausehightemporarytablespaceusage
![Page 33: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/33.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
BroadcastdistribuPonwithhighcardinality
EsPmatedrowsvs.actualrows Hightempusage HighacPvityfortempIO
![Page 34: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/34.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
BroadcastdistribuPonwithhighcardinality• OpPmizerpicksbroadcastdistribuPonbecauseofstalestats• Causesalotofrowstobebroadcastedtoallconsumerprocesses• MaycausehighPGAandtempusageinhashjoinsaseachconsumerprocessbuildsahashtableforallrows• MakesureopPmizerstatsareup-to-datetogettheopPmaldistribuPonmethod
34
![Page 35: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/35.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
BroadcastdistribuPonwithhighcardinality
35
A9ergatheringop2mizerstats
HashdistribuPonatrunPme Notempusage
![Page 36: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/36.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
EffectofDataSkewonWorkDistribuPon
36
![Page 37: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/37.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
WorkdistribuPonwithdataskew• HashdistribuPonisthemostcommondistribuPonmethodforlargetables– DoesnottakedataskewintoaccountunPlOracleDatabase12c
• Dataskewcancausesomeconsumerprocessestogetmorerows
37
PX2
PX1cust_id
3000
3000
3000
4000
c.cust_id
3000
3000
3000
c.cust_id
4000
cust_id
3000
3000
cust_id
3000
4000
PX3
PX4
TableSPX3getsmorerowsthanPX4
![Page 38: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/38.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
WorkdistribuPonwithdataskew
38
UnevenworkdistribuPon
![Page 39: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/39.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
WorkdistribuPonwithdataskew
39
AdapPvedistribuPon(HashdistribuPonatrunPmeinthiscase)
![Page 40: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/40.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
WorkdistribuPonwithdataskew
40
A9ergatheringhistogramsonthejoincolumn
HashskewdistribuPon
![Page 41: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/41.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
Skewhandlingforjoins
• Value-baseddistribuPonofskeweddataforhashdistribuPon– BroadcastrowscontainingpopularvaluesontheleZsideofthejoin– Randomizerowscontainingpopularvaluesontherightsideofthejoin
• Popularvaluesareobtainedusingsampling• DecisionbasedonskewinformaPonfromhistograms• Considersskewontherightsideofthejoin(largertable)
IntroducedinOracleDatabase12cRelease1
![Page 42: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/42.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
Skewhandlingforjoins
• Histogramneededonthejoincolumn• Popularvalues– Valuesthatareatleast10xtheaveragefrequency– Valuesthathavemorethan30%frequency
• Ifnumberofskewedvalues>DOP,noskewhandling
IntroducedinOracleDatabase12cRelease1
![Page 43: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/43.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
DegreeofParallelismandDowngrades
![Page 44: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/44.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
• Checkifyouarege}ngtheDOPyourequested– Ifnot,findoutwhy
• DOPisthefactorimpacPngperformanceforwelltunedSQLstatements– TunetheSQLbeforeplayingwiththeDOP
Degreeofparallelism(DOP)
![Page 45: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/45.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
DOPdowngrades• OthercolumnforPXcoordinatorshowsthedowngradereason
45
![Page 46: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/46.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
DOPdowngradereasonsselectqksxareasons,indx
fromx$qksxa_reason
whereqksxareasonslike'%DOPdowngrade%’;
QKSXAREASONSINDX
----------------------------------------------------------------------
DOPdowngradeduetoadaptiveDOP351
DOPdowngradeduetoresourcemanagermaxDOP352
DOPdowngradeduetoinsufficientnumberofprocesses353
DOPdowngradebecauseslavesfailedtojoin354
46
![Page 47: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/47.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
DOPdowngradereasons
• AdapPveparallelism– DowngradestheDOPbasedonsystemload
• Enabledbyinit.oraparameterPARALLEL_ADAPTIVE_MULTI_USER• DisableadapPveparallelismbyse}ngtheparametertoFALSE.– DisabledanddeprecatedinOracleDatabase12cRelease2
• UseParallelStatementQueuingtopreventsystemsaturaPon
47
DOPdowngradeduetoadap2veDOP
![Page 48: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/48.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
DOPdowngradereasons• DOPdowngradeduetoresourcemanagermaxDOP– DatabaseResourceManager(DBRM)plancappedtheDOP• PlandirecPvePARALLEL_DEGREE_LIMIT_P1• IfyouneedhigherDOPsincreasethelimitifthereisampleunusedprocessingbandwidth• OrdecreaseconcurrencyandallowhigherDOPs
• DOPdowngradeduetoinsufficientnumberofprocesses– #ofin-usePXserversreachedPARALLEL_MAX_SERVERS• IncreasePARALLEL_MAX_SERVERSifthereisampleunusedprocessingbandwidth• UseParallelStatementQueuingtolimitthenumberofin-useprocesses
48
![Page 49: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/49.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
DOPdowngradereasons• DOPdowngradebecauseslavesfailedtojoin– SomeprocessescouldnotjointheexecuPonofthestatement– UsuallyaresultofplanmismatchbetweenPXserversondifferentinstances– RaiseanSR
49
![Page 50: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/50.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
Summary
50
![Page 51: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/51.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
LeverageSQLMonitorforSQLtuning• EasilyidenPfycausesofsubopPmalperformance– CheckwherethePmeisbeingspent– MakesureesPmatedcardinalitymatchesactualcardinality– GatherappropriateopPmizerstatstofixcardinalitymismatch
• Forparallelstatements– CheckifworkanddatadistribuPonisevenforopPmalperformanceandscalability– FixunintendedDOPdowngradesdegradingperformance
51
![Page 52: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/52.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
ForfurtherinformaPon• MonitoringParallelExecuPonusingReal-TimeSQLMonitoringinOracleDatabase12c– hcps://blogs.oracle.com/datawarehousing/entry/monitoring_parallel_execuPon_using_real
• CommonDistribuPonMethodsinParallelExecuPon– hcps://blogs.oracle.com/datawarehousing/entry/common_distribuPon_methods_in_parallel
• AdapPveDistribuPonMethodsinOracleDatabase12c– hcps://blogs.oracle.com/datawarehousing/entry/adapPve_distribuPon_methods
• FindingtheDistribuPonMethodinAdapPveParallelJoins– hcps://blogs.oracle.com/datawarehousing/entry/finding_the_distribuPon_method_in
• FindingtheReasonforDOPDowngrades– hcps://blogs.oracle.com/datawarehousing/entry/finding_the_reason_for_dop
52
![Page 53: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/53.jpg)
Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|
SafeHarborStatementTheprecedingisintendedtooutlineourgeneralproductdirecPon.ItisintendedforinformaPonpurposesonly,andmaynotbeincorporatedintoanycontract.Itisnotacommitmenttodeliveranymaterial,code,orfuncPonality,andshouldnotberelieduponinmakingpurchasingdecisions.Thedevelopment,release,andPmingofanyfeaturesorfuncPonalitydescribedforOracle’sproductsremainsatthesolediscrePonofOracle.
53
![Page 54: The Best Way to Tune Your Parallel Statements: Real-Time ...€¦ · Real-Time SQL Monitoring • Introduced in Oracle Database 11g to make SQL tuning easier • Shows detailed informaon](https://reader034.vdocuments.us/reader034/viewer/2022042206/5ea8cbef80de1656d460cea6/html5/thumbnails/54.jpg)
54