oracle10G數(shù)據(jù)庫性能監(jiān)控與優(yōu)化

oracle10G數(shù)據(jù)庫性能監(jiān)控與優(yōu)化第一頁，共40頁。請獻(xiàn)上你的問題！第二頁，共40頁。為什么sql在測試庫很快，上了生產(chǎn)卻很慢？為什么遷移數(shù)據(jù)這么久？為什么只更新一條記錄等了5分鐘還在更新？為什么BSSBOSS系統(tǒng)又堵了？為什么數(shù)據(jù)庫有如此多的活動session？為什么count(*)一張小表這么慢第三頁，共40頁。培訓(xùn)目的：能夠確保更新到生產(chǎn)的sql語句高效能夠通過v$session視圖定位數(shù)據(jù)庫的繁忙程度能夠通過AWR報告準(zhǔn)確定位系統(tǒng)的性能瓶頸，通過反復(fù)優(yōu)化循序漸進(jìn)的提高bss、boss系統(tǒng)在數(shù)據(jù)庫上的性能培訓(xùn)目標(biāo)：短時間內(nèi)（5-10分鐘）快速定位數(shù)據(jù)庫性能瓶頸（具體到sql）通過AWR分析過去某個時間段數(shù)據(jù)庫的性能瓶頸熟知在數(shù)據(jù)遷移過程中如何提高性能，從而縮短數(shù)據(jù)遷移時間熟知數(shù)據(jù)庫各種等待事件背后的原理。第四頁，共40頁。交通事故？車多緩行？今天，您堵了嗎第五頁，共40頁。數(shù)據(jù)庫性能突降（阻塞）阻塞的標(biāo)志字段（blocking_session）如果blocking_session的狀態(tài)是活動（active）—>優(yōu)化語句或根據(jù)當(dāng)時情況決定是否kill如果blocking_session的狀態(tài)是不活動的（inactive）—>超過一定時間，kill掉，找原因(是jdbc、tuxedo、還是終端工具操作忘記commit)動態(tài)視圖：v$sessionblocking_session=v$session中(sid)模擬阻塞第六頁，共40頁。數(shù)據(jù)庫性能突降（無阻塞，只是活動session多）查詢v$session中event字段(都有哪些等待事件)

latch:cachebufferschains

readbyothersession

dbread

enq:SQ-contention

log

enq:TX-rowcontention

PXDeq:ExecutionMsg第七頁，共40頁。latch:cachebufferschains(熱塊)極短的時間內(nèi)對少量數(shù)據(jù)塊進(jìn)行了過于頻繁的并發(fā)訪問低效的SQL內(nèi)存——>磁盤——>內(nèi)存哈希表——>遍歷表PCTFREE表分區(qū)第八頁，共40頁。readbyothersession當(dāng)一個會話試圖修改一個數(shù)據(jù)塊，但這個數(shù)據(jù)塊正在被另一個會話修改時。當(dāng)一個會話需要讀取一個數(shù)據(jù)塊，但這個數(shù)據(jù)塊正在被另一個會話從磁盤讀取到內(nèi)存中時Oracle操作的最小單位是塊（Block），即使你要修改一條記錄，也需要對這條記錄所在的這個數(shù)據(jù)塊做操作。當(dāng)你對這個數(shù)據(jù)塊做修改時，其他的會話將被阻止對這個數(shù)據(jù)塊上的數(shù)據(jù)做修改（即使其他用戶修改的不是當(dāng)前用戶修改的數(shù)據(jù)），但是可以以一致性的方式讀取這個數(shù)據(jù)塊（fromundo）。當(dāng)前的用戶修改完這個數(shù)據(jù)塊后，將會立即釋放掉加在這個數(shù)據(jù)塊上的排他鎖，這樣另一個會話就可以繼續(xù)修改它第九頁，共40頁。什么是全表掃描dbread（全表掃描）全表掃描=慢嗎？只有20條數(shù)據(jù)為什么count(*)需要20秒甚至更久？順序地讀取分配給表的每個數(shù)據(jù)塊，直到讀到表的最高水線處。一個多塊讀操作可以使一次I/O能讀取多塊數(shù)據(jù)塊(db_file_multiblock_read_count參數(shù)設(shè)定)，而不是只讀取一個數(shù)據(jù)塊，這極大的減少了I/O總次數(shù)，提高了系統(tǒng)的吞吐量，所以利用多塊讀的方法可以十分高效地實現(xiàn)全表掃描，而且只有在全表掃描的情況下才能使用多塊讀操作第十頁，共40頁。

enq:SQ-contention（序列CACHE）賦予了CACHE屬性的Sequence調(diào)用nextval期間，許多會話同時訪問nextval值。需要將數(shù)據(jù)字典信息物理修改后，再次執(zhí)行CACHE的工作selectsequence_valfromdual并發(fā)第十一頁，共40頁。log-事務(wù)commit

當(dāng)一個會話發(fā)出一個commit命令時，LGWR進(jìn)程會將這個事務(wù)產(chǎn)生的redolog從logbuffer里面寫到磁盤上。

會話發(fā)出的commit指令后，需要等待LGWR將這個事務(wù)產(chǎn)生的redo成功寫入到磁盤之后，才可以繼續(xù)進(jìn)行后續(xù)的操作，這個等待事件就叫作log。當(dāng)系統(tǒng)中出現(xiàn)大量的log等待事件時，應(yīng)該檢查數(shù)據(jù)庫中是否有用戶在做頻繁的提交操作。第十二頁，共40頁。enq:TX-rowcontention表索引多嗎？更改相同的值嗎？（blocking_session）delete、update、insert頻繁嗎？第十三頁，共40頁。PXDeq:ExecutionMsg表或索引的degree屬性查詢指定并行（select/*+parallel(a6)*/）fromtaba;第十四頁，共40頁。等待事件（總結(jié)）

latch:cachebufferschains

readbyothersession

dbread

enq:SQ-contentioncache參數(shù)設(shè)置

log

事務(wù)頻繁度、存儲性能

enq:TX-rowcontention

pctfree參數(shù)

SQL語句慢第十五頁，共40頁。RACCacheFusion（內(nèi)存拷貝）-補(bǔ)充v$event_name什么是CacheFusion9i之前有CacheFusion嗎應(yīng)用分開終端用戶避免交叉連接不同實例相關(guān)的等待事件第十六頁，共40頁?；仡櫟却录谑唔?，共40頁。大數(shù)據(jù)量遷移例句：

insert/*+append*/intotf_f_userpartition(p1)select/*+parallel(a6)*/*fromtf_f_user@dblinka開始遷移數(shù)據(jù)

關(guān)閉數(shù)據(jù)庫歸檔

關(guān)閉數(shù)據(jù)庫自動分析

數(shù)據(jù)表空間足夠大

回滾段表空間足夠大

遷移前索引失效

遷移后索引有效

手工表分析

啟用數(shù)據(jù)庫自動分析

開啟數(shù)據(jù)庫歸檔append性能背后第十八頁，共40頁。ORA-01555：查詢結(jié)果被覆蓋了！查詢語句太慢？undo太?。炕貪L段(undo)ORA-01555回滾段（undo）原理：沒有commitinsert：存放數(shù)據(jù)的rowid（反操作delete）

update：存放update前的數(shù)據(jù)delete：存放整條語句(反操作insert)已經(jīng)commitundo將已經(jīng)提交的數(shù)據(jù)標(biāo)記為可以被覆蓋

第十九頁，共40頁。壓測時數(shù)據(jù)庫性能數(shù)據(jù)庫中所有序列的cache值足夠大

enq:SQ-contentionaltersequenceowner.sequence_namecache4000;表和索引的degree改成1(測試)

PXDeq:ExecutionMsgcreatetabletf_f_user(....)parallel6;altertabletf_f_userparallel6;alterindexowner.index_namerebuildparallel6;createindexowner.index_nameparalllel6;第二十頁，共40頁。高水位標(biāo)記(HighWaterMark)createtablet_aasselect*fromdba_objects;insertintot_aselect*fromt_a(反復(fù)多次保證足夠的數(shù)據(jù)量);commit;selectcount(*)fromt_a;deletefromt_a;commt;selectcount(*)fromt_a;降低高水位標(biāo)記

altertablet_amovetablespaceusers;重建索引

alterindexidx_namerebuild<paralleln>;alterindexidx_namenoparallel;第二十一頁，共40頁。索引的性能快1、搜索索引中列中的ROWID2、根據(jù)ROWID定位到具體的數(shù)據(jù)塊3、把數(shù)據(jù)塊中所需的數(shù)據(jù)返回ROWIDselectrowid,a.*fromUCR_BUSI_97.tf_b_tradeawhererownum<=1AAARGwAAQAAAMPMAAAAAARGw：六位表示dataobjectid，根據(jù)objectid可以確定segment。AAQ：三位表示相對文件號。根據(jù)該相對文件號可以得到絕對文件號，從而確定datafile。AAAMPM：六位表示datablocknumber。這里的datablocknumber是相對于datafile的編號，而不是相對于tablespace的編號。AAA：三位表示rownumber。第二十二頁，共40頁。索引的代價慢insert找位置insert值delete標(biāo)記為刪除update標(biāo)記為刪除找位置insert值代價排序第二十三頁，共40頁。SQL綁定變量解析：安全性檢查，語法檢查；創(chuàng)建：評估多個執(zhí)行計劃，并選擇一個最優(yōu)的執(zhí)行計劃；執(zhí)行：捆綁變量，執(zhí)行已經(jīng)創(chuàng)建的執(zhí)行計劃；獲?。韩@取結(jié)果集，進(jìn)行轉(zhuǎn)換，排序等；1->2->3->43->4select*fromtablewherecol_name='張三'select*fromtablewherecol_name=:v_name1次10次1000次1百萬次1千萬次.......第二十四頁，共40頁。表分析開啟、關(guān)閉自動分析不分析會發(fā)生什么？什么時間？誰分析？HINT提示與統(tǒng)計信息第二十五頁，共40頁。SQL優(yōu)化-建議預(yù)防是更好的優(yōu)化方式where條件中所有字段的不同值dba_tab_col_statisticsselectcolumn_name,count(*)fromtable_namegroupbycolumn_name確保表的分析時間為最新dba_tables(last_analyzed)盡可能少建單個索引，合并成符合索引在生產(chǎn)環(huán)境中查看執(zhí)行計劃的cost總值(tableaccessfull、indexrangescan對應(yīng)的csot)sql上線前需dba和開發(fā)人員集合業(yè)務(wù)特點(diǎn)、執(zhí)行頻率、執(zhí)行時間、語句本身給出建議第二十六頁，共40頁。割接期間數(shù)據(jù)庫需要做什么停止數(shù)據(jù)庫自動分析數(shù)據(jù)遷移完畢后，分析被大批量insert、update、delete的表啟用數(shù)據(jù)庫自動分析編譯數(shù)據(jù)庫中所有的失效對象確保數(shù)據(jù)庫中表空間有足夠的空余空間（system、sysaux、undo、臨時表空間）第二十七頁，共40頁。AWR:AutomaticWorkloadRepository什么是AWR?AWR和STATSPACK的關(guān)系？boss1.0

boss2.0bss4.0

bss4.2第二十八頁，共40頁。AWR:疑問AWR采樣頻率和保留時間-保留3天=3*24*60dbms_workload_repository.modify_snapshot_settings(retention=>4320,interval=>30)AWR生成的數(shù)據(jù)保存在哪個表空間？AWR相關(guān)的數(shù)據(jù)字典（dba_hist_active_sess_history、dba_hist*）怎樣生成AWR

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

或Toad工具第二十九頁，共40頁。AWR:解讀Elapsed:數(shù)據(jù)庫運(yùn)行時間（分鐘）DBTime:反映了數(shù)據(jù)庫的繁忙程度

7763/(179×cpu個數(shù))/100%第三十頁，共40頁。AWR:解讀BufferCache:允許加載到內(nèi)存中的數(shù)據(jù)庫SharedPoolSize:數(shù)據(jù)字典+最近解析（或編譯）后的SQL、PLSQL第三十一頁，共40頁。AWR:解讀%BlockschangedperRead：說明10.65%的邏輯讀是用于那些只讀的而不是可修改的塊，該系統(tǒng)只更新90%的塊。Rollbackpertransaction%：事務(wù)回滾的百分比第三十二頁，共40頁。AWR:解讀In-memorySort%：在內(nèi)存中的排序率。LibraryHit%：主要代表sql在共享區(qū)的命中率，通常在95%以上，否則需要要考慮加大共享池，綁定變量，修改cursor_sharing等參數(shù)。SoftParse%：近似看作sql在共享區(qū)的命中率，小于<95%,需要考慮到綁定，如果低于80%，那么就可能sql基本沒有被重用。ExecutetoParse%：一個語句執(zhí)行和分析了多少次的度量。在一個分析，然后執(zhí)行語句，且再也不在同一個會話中執(zhí)行它的系統(tǒng)中，這個比值為0。第三十三頁，共40頁。AWR:解讀BufferNowait%：在緩沖區(qū)中獲取Buffer的未等待比率，BufferNowait<99%說明，有可能是有熱塊。RedoNoWait%：在Redo緩沖區(qū)獲取Buffer的未等待比率。BufferHit%：數(shù)據(jù)塊在數(shù)據(jù)緩沖區(qū)中的命中率，通常應(yīng)在90%以上，否則，小于95%，需要調(diào)整重要的參數(shù)，小于90%可能是要加db_cache_size，但是大量的非選擇的索引也會造成該值很高如果一個經(jīng)常訪問的列上的索引