实践实战：在PoC中的Oracle12c优化器参数推荐含PPT

1、实践实战：在PoC中的Oracle12c优化器参数推荐(含PPT)最近，Oracle数据库优化器的产品经理NigelBayliss发布了一篇文档，介绍：SettinguptheOracleOptimizerforPoCs-在PoC测试中优化器参数的设置和调节。优化器是Oracle数据库的核心组件，我们一起来看一看12c有哪些优化器的变化。关注本公众号回复关键字：Internals即可获得本文PPT(SettingUp.),同时附送了一系列的精彩PPT学习资源。SettinguptheOracleOptimizerforPoCsCcYeringOracle1andRelease2antfGrad

2、eDatabase18cMayIrt2018NigelOptimnwPradLictManagervldbbhttp.Ublagsorack.coiri/opbmiier首先，作者描述了POC测试的基本原则，遵循KISS原则(KeepitSimpleStupid),从一个尽可能简单的基线开始；优先考虑稳定性和一致性；通过测试掌控变化；持续向前： KGbujoafuB向呻卬guMz孙9iwrgu KGLCRGCOULO|OAGL buonrise打印!|处auqcoui?（gucA Mlfp9KGGbi不必匕性（Kl?2）p9?6|!UGQGUGL9|5UUClb|G2tOL“C。首先，在Ora

3、cle12cR1中，Oracle引入了一个重要的新特性：自适应查询优化器AdaptiveQueryOptimization,该特性的主要功能有两个：对SQL的执行计划进行运行时（run-time）调整,（也就是在SQL执行过程中，具备动态改变执行计划的能力）；在SQL执行过程中，动态统计和发现新的统计信息，以实现更佳的执行计划；通过这个特性的描述，我们可以知道，当现有统计数据不足以生成最佳计划时，自适应查询优化器会很有用；当然相反方向是，如果我们数据库中执行计划是稳定的、优化的、满足需要的，那么这个新的特性对我们就基本不需要。下图展示了这个新特性的两个路径：自适应执行计划、自适应统计信息。在1

4、2.1版本中，是否启用自适应优化器参数由初始化参数optimizer_adaptive_features决定。NewinOracleDatabase12cRelease1TheAdaptiveOptimizerOfrtifflixFrJAdaptiveF一StaticPlansHl5oacijE基于在执行过程中获得的真实统计信息，优化器动态调整执行计划的能力可以极大地提高查询性能。下图展示了一个最常见的场景，基于静态统计信息，Oracle选择了NestLoop的执行计划，当执行中动态统计信息（自适应统计信息）被收集之后，SQL的执行计划自动变更为HashJoin的执行方式。己deOptimrz

5、erAdaptiveFeaturesFromOracleDatabase12c1Sigi再I户edb，MkfortinffletibkicardinalilYOFltfjginDataDictionary1.1c.SQLPIvOlrKtlweiColumnU»efStllHtlCSAWptIWPl*MC«th>rtr»t4fttUILf口前AllCO-ltaMtAlt*aufft1"HistogramsColuvringroupstatirticscreatedautomatimlly在Oracle官方文档中，有这样一个举例，可以更清晰的看到这个过

6、程和含义:SELESFMHTABLE(IS.XPIJW.dispUy.cursariFOWAT。*AJBAPTIVE"J);543t_ioJhjtkhiwyfegin.rpfchildOLineer®SELECTpr时uct_FWi«FW)Horfler_it«i53prod.inifDpM<RiEo>unit_priceISMDquantity*1MD。田n>duG_idiouprwuct。Planhashval：ue:|SSLtCTHATWEMT11111PI川m12BHUM)I717闻111岫鞘g11)1-HH«MJOI

7、NM椁TEOLWP5111NE5TERLOOPSIKI126|1(81i««e：ui川STATISTICSCOLLECTOR111111|*5|TABLEACCESSFULL|DRDEAJTEffiMl4fl1(911&1IhOEXU4IQUE算W|PM&,IhFO_P«111|»(ai11|TABLEACCESS8rMDEXAOWlOjPM_INF电111It|1(«i11TABLEaccess小式IPSC&_IKF(JHI11(*JIId|CqratlOinINwc)R-cm|Bytts|Co&ttWMI(T

8、lmc|PredirateTnfwnutimni(idntifledbfopratiionidt:1-ac«ss("RPROOWT_ID"s"0R."PK)DOCT_Iir)S-filter11_RUcr-isMDb-access(11r*bPR0goCT_iDta""C"."-IWDI1KT.IIT1Mdle-thisl$*119d«ptlveplinfnm>rkrdsreinactivet其动态变化过程如下图所示Defaultplanisanesle-dloopsjoinM总总1它dH

9、awkLoopsJornTheoplimi25rbuttersrowscomingfromth.rd.it占INbt。uptoapoint,iftheno”court虏!e&sthanthetnrestiotd.thenahesledloopsjoin.Olhenvi&a,bwitchtoahashjoin.Thr”h。旧阱G网命6sosubplanswitchesTh&optiTnizardisablestheststisti-cscoHwt&raftermakingthedecision,andfetsiherowspassthrough.在使用这个新特性获

10、取收益时，你必须记住以下这些可能带来的负面影响?优化器随着时间的推移而改变执行计划-SQL执行计划可能随时间而改变-运行时性能可能不一致?自适应统计数据在硬解析率较高的系统中有额外开销-高硬解析率不是最佳实践，但现实中太常见；-比Oracle11g更多的动态采样查询；-可能会观察到LibraryCache竞争；-12cR1（在12cR2版中修复）中的'RClatch'（结果缓存）争用;AdaptiveOptimizerRemember.*Theoptimizeradaptsovertime SQLexecutionplansmaychangeovertimePerforrnan

11、cemightnotbe0ansi5tentbetweenrunswhileitadapts“Adaptivestatisticshaveanoverheadin$ystem$withhighhardparseratesHighhardparseratesarenotbestpractice,butrealit/getsintheway MoredynamicsamplingqueriesthanmOracleDatabase11gSomeseeNbrarycchecontentionassociatedwithlongerparsetimes SomeseeRClatch1(resultca

12、che)conterrtioninOracle口3mse12cRelease1(fixedinOracleDatabase12cRelease2)i接下来进入12.2版本，在这个版本中optimizer_adaptive_features这个参数被废弃了，自适应优化器的两部分，自适应计划(adaptiveplans)和自适应统计信息(adaptivestatistics)由2个独自的参数进行控制，这两个参数是：optimizer_adaptive_plans默认值为TRUEoptimizer_adaptive_statistics默认值为FALSEFromOracleDatabase12cRe

13、lease2FinercontrolofadaptivefeaturesnewparmetersOPTIMIlER_AtMFRWHAHJRE5%切酸LJcOabmlifrIAdaptfw3OuAdaptivePlans0imi>»IStatisticsORACLE在Oracle12.2和18c中，推荐的缺省行为都已改变，自适应的执行计划，缺省未开启的自适应统计信息：好了，有了这些基础知识之后，我们看看在PoC过程中，为了性能稳定，推荐的配置。在Oracle12cR1中，除了推荐安装必要的补丁修正外，剩下的主要推荐就是设置初始化参数，去除optimizer_adaptive_fe

14、atures设置，按照12.2和18c的缺省设置来选择optimizer_adaptive_plans和optimizer_adaptive_statisticsRecommendationsforOracleDatabase12cRelease1AssumingInstalllatestProartiiwBundkPitch(s*=October2017-RecommenddliotisforAdaptiveF&ature$mOracleDatsbs<1.2cRtea$&1eg.Ufiuary201B,PMch2701gM"心加油"心

15、PrMrte83aPitch12.102180116(AdaptIve“网MaptiveStatisticsand12cSQLPerformance)IDecID23129114)CQn(rQl=3617716732417;1"；20243260;1* ftemowpphrn/jie/&odbpbvr_jfroturifs(parameterfronnpfile/spfile* SetthelollowlpgcptimlnerparaEet2rs(thettsrethedehultsIn12cR2andISc):* Onthedatabase,extcijtthefdlQiw

16、ingOFF"i%thedwgullfKEC.iJOSAL-ffltFSrAIJWSfAf.tJCTENQrYOfF1)RACLE婿就云最后一个推荐是:EXECDBMS_STATS.SET_GLOBAL_PREFS('AUTO_STAT_EXTENSIONS','OFF')此外，有了12.1的实践之后，看起来12.2和18c的推荐就简单了许多:RecommendationsforOracleDatabase12cRelease2 Donotsetthefollowingoptimizerparameters-theywilltaketheirdefau

17、ltvalues印出扁醴匚口的前"/由，IdefsuhTRUE)adaptive(defaultFALS£ Onthedatabase1rexecutethefollowing(OFFisthedefault)EX£C0BM5_STATS.5Er_GUOBAI_PREF5AUTQ_5TAT_EJCTEfi5ION5rCFF*ORACLERecommendationsforOracleDatabase18c Donotsetthefodowingoptimizerparameters-theywilltaketheirdefaultvalues-cptimiier_

18、adaptwsplamdefauhTRUE)-optimizw_adaptiwsta-trstics|defaultFALSE Onthedatabase,executethefollowing(OFFisthedefault)EXECQBMS.STOR.SETGLOBA<_I>RFFS(AUTO.STATEITfSIQNS'/OFFC»ACL£可是经过了这么多讨论之后，我们发现AdaptiveStatistics完全没有了用武之地，那么这个特性的场景是什么呢？在DSS/BI的工作负载环境下，这个特性可能会发挥其优势：UsingAdaptiveStati

19、stics? AdaptivestatisticscanbeusefulforDSS/BIworkloads- Ad-tiocquerbe?- LQN±-ru弁Ri>M0u也iN- LowhxdpjixeratesStartwithdefaultsQdmmi*statistiesdisabled)andyoumay科anttotestBenefit*Multiplerunimayberequiredtoachievebestperformance-SQLp4indirectlwicwrtimtSQLpLandrir«Elve9*recrvilrdewenIfoplamaeradjipfrve_itrtHtdci=FAL5E-Eh/jfcjustnotu&edGithenngstatisbeii专requiredtogeneriilecOfurYingroupsttsHandpoterrtiaNyrewhistograms)*Settingoptimiier_adaptrve_5ta