optimizing table scans in the cloud - oracle user grouppartitioning confidential –oracle...
TRANSCRIPT
![Page 1: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/1.jpg)
![Page 2: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/2.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
OptimizingTableScansintheCloud
JohnClarkeSoftwareDevelopmentDirectorReal-WorldPerformanceServerTechnologies
Confidential– OracleInternal/Restricted/HighlyRestricted
![Page 3: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/3.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
SafeHarborStatementThefollowingisintendedtooutlineourgeneralproductdirection.Itisintendedforinformationpurposesonly,andmaynotbeincorporatedintoanycontract.Itisnotacommitmenttodeliveranymaterial,code,orfunctionality,andshouldnotberelieduponinmakingpurchasingdecisions.Thedevelopment,release,andtimingofanyfeaturesorfunctionalitydescribedforOracle’sproductsremainsatthesolediscretionofOracle.
Confidential– OracleInternal/Restricted/HighlyRestricted 3
![Page 4: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/4.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
BridgingtheDividefromToday’sPerformancetoWhatisPossibleWhatisReal-WorldPerformancein2018?
![Page 5: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/5.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Real-WorldPerformance2018
• PartoftheDatabaseDevelopmentOrganization• GlobalTeamlocatedinUSA,Europe,Asia• 350+combinedyearsofOracledatabaseexperience• InnovatetoachieveexceptionalDatabasePerformance• Ourmethods:
• Usetheproductasitwasdesignedtobeused• Numericalandlogicaldebuggingtechniques• Educateothersaboutthebestperformancemethodsandtechniques• Avoidandeliminate“tuning”byhacking/guessing/luck
WhoWeAre
![Page 6: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/6.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
We’veBeenHereBefore
• Howmanyrowsdoyouneedtofind?a) Oneb) Afewc) Alotd) Idon’tknow
• Doyouscanoruseanindex?• Ifyoudon’tknow,whataccessmethodistheleastrisky?
Confidential– OracleInternal/Restricted/HighlyRestricted 6
![Page 7: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/7.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
TheQuestionWe’reAsking
Confidential– OracleInternal/Restricted/HighlyRestricted 7
SELECT d_year, d_sellingseason, c_region, SUM(lo_extendedprice),SUM(lo_supplycost)FROM lineorder
JOIN customer ON lo_custkey = c_custkeyJOIN date_dim ON lo_orderdate = d_datekeyJOIN part ON lo_partkey = p_partkeyJOIN supplier ON lo_suppkey = s_suppkey
WHERE d_month IN (’June’,’July’,’August’)AND p_mfgr IN (’MFGR#1’,’MFGR#2’)AND s_nation = ’China’
GROUP BY d_year, d_sellingseason, c_regionORDER BY d_year, d_sellingseason, c_region
“Showmethepriceandcostbyyear,sellingseason,andcustomerregionforallgoodssoldinJune,July,andAugustforpartsmanufacturedbyMFGR#1
andMFGR#2inChina”
![Page 8: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/8.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
OracleCloudInfrastructure
• OracleDatabaseCloudService– OracleCloudInfrastructure• OracleExadataCloudService- OracleCloudInfrastructure
WhereWe’reAskingtheQuestion
Confidential– OracleInternal/Restricted/HighlyRestricted 8
![Page 9: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/9.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Agenda
WhyTableScans?
MakingScansSmaller
Where’sOurLeverage?
RearrangingtheData
ThingsWeCanDotoSpeedUpNextOperationinPlan
1
2
3
4
5
Confidential– OracleInternal/Restricted/HighlyRestricted 9
![Page 10: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/10.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Agenda
WhyTableScans?
MakingScansSmaller
Where’sOurLeverage?
RearrangingtheData
ThingsWeCanDotoSpeedUpNextOperationinPlan
1
2
3
4
5
Confidential– OracleInternal/Restricted/HighlyRestricted 10
![Page 11: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/11.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
WhyTableScans?
• Ourqueryjoins4dimensiontablestoa1-billionrowfacttable• WouldyouexpectthisquerytobeI/Obound?• Ifweuseindexes:– Firstjointofacttableretrieves42millionrows,or4.2%– Aftercompletingadditionaljoins,weendupdoing~320millionrandomreads
• 320mrandomreads@5ms/read=~1600seconds– Is5msforarandomI/Oan“oldtechnumber”?– Ifthe“newtechnumbernumber”is1ms,we’relookingat~5minutesforI/O
SomeHistoryandMath
Confidential– OracleInternal/Restricted/HighlyRestricted 11
![Page 12: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/12.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
WhyTableScans?
• Wespend43seconds onI/O,not26minutesor5minutes• Ouraveragerandomreadistakingafractionofamillisecond• We’reCPU-bound,notI/Obound
IndexAccess
Confidential– OracleInternal/Restricted/HighlyRestricted 12
![Page 13: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/13.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
WhyTableScans?
• Whataboutafulltablescan?• Wehaveabout14millionblocks• The“oldtechnumber”formulti-blockreadsisabout6or7msperMBR• Amulti-blockreadcountof128=109kmulti-blockreads• 109kmulti-blockreadsat6.5msperMBRmeanswe’dspendunderaseconddoingI/O• Let’ssee…
FullTableScans
Confidential– OracleInternal/Restricted/HighlyRestricted 13
![Page 14: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/14.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
WhyTableScans?
• We375msdoingI/OwithanaverageI/Osizeof25MB• “Effective”MBRCmuchhigherthan128• We’restillCPU-bound
FullTableScans
Confidential– OracleInternal/Restricted/HighlyRestricted 14
![Page 15: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/15.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
WhyTableScans?
• InOracle’sCloud,randomandsequentialreadsaremuchfasterthantheoldnumberspeoplethinkabout• Forbothindexandtablescanaccess,thequeriesareCPU-bound,notI/Obound• Inthiscase,scanswere6xfaster.Wasthisbecauseofscans,joins,aggregation,orsomethingelse?• Timetodigabitdeeper!
TheNewMath&WhatWe’veLearned
Confidential– OracleInternal/Restricted/HighlyRestricted 15
![Page 16: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/16.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
WhyTableScans?LeverageMatrix
Confidential– OracleInternal/Restricted/HighlyRestricted 16
Method %of TimeinDataAcquisition
%of TimeinJoins %of TimeinSort/Aggregate
Elapsedseconds
ScanswithHashJoins
86% 12.24% 1.60% 187
IndexaccessandNLJoin
99% .08% .17% 1,195
![Page 17: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/17.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
WhyTableScans?NumbersSoFar
Confidential– OracleInternal/Restricted/HighlyRestricted 17
![Page 18: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/18.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Agenda
WhyTableScans?
MakingScansSmaller
Where’sOurLeverage?
RearrangingtheData
ThingsWeCanDotoSpeedUpNextOperationinPlan
1
2
3
4
5
Confidential– OracleInternal/Restricted/HighlyRestricted 18
![Page 19: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/19.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
MakingScansSmaller
• Partitioning isameanstoprunedataandreduceI/O&CPU• CompressionisameanstoreducesizeofdataondiskandreduceI/O• OracleCloudInfrastructuresupportsHybridColumnarCompression• Let’stryitout
PartitioningandCompression
Confidential– OracleInternal/Restricted/HighlyRestricted 19
![Page 20: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/20.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
MakingScansSmallerPartitioning
Confidential– OracleInternal/Restricted/HighlyRestricted 20
StillCPU-bound,butusesalotlessCPUthanscanswithout
partitioning
![Page 21: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/21.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
MakingScansSmallerPartitioning
Confidential– OracleInternal/Restricted/HighlyRestricted 21
AlthoughCPU-bound,I/ObytesisaproxyforCPUconsumption
TableScanswithout
partitioning
TableScanswithpartitioning
Wepartitionedondatejoinkeyandourpredicatesfiltered¾ofthe
data
![Page 22: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/22.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
MakingScansSmaller
• Acommonproblemweseeisover-partitioning– Proxyforindexes?– Usedtoavoidcontention?
• Toomanypartitionscancausemanyproblems:– Excessivetimeduringparse&execute– Highmetadatacost– DDLmoreexpensiveduetodatadictionaryoverheads– ExacerbatedwithRAC– Problemscouldrevealthemselvesinnon-obviousways
PartitioningWarnings
Confidential– OracleInternal/Restricted/HighlyRestricted 22
![Page 23: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/23.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
MakingScansSmallerHCCCompressionwithScansandPartitioning,OracleDatabaseCloudService
Confidential– OracleInternal/Restricted/HighlyRestricted 23
QueryisCPU-boundagainbutonlyranmarginallyfaster
Without HCC
![Page 24: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/24.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
MakingScansSmallerHCCCompressionwithScansandPartitioning,OracleDatabaseCloudService
Confidential– OracleInternal/Restricted/HighlyRestricted 24
Wescanned3.5xlessdatabutonlyimprovedqueryperformanceby
35%
Without HCC
![Page 25: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/25.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
MakingScansSmaller
• Weweren’tI/Oboundtobeginwith,wewereCPU-bound• CPU&timetoparseHCCblockslessthantimetoparseuncompressedblocks,but…• WeneedCPUtodecompresscompresseddata• Queryingsmallerdatasetsdoesn’tyieldlinearperformancegains• Conventionalmindsetvs.moderncapabilities
HCCCompressionwithScansandPartitioning,OracleDatabaseCloudService
Confidential– OracleInternal/Restricted/HighlyRestricted 25
![Page 26: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/26.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
MakingScansSmallerLeverageMatrix
Confidential– OracleInternal/Restricted/HighlyRestricted 26
Method %of TimeinDataAcquisition
%of TimeinJoins
%of TimeinSort/Aggregate
ElapsedSeconds
PartitioningwithCompression(OCIDBCS) 81% 14.5% 5.8% 36
Partitioningon(OCIDBCS) 79% 17% 4% 55
ScanswithHashJoins(OCI DBCS) 86% 12.24% 1.60% 187
IndexaccessandNLJoin(OCIDBCS) 99% .08% .17% 1,195
![Page 27: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/27.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
MakingScansSmallerNumbersSoFar
Confidential– OracleInternal/Restricted/HighlyRestricted 27
![Page 28: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/28.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Agenda
WhyTableScans?
MakingScansSmaller
Where’sOurLeverage?
RearrangingtheData
ThingsWeCanDotoSpeedUpNextOperations`inPlan
1
2
3
4
5
Confidential– OracleInternal/Restricted/HighlyRestricted 28
![Page 29: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/29.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Where’sOurLeverage?SomeProfilingData
Confidential– OracleInternal/Restricted/HighlyRestricted 29
IndexScans TableScans
Mostofourtimeisinparsingrows/blocks
![Page 30: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/30.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Where’sOurLeverage?ProfilingDatawithHCCandPartitioningonOracleDatabaseCloudService
Confidential– OracleInternal/Restricted/HighlyRestricted 30
Timespentparsingcolumns/rowsinHCCformat
![Page 31: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/31.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Where’sOurLeverage?
• Mostofourtimeisbeingspentondataacquisition• DataacquisitionisCPU-boundonOracleDBCloudService• Profilingshowsit’slargelyrelatedtoparsingblocks• Whatifwecouldrecruitmoreresourcesforblockparsing,parseblocksinparallel,offloadthisworktodifferentmachines,anddecreasewallclocktime?• Dowehaveanytechnologythatdoesthis?
BlockParsing
Confidential– OracleInternal/Restricted/HighlyRestricted 31
![Page 32: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/32.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Where’sOurLeverage?ExadataCloudService
Confidential– OracleInternal/Restricted/HighlyRestricted 32
• QuerystillCPU-boundbutwesee7.5%(2seconds)onI/O• RememberwesawnoI/OonDBCS
• I/OinthiscasemeansanythingintheI/Opath,includingCPUonstoragecells.4.35%ofourtimeison“smartscan”
![Page 33: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/33.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Where’sOurLeverage?
• 4.35%of13seconds=~.5seconds,multiplyby2slaves=~1 seconcells• OnExadataweuseupto10parallelrequestsperslave– 2slaves=up20parallelrequestspercell– 7cells=~140 parallelrequestsintotal,whichis70xmorethanDWCS
• InBMCwespend=~70 CPUsecondsonscan• Offloadingallowsustoparseblocksinparallel,reduceelapsedtime,andreducecomputenodeCPUtime• Bonusquestion– ifwedo10requestsperslavewith1MBI/Osize,whatisshouldourminimumpartitionsizebe?
ExadataCloudService toOffloadBlockParsing
Confidential– OracleInternal/Restricted/HighlyRestricted 33
![Page 34: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/34.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Where’sOurLeverage?
Didyounoticewe’restillspending24.5CPUsecondsnotdoingthescans?
ExadataCloudService toOffloadBlockParsing
Confidential– OracleInternal/Restricted/HighlyRestricted 34
![Page 35: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/35.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Where’sOurLeverage?ExadataCloudService toOffloadBlockParsingComputenodeProfiling
Confidential– OracleInternal/Restricted/HighlyRestricted 35
Timeoncomputenodenolongerdominatedbyparsingblocks
![Page 36: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/36.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Where’sOurLeverage?LeverageMatrix
Confidential– OracleInternal/Restricted/HighlyRestricted 36
Method %of TimeinDataAcquisition
%of TimeinJoins
%of TimeinSort/Aggregate
ElapsedSeconds
PartitioningwithCompression(OCIExaCS) 48% 51% 1% 13
PartitioningwithCompression (OCIDBCS) 81% 14.5% 5.8% 36
PartitioningonBMC(OCIDBCS) 79% 17% 4% 55
ScanswithHashJoins (OCIDBCS) 86% 12.24% 1.60% 187
IndexaccessandNLJoin(OCIDBCS) 99% .08% .17% 1,195
![Page 37: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/37.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Where’sOurLeverage?
• Exadataprovidessomeotherinterestingalternativestoexplore• ZoneMapswithAttributeClusteringprovideandadditionalmeanstopruneI/OandreduceCPU• Inadditiontopartitioningonourdatedimension’sjoinkey,let’simplementAttributeClusteringwithaZoneMaponourSupplierdimension’sjoinkey
MoreYouCanDoonExadata
Confidential– OracleInternal/Restricted/HighlyRestricted 37
![Page 38: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/38.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Where’sOurLeverage?ExadataCloudService withClusteringandZoneMaps
Confidential– OracleInternal/Restricted/HighlyRestricted 38
• Elapsedtimereducedfrom13secondsto5seconds• ComputenodeCPUreducedfrom24.5to6.7seconds• Likepartitioning,ZoneMapswithClusteringmeansfewer
calls toExadata,witheachcallbeingmore“row-rich”
![Page 39: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/39.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Where’sOurLeverage?LeverageMatrix
Confidential– OracleInternal/Restricted/HighlyRestricted 39
Method %of TimeinDataAcquisition
%of TimeinJoins
%of TimeinSort/Aggregate
ElapsedSeconds
PartitioningwithHCC&ZoneMaps(OCIExaCS)
57% 29% 14% 5
PartitioningwithHCC(OCIExaCS) 48% 51% 1% 13
PartitioningwithCompression (OCIDBCS) 81% 14.5% 5.8% 36
Partitioning(OCIDBCS) 79% 17% 4% 55
ScanswithHashJoins (OCIDBCS) 86% 12.24% 1.60% 187
IndexaccessandNLJoin(OCIDBCS) 99% .08% .17% 1,195
![Page 40: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/40.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Where’sOurLeverage?NumbersSoFar
Confidential– OracleInternal/Restricted/HighlyRestricted 40
![Page 41: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/41.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Agenda
WhyTableScans?
MakingScansSmaller
Where’sOurLeverage?
RearrangingtheData
ThingsWeCanDotoSpeedUpNextOperationinPlan
1
2
3
4
5
Confidential– OracleInternal/Restricted/HighlyRestricted 41
![Page 42: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/42.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
RearrangingtheData
• We’vedemonstratedthatparsingblocksconsumesCPUandcontributestoqueryelapsedtimeduringscans• OffloadingtoExadataprovidesusmoreCPUstoparseblocks• HowwouldIn-MemoryColumnarrepresentationimpactourresults?• Let’stestwithDatabaseIn-Memory
In-MemoryColumnar
Confidential– OracleInternal/Restricted/HighlyRestricted 42
![Page 43: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/43.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
RearrangingtheDataIn-MemoryColumnar
Confidential– OracleInternal/Restricted/HighlyRestricted 43
ScanwithPartitioningandIn-Memory(DBCloudService)
ScanwithPartitioning(DBCloudService,rowformat)
![Page 44: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/44.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
RearrangingtheDataIn-MemoryColumnar
Confidential– OracleInternal/Restricted/HighlyRestricted 44
ScanwithPartitioningand
DBIM
![Page 45: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/45.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
RearrangingtheDataIn-MemoryColumnarLeverageMatrix
Confidential– OracleInternal/Restricted/HighlyRestricted 45
Method %of TimeinDataAcquisition
%of TimeinJoins
%of TimeinSort/Aggregate
ElapsedSeconds
DBIMwith Partitioning(OCIDBCS) 18% 64% 18% 13
PartitioningwithHCC&ZoneMaps(OCIExaCS) 57% 29% 14% 5
PartitioningwithHCC(OCIExaCS) 48% 51% 1% 13
PartitioningwithCompression (OCIDBCS) 81% 14.5% 5.8% 36
Partitioning(OCIDBCS) 79% 17% 4% 55
ScanswithHashJoins (OCI DBCS) 86% 12.24% 1.60% 187
IndexaccessandNLJoin(OCIDBCS) 99% .08% .17% 1,195
![Page 46: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/46.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
RearrangingtheDataNumbersSoFar
Confidential– OracleInternal/Restricted/HighlyRestricted 46
![Page 47: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/47.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Agenda
WhyTableScans?
MakingScansSmaller
Where’sOurLeverage?
RearrangingtheData
ThingsWeCanDoforJoinsandAggregation
1
2
3
4
5
Confidential– OracleInternal/Restricted/HighlyRestricted 47
![Page 48: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/48.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
ThingsWeCanDoforJoinsandAggregation
• BloomFiltersprovidemeanstoefficientlyfilterdata,reducingthevolumeofdataforhashjoinsanddistributioninsubsequentplansteps• BloomFilterevaluationcanbepusheddowntoExadatasowecanleveragestoragecellCPUs• BloomFilterevaluationalsopusheddowntoIn-Memorycolumnstoreandabletousedifferent&moreefficientalgorithms• Let’stest
BloomFilters
Confidential– OracleInternal/Restricted/HighlyRestricted 48
![Page 49: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/49.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
ThingsWeCanDoforJoinsandsAggregationBloomFilters
Confidential– OracleInternal/Restricted/HighlyRestricted 49
ScanwithPartitioningand
DBIM
ScanwithBloomFilters,
PartitioningandDBIM
![Page 50: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/50.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
ThingsWeCanDoforJoinsandAggregationNumbersSoFarwithBloomFiltersonDBCSandExaCS
Confidential– OracleInternal/Restricted/HighlyRestricted 50
![Page 51: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/51.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
ThingsWeCanDoforJoinsandAggregationNumbersSoFarwithBloomFiltersonDBCSandExaCS
Confidential– OracleInternal/Restricted/HighlyRestricted 51
![Page 52: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/52.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
ThingsWeCanDoforJoinsandAggregation
• Pushdownaggregationtoscan• In-MemoryAggregation performsaggregationduringscan• Let’senableitandtest
In-MemoryAggregation
Confidential– OracleInternal/Restricted/HighlyRestricted 52
![Page 53: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/53.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
ThingsWeCanDoforJoinsandAggregationElapsedSeconds
Confidential– OracleInternal/Restricted/HighlyRestricted 53
![Page 54: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/54.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
ThingsWeCanDoforJoinsandAggregationCPUSeconds
Confidential– OracleInternal/Restricted/HighlyRestricted 54
![Page 55: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/55.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
ThingsWeCanDoforJoinsandAggregationLeverageChart
Confidential– OracleInternal/Restricted/HighlyRestricted 55
Method %of TimeinDataAcquisition
%of TimeinJoins %of TimeinSort/Aggregate
ElapsedSeconds
IMCwithIMAandPartitioning(OCIDBCS) 100% 0% 0% 1
IMC&Partitioning withBFsandZoneMaps(OCIExaCS) 28% 58% 14% 3
Partitioning withBFsandZoneMaps(OCIExaCS) 33% 50% 17% 4
Partitioning withBFs(OCIExaCS) 30% 50% 20% 6
IMCwithBFsandPartitioning(OCIDBCS) 30% 40% 10% 5
IMCwith Partitioning(OCIDBCS) 18% 64% 18% 13
PartitioningwithHCC&ZoneMaps(OCIExaCS) 57% 29% 14% 5
PartitioningwithHCC(OCIExaCS) 48% 51% 1% 13
PartitioningwithCompression (OCIDBCS) 81% 14.5% 5.8% 36
Partitioning(OCIDBCS) 79% 17% 4% 55
ScanswithHashJoins ((OCIDBCS) 86% 12.24% 1.60% 187
IndexaccessandNLJoin(OCIDBCS) 99% .08% .17% 1,195
![Page 56: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/56.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Bonus
• Wecan’tuseZoneMapswithAttributeClusteringonnon-Exadata,buthere’ssomething(relatively)freeintheCloud• Let’smanuallysortthedatatoleverageIn-MemoryMin-Maxpruning
SortingonDatabaseCloudService,In-Memory,In-MemoryAggregation
Confidential– OracleInternal/Restricted/HighlyRestricted 56
Lookfor”IMscanCUspruned”or“IMscanrowsoptimized”stats
![Page 57: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/57.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
FeaturesAvailability
Feature Oracle CloudInfrastructureDatabaseCloudService
Oracle CloudInfrastructureExadataCloudService
Partitioning
Hybrid ColumnarCompression
ZoneMapsand AttributeClustering
In-MemoryandIn-Memory Aggregation
BloomFilters
57
![Page 58: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/58.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Summary
• Thesedays,scanperformanceusuallyisn’taboutreducingI/O,it’saboutreducingCPU• Thereareanumberofwaystodothis• WereducedCPUfrom1,176secondsto1.7secondsforthesamequery• Wereducedelapsedtimefrom1,195secondsto1secondforthesamequery• Lookforleverage!• Don’tsettlefor“goodenough”
Confidential– OracleInternal/Restricted/HighlyRestricted 58
![Page 59: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/59.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
Summary
Confidential– OracleInternal/Restricted/HighlyRestricted 59
Whatcanyoudowithyoursystem?
![Page 60: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/60.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|
SafeHarborStatementTheprecedingisintendedtooutlineourgeneralproductdirection.Itisintendedforinformationpurposesonly,andmaynotbeincorporatedintoanycontract.Itisnotacommitmenttodeliveranymaterial,code,orfunctionality,andshouldnotberelieduponinmakingpurchasingdecisions.Thedevelopment,release,andtimingofanyfeaturesorfunctionalitydescribedforOracle’sproductsremainsatthesolediscretionofOracle.
Confidential– OracleInternal/Restricted/HighlyRestricted 60
![Page 61: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/61.jpg)
Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.| Confidential– OracleInternal/Restricted/HighlyRestricted 61
![Page 62: Optimizing Table Scans in the Cloud - Oracle User GroupPartitioning Confidential –Oracle Internal/Restricted/Highly Restricted 21 Although CPU-bound, I/O bytes is a proxy for CPU](https://reader034.vdocuments.us/reader034/viewer/2022050305/5f6d64e34c52061cab1c9b57/html5/thumbnails/62.jpg)