oracle数据仓库最佳实践白皮书

BestpracticesforaDataWarehouseonOracleDatabase11gAnOracleWhitePaperSeptember2008BestPracticesforaDataWarehouseonOracleDatabase11gPage2NOTE:Thefollowingisintendedtooutlineourgeneralproductdirection.Itisintendedforinformationpurposesonly,andmaynotbeincorporatedintoanycontract.Itisnotacommitmenttodeliveranymaterial,code,orfunctionality,andshouldnotberelieduponinmakingpurchasingdecisions.Thedevelopment,release,andtimingofanyfeaturesorfunctionalitydescribedforOracle’sproductsremainsatthesolediscretionofOracle.BestPracticesforaDataWarehouseonOracleDatabase11gPage3BestPracticesforaDataWarehouseonOracleDatabase11gNote:....................................................................................................................2ExecutiveSummary..........................................................................................4Introduction.......................................................................................................4BalancedConfiguration....................................................................................5Interconnect..................................................................................................6DiskLayout...................................................................................................7LogicalModel....................................................................................................9PhysicalModel.................................................................................................10Staginglayer.................................................................................................10EfficientDataLoading..........................................................................11Foundationlayer-ThirdNormalForm.................................................14Optimizing3NF.....................................................................................15Accesslayer-StarSchema........................................................................19OptimizingStarQueries........................................................................20SystemManagement.......................................................................................22WorkloadManagement..............................................................................22WorkloadMonitoring................................................................................26ResourceManager......................................................................................31OptimizerStatisticsManagement............................................................32InitializationParameter..............................................................................34Memoryallocation.................................................................................34ControllingParallelExecution.............................................................36EnablingefficientIOthroughput........................................................36StarQuery...............................................................................................37Conclusion........................................................................................................37BestPracticesforaDataWarehouseonOracleDatabase11gPage4BestpracticesforaDataWarehouseonOracleDatabase11gEXECUTIVESUMMARYIncreasinglycompaniesarerecognizingthevalueofanenterprisedatawarehouse(EDW).AtrueEDWprovidesasingle360-degreeviewofthebusinessandapowerfulplatformforawidespectrumofbusinessintelligencetasksrangingfrompredictiveanalysistonearreal-timestrategicandtacticaldecisionsupportthroughouttheorganization.InordertoensuringtheEDWwillgettheoptimalperformanceandwillscaleasyourdatasetgrowsyouneedtogetthreefundamentalthingscorrect,thehardwareconfiguration,thedatamodelandthedataloadingprocess.BydesigningthesethreecornerstonescorrectlyyoucanseamlesslyscaleoutyourEDWwithouthavingtoconstantlytuneortweakthesystem.INTRODUCTIONToday’sinformationarchitectureismuchmoredynamicthanitwasjustafewyearsago.BusinessesnowdemandmoreinformationsoonerandtheyaredeliveringanalyticsfromtheirEDWtoanevery-wideningsetofusersandapplicationsthaneverbefore.InordertokeepupwiththisincreaseindemandtheEDWmustnowbenearreal-timeandbehighlyavailable.Howdoyouknowifyourdatawarehouseisgettingthebestpossibleperformance?Orwhetheryou'vemadetherightdecisionstokeepyourmulti-TBsystemhighlyavailable?Basedonoveradecadeofsuccessfulcustomerdatawarehouseimplementationsthiswhitepaperprovidesasetofbestpracticesand“how-to”examplesfordeployingadatawarehouseonOracleDatabase11gandleveragingit’sbest-of-breedfunctionality.Thepaperisdividedintofoursections:Thefirstsectiondealswiththekeyaspectsofconfiguringyourhardwareplatformofchoicetoensureoptimalperformance.Thesecondbrieflydescribesthetwofundamentallogicalmodelsusedfordatabasewarehouses.ThethirdoutlineshowtoimplementthephysicalmodelfortheselogicalmodelsinthemostoptimalmannerinanOracledatabase.Finallythefourthsectioncoverssystemmanagementtechniquesincludingworkloadmanagementanddatabasecon