Oracle數(shù)據(jù)庫業(yè)務(wù)優(yōu)化心得
2024-08-29 13:34:41
供稿：網(wǎng)友
做了一大半年的業(yè)務(wù)數(shù)據(jù)庫優(yōu)化了，有一點(diǎn)感想，寫出來自勵(lì)一下。 公司開發(fā)的產(chǎn)品基本上都使用Oracle數(shù)據(jù)庫，而且業(yè)務(wù)系統(tǒng)中存儲(chǔ)和使用的數(shù)據(jù)量很大，使用業(yè)務(wù)系統(tǒng)的用戶也很多。在系統(tǒng)忙時(shí)，大約會(huì)有一千個(gè)用戶同事訪問數(shù)據(jù)庫系統(tǒng)，因此經(jīng)常會(huì)有用戶抱怨系統(tǒng)慢，點(diǎn)擊查詢后，系統(tǒng)出現(xiàn)假死機(jī)現(xiàn)象，后臺運(yùn)行ORACLE系統(tǒng)的小型機(jī)的IDLE值偏低(甚至出現(xiàn)IDLE為0的情況，這時(shí)用戶會(huì)驚呼數(shù)據(jù)庫系統(tǒng)會(huì)癱掉，雖然小型型機(jī)在IDLE值為0的情況下也不一定會(huì)癱機(jī)，但這無疑會(huì)增加用戶的擔(dān)憂)，系統(tǒng)運(yùn)行在一個(gè)不安全的狀態(tài)等等。 對于我現(xiàn)在所做的ORACLE優(yōu)化，其實(shí)還停留在SQL優(yōu)化的層次(以前我的前輩曾給我說關(guān)于數(shù)據(jù)庫優(yōu)化的三個(gè)層次:一是針對SQL的優(yōu)化，如使用正確是索引，使用ORACLE提示等;二是針對數(shù)據(jù)庫對象的優(yōu)化，如增加索引，微調(diào)表結(jié)構(gòu)等;三針對業(yè)務(wù)的優(yōu)化，需要更改業(yè)務(wù)邏輯或者表結(jié)果，此類優(yōu)化一般代價(jià)比較大，一般很少針對正在運(yùn)行的系統(tǒng)做類似的操作)。 公司的客戶很多，大多數(shù)情況會(huì)在公司遠(yuǎn)程處理客戶的數(shù)據(jù)庫問題。作為問題的定位者，一定要搞清楚實(shí)際運(yùn)行的數(shù)據(jù)庫系統(tǒng)到底出現(xiàn)了什么問題。現(xiàn)場的維護(hù)人員和用戶最喜歡使用的形容詞是:系統(tǒng)很慢;數(shù)據(jù)庫鎖表了等等。用戶向外面?zhèn)鬟_(dá)的信心往往是非常模糊的，在開始接觸這個(gè)問題時(shí)候起，我們就要引導(dǎo)用戶去幫外面了解系統(tǒng)真實(shí)的情況。有幾個(gè)東西是一定要看看的，一個(gè)是ORACLE系統(tǒng)的警告日志文件，在系統(tǒng)運(yùn)行的有問題或者是忙時(shí)的幾份STATSPACK報(bào)告(一般間隔時(shí)間10到15分鐘)，UNIX下SAR命令的輸入結(jié)果(該命令可以按既定的時(shí)間間隔收集小型機(jī)系統(tǒng)CPU的使用情況)。通過這三個(gè)從現(xiàn)場收集的結(jié)果，我們基本可以了解現(xiàn)場數(shù)據(jù)庫的運(yùn)行情況。 其中ORACLE系統(tǒng)的警告日志文件能讓我們了解ORACLE系統(tǒng)運(yùn)行有沒有一些重大的問題。 STATSPACK報(bào)告中概括了數(shù)據(jù)庫系統(tǒng)的運(yùn)行基本情況，其中關(guān)于如何解讀報(bào)告可以寫一本書了，不過我們首先要關(guān)注的是報(bào)告中有一段“Top 5 Timed Events”，這一段描述了當(dāng)前ORACLE主要的等待事件是什么(關(guān)于ORACLE等待事件的概念可以參考相關(guān)的資料)。 SAR命令的輸入我們要關(guān)注三個(gè)輸出的分布情況USR、WIO、IDLE。其中SYS+USR+WIO+IDLE應(yīng)該等于100%，USR占的比例高，一般說明SQL語句執(zhí)行效率有問題，這種問題一般是由于索引選擇性不高、表連接順序和方式不對等等;WIO高一般說明SQL語句頻繁進(jìn)行I/O操作。對于具體的問題，則需要對具體的SQL語句進(jìn)行分析，在分析過程中，閱讀執(zhí)行計(jì)劃是我們的一個(gè)重要的工具。 在對ORACLE系統(tǒng)的整體情況有了一定的了解之后，下一步需要分析的就是系統(tǒng)運(yùn)行過程效率不高的SQL，這是對業(yè)務(wù)優(yōu)化的一個(gè)起點(diǎn)。假如這時(shí)不能夠在實(shí)際系統(tǒng)中操作，了解SQL的運(yùn)行過程是一個(gè)比較費(fèi)時(shí)的過程的。不管怎么樣，對于我們懷疑有問題的SQL，首先要閱讀的就是該SQL的在實(shí)際系統(tǒng)中的執(zhí)行計(jì)劃，語句涉及到的表的數(shù)據(jù)量，訪問表使用索引的選擇性如何，表連接的順序，多表之間的關(guān)聯(lián)關(guān)系等等。 對于ORACLE應(yīng)用系統(tǒng)的優(yōu)化，大方向上有一個(gè)順序，首先考慮優(yōu)化業(yè)務(wù)系統(tǒng)、再考慮優(yōu)化ORACLE系統(tǒng)本身的參數(shù)(如內(nèi)存分配等)，再考慮操作系統(tǒng)本身的優(yōu)化;在優(yōu)化業(yè)務(wù)系統(tǒng)中，首先是首先相關(guān)的SQL，以SQL入手分析表是否缺少索引，表連接順序是否正確，使用的索引是否正確等，然后再考慮調(diào)整表結(jié)構(gòu)，調(diào)整業(yè)務(wù)邏輯等等。因此，SQL語句是我們對一個(gè)ORACLE業(yè)務(wù)系統(tǒng)進(jìn)行優(yōu)化的敲門磚。 對于SQL優(yōu)化，前面提到了，ORACLE的執(zhí)行計(jì)劃是我們必須使用的工具。本來按ORACLE系統(tǒng)本身提供的方法獲取執(zhí)行計(jì)劃是一件非常麻煩的事情，不過現(xiàn)在的可視化工具比如PL/SQL DEVELOP或者TOAD等都給我們提供了非常方便的手段來獲取SQL語句的執(zhí)行計(jì)劃，不過我認(rèn)為ORACLE本身提供的方法還是有必要會(huì)的，非凡是在遠(yuǎn)程處理問題的時(shí)候(我也不會(huì)，一定要學(xué)習(xí)一下)。 獲取有性能問題的SQL語句，我們主要有兩個(gè)途徑，一個(gè)是通過STATSPACK報(bào)告。報(bào)告中有兩節(jié)是我們需要重點(diǎn)關(guān)注的:《SQL ordered by Gets for》和《SQL ordered by Reads for DB》，這兩節(jié)中分別按語句讀取內(nèi)存數(shù)據(jù)庫塊和讀取的物理數(shù)據(jù)庫塊(數(shù)據(jù)庫塊是指ORACLE的塊大小，一般是操作系統(tǒng)最小塊的整數(shù)倍)的數(shù)量倒序排列，假如其中的語句不全(太長)，可通過HASH_VALUE值到ORACLE的動(dòng)態(tài)視圖V$SQLTEXT中獲取該SQL的全部語句。第二個(gè)是通過ORACLE系統(tǒng)的動(dòng)態(tài)視圖，V$SQL，該視圖記錄了每個(gè)SQL語句的執(zhí)行次數(shù)，物理讀和內(nèi)存讀、執(zhí)行時(shí)間等等很多SQL語句的執(zhí)行信息，可以通過如下語句選擇一下物理讀和內(nèi)存讀較高的語句: SELECT
t.HASH_VALUE,
t.EXECUTIONS,
t.DISK_READS,
round(t.DISK_READS/t.EXECUTIONS) AS perDiskReads,
t.BUFFER_GETS,
round(t.BUFFER_GETS/t.EXECUTIONS) AS perBufferReads,
t.ELAPSED_TIME,
round(t.ELAPSED_TIME/t.EXECUTIONS) AS perElayTime,
t.CPU_TIME,
round(t.CPU_TIME/t.EXECUTIONS) AS perCpuTime,
t.FIRST_LOAD_TIME,
t.SQL_TEXT
FROM v$sql t
WHERE (t.DISK_READS/t.EXECUTIONS > 500 OR t.BUFFER_GETS/t.EXECUTIONS > 20000)
AND t.EXECUTIONS > 0
ORDER BY 6 DESC;
這個(gè)語句查詢出來的SQL可能也不全，也可以通過HASH_VALUE在V$SQLTEXT中找到其全部的SQL。 對于找到的SQL語句我們可以逐一分析其執(zhí)行計(jì)劃，結(jié)合涉及到的表的數(shù)據(jù)量，我們可以估算或者測試該語句的執(zhí)行效率，分析表WHERE條件中涉及的字段(術(shù)語叫做謂詞)，該字段數(shù)據(jù)分布如何，選擇性是否好，是否有索引。這是一個(gè)非常繁雜和瑣碎的工作，但從這些瑣碎的工作中，我們能發(fā)現(xiàn)那些SQL執(zhí)行時(shí)選擇的索引不對，哪些表缺少相應(yīng)的索引導(dǎo)致了全表掃描，哪些語句條件不夠?qū)е聦Ψ謪^(qū)表進(jìn)行了全表掃描。總之，對于一個(gè)給定的SQL，我們結(jié)合其表數(shù)據(jù)量的大小和分布，SQL中使用的查詢條件，能夠找到一個(gè)性能最優(yōu)的執(zhí)行方式，通過調(diào)整索引、使用ORACLE提示，使ORACLE系統(tǒng)按照最優(yōu)的方式來執(zhí)行SQL。如何去分析和確定ORACLE的執(zhí)行方式，一個(gè)最普遍的原則就是盡量根據(jù)其謂詞(查詢條件)，使用選擇性最好的索引(當(dāng)然，對于一些小表，可以考慮使用全表掃描的方式性能會(huì)更好)。對于SQL的執(zhí)行方式，需要在工作中不斷積累經(jīng)驗(yàn)，比如曾經(jīng)在一次優(yōu)化中發(fā)現(xiàn)對一個(gè)表安三個(gè)字段查詢的非常多，因此決定建立該三個(gè)字段的復(fù)合索引，但結(jié)果其語句執(zhí)行效率卻更差。 呵呵，今天先寫這么多了，希望大家多多指教
