maximize database join and sort performance utilizing ... · maximize database join and sort...

•ManagedServices•CloudServices•ConsultingServices•Licensing

MaximizeDatabaseJoinandSortPerformanceUtilizingExadataFlash

KaseyParkerSr.EnterpriseArchitectKasey.Parker@centroid.com

ManagedServices

CloudServices

ConsultingServices

Licensing

Leader in Technology, Consulting and Managed Services since 1997• Part of Oracle’s Top 25 Strategic Partner Program • Focused on Consulting, Managed Services, Cloud Services and Resell • Capabilities From Oracle Applications to Technology to Infrastructure• Oracle’s Tech and Engineered Systems Partner of the Year: 2014 & 2015

SpecializationsOracle Database & Core TechnologiesOracle Engineered SystemsOracle Server & Storage SolutionsOracle Performance TuningOracle Data WarehousingOracle Business IntelligenceOracle Applications

Top 25 Strategic Partner

CentroidOverview

AboutKaseyParker

§ ResideinFrisco,TXwithwifeand5children§ OracleArchitect/DBA

§ Over15YearsofOracleExperience§ OracleCertifiedExadataSpecialist§ OracleCertifiedProfessional- DBA§ PerformanceTuningspecialist§ DataWarehousingspecialist§ Last5yearsfocusedonOracleEngineeredSystems

§ Manyindustries§ FinancialServices,Manufacturing,Health/Nutrition,Government,Retail

§ UtahOracleUsersGroup(UTOUG)BoardMember§ Academic

§ BrighamYoungUniversityAlumnus§ B.S.inManagementInformationSystems

QUICKFACTS

Agenda§ Exadata Overview§ WhyExadata?§ ExadataFlash

§ SmartFlashCache§ Flash-basedGriddisks

§ UsingFlashforTemptospeedupjoins&sorts§ Risk/Reward§ Howtoconfigure

§ Q&A

EXADATA OVERVIEW

ExadataArchitecture– X6Complete|Optimized|FullyRedundant|Scale-Out

Scale-OutDatabaseServers• 2-socketserverè 44cores,256GBto768GBDDR4DRAM

or• 8-socketserverè 144cores,upto6TBDRAM• OracleDatabase,ASM,RAC, OracleLinux

Scale-Out,2-socketIntelligentStorageServers• 20Xeoncores/serverenablesDBoffloadtostorage• ExtremeFlashStorageè 8x3.2TBPCIFlashDrives

or• HighCapacityStorageè 4x3.2TBPCIFlashCards+

12x8TBSASdrives

High-SpeedInfiniBandNetwork• Unifiedinternalconnectivity(40Gb/sec)• 10Gbor1GbEthernetdatacenterconnectivity

SlideMaterialcourtesyofOracle

ElasticConfig:IncrementalScaleOutAchieveanyLevelofPerformancewithMinimumHardware

Multi-Rack

Database Server

FullRack

• EnableDBCPUcoresasneededwithCapacityonDemand• ExpandolderExadatamachineswithnewservers

ExtremeFlashStorage

High-CapacityStorage

StartSmall2DBServers

3StorageServers

IncrementallyaddDBorStorageServers

AddRackstoContinueScaling

25.6TBPCIFlash20CPUCores

44CPUCores

12.8TBPCIFlash96TBDisk

20CPUCores

WorkloadOptimizedConfigurations

16DatabaseServers+

5 HighCapacityStorageServers

DBIn-MemoryMachine ExtremeFlashOLTPMachine

8DatabaseServers+

8ExtremeFlashStorageServers

DataWarehouseMachine

8DatabaseServers+

14HighCapacityStorageServers

WantsmanyDBServersfewStorageServers

All-flashIOPsenablescapacitybasedOLTPsizing

MoreHighCapacityStorageforlongerdataretention

SlideMaterialcourtesyofOracle

• All OracleDatabaseEEfeaturesandoptions

• ExtremeperformancewithIn-memoryforOLTP,analytics

• Proven Exadataperformanceandtechnologies

• Businesscriticalavailabilityandsecurity

• Easyno-riskmigrationforpublicorhybridcloud

• Zero infrastructuremanagement

• NoCapEx,lowOpExwithmonthlysubscription

FINANCE

CALLCENTER

SUPPLYCHAIN

ExadataintheCloud

ExadataX6-2StandardConfigsX6-2 Full X6-2 Half X6-2 Quarter X6-2 Eighth

Database Servers 8 4 2 2

Database Grid Cores 352 (min 112) 176 (min 56) 88 (min 28) 44 (min 16)

Database Grid Memory (GB) 2048 (max 6144) 1024 (max 3072) 512 (max 1536) 512 (max 1536)

InfiniBand switches 3 3 2 2

Ethernet switch 1 1 1 1

Exadata Storage Servers 14 7 3 3

Storage Grid CPU Cores 280 140 60 30

Raw PCI Flash CapacityEF 358.4 TB 179.2 TB 76.8 TB 38.4 TB

HC 179.2 TB 89.6 TB 38.4 TB 19.2 TB

Raw Hard Disk CapacityEF N/A N/A N/A N/A

HC 1344 TB 672 TB 288 TB 144 TB

Usable mirrored capacityEF 130 TB 65 TB 27.9 TB 13.9 TB

HC 508.3 TB 254.2 TB 108.9 TB 54.5 TB

Usable Triple mirrored capacity

EF 102 TB 51 TB 21.9 TB 10.9 TB

HC 398.7 TB 199.4 TB 85.4 TB 42.7 TB

Use(OECA)forotherconfigurations

ExadataX6-2StandardConfigsX6-2 Full X6-2 Half X6-2 Quarter X6-2 Eighth

Database Servers 8 4 2 2

Database Grid Cores 352 (min 112) 176 (min 56) 88 (min 28) 44 (min 16)

Database Grid Memory (GB) 2048 (max 6144) 1024 (max 3072) 512 (max 1536) 512 (max 1536)

InfiniBand switches 3 3 2 2

Ethernet switch 1 1 1 1

Exadata Storage Servers 14 7 3 3

Storage Grid CPU Cores 280 140 60 30

Raw PCI Flash CapacityEF 358.4 TB 179.2 TB 76.8 TB 38.4 TB

HC 179.2 TB 89.6 TB 38.4 TB 19.2 TB

Raw Hard Disk CapacityEF N/A N/A N/A N/A

HC 1344 TB 672 TB 288 TB 144 TB

Usable mirrored capacityEF 130 TB 65 TB 27.9 TB 13.9 TB

HC 508.3 TB 254.2 TB 108.9 TB 54.5 TB

Usable Triple mirrored capacity

EF 102 TB 51 TB 21.9 TB 10.9 TB

HC 398.7 TB 199.4 TB 85.4 TB 42.7 TB

Use(OECA)forotherconfigurations

ExadataX6-2SQLIOPerformance

1- BandwidthispeakphysicalscanbandwidthachievedrunningSQL,assumingnocompression.Effectivedatabandwidthwillbemuchhigherwhencompressionisfactoredin.2- IOPS– BasedonreadIOrequestsofsize8KrunningSQL,typicallywithsub-millisecondlatencies.NotethattheIOsizegreatlyeffectsflashIOPS.OthersquoteIOPSbasedon2K,4KorsmallerIOsthatarenotrelevantfordatabasesandmeasureIOsusinglowleveltoolsinsteadofSQL.3- ActualPerformancevariesbyapplication.

X6-2Full Rack

X6-2 Half Rack

X6-2Quarter

X6-2 Eighth

Flash (Cache)SQL Bandwidth1,3

Extreme Flash 350 GB/s 175 GB/s 75 GB/s 38 GB/s

High Capacity 301 GB/s 150 GB/s 64 GB/s 32 GB/s

Flash SQL IOPS2,38K Reads 4,500,000 2,250,000 1,125,000 562,500

8K Writes 4,144,000 2,072,000 1,036,000 518,000

Disk SQL Bandwidth1,3

Extreme Flash N/A N/A N/A N/A

High Capacity 25 GB/s 12.5 GB/s 5.4 GB/s 2.7 GB/s

Disk SQL IOPS2,3Extreme Flash N/A N/A N/A N/A

High Capacity 36,000 18,000 7,800 3,900

WHY EXADATA?

WhyExadata?

Exadatadesignedtoeliminatethemostcommonbottleneckforlargedatabases…

IOperformancefromstoragetodatabase

WhyExadata?SolvingtheIOBottleneckSolution1:Enlargethepipe

• Physicaldisks,onallcells,workinparalleltoserveIOrequests• LargeInfiniband pipe(40GB/Sec)

Can’twedothatwithotherhigh-performancestoragesolutions?

YES…NothingMagicalaboutExadatahardware,andit’sstillthesameOracleDatabase

WhyExadata?

WhyExadata?SolvingIOBottleneckSolution2:ReduceIOoperations

• Exadata’s SecretSauce:StorageOffloading,SmartFlashCacheandHybridColumnarCompression(HCC)

• 10Xreductionindatasenttodatabaseserverscommon

EXADATA FLASH

SmartFlashCache• CachesReadandWriteI/OsinPCIflash• Transparentlyacceleratesreadandwriteintensive

workloads• Dual-formatColumnarFlashCache• Persistentwritecachespeedsdatabaserecovery• ExadataFlashCacheismuchmoreeffectivethan

flashtieringarchitecturesusedbyothers– Cachescurrenthotdata,notyesterday’s– Cachesdataingranules8xto16xsmallerthantiering

• Greatlyimprovestheeffectivenessofflash

4.5Million8KRead4.1Million8KWriteIOPSfromSQL

OtherFlashFeaturescanbeconfiguredifneededE.g.Cachecompression,Cachepinning,FlashDisks(forTemp)

SmartFlashCache• CachesReadandWriteI/OsinPCIflash• Transparentlyacceleratesreadandwriteintensive

workloads• Dual-formatColumnarFlashCache• Persistentwritecachespeedsdatabaserecovery• ExadataFlashCacheismuchmoreeffectivethan

flashtieringarchitecturesusedbyothers– Cachescurrenthotdata,notyesterday’s– Cachesdataingranules8xto16xsmallerthantiering

• Greatlyimprovestheeffectivenessofflash

4.5Million8KRead4.1Million8KWriteIOPSfromSQL

OtherFlashFeaturescanbeconfiguredifneededE.g.Cachecompression,Cachepinning,FlashDisks(forTemp)

SmartFlashCache– ComingSoonAnnouncedatOOW2016– foraFutureExadataRelease• WhenquerieshavehightempIOandbecomebottleneckedondisk

– SmartflashcacheintelligentlycachestempIO– Writestoflashfortempreduceselapsedtime– Readsfromflashfortempreduceselapsedtimefurther

• Oncereleasedandtested,mayremoveneedforcreatingflash-basedgriddisksfortemp

FlashBasedCellDisks

Usage• SmartFlashCache

– Usesallavailablespacebydefault– Managedautomaticallyformaximumefficiency

• Flash-basedGridDisks– Premium,persistentdatabasestorage– Requiresdeliberateplanningforefficientusage– Onepotentialusecaseisfortemptablespace

WhyUseFlashforTemp?• Elapsedtimegreatlyreducedforstatementsbottleneckedby

tempI/O– Evenwell-tuneddatawarehousesoftenhavehighTempI/O– Particularlyrelatedtolargehashjoins

• OffloadsIOPSfromharddisks– ImprovestempI/OperformanceandfreesupHDDI/Ocapacity

• BydefaultExadatadoesnothingtospeeduptempI/O– Nostoragecelloffloadingfortemp– Flashcachenotusedfortempoperations(yetJ)– Mustuseflash-basedgriddiskstouseflashfortemp

• NewerExadatahaveenoughflashforflashcacheandtemp

CaveatsEvaluatethetrade-offsanddetermineiftemponflash-basedgriddisksisrightforyourenvironment• Reducedflashcachesize• Redundancyrequirementsfortemptablespace

– Externalredundancycarriesavailabilityrisk– evenfortemp– Normalredundancyrequiresusingdoubletheamountofflash

• Mayrequireadditionalmaintenanceduringpatching• Isyourdatabaseevenbottleneckedbytempoperations?

BenefitsCaseStudy1:• LargeorganizationinUtah• DataWarehouserunningonExadataX2-2¼rack(1TBflash)• TempI/Osignificantbottleneck

• TempreadandwriteI/O4th and5th topwaiteventsonDB• Significantimprovementaftermovingtempto340GBflashdisk

• DroppedtempI/Ooutofthetop10waitevents• TempI/Olatencyreduced8X• TempheavySQLsawaverageof3Xperformanceimprovement

BenefitsCaseStudy2:• LargeorganizationinMichigan• DataWarehouserunningonExadataX5-2Fullrack(89TBflash)• Significantimprovementaftermovingtempto9TBflashdisk:

• DramaticperformancegainontempheavyETL:• OneJobReducedfrom17.5hoursto2.7hours(6.5Ximprovement)• AnotherJobReducedfrom6.5hoursto2.1hours(3.1Ximprovement)

• BIteamsawover2.5XperformancegainonreportsoverallReport HDD Temp FlashTemp Improvement

1 99 38 2.6 X2 12 4 3X3 10 3 3.3 X4 794 217 3.7X5 894 233 3.8X6 5 1 5X

CreatingTempTablespace onFlash1) Determinesizeofflash-basedgriddisk2) DropFlashCache3) RecreateFlashCacheatnewreducedsize4) Createtheflashgriddisk5) CreateASMDiskgroup ontheflashgriddisk6) Createtemporarytablespace onthenewdiskgroup7) Alteruserstousethenewtemptablespace

CreatingFlash-basedGridDisks

CELLCLI> list celldisk detail

size: 3.6049652099609375T

...(12totalharddisksdisksonacell)name: FD_00_cm01celadm01

diskType: FlashDisksize: 1.455474853515625T

...(4 totalflashdisksonacell)

FindCurrentCelldisk detailusingCellCLI:

CELLCLI> list flashcache detail

...(thisisperstoragecell)

FindCurrentFlashcache detail:

Note:Tokeepdatabasesonline,doallstepsonecellatatime;otherwise,candostepsinparallel

1)Calculatethenewflashcache size:• E.g.Need9728GBforflashgriddiskonX5fullrack

• 5961G(originalflashcache)*14(#cells)– 9728G(flash fortemp)=73726G

• 73726GBavailable for flash cache• Divide by number of cells (e.g.14)=5266.14GBflashcacheper

oracle@cm01dbadm01 ~]$ dcli -c cm01celadm01 cellcli -e drop flashcache

cm01celadm01: Flash cache cm01celadm01_FLASHCACHE successfully dropped

2)DropFlashCache:• Ifkeepingdatabasesonlinedoonecellatatime,

otherwisecoulddoinparallel• Ifwrite-backflashisenabledwillneedtoflushfirst

• cellcli >alterflashcache allflush

[oracle@cm01dbadm01 ~]$ dcli -c cm01celadm01 cellcli -e create flashcache all size=5.1426566t

cm01celadm01: Flash cache cm01celadm01_FLASHCACHE successfully created

3)CreateFlashcache atnewsize:• Totalflashminusthesizeofthenewgriddisk

• VerifyflashcachestatusisnormalCELLCLI> list flashcache detail

[oracle@cm01dbadm01 ~]$ dcli -c cm01celadm01 cellcli -e create griddisk all flashdiskprefix=flash

cm01celadm01: GridDiskflash_FD_00_cm01celadm01 successfully created

cm01celadm01: GridDiskflash_FD_01_cm01celadm01 successfully created

cm01celadm01: GridDiskflash_FD_02_cm01celadm01 successfully createdcm01celadm01: GridDiskflash_FD_03_cm01celadm01 successfully created

4)CreatenewFlash-basedGridDisks

CreatingFlash-basedGridDisksRepeatSteps2-4forotherstoragecells• E.g.

• dcli-ccm01celadm02cellcli -edropflashcache• dcli-ccm01celadm02cellcli -ecreateflashcache all

size=5.1426566t• dcli-ccm01celadm02cellcli -ecreategriddisk allflashdisk

prefix=flash• …andsoonforeachofthestoragecells

CreateFlash-basedASMDiskgroup

SQL> create diskgroup FLASH_TEMP

normal redundancy disk 'o/*/flash*'attribute

'compatible.rdbms'='11.2.0.4.0','compatible.asm'='12.1.0.2.0',

'cell.smart_scan_capable'='TRUE',

'au_size'='4M';

Diskgroup created.

1) LoginviaSQLPlus toASMinstanceassysasm2) Createnewdiskgroup ontheflashgriddisk

SELECT GROUP_NUMBER AS GRP_NUM,

STATE,

TOTAL_MB,

FREE_MB, USABLE_FILE_MB,

ROUND((CASE WHEN (TOTAL_MB != 0) THEN FREE_MB / TOTAL_MB ELSE 0 END), 2)*100 || '%' PERCENT_FREE

FROM V$ASM_DISKGROUP

ORDER BY 1;

3)Mountnewdiskgroup onallASMinstances• First,logintoeachASMinstanceandverifystate• Shouldalreadybemountedoninstancecreatedon

SQL> SELECT GROUP_NUMBER AS GRP_NUM, NAME, STATE, TOTAL_MB, FREE_MB FROM V$ASM_DISKGROUP ORDER BY 1;

GRP_NUM NAME STATE TOTAL_MB FREE_MB

------- ------------ ------------ ----------- -----------

4 FLASH_TEMP DISMOUNTED 0 0

3 DATAC1 MOUNTED 571490304 409207004

2 DBFS_DG MOUNTED 4845120 4735532

1 RECOC1 MOUNTED 63555072 25188236

Note:QueryallASMinstancesatonceusingGV$ASM_DISKGROUP

OnOtherASMinstancesthediskgroup isdismounted…

SQL> alter diskgroup FLASH_TEMP mount;

Diskgroup altered.

SQL> SELECT GROUP_NUMBER AS GRP_NUM, NAME, STATE, TOTAL_MB, FREE_MB FROM V$ASM_DISKGROUP ORDER BY 1;

GRP_NUM NAME STATE TOTAL_MB FREE_MB

------- ------------ ------------ ----------- -----------

1 DATAC1 MOUNTED 571490304 409207004

2 DBFS_DG MOUNTED 4845120 4735532

3 RECOC1 MOUNTED 63555072 25191856

4 FLASH_TEMP MOUNTED 9961728 9959904

Mountdiskgroup oneachASMinstance…

CreateFlashTempTablespace

SQL> CREATE TEMPORARY TABLESPACE FLASH_TEMP TEMPFILE'+FLASH_TEMP' SIZE 20480M AUTOEXTEND ON NEXT

4096M MAXSIZE 20480M,

'+FLASH_TEMP' SIZE 20480M AUTOEXTEND ON NEXT 4096M MAXSIZE 20480M,

TABLESPACE GROUP ''

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64M/

Tablespace created.

MoveUserstoTempTablespace

SQL> ALTER USER SH TEMPORARY TABLESPACE FLASH_TEMP;

User altered.

Recommendusingonlyforuserswhoneedit

Conclusion

WhentouseExadataFlashforTemptablespace?• Needtospeeduplargehashjoinsandsorts• IOisbottlenecked• SignificantportionofIOisfromTempoperations• TypicallysuitedmoreforDWthanOLTPworkloads

– BecauseDWworkloadstypicallydon’tuseflashcacheasmuchanddrivelargertempoperations

• NewerExadataversions– havemoreflashTEMP

Questions?

maximize database join and sort performance utilizing ... · maximize database join and sort...

Documents

maximize your database for fundraising success (neahp)

simple sort algorithms selection sort bubble sort insertion...

maximize your database for fundraising success (inapef)

sorting algorithms bubble sort merge sort quick sort...

filemaker server 10 advanced maximize your database sharing

sorting - sjiang1.github.io · sorting •selection sort...

response project database - nps and related ......11 ms...

chapter 7: sorting algorithms insertion sort. sorting...

bbcon 2012 - maximize your school's website & leverage...

3 - maximize availability with oracle database 12c ·...

overview of excel object model isys 562. excel’s database...

a study of sort algorithms for multiprocessor database...

oracle filev$transaction oracle 12c database. oracle –12c...

cps216: advanced database systems notes 06:query execution...

pengurutan -...

maximize availability · maximize availability with oracle...

maximize availability with oracle database 12c

sorting routines. objectives introduction bubble sort...

maximize availability with oracle database 11g release 2...

maximize availability with oracle database 12c · maximize...