Table Of ContentTEAM LinG - Live, Informative, Non-cost and Genuine!
Database Tuning
PRINCIPLES, EXPERIMENTS,
AND TROUBLESHOOTING TECHNIQUES
TEAM LinG - Live, Informative, Non-cost and Genuine!
TheMorganKaufmannSeriesinDataManagementSystems
SeriesEditor:JimGray,MicrosoftResearch
DatabaseTuning:Principles,Experiments,and DataontheWeb:FromRelationstoSemistructured
TroubleshootingTechniques DataandXML
DennisShashaandPhilippeBonnet SergeAbiteboul,PeterBuneman,andDanSuciu
SQL:1999—UnderstandingRelationalLanguage
DataMining:PracticalMachineLearningTools
Components
andTechniqueswithJavaImplementations
JimMeltonandAlanR.Simon
IanWittenandEibeFrank
InformationVisualizationinDataMiningand
JoeCelko’sSQLforSmarties:AdvancedSQL
KnowledgeDiscovery
Programming,SecondEdition
EditedbyUsamaFayyad,GeorgesG.Grinstein,
JoeCelko
andAndreasWierse
Transactional Information Systems: Theory, Algorithms, JoeCelko’sDataandDatabases:ConceptsinPractice
andPracticeofConcurrencyControlandRecovery JoeCelko
GerhardWeikumandGottfriedVossen
DevelopingTime-OrientedDatabaseApplications
SpatialDatabases:WithApplicationtoGIS inSQL
PhilippeRigaux,MichelScholl,andAgnesVoisard RichardT.Snodgrass
InformationModelingandRelationalDatabases:
WebFarmingfortheDataWarehouse
FromConceptualAnalysistoLogicalDesign
RichardD.Hackathorn
TerryHalpin
ComponentDatabaseSystems DatabaseModeling&Design,ThirdEdition
EditedbyKlausR.DittrichandAndreasGeppert TobyJ.Teorey
ManagingReferenceDatainEnterpriseDatabases: ManagementofHeterogeneousandAutonomous
BindingCorporateDatatotheWiderWorld DatabaseSystems
MalcolmChisholm EditedbyAhmedElmagarmid,MarekRusinkiewicz,
andAmitSheth
DataMining:ConceptsandTechniques
JiaweiHanandMichelineKamber
Object-RelationalDBMSs:TrackingtheNextGreat
Wave,SecondEdition
UnderstandingSQLandJavaTogether:AGuideto
MichaelStonebrakerandPaulBrownwith
SQLJ,JDBC,andRelatedTechnologies
DorothyMoore
JimMeltonandAndrewEisenberg
Database: Principles, Programming, and Performance, ACompleteGuidetoDB2UniversalDatabase
SecondEdition DonChamberlin
PatrickandElizabethO’Neil
UniversalDatabaseManagement:AGuideto
TheObjectDataStandard:ODMG3.0 Object/RelationalTechnology
EditedbyR.G.G.CattellandDouglasK.Barry CynthiaMaroSaracco
TEAM LinG - Live, Informative, Non-cost and Genuine!
ReadingsinDatabaseSystems,ThirdEdition BuildinganObject-OrientedDatabaseSystem:
EditedbyMichaelStonebrakerand TheStoryofO2
JosephM.Hellerstein EditedbyFran¸coisBancilhon,ClaudeDelobel,
andParisKanellakis
UnderstandingSQL’sStoredProcedures:AComplete
GuidetoSQL/PSM DatabaseTransactionModelsforAdvancedApplications
JimMelton EditedbyAhmedK.Elmagarmid
PrinciplesofMultimediaDatabaseSystems AGuidetoDevelopingClient/ServerSQLApplications
V.S.Subrahmanian SetragKhoshafian,ArvolaChan,AnnaWong,
andHarryK.T.Wong
PrinciplesofDatabaseQueryProcessingforAdvanced
Applications TheBenchmarkHandbookforDatabaseandTransaction
ClementT.YuandWeiyiMeng ProcessingSystems,SecondEdition
EditedbyJimGray
AdvancedDatabaseSystems
CarloZaniolo,StefanoCeri,ChristosFaloutsos, CamelotandAvalon:ADistributedTransactionFacility
RichardT.Snodgrass,V.S.Subrahmanian, EditedbyJeffreyL.Eppinger,LilyB.Mummert,
andRobertoZicari andAlfredZ.Spector
PrinciplesofTransactionProcessing ReadingsinObject-OrientedDatabaseSystems
PhilipA.BernsteinandEricNewcomer EditedbyStanleyB.ZdonikandDavidMaier
UsingtheNewDB2:
IBMsObject-RelationalDatabaseSystem
DonChamberlin
DistributedAlgorithms
NancyA.Lynch
ActiveDatabaseSystems:TriggersandRules
ForAdvancedDatabaseProcessing
EditedbyJenniferWidomandStefanoCeri
MigratingLegacySystems:Gateways,Interfaces,
&theIncrementalApproach
MichaelL.BrodieandMichaelStonebraker
AtomicTransactions
NancyLynch,MichaelMerritt,WilliamWeihl,
andAlanFekete
QueryProcessingforAdvancedDatabaseSystems
EditedbyJohannChristophFreytag,DavidMaier,
andGottfriedVossen
TransactionProcessing:ConceptsandTechniques
JimGrayandAndreasReuter
TEAM LinG - Live, Informative, Non-cost and Genuine!
TEAM LinG - Live, Informative, Non-cost and Genuine!
Database Tuning
PRINCIPLES, EXPERIMENTS,
AND TROUBLESHOOTING TECHNIQUES
Dennis Shasha
COURANT INSTITUTE OF MATHEMATICAL SCIENCES
NEW YORK UNIVERSITY
Philippe Bonnet
UNIVERSITY OF COPENHAGEN
TEAM LinG - Live, Informative, Non-cost and Genuine!
PUBLISHING DIRECTOR DianeD.Cerra
ASSISTANT PUBLISHING SERVICES MANAGER EdwardWade
SENIOR PRODUCTION EDITOR CheriPalmer
SENIOR DEVELOPMENTAL EDITOR BelindaBreyer
EDITORIAL ASSISTANT MonaBuehler
COVER DESIGN YvoRiezebos
COVER IMAGSE Dr.MikeHill(Cheetah)andEyeWireCollection(Violin)/gettyimages
TEXT DESIGN FrancesBaca
TECHNICAL ILLUSTRATION Technologies‘n’Typography
COMPOSITION InternationalTypesettingandComposition
COPYEDITOR RobertFiske
PROOFREADER JenniferMcClain
INDEXER TyKoontz
PRINTER EdwardsBrothersIncorporated
Designationsusedbycompaniestodistinguishtheirproductsareoftenclaimedastrademarksorregistered
trademarks.InallinstancesinwhichMorganKaufmannPublishersisawareofaclaim,theproductnamesappearin
initialcapitalorallcapitalletters.Readers,however,shouldcontacttheappropriatecompaniesformorecomplete
informationregardingtrademarksandregistration.
MorganKaufmannPublishers
AnImprintofElsevierScience
340PineStreet,SixthFloor
SanFrancisco,CA94104-3205,USA
www.mkp.com
©2003byElsevierScience(USA)
Allrightsreserved.
PrintedintheUnitedStatesofAmerica
07 06 05 04 03 03 5 4 3 2 1
Nopartofthispublicationmaybereproduced,storedinaretrievalsystem,ortransmittedinanyformorbyany
means—electronic,mechanical,photocopying,orotherwise—withoutthepriorwrittenpermissionofthepublisher.
LibraryofCongressControlNumber:2001099791
ISBN1-55860-753-6
Thisbookisprintedonacid-freepaper.
TEAM LinG - Live, Informative, Non-cost and Genuine!
ToKaren,whoalwayssingsintune
D.S.
ToAnnie,Rose,andtothememoryof
LouiseandAntoineBonnet
P.B.
TEAM LinG - Live, Informative, Non-cost and Genuine!
TEAM LinG - Live, Informative, Non-cost and Genuine!
F O R E W O R D
byJimGray, MicrosoftResearch
SeriesEditor,MorganKaufmannSeriesinDataManagementSystems
Each database product comes with an extensive tuning and performance guide, and
then there are the after-market books that go into even more detail. If you carefully
read all those books, then did some experiments, and then thought for a long time,
you might write this book. It abstracts the general performance principles of de-
signing, implementing, managing, and use of database products. In many cases, it
exemplifiesthedesigntrade-offsbycrispexperimentsusingallthreeofthemostpop-
ular products (IBM’s DB2, Oracle’s Oracle, and Microsoft’s SQLServer). As such, it
makesinterestingreadingforanestablisheduseraswellasthenovice.Thebookdraws
heavily from the author’s experience working with Wall Street customers in transac-
tion processing, data warehousing, and data analysis applications. These case studies
makemanyoftheexamplestangible.
For the novice, the book gives sage advice on the performance issues of SQL-
level logical database design that cuts across all systems. For me at least, the physi-
cal database design was particularly interesting, since the book presents the implica-
tions of design choices on the IBM, Oracle, and Microsoft systems. These systems
are quite different internally, and the book’s examples will surprise even the systems’
implementers. It quantifies the relative performance of each design choice on each
of the three systems. Not surprisingly, no system comes out “best” in all cases; each
comesacrossasastrongcontenderwithsomeblindspots.
Thebookcanbereadasatutorial(ithasanassociatedWebsiteatwww.mkp.com
/dbtune/),oritcanbeusedasareferencewhenspecificissuesarise.Ineithercase,the
writing isdirect and very accessible. The chapters on transaction design and transac-
tionchopping,thechaptersontimeseriesdata,andthechaptersontuningwillbeof
greatinteresttopractitioners.
ix
TEAM LinG - Live, Informative, Non-cost and Genuine!
