Oracle之系統(tǒng)統(tǒng)計信息
點擊上方“IT那活兒”，關(guān)注后了解更多精彩內(nèi)容?。。?/span>
指縫太寬，時光太瘦。轉(zhuǎn)眼間，我們又迎來了天寒地凍的冷冬—小雪。你，還記得小時候看到雪的樣子嗎？
雪后，放下手中的電腦，陪家人做一餐飯，逛一逛街；約上三五好友聚上一聚，豈不美哉！
該醒醒了，起來干活！
2021年11月1日月初保障，某營業(yè)商業(yè)務(wù)側(cè)反饋凌晨營業(yè)B庫某進(jìn)程執(zhí)行效率較營業(yè)A庫慢很多，經(jīng)分析是同一個SQL（sql_id: gucsa3276bhgx）在營業(yè)AB庫執(zhí)行計劃不一樣,營業(yè)A該SQL走了TBCS.SUBS_PRODUCT的PRODID列的索引，而營業(yè)B的執(zhí)行計劃卻走了全表掃描（453GB），在RAC的一個節(jié)點去掃描一個453GB的表導(dǎo)致數(shù)據(jù)庫性能嚴(yán)重下降。
在這里說明一下，營業(yè)A庫和營業(yè)B庫業(yè)務(wù)邏輯完全一樣，只是地市不同而已。進(jìn)一步分析檢查營業(yè)A、營業(yè)B 都是相同的采樣比(5%)，在BCV測試環(huán)境嘗試收集30%的采樣后，依然不走索引，經(jīng)分析因為營業(yè)B庫Oracle 的優(yōu)化器CBO 系統(tǒng)統(tǒng)計信息過于陳舊，導(dǎo)致CBO無法精確的評估cpu和io的cost，導(dǎo)致優(yōu)化器選擇錯誤的表連接方式(營業(yè)A 選擇了NL、營業(yè)B選擇了HASH連接)以及執(zhí)行計劃錯誤。最終導(dǎo)致AB庫的執(zhí)行計劃不一樣。系統(tǒng)統(tǒng)計信息和優(yōu)化器概念
分析之前，我們來看下oracle系統(tǒng)統(tǒng)計信息和優(yōu)化器的概念。oracle優(yōu)化器（optimizer）是oracle數(shù)據(jù)庫內(nèi)置的一個核心子系統(tǒng)。優(yōu)化器的目的是按照一定的判斷原則來得到它認(rèn)為的目標(biāo)SQL在當(dāng)前的情形下的最高效的執(zhí)行路徑，也就是最佳執(zhí)行計劃。以目前最常用的CBO為例，CBO以目標(biāo)SQL成本為判斷原則，通過目標(biāo)SQL語句所涉及的表、索引、列、系統(tǒng)等的統(tǒng)計信息算出各條執(zhí)行路徑的成本，從而選擇成本最小的執(zhí)行計劃作為SQL的執(zhí)行計劃。Oracle優(yōu)化器統(tǒng)計信息包括：其中系統(tǒng)統(tǒng)計信息背后的概念是衡量系統(tǒng) CPU 和存儲子系統(tǒng)（如 NAS、SAN、JBOD 或閃存）的性能，并在計算替代 SQL 執(zhí)行計劃的成本時使用這些信息。當(dāng)數(shù)據(jù)庫知道存儲和 CPU 的實際速度有多快，它才可以對每個替代計劃的成本做出更精細(xì)的判斷。營業(yè)AB庫執(zhí)行計劃確實不一致，按經(jīng)驗可以確定是某一塊統(tǒng)計信息有誤導(dǎo)致。2 營業(yè)AB庫統(tǒng)計信息采樣對比AB庫統(tǒng)計信息采樣比是一樣的，但執(zhí)行計劃卻不一樣，在BCV庫嘗試收集了30%的采樣比，B庫仍然不會走索引，說明表和列本身的統(tǒng)計信息應(yīng)該沒有問題，于是做了一個優(yōu)化器的trace。從優(yōu)化器的trace來看，經(jīng)過復(fù)雜的cost計算后，營業(yè)A庫認(rèn)為NL關(guān)聯(lián)為最優(yōu)，而營業(yè)B庫則認(rèn)為Hash關(guān)聯(lián)為最優(yōu)。嘗試強制去指定驅(qū)動表，更改營業(yè)B庫表的連接方式：可以看到執(zhí)行計劃走了正確的索引，說明索引的統(tǒng)計信息也無問題。通過修改參數(shù)db_file_multiblock_read_count為8（原先為16），嘗試重新評估IO的cost，如下：可以發(fā)現(xiàn)已走正確的執(zhí)行計劃，說明IO的統(tǒng)計信息可能有誤，檢查CBO的系統(tǒng)統(tǒng)計信息，發(fā)現(xiàn)B庫是2018年收集的，統(tǒng)計信息過于陳舊。檢查營業(yè)A庫的系統(tǒng)統(tǒng)計信息是2014年收集的。--重新收集CBO優(yōu)化器系統(tǒng)統(tǒng)計信息EXEC DBMS_STATS.GATHER_SYSTEM_STATS;營業(yè)B庫該SQL已經(jīng)可以走正確的執(zhí)行計劃。綜上所述，可以看出由于營業(yè)B庫優(yōu)化器的系統(tǒng)統(tǒng)計信息不準(zhǔn)，引起SQL語句（sql_id: gucsa3276bhgx）執(zhí)行計劃產(chǎn)生偏差。一般來說，數(shù)據(jù)庫的系統(tǒng)統(tǒng)計信息只有在主機cpu、內(nèi)存、存儲、操作系統(tǒng)等有進(jìn)行升級替換的時候才需要重新收集，如下圖，可以使用命令進(jìn)行手動收集，在BCV庫測試后，可以發(fā)現(xiàn)IOSEEKTIM（IO尋址時間（毫秒））由6變?yōu)?0，IOTFRSPEED（IO傳輸速率（字節(jié)/毫秒））由54682變?yōu)?096，和營業(yè)A庫一致。但如無異常情況，不建議手動收集系統(tǒng)統(tǒng)計信息，避免帶來未知的風(fēng)險。考慮到系統(tǒng)統(tǒng)計信息重新收集后，可能影響較多的SQL執(zhí)行計劃，建議可以先對問題SQL進(jìn)行sqlprofile綁定執(zhí)行計劃。參數(shù)解釋如下：
FLAGS：標(biāo)志
CPUSPEEDNW：非工作量統(tǒng)計模式下CPU主頻，直接來自硬件
IOSEEKTIM：IO尋址時間（毫秒），直接來自硬件
IOTFRSPEED：IO傳輸速率（字節(jié)/毫秒）
SREADTIM：讀取單個數(shù)據(jù)塊的平均時間
MREADTIM：讀取多個數(shù)據(jù)塊的平均時間
CPUSPEED：工作量統(tǒng)計模式下CPU主頻，根據(jù)當(dāng)前工作量評估出一個合理值
MBRC：oracle收集完統(tǒng)計信息后評估出的一次多塊讀可以讀幾個數(shù)據(jù)塊db_file_multiblock_read_count
MAXTHR：最大IO吞吐量（字節(jié)/秒）
SLAVETHR：平均IO吞吐量（字節(jié)/秒）
更多精彩干貨分享
點擊下方名片關(guān)注
IT那活兒
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請注明本文地址：http://www.ezyhdfw.cn/yun/129738.html
