實踐實戰(zhàn)在PoC中的Oracle 12c優(yōu)化器參數(shù)推薦

1、實踐實戰(zhàn)：在PoC中的Oracle 12c優(yōu)化器參數(shù)推薦(含PPT)最近，Oracle數(shù)據(jù)庫優(yōu)化器的產(chǎn)品經(jīng)理Nigel Bayliss發(fā)布了一篇文檔，介紹：Setting up the Oracle Optimizer for PoCs -在PoC測試中優(yōu)化器參數(shù)的設(shè)置和調(diào)節(jié)。優(yōu)化 器是Oracle數(shù)據(jù)庫的核心組件，我們一起來看一看12c有哪些優(yōu)化器的變化。關(guān)注本公眾號回復(fù)關(guān)鍵字：Internals即可獲得本文PPT(SettingUp.)，同時附送了一 系列的精彩PPT學(xué)習(xí)資源。Setting up the Oracle Optimizer for PoCsCovering Oracle D

2、atabase 12c Release 1 and Release 2 and Oracle Database Uc May 1512013Nigel 3$丫1昭Optimizer Product Ma nagvldbbhttp:/ blogs.orac I e.cDm/optim izerORACIljE首先，作者描述了 POC測試的基本原則，遵循KISS原則(Keep it Simple Stupid), 從一個盡可能簡單的基線開始；優(yōu)先考慮穩(wěn)定性和一致性；通過測試掌控變化；持續(xù)向刖：LUOArug朋阡。牌uEW 0L 9EXGLQW& COULO| OA6L C(J9UG.tJRLKRG

3、 卻auq cou?i?(sucA?f3i4 JWifp s之qolaiq(K1P2)P6|iug(GUGL9I tjLIUClblG? OL b。首先，在Oracle 12cR1中，Oracle引入了一個重要的新特性：自適應(yīng)查詢優(yōu)化器-Adaptive Query Optimization，該特性的主要功能有兩個：對SQL的執(zhí)行計劃進行運行時（run-time）調(diào)整，（也就是在SQL執(zhí)行過程 中，具備動態(tài)改變執(zhí)行計劃的能力）；在SQL執(zhí)行過程中，動態(tài)統(tǒng)計和發(fā)現(xiàn)新的統(tǒng)計信息，以實現(xiàn)更佳的執(zhí)行計 劃；通過這個特性的描述，我們可以知道，當現(xiàn)有統(tǒng)計數(shù)據(jù)不足以生成最佳計劃時，自適應(yīng) 查詢優(yōu)化器會很有用

4、；當然相反方向是，如果我們數(shù)據(jù)庫中執(zhí)行計劃是穩(wěn)定的、優(yōu)化的、 滿足需要的，那么這個新的特性對我們就基本不需要。下圖展示了這個新特性的兩個路徑：自適應(yīng)執(zhí)行計劃、自適應(yīng)統(tǒng)計信息。在12.1版本 中，是否啟用自適應(yīng)優(yōu)化器參數(shù)由初始化參數(shù)optimizer_adaptive_features決定?；谠趫?zhí)行過程中獲得的真實統(tǒng)計信息，優(yōu)化器動態(tài)調(diào)整執(zhí)行計劃的能力可以極大地提 高查詢性能。下圖展示了一個最常見的場景，基于靜態(tài)統(tǒng)計信息，Oracle選擇了 Nest Loop的執(zhí)行 計劃，當執(zhí)行中動態(tài)統(tǒng)計信息（自適應(yīng)統(tǒng)計信息）被收集之后，SQL的執(zhí)行計劃自動變 更為Hash Join的執(zhí)行方式。Adaptiv

5、e12c QprfeiirnjnfSQL PlanDlrectiweColumn Utaw StatastlcsData DictionaryOracle Optimizer Adaptive FeaturesFrom Oracle Database 12c Release 1r A.LL COlUMN 5- mu vr$HistogramsStatistics, feedback for3 single faWe tardinaliTv and jQin$Column groupFl十/R statistic”neatedI autdEetiwIlY在Oracle官方文檔中，有這樣一個舉例，

6、可以更清晰的看到這個過程和含義:SELECT * FROM TABLE(DBMSJCPUNBdisplaycursar AJDAPTIWJ)：Plan hash valuer 了典WSOL_ID ThjSMwy67pchild nunher &SELECT pradut_nane FftQHorder-items. % prad_inf p 尚EHEg Ltnit_prie 15 AND quantitv 1 .AND p- prBduct_ixl d. prDduct.iid| Id | Operation| Naric | R.cm |Btes )CD3-t (%CPU| )Tln|Pre

7、dicatB Inf o-rmation C identif iied by operation iURN-ote1 - aect5 S f P,11 PRDOl!il6 - accesst ,P,i,H PRmiCT_ID,VD. PftDDUCT_I )-this； Is additive plan (rows marked /re insccivet|0| SELECT STATEMENT| 中1| HASH JOINI- 2| NESTED LOOPSNESTED LOOPS|4| STATISTICS COLLECTOR.17(100|IM | 7 W)|的：的：如 | 128 |

8、7 WJ|鍬H 和；如 | IM | 7您|蹄；郵；町|I TABLE ACCESS FULL | ORDERLITWSIHKX UNIQUE SC呻| PROD_INFO_PK| TABLE ACCESS GY INDEX R0WID| PROD_INFO I TAELE ACCESS FULL| PROD_INFOI 3 $ej|w：ei|I M0J| II i (ej|&：ei|I 1陟g01|其動態(tài)變化過程如下圖所示:Default pJan is a n&ste-d loops JornThe optimer buffers rows coming fronra the ir;l-e

9、T_iteriS table upto a point. If lha row count is la&s than the threshold, men us & nested loo歸 gin. Otherwlss, switch to a hash ioIn.Thresh 職0瞻 cted, so subplan sivitctweIhs aptirritser disables the statiti-ts cdll&ita-r after making the d&ciion. and lets the rows pa&s through.Finaf plan： js 3 hasfi

10、 joinTabte scan proti. inlQHash JainStatistics CollectorTable scan or=October 2017一 January 衛(wèi)Patch 27010930 - gtab非e Proiactiwe fiumdle Patch 12.1.Q.2.1SO11BRcorrnneindanDns for Adaptive Features in Oracle112c(Adaptiw Festure5r Adaptive Statistiits and 12c SQL Perfdrnni3ince (Doc ID 21129114)-_f_con

11、trol= 26&B4361; 7/16732417; l1；2O243268;l,Remove optimizer_adoptive_Jeaturs parameter from pfiEe/spfil, Set the following Gptirnizd potentially newSetti ng optl m Ize r adapt I ve_stat I sties 二 FALSE car be used to restore behavior once test is complete 一 and remember vou may have new histograms/column group stats 除了自適