资源描述：
《oracle statspack使用指南》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、DBAStatistics–howoftenandhowprecise?(Sept2004)JonathanLewis,JLComputerConsultancyPreambleTheCostBasedOptimizerneedsinformationaboutyourdata-butwhy,andhowmuch?Somesitesrecomputestatisticsforallcolumnsofeveryschemaeverynight;somesitestakeasmallestimateoftheod
2、dobjectfromtimetotime;mostsiteswillprobablybesomewhereinthemiddleoftherange.SomepeoplewilljustswitchtoAUTOwith10g.Isthereanyapproachthatwillbejustrightforeverybody?Andifnot,whynot?Thepresentationexplainssomeofthebasicsofoptimizerstatistics,andwillhelpyoudec
3、idehowtominimizetheresourcesspentgatheringstatistics.OptimalThinking.Thefirst(andonly)ruleofoptimizationis:"Avoidunnecessaryeffort".Butyouhavetooperatethisruleatmanylevels.Inthecaseofstatistics,forexample,youhavethreeconsiderations:·Theoptimizershouldbeable
4、touseyourstatisticstofindthemostefficientexecutionplanforastatement.·Youshouldnotuseunnecessarymachineresourcesgeneratingstatistics.·Youshouldnotspendexcessiveamountsoftimetryingtoworkoutaminimalstrategyforgeneratingstatistics.Youcancauseproblems(otherthant
5、hesimplewasteofresources)bycollectingtoomanystatisticsbutasageneralrule:ifyoudon'tseeanyperformanceproblemsthatcouldbecausedbyinappropriatestatistics,andifyoudon'thaveanyproblemshittingyourSLAsbecauseofthetimespentgatheringstatistics,thenpoint3applies:youca
6、nprobablyfindbetterthingstodowithyourtimethanrefineastrategythatisalreadyworking.Thispaperisparticularlyforpeoplewhothinktheirperformanceproblemsmayberelatedtooptimizerissues,orforpeoplewhoareunderpressuretofindsomemoretimeintheirbatchwindows.However,ifyoua
7、reinterestedinhowtheoptimizerworks,youmightwanttoreadthispapersimplytoenhanceyourunderstanding.Paper#DBAWhatdoestheCBOusestatisticsfor?Inordertoworkouttheoptimumexecutionpath,theoptimizerneedstodecidethebestjoinorder,thebestjoinmechanism(e.g.hash,merge,nest
8、edloop),andthebestdataacquisitionmethod(e.g.index,tablescan)foreachtable.UnlessOraclecanworkoutareasonablyaccurateestimateofthenumberofrowsthatwillbeacquiredateachstageofthejoinitislikelytomakesomebadd
