oracle database: processing engine or persistence layer? · • oracle’s fusion applications/saas...

Copyright©2018, Oraclend/oritsaffiliates.Allrightsreserved.|

OracleDatabase:ProcessingEngineorPersistenceLayer?

JohnClarkeSoftwareDevelopmentDirectorReal-WorldPerformanceOracleServerTechnologies

Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|

SafeHarborStatementThefollowingisintendedtooutlineourgeneralproductdirection.Itisintendedforinformationpurposesonly,andmaynotbeincorporatedintoanycontract.Itisnotacommitmenttodeliveranymaterial,code,orfunctionality,andshouldnotberelieduponinmakingpurchasingdecisions.Thedevelopment,release,andtimingofanyfeaturesorfunctionalitydescribedforOracle’sproductsremainsatthesolediscretionofOracle.


BridgingtheDividefromToday’sPerformancetoWhatisPossibleWhatisReal-WorldPerformancein2018?


Real-WorldPerformance2018

• PartoftheDatabaseDevelopmentOrganization• GlobalTeamlocatedinUSA,Europe,Asia• 350+combinedyearsofOracledatabaseexperience• InnovatetoachieveexceptionalDatabasePerformance• Ourmethods:

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

WhoWeAre


Areyouhappywithincrementalperformanceimprovements?

Doyouhavetheabilitytochoose yourperformance?

Doyoumovedatatoprocessingorprocessingtodata?


Acknowledgements

• ToonKoppelaars,RWP– Performedmuchoftheresearch&authoredmuchofthecontent– Leads#SmartDBchargefromReal-WorldPerformance

• BrynLlewellyn,PLSQLProductManager• ManyotherRWPengineersovertheyears• Oracle’sFusionApplications/SaaSteams,designs,andsolutions,whichmotivatedustodotheresearch


IntroductionIntroduction

LayeredArchitecture:History,Landscape,andIssues

DemosandTechnicalStuff

BigPicture

ConcludingThoughts

1

2

3

4

5


ApplicationArchitecture

§ Ifyouhadtovalidateasinglerowonawebform,howwouldyoudesignyourapp?

§ Whatifyouhadtovalidatemillionsofrowsthatarrivedasabatch?

§ Doyoudothis?Design(Manyrows)=Design(Onerow)*#Rows

HowDoYouDesignapps?


SomeContext• Thedatabase->aprocessingengine,orpersistencelayer?• OLTP:transactionalenterpriseapplications– Datastoreasfoundation, forwhichweuseOracle’sRDBMS–Much/complexCRUDfunctionalityontop– Userinterfaces,workflows,batchjobs,reports,API'stootherapplications– Potentiallymanyusers

• OLTPtechniquesforbatchprogramming• Not:data-warehouse,business-intelligence,big-data– ButCSconcepts applicable


TransactionalEnterpriseApplications

• Abigcomponentoftheseapplicationsis“BusinessLogic”

• Whatis“BusinessLogic”?


TransactionalEnterpriseApplications

• Conceptuallythreetiers

– Functionalityexposedviainterfaces• GUI'sforhumaninteraction• REST,Soaporotherwise,forsoftwareinteraction

– Businesslogic

– Datastore,relationaldatabase

UserInterfaces

SoftwareInterfaces

BusinessLogic

TableDataStore


Wiki

BusinessLogic

SQLSQLSQL

SQLSQL

SQL

Conditional“If-then-else-loop”codewithembeddeddataaccessstatementsinit

Thewaythebusinessrequiresthistobedone


WeSeeTwoMutuallyDistinctApproachesRDBMS=PersistenceLayer“LayeredArchitecture”

RDBMS=ProcessingEngine“#SmartDB”

SQL+/- PL/SQL=“If-then-else-loop”

languageused

Somethingelse=“If-then-else-loop”languageused


Aquickpoll• InyourtypicalDBapplication,whatwouldbethedistributionofCPUworkbetweenBLandSQL?

20% 50% 80%

80% 50% 20%


EverSeenThis?

Constitutesthebyte-streamthatisyourexecutableprogram

thatgetsrunbytheCPUMachinecodemnemonics

ProgramCounter

Clock-ticksrequiredforinstruction

Conditionalbranching/looping

Datamanipulation


WhyDidIShowThat?

• Re-introductionofterm“if-then-else-looplanguage”• Startthinkingabouthowcomputersaccomplishthetasksthey’reaskedtoperform…


“If-then-else-loop”Languages• Everycompiledprogramtranslatestomachinecodewhichalwaysissequenceof:–Movingdatabetweenmainmemoryandon-coreregisters– Changingvaluesintheseregisters– Computingnewvaluesusingtheseregisters– Comparingvaluesinregisterswithliteralsand/orotherregisters– Conditional(orunconditional)branching

• Allprogramlanguages(PL/SQL,Java,JavaScript,…)arejusthigherlevelsofabstractiontothis=if-then-else-loop languages


ExampleBusinessLogic:CodeWithEmbeddedSQLSingle-rowdataaccess

Businesslogic

Businesslogic

Rowfetching(dataaccess)

Businesslogic

Row-by-rowupdating (dataaccess)

“if-then-else-loop”code

Primitivedataaccess(single table,row-by-row)


Fornow,we’regoing toassumethisishowitappears

ExampleBusinessLogic:AllinSQL

Pointtobemadeà Businesslogiccanappear:– Ascode-linesinsomeprogramminglanguagethatissuessimple(poor)SQL– Aspartof(rich)SQL– Both…

Set-baseddataprocessing, aka“rich”SQLMultiple tablesreferencedMultiple rowsprocessed

Youdeclarewhatneedstobedone.

SQLexecengine&optimizer translatethisintocodeequivalentwithcode

onpreviouspage



Introduction



BigPicture

ConcludingThoughts

1

2

3

4

5


Oraclev4,v5,v6DatabaseDocumentation


Oracle7,8i:DatabaseDocumentation


HistoryObservation:Endof"SmartDB"Era

1990 1995 2000 2005 20152010

Oracle7

Reignof"SmartDB"eraDB=processingengine

Riseof”LayeredArch"eraDB=persistencelayer

Featuresavailable inDB

1985

DB-featuresused byapplicationdevelopers

Adventof J2EEandMVCframeworks

CollapseofJEE

Adventof JavaScriptframeworks

Featurerichness


WhereWereWeatEndof1990's?• Applicationscapitalizedondatabasebeingaprocessingengine

Thinclient:UI-only

Tablestypicallyneveraccesseddirectly

StoredPL/SQLmodulesandviews BusinesslogicdonewithPL/SQLandrichSQL

RDBMS Properlydesigned relationaldatabaseschemadecoratedwithdeclarativeconstraints

Callsto“smartdataservices”


WhatHasHappenedSince?• Databasetoonlyfulfillpersistencelayerrole(bitbucket)

Directaccesstoalltables

JDBCPersistence-fwModel-fwBusiness-fwControl-fwUI/View-fw

Generated/used tables(topersistobjecthierarchy)oftenwithoutconstraints

Model-View-Controller (MVC)frameworkeraduring

1st decadeofnewmillennium

Allbusiness logicinapplicationserverbasedonhierarchical/network

domainmodelPoorSQL issued toDB


WhatisaLayeredSoftwareArchitecture?

§ Relativelycommonarchitecture

§ "n-Tier"architecture

§ StandardformostJavaEEapplications

§ Widelyusedbyarchitects,designers,developers


WhatisaLayeredSoftwareArchitecture?

§ Organizedintohorizontallayers

§ Eachlayerperformsspecificrole

§ Mostconsistof4-5majorlayers

§ Layersgenerallyincludepresentation,businesslogic,model,persistence,anddatabase


WhyaLayeredSoftwareArchitecture?§ Layersaretypicallyclosedandexpose

API'sforinvocation

§ EachlayermustgothroughlayerAPI'sdirectlybelowit;enforcesisolation

§ Isolationenablesseparationofconcernsandlayerindependence

§ Makesiteasytore-use,build,and(theoretically)test andmaintainapplication


PresentationLayer

BusinessLayer

ModelLayer

PersistenceLayer

DatabaseLayer

ImplicationsofaLayeredSoftwareArchitecture§ Allapplicationlogicbuiltinlayers

outsidedatabase

§ SQLishiddenfor/fromdevelopers

§ BottomlayerstranslateGUIstructurestorelationaldata,oftengeneratingrow-by-rowSQLstatements

§ Database=(dumb)tablestoreor"bitbucket",notaprocessingengine

SQL


ImportantPointstoMake(1/2)• InlayeredMVCapproachSQLisinvisible• AlmostalwaysSQLishiddenfromdevelopers–Objectorienteddomainmodelsareused– Developersinvokemethodsonobjects–Objectsmaptotablesviapersistenceframework(ORM)• ObjectRelationalMapping tools

• Commonfor Moderndevelopers to know Java,not SQL

• ORM'sproducesingle-row(orcolumn),single-tableSQL– Incontrasttorich-SQL Directaccesstoalltables

JDBCPersistence-fwModel-fwBusiness-fwControl-fwUI/view-fw


ImportantPointstoMake(2/2)• Doingeverythingwithsingle-row,single-tableSQLresultsin"chatty"applications– Especiallyforbatchprocesses– Think:50K-200KDB-calls/second

• In‘90swereferredtothisas"roundtrips"– Roundtripswerebad(forperformance)then,andstillaretoday• Badthenbecauseofnetwork-latency(now:becauseofCPUrequired)

–Oracle7,withstoredPL/SQL,helpedusmitigatethis– Bymovingbusinesslogicintodatabase

Lotsandlotsofcallsgoingbackandforth

I’llcomebacktothisshortly


“Chatty”Applications• MovingbusinesslogicintostoredPL/SQL,reduces#roundtrips

Hire_EmployeeBusinessLogic

TableDataStore

UI

SQL SQL SQL SQL SQL

Hire_EmployeeBusinessLogic

TableDataStore

UI

SQL SQL SQL SQL SQL

Fiveroundtrips betweenprocesses/machines

FiveSQLcallswithincontextofsingleprocess

Oneroundtripbetweenprocesses/machines

Layered SmartDB


NewParadigmShiftHappening:Javaà JavaScript• Server-side JavaMVC-frameworksapproachhasbeenubiquitous

• Newarchitectureisarising:– Browser-side JavaScript(V+C)– Server-side JavaScript(M)– RESTtoglueittogether

– Databasestill aspersistencelayer

• Inasense,thisisjustclient/serveralloveragain– ResponsiveUIrunningonclient:browserhasControl– Smartdataservicesrunningonserver(JVM)

Directaccesstoalltables

JDBCPersistence-fwModel-fwBusiness-fwControl-fwUI/View-fwREST

JVM


We’veNowTrulyGoneFull-Circle…

Javascript/Ajax/DhtmlPartialpagerefreshV+Cbackintobrowser“Client/Server2.0”...1990Stateless

Stateful

Character Graphical

2000-2010

1995-2000

Nomoreresponsiveness

1990-1995

ß Client-servereraà


RealWorldExample

Thereareanumberoflayereddevelopmentframeworksavailable

DevelopmentFrameworkDecisions


LayeredArchitecturewithHibernate

PresentationLayer

BusinessLayer

ModelLayer

PersistenceLayer

DatabaseLayer

Table/DMLclasses Validation

HibernateDAO'sDataAccessObjects

BusinessentityclassesUsableinpresentationlayer

UI

TABLES


LayeredArchitecturewithOracleADF

PresentationLayer

BusinessLayer

ModelLayer

PersistenceLayer

DatabaseLayer

ADF-BC ViewObjects(VO's)

ADF-BC EntityObjects(EO's)

ADF-BC ApplicationModule(AM)

UI

TABLES


IssueswithLayeredArchitectures

1. Stabilityoftechnologystack2. Developmentandmaintenancecost3. Performanceandscalability


Reminder…

Thisisthe“stable”factor”overtheyears- everythingelseontopofit,hasnotbeen…


StabilityofTechnologyStack– JavaMVC• Javaframeworkscameandwentmuchfasterthandidourapplications

BC4J/ADF-BC

12

MVCusedhere

TotallydifferentfromMVCused

herePL/SQLandSQL

usedhere

IsstillPL/SQLandSQL

usedhere


StabilityofTechnologyStack– JavaScriptMVC

MVCusedhere

Istotallydifferent fromMVCusedhere

RiseofJavaScripteverywhere

Technologyvolatilitycurrentlyworseinemerging

newworld

JavaScriptusedhere

IstotallydifferentfromJavaScript

usedhere

2020


Issue1:StabilityofTechnologyStack• Iflayersinyourchosentechnologystacksarevolatile…Thenyououghttousethem"thinly"– I.e.donotdobusinesslogicinthem– Instead,pushbusinesslogicfurtherdownintocode-stackwherestablelayersexistWhy?Enablesagilityà it’llbeeasier/cheapertodealwiththevolatility

• Butnobodyhasbeendoingthat…Wehavebeencreatingcementedmaintenancenightmaresinpast15years

• Prediction:PL/SQLandSQLwillstillbehere10yearsfromnowwhenJavaScript'sreignends


Issue2:DevelopmentandMaintenanceCosts• Issueismultifaceted

– Layeredtechnologystacksarecomplex

–Wheelsarereinvented

– IsOOagoodfitforbusinesslogicofdatabaseapplications?


Hibernate…• “Ihadtolearn Hibernate architecture,configuration,logging,namingstrategies,tuplizers,entitynameresolvers,enhancedidentifiergenerators,identifiergeneratoroptimization,union-subclasses,XDoclet markup,bidirectionalassociationswithindexedcollections,ternaryassociations,idbag,mixingimplicitpolymorphismwithotherinheritancemappings,replicatingobjectbetweentwodifferentdatastores,detachedobjectsandautomaticversioning,connectionreleasemodes,statelesssessioninterface,taxonomyofcollectionpersistence,cachelevels,lazyoreagerfetching andmany,manymore.”

https://www.toptal.com/java/how-hibernate-ruined-my-career


MVCTechnologyStacksAreComplex• Witnessedmanyprojects:both#SmartDBandLayered/MVC• DisproportionallargeamountoftimespentongettingframeworksknownandthensetupandworkalltogetherManydiscussionson"howtodothis?","howtodothat?"

• (Counterargument?):Moderndeveloperstaughtto”plug”• Muchtimeisspenton"plumbing"code– Codethatdoesn’taddvaluetothebusiness

• SmartDBdevelopersproportionallyspendmoretimeonwhatend-userscarefor,onwhatisuniquetoapplication:itsbusinesslogic

JDBCPersistence-fwModel-fwBusiness-fwControl-fw

UI-fw


WheelsAreReinvented• Bothbyframeworksaswellasbydevelopers

– Transactionmanagement,cachesynchronization,read-consistency,security,…

–Do-it-yourself:joining,set-operations,grouping,sorting,aggregation,…

• Allavailableout-of-the-boxinsidedatabaseordeclarativelyviaSQL


IsObjectOrientation(OO)aGoodFit?• Exampleuse-case:fundstransferInputs:source-account,target-account,transfer-amount– Performvalidationsoninputvalues– Applyvarious"businessrules"• Lookupcustomer-typeandapplytypespecificpolicies• Lookupaccount-typeandapplytypespecificpolicies• Validateenoughfundsavailablefortransfer

– Perform/transactfundstransfer– Logtransactionincludingpoliciesapplied

• InessencenothingOO-ish aboutbusinesslogicNaturalfit=implementationviasomeif-then-else-looplanguagePreferablyonethatdoesSQLreallywell:thinkPL/SQL

SequentialproceduralcodewithembeddedqueriesandDML


Issue3:PerformanceandScalability• "Databaseisalwaysbottleneck",sohere'stheLayered/ORM/MVCpromise:

– GetdatafromDBonceintomid-tiercaches– Thenre-usemanytimes inbusinesslogicrunninginhorizontallyscalablemid-tierservers–WritedatabacktoDBonce

• AlwaysmajorargumenttorejectSmartDBapproach

“SmartDBapproach(runningBLinDB)willsaturatedatabaserealquick““Itwon’tscale”


PerformanceandScalability• Howeverinreal-world:– Hardlyeverseethesecacheddata"re-uses“à theseapplicationsare*always*chatty– Dataread+manipulatedonce,thenwrittenback,andnotusedagainwhileincache– Where'stheadvantagethen?

• Also:– Instantiatingobjectsforrows,takesalotofmemoryandCPU

Dataisalwayscachedinmultiplelayers:JDBC,ORM,modelframework,BusinessLogicmodules– Cacheddatavolumesbecomesobigthatcachesneedtoflushdatapre-maturely

• Also,partII:– Whereismytimebeingspent?Moreonthislater…

• Btw:dataisalreadycachedveryefficientlyatdatabasetier…



Introduction



BigPicture

ConcludingThoughts

1

2

3

4

5


SummaryHere• OriginalstoryatOracleLearningLibrarychannelonyoutubehttps://www.youtube.com/watch?v=8jiJDflpw4YSearch:"toon koppelaars"

Figureout


Our(First)Experiment• Event-processingmodulebasedonreal-worldexample– BuiltusingSmartDBapproach:PL/SQLstoredprocedurewithembeddedSQL– Builtusing layered approach:JavawithembeddedSQLontopof(thin)JDBCBothbuiltusingrow-by-rowsimple/poorSQLpattern

• Straight-forwardbusinesslogic:if-then-else,looping

• Withtypicalloadprofilethatweseeallthetime:–Manysingle-rowSQLstatements,mixofreadsandwrites– Indexmaintenance

Bothrunsexecutethesame

5MSQL-statements


SQLengine

BusinesslogicinPL/SQLwith

embeddedSQLSQL

Java/JDBCversusPL/SQL

BusinesslogicinJavawith

embeddedSQL

SQL

JVM

Invokemethodonmainclass

Invokepackagedprocedure

SQLengine

NonetworkSameboxà IPC


Java/JDBCversusPL/SQL


embeddedSQL

SQL

JVM

SQLengine


embeddedSQLSQL

SQLengine

Elapsed-time:11minutes Elapsed-time:3minutes30seconds

217 CPUseconds

437 DB-CPUseconds

204 DB-CPUseconds


RealWorldExample

• VATClearing/Matching– CompanyAsellstoCompanyB– CompanyBbuysfromCompanyA– EachreportVATonpurchasedgoodsandsoldgoods;onebusinesscanclaimtheVAT

• BuyandSellrecordsmustbematched• Batchesreceiveddailyinsetsof2:buyrecordsandsellrecords• Possibleexceptions:– Duplicates–Outofsyncrecords;i.e.,BuyandSellrecordsindifferentbatches

DailyBatchProgramforTaxMatching:Briefing


RealWorldExampleInvolvedObjects

PREMATCH_BUYtable

PREMATCH_SELLtable

DUPLICATE_SELLtable

DUPLICATE_BUYtable

Buytransactions

file

Selltransactions

file

MATCHEDtable


RealWorldExampleDataFlow

PREMATCH_BUYtable

PREMATCH_SELLtable

DUPLICATE_SELLtable

DUPLICATE_BUYtable

Buytransactions

file

Selltransactions

file

MATCHEDtable

Loadrowsdaily

Loadrowsdaily



PREMATCH_BUYtable

PREMATCH_SELLtable

DUPLICATE_SELLtable

DUPLICATE_BUYtable

Buytransactions

file

Selltransactions

file

MATCHEDtable

MatchandinsertrowsMatchandinsertrows



PREMATCH_BUYtable

PREMATCH_SELLtable

DUPLICATE_SELLtable

DUPLICATE_BUYtable

Buytransactions

file

Selltransactions

file

MATCHEDtable

Removematchedrows.Unmatchedrowsremainfornextday

Removematchedrows.Unmatchedrowsremainfornextday


MATCHEDtable

Duringloadverify

forduplicates

RealWorldExampleAdditionalBusinessLogic:DiscardDuplicates

PREMATCH_BUYtable

PREMATCH_SELLtable

DUPLICATE_SELLtable

DUPLICATE_BUYtable

Buytransactions

file

Selltransactions

file

During loadverifyforduplicates

During loadverifyforduplicates

Beforeloadingarowverify forduplicatesParkduplicate

rowsinseparatetable

Parkduplicaterowsinseparate

table


RealWorldExample

Doesabitofeverything:• Insertsintofivetables• Performsindexedlookupsforde-duplicationandmatching• DeletesfromPREMATCHtables• Indexmaintenance• Somebusinesslogic(if-then-else)tode-dupeandmatch

• WealreadyhaveaWebUIthatdoesVATmatchingforsinglebuy/sellrecord• We'llreuseitsbusiness/model/persistence layers

LoadProfile


TaxDemo– Run#1

Config Threads File1Seconds

File2Seconds

File3Seconds

File4Seconds

File5Seconds

TotalSeconds

ADF 1 1,674 2,081 1,561 1,111 2,131 8,558

ADF 4 460 562 425 302 579 2,328

ADF 16 158 192 145 109 198 802

PL/SQL 1 337 421 315 224 430 1,727

DEMO


TaxDemo– Run#1

PL/SQL1Thread

ADF1Thread

ADFjobspends lesstimeinthedatabase

DEMO


TaxDemo– Run#1(Continued)

ADF16threads

With16threads,ourADFjobisonlyactivein

thedatabase5.7secondspersecond

DEMO


WhydidweshowPL/SQLagain?

§ Ourlayered(ADF)applicationforcedusdownarow-by-rowpath

§ Wecaneasilydorow-by-rowprocessinginPL/SQL


TaxDemo– Run#1

WillSQLtuninghelp?

DEMO


TaxDemo– Run#1

Thisiswhatrow-by-rowprocessing looks

like

DEMO


LayeredArch’sPromise?è OppositeEffect

• Runtimelonger à Layeredapproachperformsworse• DBresource-usagemore à Layeredapproachscalesworse• LayeredapproachusesmoreCPU• Seems“performance/scalability”argumentiswrong?


TaxDemo– Run#2

Config Threads ArraySize File1Seconds

File2Seconds

File3Seconds

File4Seconds

File5Seconds

TotalSeconds

ADF 16 1 158 192 145 109 198 802

PL/SQL 1 256 163 210 115 113 210 811

PL/SQL 16 1 37 43 31 23 44 178

PL/SQL 16 256 26 31 24 17 33 131

DEMO


ADFvs.PL/SQLCPUEfficiency

ADF(16Threads)

PL/SQL(16Threads)

TheADFprogramusesmoreCPU

DEMO


ExactSameRow-by-rowSQL:WhyTheHugeDifference?• "TheLivingRoom"analogy• WithSmartDB:– PL/SQLisalreadyinlivingroom,whichiswhereSQL-enginelives

• Allotherlanguagesneedtoenterfrom“outside”– Gothroughfrontdoor,traversehall,enterlivingroom

Andapparentlythisis*not*forfreeifyouexecutelotsofDBcalls


Oracle

LinuxTheLivingRoom• SQLengine

SQLengine


TheLivingRoom• SQLengine– AccessibleviaOPIlayer–OracleProgramInterface

• PL/SQLdirectlycallsOPI

OPISQLengine

PL/SQLengineEmbeddedSQL

Oracle

Linux


Device-driver/Ethernet/IP/TCP-UDP/Sockets

Two-task

SQL*Net

TNS

Prot.adapter

OracleLinux

Systemlibrary

SQLTheLivingRoom• OutsideSQLroute:–OSnetwork/ipc layers• Frontdoor,doormat

– Net/TNS/TTlayers• Hallway

–OPI

èMorecodepath:Forrow-by-rowSQL,younoticethisoverhead

OPISQLengine

PL/SQLengineEmbeddedSQL


ADFvs.PL/SQLCPUEfficiencyWhyisRow-by-RowPL/SQLUsingLessCPU?

Device-driver/Ethernet/IP/TCP-UDP/Sockets

OPI OPI

Two-task

SQL*Net

TNS

Prot.adapter

SQLengine

PLSQLengine

OracleLinux

Systemlibrary

SQL

EmbeddedSQL

Sockets/TCP-UDP/IP/Ethernet/Device-driver

LinuxJVM

Persistencylayers

JDBClayers

Systemlibrary

Domainmodellayers

ApplicationcodeSettersandgetters

Transactionlayers


ResearchedThisThroughFlameGraphs

• Flamegraphs visualizeproportionallywhere,inthecode,aprogramspendsitstime


GeneratingaFlameGraph• Isreallyeasy• Allyouneedistwoperl scripts,whichyoucandownloadfrom– https://github.com/brendangregg/FlameGraph


PL/SQLvs.ADF:CPUImpactADFOraclecallstackwithFlameGraph

Shipping datainandoutofthedatabase;outsideOPI/SQL

Engine

ApplicationSQL(insideSQLEngine/OPI)

OSnetwork, IPC,Net/TNS/TTS

Deletes InsertsExecutes&fetches

Stateinitialization,contextprep,find

cursor,unmap cursor

Recursivecalls


PL/SQLvs.ADF:CPUImpactPL/SQLOraclecallstackwithFlameGraph

PL/SQLEngine

ApplicationSQL(insideSQLEngine/OPI)

Deletes Inserts

Executes&fetches

Stateinitialization,contextprep,find

cursor,unmap cursorRecursive

calls


PL/SQLvs.ADF:CPUImpact

opiexe

opiexe

Samecode– whydoesADFusemoreCPU?


GeneratingaFlameGraph

Processthatwewantto“Flamegraph”

Eg.Oraclededicatedserver

pid=12345

main>p9main>p1>p2main>p1main>p1>p2>p3main>p1>p2main>p1>p2>p3main>p1>p2main>p1>p2>p3main>p1>p2main>p9main>p9>p4…

SamplecallstackWritetoperf.data

main>p1main>p1>p2main>p1>p2main>p1>p2main>p1>p2main>p1>p2>p3main>p1>p2>p3main>p1>p2>p3main>p9main>p9main>p9>p4…

Sort

Perl-1

perf record-g-F99--pid 12345

Perf:Linuxprocessprofiler

attach

perf.data file mainp1 p9

p4p2p3

Perl-2

Generatesvg-file Viewinbrowser


HowtoReadaFlamegraph

• Moreinfo:https://github.com/brendangregg/FlameGraph

mainp1 p9

p4p2p3

Widthoftop-surfacerepresentswheretimeisspent

Widthrepresents#ofsamples=cpu-timespent


Reason#1• With layered architecture approach,every databasecall/SQL-statementincurs aRDBMS-entrytaks

• In#SmartDBthereisnoentryrequiredforSQL,it’salreadyinthere

Researchshowed:40-50%addititionalCPU-cyclesperSQL-statementRemember,we’redealingwithrow-by-rowSQLhere

• Butthat’snottheobserved>2XincreaseinDB-Time...


Reason#2:CPUEfficiency• ModernCPUcoresarecomplexfactories(justlikeRDBMS)– Asathread,it’sbesttostayinfactoryaslongaspossible– GettingoffandbackonCPUisveryexpensiveintermsofrequiredclock cycles– Processcontext-switchingismostexpensiveCPUoperation

• SmartDBapproachhasfewer process context-switches– stays onthe CPU• Layered approachdeschedulesmany moremillions oftimes perSQLstatement,causing CPUtohavetoexecuteadditionalmicro-operations

• Whatthismeansis:Layered arch.approachesusemore DBCPU• What this meansis:You use less CPUif you can “stay onit”to getyour BLdone

ResearchedthroughCPUprofiling


363395.733099 cpu-clock363333.886369 task-clock

1,054,507,284,440 cycles497,951,316,616 instructions35,271,327,010 bus-cycles

555,928 faults6,782 cpu-migrations

29,011,223,921 cache-references262,587,106 cache-misses

4,496,968 context-switches97,003,543,743 branches3,132,424,179 branch-misses

954.553486931 seconds time elapsed

163360.334736 cpu-clock163360.401705 task-clock

485,147,753,205 cycles224,676,903,673 instructions16,182,224,526 bus-cycles

749,871 faults1,802 cpu-migrations

13,863,704,572 cache-references149,387,122 cache-misses

115,832 context-switches42,229,605,073 branches

821,626,951 branch-misses

165.182311836 seconds time elapsedPL/SQL

PL/SQLvs.ADF:CPUImpact

ADF

200%moreinstructions

Requiring100%moreCPU

Causedbymorebranch&cachemisses


Combined:LayeredapproachPuts>2XLoadOnRDBMS

• Reason#1:OverheadinOraclekernelperSQLstatement• Reason#2:OverheadatCPU-leveldueto stacktraversal,scheduling,context-switching,etc.


OneMorePointtoMake…

Apartfromspendingtimeexecuting SQL…

• We’realsospendingtimeexecutingtheif-then-else-looplanguagefromwhichSQLgetssubmitted– InLayered approachwe’respendingtimeinJavadoingbusinesslogic– In#SmartDBapproachwe’respendingtimeinPL/SQLdoingbusinesslogic


RequiredCPUForGettingtheBusinessLogicDone


embeddedSQL

SQL

JVM

SQLengine


embeddedSQL

SQL

SQLengine437 DB-CPUseconds

204 DB-CPUseconds

217 CPUseconds

184 DB-CPUseconds

20 DB-CPUseconds


10X!• ResearchedthroughFlameGraphingtheJVM• Analogy:notonlydoyouhavetocomeinto(DB)housefromoutsideYoualsofirsthavetoexityour(JVM)houseforeverySQLstatement

• 90%oftimespentinJVMisin:1. ExecutingJDBCcode-layers2. GettinginandoutofJVM3. OtherJVM-specifics(JITcompilation,GarbageCollection,...)

Allthreesimplydonotexistin

SmartDBapproach


FlameGraph ofJVM

Shipping datainandout1)Shipping

data inandout

Our"program"2)MostofitisJDBC,ie.getting

SQLreadytobeshipped

3)Restis"JVMhousekeeping"

217CPUsecondsOurbusiness logicissimplynotvisible


InSummary:Row-by-RowLayeredvs.Row-by-RowSmartDB• If youhaveyourSQLgeneratedbypersistence/ORMframeworks,• Then you’llgetchatty,row-by-row,applications• Whichthenresultsinhugelyinefficientuseofresources– BothatDB-server(2X)and JVMside(10X)

• Themorechattyyourapplicationis,theworsethiswillbe– Also,thegreaterthelatency,theworsethiswillbe– Also,thebusieryourDBserverorappserver,theworsethiswillbe

• Roundtrips– CausemassiveincreaseinrequiredCPUpower– Nowadaysthisisprobablyworsethanthe“timespentonnetwork”


WhatDoesthePL/SQLArrayInterfaceDoforYou?ArraySize1 ArraySize256

ArrayprocessinguseslessCPU

DEMO


TaxDemo– Run#3Config(Thread)

Threads/DoP ArraySize File1

SecondsFile2

SecondsFile3

SecondsFile4

SecondsFile5

SecondsTotal

Seconds

ADF 16 1 158 192 145 109 198 802

PL/SQL 16 1 37 43 31 23 44 178

PL/SQL 16 256 26 31 24 17 33 131

Set 8 N/A 3 3 2 3 3 14

DEMO


0

5000

10000

ADF 1Thread

ADF 4Threads

ADF 16Threads

PL/SQL 1Thread

PL/SQL 1Thread

Array 256

PL/SQL 16Threads

PL/SQL 16Threads

Array 256

Set-BasedDoP=8

Config

CP

U S

econ

ds

TierApps

DB

CPU Seconds per Test by Tier

CPUUsage

BlueisAppsTierCPUseconds

RedisDBTierCPUseconds

DEMO


CPUUsage

Config Threads/DoP Array Size DBCPU AppsTier CPU TotalCPUCPUper1k

RowsProcessed

ADF 1 1 3,583.18 9,464.46 13,047.65 3.45

ADF 4 1 3,476.46 7,330.17 11,454.98 3.03

ADF 16 1 4,608.08 8,039.97 12,699.14 3.35

PL/SQL 1 1 1,775.04 0.00 1,775.04 0.47

Pl/SQL 1 256 863.03 0.00 863.03 0.22

PL/SQL 16 1 2,351.20 0.00 2,351.20 0.62

PL/SQL 16 256 1,755.99 0.00 1,755.99 0.46

Set 16 N/A 101.22 0.00 101.22 0.02

DEMO


CPUUsage

0

1

2

3

ADF 1Thread

ADF 16Threads

ADF 4Threads

PL/SQL 1Thread

PL/SQL 1Thread

Array 256

PL/SQL 16Threads

PL/SQL 16Threads

Array 256

Set-BasedDoP=8

Test

CP

U p

er 1

K R

ows

Config

ADF 1 Thread

ADF 16 Threads

ADF 4 Threads

PL/SQL 1 Thread

PL/SQL 1 Thread Array 256

PL/SQL 16 Threads

PL/SQL 16 Threads Array 256

Set-Based DoP=8

CPU Seconds per 1k Rows

DEMO


Set-BasedProcessing

• Techniquesinclude:– DatabaseParallelism– DDLinsteadofDML–Multi-tableinsertswithCommonTableExpressions–Windowfunctionsforrowlabeling

• Faster,useslessCPU,and easier tocodeandsupport• Set-basedSQLmovesprocessingtodata– OracleDBisaprocessingengine• WithSet-BasedSQL,wecanchoose ourperformancewithDoP

ExploitthepowerofSQL


EmbracingSet-BasedSQL• Onceyou'reinPL/SQLopportunitiesforset-basedSQLopenupnaturally– You,thedeveloper,writetheSQL– Layeredsoftwarearchitecturesusuallypreventthisas,bydesign,SQLisinvisible

• Veryoftenthereisopportunitytoembraceset-basedSQL– Youspecifythe“what”and Oraclefigures out“how”• Speedupofdevelopment

– Replacingrow-by-rowwithset-basedSQLalsodeliversexecutionspeedups• Comingfromrow-by-rowLayered architectures,10X-100Xormoreoften achievable

• YoumovebusinesslogicintoSQL(richSQL)


OriginalExample:BatchProgram• Abletorewriteusingset-basedmulti-tableinsertstatements(MTI)

• Row-by-rowJava/JDBCused :437DB-CPUseconds• Row-by-rowPLSQLused :204DB-CPUseconds• Set-basedused :7DB-CPUseconds


OriginalResultsVisualized

• IfyouchooseLayered,you’vecommittedto:– HavingtopurchasealotofhardwareandDBlicenses– Blamingdatabaseforyourperformance/scalabilityissues

Whereasyoushouldblameyourchosenarchitecture

Almost100XJustthinkaboutthis…


BigPicture

Introduction



BigPicture

ConcludingThoughts

1

2

3

4

5


PerformancewithLayeredArchitectures

Database NetworkApplication

serverrunningJVM

Fromdatabase'sperspective…

Elapsedtime =(DBtime)+("Timenotindatabase")



Database NetworkApplication

serverrunningJVM

Fromappsserver'sperspective…

Elapsedtime =(JVMtime)+("Timenot inJVM")


OS/Thread/Procstart/wake/other


Database Network Applicationserverrunning

JVM

16ThreadADFExample

Elapsed=13.4minutes

DBT=4.8mins JVM=5mins3.6mins

Whatifyournetworkisslow?





JVM

16ThreadADFExample

Elapsed=13.4minutes


Whenyoumovedatatoprocessing(row-by-row),there'sapenalty





JVM

16ThreadADFExample

Elapsed=13.4minutes


Howmuchcontrol doyouhaveoverperformancewiththepenaltyforeachrow?


PerformancewithLayeredArchitecturesWhereistheLeverage?

Wheredoyoustart?

Inthelandofincrementalgains


PerformancewithLayeredArchitecturesWhereistheLeverage?

Wheredoyoustart?


PerformancewithLayeredArchitecturesThrowingapplicationparallelismattheproblem

Over-processed

Contention

Isthisthepointyoustartbottom-up

analysis?


PerformancewithLayeredArchitecturesThrowingapplicationparallelismattheproblemAdminServer-diagnostic.log:[2016-09-15T14:03:23.210-07:00] [AdminServer] [NOTIFICATION] [DFW-40101] [oracle.dfw.incident] [tid: TaxMatch125] [userId: <anonymous>] [ecid: 47e4c839-a6ff-4dc8-b4f6-7155f95baca7-00000023,0:4:1548] [APP: RwpTax2] An incident has been signalled with the incident facts: [problemKey=DFW-99998 [oracle.jbo.pool.ResourcePoolException][oracle.jbo.pool.ResourcePool.allocateResource][RwpTax2] incidentSource=SYSTEM incidentTime=Thu Sep 15 14:03:23 PDT 2016 errorMessage=DFW-99998 executionContextId=null]AdminServer-diagnostic.log:[2016-09-15T14:03:23.210-07:00] [AdminServer] [WARNING] [DFW-40125] [oracle.dfw.incident] [tid: TaxMatch125] [userId: <anonymous>] [ecid: 47e4c839-a6ff-4dc8-b4f6-7155f95baca7-00000023,0:4:1548] [APP: RwpTax2] incident flood controlled with Problem Key "DFW-99998 [oracle.jbo.pool.ResourcePoolException][oracle.jbo.pool.ResourcePool.allocateResource][RwpTax2]"e21b0a54-30f2-4028-bf18-a1c62871c7d4-00000373,0:2:11:1556284] [errid: 349] [detailLoc: /nfs-shared/wls/home/user_projects/domains/rwp_domain/servers/AdminServer/adr/diag/ofm/rwp_domain/AdminServer/incident/incdir_349] [probKey: DFW-99997 [java.lang.OutOfMemoryError]] [APP: RwpTax2] incident 349 created with problem key "DFW-99997

Whatdoyoudowhenyourunoutofapplicationserver

resources?

Canyoureallychooseyourperformance

thisway?


ConcludingThoughts

Introduction



BigPicture

ConcludingThoughts

1

2

3

4

5


Remarks

1. Theimplicationofallthis2. SQLisn’taccidental3. “Myenterpriseapplicationistoocomplex”4. BewareofrisksifyougoSmartDB


1:TheImplicationOfAllThis,VisualizedAppserver

Appserver

Appserver

Appserver

Appserver

Databaseisalwaysfirstbottleneck100TX/Sec200TX/Sec300TX/Sec400TX/Sec500TX/Sec

WithSmartDByoucanprocessmorewithsameDBhardware

WithSmartDByoucanprocesssame

withlessDBlicenses

Layered

SMARTDB

500TX/Sec

Set-basedSQL…

500TX/Sec


1:TheImplicationOfAllThis• MovingpoorSQLintoPL/SQLlikelyfreesup50%ofyourDB-CPUtime

• Movingbusinesslogicfromlayeredsw-architectureinJVM’s,tostraightforwardPL/SQL,makesitrequire10XlessCPU

• Yourquestion1:doesthe10XlessCPUfitinthe50%freedupDB-CPU’s?Incase,“no”:• Yourquestion2:wherecanIembraceset-basedSQLtomakeit“yes”?


2:SQLIsn’t Accidental,It’sFundamental

• Therearenearlyalwaysopportunitiesforyourbusinesslogictobepushedintoset-basedSQL

• Whyisthisthecase?• There’safundamentalreason forthis...


2:SQLIsn’t Accidental,It’sFundamental

TheRealWorld

Us,living intherealworld,usingnaturallanguage toreasonwitheachother

abouttherealworld

Wereasoninthismodelusingrich,set-based, SQL

Application:modelofapartoftherealworld

aboutwhichwewanttoreasonusingcomputers

SQLisbasedonlogicandsettheory

Logicandsettheoryarebasedonnaturallanguage,particularlythepartsofitthatdealwithreasoning

Sowereasoninthemodelusinglanguagethatwasbasedonhow

wereasoninthereal-worldErgo,SQLfundamentallyfitswhat

wewanttoachieve


3:MyEnterpriseApplicationIsTooComplex• “IcannotdomyapplicationlogicinSQLandPL/SQL”– BothSQLandPL/SQLhavebecomeincrediblyrich– Givenourcontext (transactionalenterpriseapplications)andSQL’sfundamentalfit,itwouldbestrangeifyourlogiccannotbedealtwith

• Don’tunderestimatewidthanddepthofSQLandPL/SQL• And,allDBfeaturessurroundingthesetwolanguages• Counterpoint– bepreparedforyourdeveloperstosay“Idon’twant tomovemyapplicationlogic…”


3:OftenThisIsTheIssueWhenSQL+PL/SQLAreDismissed

• Amindshiftisrequired:

• Youneedtostartthinkingin“processingdata”• Insteadof“interactingwithobjectsthathavebehavior”

• Arelationaldatabasedesign shouldbeyourframeofreference• Andnotanobjectorienteddomainmodel


4:BewareOfRisksIfYouGoSmartDB

• Makesureyouinvolvepeople–Who’vedonethisbefore–Whothink“processingdata”–Whoareexperiencedindesigningdatabases–WhoknowfullpowerofSQLand PL/SQL

• Ifyou’renewtothis:obviouslystartsmall


4:ReachOuttoActiveSmartDBCommunity• OracleTechnologyNetwork,Database,SQLandPL/SQLforums:https://community.oracle.com/welcome• AskTheOracleMasters:https://asktom.oracle.com• OracleDevGym:https://devgym.oracle.com/• StackOverflow:https://stackoverflow.com/questions/tagged/plsqlhttps://stackoverflow.com/questions/tagged/oracle• Oracle-lmaillist:https://www.freelists.org/list/oracle-l


Conclusions

Layeredarchitecturesdon'tletyouchoose yourperformance• Row-by-rowalgorithmsacrossmultiplestacks=moretime,lessthroughput• Thisper-rowtaxmultipliesasdatavolumeincreases• Youcanonlycontrolasmallpiece– thepieceofcodeorinfrastructureyouown• IncreasedCPUusage,whichincreasessoftware/hardware/Cloudcosts• Easytorunintocontention/bottlenecksanddifficulttoresolve– usuallyinvolvescomplexapplicationcodechange

CanYouChooseYourPerformancewithLayeredArchitectures?


Conclusions

Layeredarchitecturesdrivedatatoprocessing,notprocessingtodata• DatashippedtoAppstieratunitoflowestcommondenominator– arow(orcolumn!)

• Databaseviewedaspersistencelayer,notprocessingengine• Equatesto:– Lotsofroundtrips,lotsofstacktraversal,thread/processsleep/wakeup/start/stop–…meansmoreCPUandlessefficient,and–…bothperformanceandresourceusagedependenton#ofrows/columns

• IncreasedCPU=highersoftware/hardware/Cloud

MovingProcessingtoData


Conclusions

Layeredarchitecturesfosterabottom-upperformanceculture• Layerownersonlyhavevisibility/responsibilityoversmallpieceofpuzzle• Ownersspendlotsoftime"tuning"theirpiece,chasingpercentagepoints• Lackofabilitytoinnovate• "Racetothebottom"mindset– expertineachlayerfocusesontheirlayerandnobodylooksholistically

IncrementalPerformanceImprovements


IfWeHaveTime…

• Going#SmartDB,or• ConnectionsandConnectionPools(it’srelevant,Ipromise)

oracle database: processing engine or persistence layer? · • oracle’s fusion applications/saas...

Documents