Oracle數(shù)據(jù)庫(kù)性能優(yōu)化精解.ppt

1、李軼楠Mail:ora技術(shù)服務(wù)人生,Oracle數(shù)據(jù)庫(kù)性能優(yōu)化精解,李軼楠Mail:ora技術(shù)服務(wù)人生,診斷工具中的七種武器,多情環(huán) sql tuning advisor/sql access advisor：多情環(huán)似乎是一個(gè)情種，誰擁有它似乎都會(huì)產(chǎn)生感情，從而對(duì)許多江湖事看的很淡。在Oracle應(yīng)用中，誰對(duì)性能影響最大，不言而喻，是SQL，準(zhǔn)確地說是SQL語句的算法，可以說，80%以上的性能問題都可以通過調(diào)整SQL來解決或者緩解，擁有調(diào)優(yōu)SQL性能的能力，基本上可以算作一個(gè)DBA高手咯。,李軼楠Mail:ora技術(shù)

2、服務(wù)人生,以前 檢查系統(tǒng)使用情況 查看等待事件 查看數(shù)據(jù)庫(kù)分散讀取上的等待事件 通過以下方法識(shí)別 SQL（難以操作） 識(shí)別具有大量數(shù)據(jù)庫(kù)分散讀取等待事件的會(huì)話并跟蹤它們，或者在 OEM 中查看最突出的會(huì)話 獲得解釋計(jì)劃 檢查被訪問的對(duì)象（大小/基數(shù)） 查看 SQL 統(tǒng)計(jì)信息和/或與對(duì)象統(tǒng)計(jì)信息相比較 (v$sql) （難以操作） 識(shí)別問題 聯(lián)系打包應(yīng)用程序的供應(yīng)商 為供應(yīng)商提供測(cè)試方案 供應(yīng)商提供補(bǔ)丁/升級(jí) 安裝在客戶的下一個(gè)維護(hù)周期中的補(bǔ)丁/升級(jí),Oracle10g 查看 ADDM 建議 根據(jù)鏈接來運(yùn)行自動(dòng) SQL 調(diào)整 接受來自 SQL 調(diào)整的 SQL 描述文件建議,李軼楠Mail:ora

3、技術(shù)服務(wù)人生,執(zhí)行計(jì)劃,執(zhí)行計(jì)劃是一系列的優(yōu)化器用來完成SQL操作的步驟和操作,李軼楠Mail:ora技術(shù)服務(wù)人生,曾經(jīng)我們?nèi)绾尾榭磮?zhí)行計(jì)劃,通過下面的工具能夠看到執(zhí)行計(jì)劃 EXPLAIN PLAN V$SQL_PLAN SQL Trace SQL*Plus AUTOTRACE 看到執(zhí)行計(jì)劃不是目的，優(yōu)化與分析仍然靠DBA去努力。,李軼楠Mail:ora技術(shù)服務(wù)人生,SQL調(diào)優(yōu)建議,SQL Tuning ,SQL SELECT /*+ INDEX(customers gen_idx)*/ 2 cust_last_na

4、me, cust_street_address, 3 cust_postal_code 4 FROM sh.customers 5 WHERE UPPER (cust_gender) = M;,李軼楠Mail:ora技術(shù)服務(wù)人生,Optimizer Hint Example,SQL update -+ INDEX(p PROD_CATAGORY_IDX) 2 products p 3 set d_min_price = 4 (select 5 (d_list_price*.95) 6 from products pr 7 where d

5、_id = d_id) 8 where d_category = Men 9 and d_status = available, on stock 10 /,李軼楠Mail:ora技術(shù)服務(wù)人生,診斷工具中的七種武器,拳頭：沒有武器就是有武器，有武器就是沒有武器。最后一種武器-拳頭，就是對(duì)整個(gè)體系的全面理解，無形的武器勝于有形的武器，就像太極，沒有招數(shù)就是最好的招數(shù)。 作為一個(gè)DBA，或者更高一些，作為一個(gè)架構(gòu)管理員，能夠理解整個(gè)業(yè)務(wù)系統(tǒng)，對(duì)數(shù)據(jù)庫(kù)、存儲(chǔ)、網(wǎng)絡(luò)、系統(tǒng)、應(yīng)用軟件、業(yè)務(wù)流程都非常清楚，甚至于對(duì)使用者的使用習(xí)慣都非常清楚，優(yōu)化就

6、不再是什么高難度了。天地之大皆裝于我胸中，萬物皆為我之神兵。如果真有那么一天一切都在你的掌握之中，優(yōu)化也許會(huì)變得非常easy,李軼楠Mail:ora技術(shù)服務(wù)人生,七種武器之外,除了介紹到的這七種武器，實(shí)際上做優(yōu)化和診斷的還有很多很多利器，不是一定要“上榜”的才是好兵器 只要管用，板磚也是好武器（何況有些板磚還很趁手） 例如： DBMS_XPLAN包 SELECT * FROM table(DBMS_XPLAN.DISPLAY); SELECT * FROM table(DBMS_XPLAN. DISPLAY_CURSOR(SQL_ID, CURSOR_CHILD_NO

7、); SELECT * FROM table(dbms_xplan.display_cursor(null,null,iostats last); SELECT * FROM table(dbms_xplan.display_awr(SQL_ID); 10046事件 Oradebug setospid Oradebug event 10046 trace name context forever,level 8 v$sql_plan Segment advisor Memory advisor Lock momitor ,李軼楠Mail:ora技術(shù)服務(wù)人生,總結(jié),優(yōu)化

8、的工具有千千萬，找到適合的最關(guān)鍵 精通兩、三個(gè)工具，比什么工具都“會(huì)”使更有用 工具就是工具，最終優(yōu)化人來定 工具是可以換的，人“才”是換不來的 優(yōu)化應(yīng)該在系統(tǒng)中整體貫穿，早期的優(yōu)化會(huì)帶來更大的性能提升，而當(dāng)需要我們用優(yōu)化工具的時(shí)候似乎已經(jīng)有點(diǎn)晚。,李軼楠Mail:ora技術(shù)服務(wù)人生,性能優(yōu)化經(jīng)典案例詳解,李軼楠Mail:ora技術(shù)服務(wù)人生,案例1 OS配置不當(dāng)造成的數(shù)據(jù)庫(kù)掛起,場(chǎng)景： AIX5L， rac，服務(wù)器物理內(nèi)存8G 故障現(xiàn)象： 數(shù)據(jù)庫(kù)啟動(dòng)后正常，業(yè)務(wù)連結(jié)后開始沒有問題，運(yùn)行一段時(shí)間后所有操作掛起，包括os的命令（報(bào)

9、內(nèi)存不足） 分析思路： 主要原因應(yīng)該是：資源耗盡（確定哪種資源） A.死進(jìn)程造成資源耗盡 B.其他應(yīng)用資源泄露 C.服務(wù)器限制了某種資源不足 也可能是bug或者異常 重新啟動(dòng)后確定是否仍然出現(xiàn) 查詢相關(guān)文檔，確定是否存在bug 結(jié)論： 安裝os時(shí)由于使用了默認(rèn)安裝方式，導(dǎo)致交換區(qū)設(shè)置太小，僅為512M，因此在安裝oracle數(shù)據(jù)庫(kù)時(shí)可以安裝，但運(yùn)行一段時(shí)間后交換區(qū)耗盡，操作掛起,李軼楠Mail:ora技術(shù)服務(wù)人生,案例2 一條簡(jiǎn)單SQL帶來的硬解析麻煩,場(chǎng)景： linuxas4， 單機(jī)，服務(wù)器物理內(nèi)存8G 故障現(xiàn)象： 服務(wù)器CPU持續(xù)高消耗，即使連結(jié)斷

10、開，CPU持續(xù)消耗30-40% 分析思路： 數(shù)據(jù)庫(kù)中有自動(dòng)運(yùn)行的job 存在少量job，其中有一個(gè)job每5分鐘執(zhí)行一次，job中存在for循環(huán)，不斷查詢一張表的每條記錄，當(dāng)發(fā)現(xiàn)查出的記錄標(biāo)志字段被改，則執(zhí)行特定操作，查詢語句類似如下： execute immediate select * from emp where rownum=|x; 服務(wù)器上有高消耗cpu的程序 Cpu資源均被oracle進(jìn)程消耗 結(jié)論： 頻繁執(zhí)行的未綁定SQL會(huì)帶來大量硬解析，延長(zhǎng)語句的執(zhí)行時(shí)間，并嚴(yán)重消耗CPU,李軼楠Mail:ora技術(shù)服務(wù)人生,案例3 增加索引帶來的性能問題,場(chǎng)景： 9

11、i單機(jī)，windows XP 故障現(xiàn)象： 用戶原有語句執(zhí)行效率很高，為了滿足另一個(gè)查詢的需求，用戶增加了一個(gè)新的索引，造成原有語句效率嚴(yán)重下降 分析思路： 僅僅是增加了索引就造成性能下降，應(yīng)該是選擇了錯(cuò)誤的索引算法 結(jié)論： RBO下索引的選擇很可能出錯(cuò)，一定要小心，必要時(shí)可以固定執(zhí)行計(jì)劃,李軼楠Mail:ora技術(shù)服務(wù)人生,案例4 內(nèi)存自動(dòng)管理的性能問題,場(chǎng)景： 10G數(shù)據(jù)庫(kù)，物理內(nèi)存16GB，SGA自動(dòng)管理，大小為8GB 故障現(xiàn)象： 運(yùn)行一段時(shí)間，服務(wù)器上所有操作全部掛起，連接也無法建立，大約幾分鐘之后自動(dòng)恢復(fù)正常 分析思路： 出現(xiàn)了高負(fù)載操作，造成系統(tǒng)突然資源消耗

12、過度，不能相應(yīng)其他請(qǐng)求 系統(tǒng)bug 結(jié)論： 由于10G SGA自動(dòng)管理，當(dāng)業(yè)務(wù)操作特性發(fā)生變化造成內(nèi)存收縮時(shí)，由于大內(nèi)存回收會(huì)帶來很大的開銷，尤其是各種latch的消耗，因此在沒有完成收縮之前，所有操作都會(huì)受到很大影響,李軼楠Mail:ora技術(shù)服務(wù)人生,案例5 SQL書寫與索引的使用,場(chǎng)景： 10g，將特定的數(shù)據(jù)排除后分組匯總，使用符號(hào) 故障現(xiàn)象： 在加載了一些數(shù)據(jù)后，原有的查詢語句取出的數(shù)據(jù)不多，但速度很慢 分析思路： 10g默認(rèn)走CBO，懷疑統(tǒng)計(jì)信息陳舊或者參數(shù)調(diào)整導(dǎo)致算法錯(cuò)誤，或者本身Oracle計(jì)算出的執(zhí)行計(jì)劃就有問題 發(fā)現(xiàn)需要排除的數(shù)據(jù)特別的多 結(jié)論： 符

13、號(hào)不論在CBO還是RBO中，都會(huì)帶來全表掃描的操作，當(dāng)數(shù)據(jù)分布均勻時(shí)，這是正確的選擇，但如果出現(xiàn)特殊情況，需要排除的是大量數(shù)據(jù)，查詢出的是少量數(shù)據(jù)，則索引是更好的選擇，因此在CBO下應(yīng)該將符號(hào)替換成“” 的寫法,李軼楠Mail:ora技術(shù)服務(wù)人生,案例6 綁定變量帶來的性能問題,場(chǎng)景： 10g，范圍查詢語句，為了減少硬解析，使用了綁定變量 故障現(xiàn)象： SQL執(zhí)行速度有時(shí)很快，有時(shí)很慢 分析思路： 綁定變量帶來的好處是硬解析的減少，但硬解析的減少也意味著執(zhí)行計(jì)劃的不變 范圍查詢時(shí)，取數(shù)據(jù)的多少直接決定了執(zhí)行計(jì)劃的選擇 結(jié)論： 在范圍查詢時(shí)，如果查詢獲取的數(shù)據(jù)量并不是確定

14、不變的，而是有可能有大范圍的變化，不要使用綁定變量，大數(shù)據(jù)量訪問時(shí)，執(zhí)行計(jì)劃的準(zhǔn)確性對(duì)性能的影響遠(yuǎn)大于減少硬解析,李軼楠Mail:ora技術(shù)服務(wù)人生,案例7 觸發(fā)器對(duì)性能的影響,場(chǎng)景： 10g，aix主機(jī)，應(yīng)用系統(tǒng)升級(jí)，新增加部分業(yè)務(wù)功能 故障現(xiàn)象： 兩個(gè)db之間需要進(jìn)行數(shù)據(jù)同步，原本在5分鐘即可同步完成，現(xiàn)在需要15-20分鐘 分析思路： 同步即數(shù)據(jù)插入，在數(shù)據(jù)量不變的情況下，數(shù)據(jù)插入速度嚴(yán)重下降，可能有鎖競(jìng)爭(zhēng)、回滾段競(jìng)爭(zhēng)、日志緩存區(qū)等待或者索引、約束、觸發(fā)器的影響 有部分業(yè)務(wù)變更，因此并發(fā)競(jìng)爭(zhēng)、索引、約束、觸發(fā)器的可能性較大 結(jié)論： 由于在表上增加了行級(jí)觸發(fā)器，造

15、成每行插入時(shí)都不得不執(zhí)行觸發(fā)器，造成整個(gè)同步動(dòng)作變慢，減少觸發(fā)器的使用，尤其是行級(jí)觸發(fā)器,李軼楠Mail:ora技術(shù)服務(wù)人生,案例8 TB級(jí)分區(qū)表上分區(qū)索引的選擇，3小時(shí)與0.3秒,場(chǎng)景： 數(shù)據(jù)量在TB級(jí)的分區(qū)表，按時(shí)間字段進(jìn)行范圍分區(qū)，分區(qū)字段上有索引，根據(jù)用戶選擇條件進(jìn)行數(shù)據(jù)查詢 故障現(xiàn)象： 用戶每次查詢時(shí)間長(zhǎng)達(dá)2-3小時(shí) 分析思路： 數(shù)據(jù)量巨大，用戶查詢時(shí)間長(zhǎng)，很可能跟算法錯(cuò)誤造成大量I/O有關(guān) 用戶查詢需求不確定，而且用戶在查詢時(shí)經(jīng)常不選擇時(shí)間范圍 結(jié)論： 當(dāng)分區(qū)字段沒有作為查詢條件出現(xiàn)或該字段沒有過濾大量數(shù)據(jù)時(shí)，將不得不走全表掃描 如果不能使用分區(qū)字段進(jìn)行數(shù)

16、據(jù)過濾，則必須在其他查詢字段上建立索引 一般來說，分區(qū)索引的效率僅比全局索引效率略低(主要體現(xiàn)在需要更多的索引分區(qū)I/O)，但全局索引的維護(hù)開銷更大，綜合考慮進(jìn)行選擇,李軼楠Mail:ora技術(shù)服務(wù)人生,案例9 不同Count的效率分析,場(chǎng)景： 9i，經(jīng)常需要對(duì)一些表進(jìn)行記錄數(shù)匯總 故障現(xiàn)象： 統(tǒng)計(jì)記錄數(shù)的效率較低 分析思路： Count在進(jìn)行匯總時(shí)，經(jīng)常會(huì)走全表掃描，dba建議將count(*)調(diào)整為count(1)，效果不大 結(jié)論： Count(*)與count(1)均是匯總符合條件的總記錄數(shù)，在沒有索引或者RBO下均需要走全表掃描，要提高匯總速度，最好走索引，在

17、CBO模式下，索引字段如果限制了非空約束，Oracle會(huì)將Count(*)或者count(1)轉(zhuǎn)換為非空索引的全索引掃描,李軼楠Mail:ora技術(shù)服務(wù)人生,案例10 索引創(chuàng)建順序?qū)λ饕x擇的影響,場(chǎng)景： 9i，rbo模式 故障現(xiàn)象： 大字段和小字段都有索引，但語句執(zhí)行時(shí)選擇錯(cuò)誤的大索引 例如：select * from test10 where c2 and b2; 分析思路： RBO下執(zhí)行計(jì)劃與語句書寫有關(guān)，但該語句書寫上沒有明顯問題 結(jié)論： 當(dāng)謂詞級(jí)別不同時(shí)，選則優(yōu)級(jí)別高的索引，當(dāng)謂詞級(jí)別相同時(shí)，選最新創(chuàng)建的索引,李軼楠Mail:ora

18、技術(shù)服務(wù)人生,案例11 數(shù)據(jù)分布與索引,場(chǎng)景： 8i 升級(jí)至 9i，根據(jù)開發(fā)商建議，收集統(tǒng)計(jì)信息，開始使用CBO模式 故障現(xiàn)象： 大部分語句性能得到提升，但有部分語句效率極低，主要集中在某幾個(gè)表特定字段的查詢上 分析思路： CBO基于統(tǒng)計(jì)信息進(jìn)行代價(jià)計(jì)算 統(tǒng)計(jì)信息的準(zhǔn)確性和全面性直接影響執(zhí)行計(jì)劃 結(jié)論： CBO對(duì)統(tǒng)計(jì)信息的準(zhǔn)確性和全面性要求非常高 數(shù)據(jù)分布均勻與否，決定了是否需要進(jìn)行直方圖的收集，而直方圖的柱數(shù)決定了收集信息的準(zhǔn)確性 動(dòng)態(tài)采樣也是一種選擇，總好過錯(cuò)誤的信息收集,李軼楠Mail:ora技術(shù)服務(wù)人生,案例12 索引對(duì)分組計(jì)算的影響,場(chǎng)景： 10g，在大表上

19、分組匯總，計(jì)算記錄數(shù)，考慮到分組需要排序，而有索引可以減少排序，因此在匯總字段上建立了索引 故障現(xiàn)象： 匯總記錄數(shù)的操作仍然很慢 分析思路： 速度很慢說明語句仍然在走全表掃描，索引沒有有效利用 結(jié)論： 如果需要通過索引字段進(jìn)行count計(jì)算，必須保證索引記錄與表中記錄數(shù)完全相同，而能夠提供這種保證的，一定是非空約束,李軼楠Mail:ora技術(shù)服務(wù)人生,案例13 反轉(zhuǎn)索引對(duì)查詢的幫助,場(chǎng)景： 10g，用戶需要做模糊查詢，查詢最后幾個(gè)字母確定的記錄 故障現(xiàn)象： 由于like在模糊查詢時(shí)必須首字母確定才能夠走索引，而用戶的查詢條件是最后幾個(gè)字母確定，因此like正常使用將不

20、得不走全表掃描 分析思路： 最后幾個(gè)字母確定，如果最后幾個(gè)確定的字母能成為like查詢的首字母，則like查詢可以走索引 結(jié)論： 反轉(zhuǎn)索引除了可以在順序字段做等值比較時(shí)分散I/O，減少熱點(diǎn)塊，也能夠使這種需求的查詢走索引，從而更快的獲取數(shù)據(jù),李軼楠Mail:ora技術(shù)服務(wù)人生,案例14 盡量避免的外聯(lián)接,場(chǎng)景： 9i，多表連接，選擇算法為外連接，但在外連接的表上有過濾條件 故障現(xiàn)象： 效率較低 分析思路： 檢查發(fā)現(xiàn)，過濾條件已經(jīng)將所有不完全匹配的記錄過濾 結(jié)論： 如果經(jīng)過過濾之后的數(shù)據(jù)能夠完全匹配，應(yīng)該用等值連接代替外連接,李軼楠Mail:ora-1333119203

21、0 技術(shù)服務(wù)人生,案例15 外鍵索引與delete的性能關(guān)系,場(chǎng)景： 子表上已經(jīng)刪除了大量數(shù)據(jù)，需要將主表上相關(guān)的數(shù)據(jù)刪除 故障現(xiàn)象： 在主表上刪除數(shù)據(jù)時(shí)非常慢 分析思路： Delete性能差，主要的原因是鎖競(jìng)爭(zhēng)、回滾段競(jìng)爭(zhēng)、日志、索引維護(hù)等原因，但察看發(fā)現(xiàn)這些問題均不是很嚴(yán)重，發(fā)現(xiàn)i/o讀相當(dāng)大 結(jié)論： 由于外鍵需要校驗(yàn)數(shù)據(jù)參照完整性，因此在刪除主表記錄時(shí)必須在子表上查詢相關(guān)數(shù)據(jù)，而子表上外鍵字段上沒有索引造成每校驗(yàn)主表一條數(shù)據(jù)，就不得不全表掃描子表一次,李軼楠Mail:ora技術(shù)服務(wù)人生,案例16 分頁(yè)查詢的性能,場(chǎng)景： 網(wǎng)站，10g數(shù)據(jù)庫(kù)，根據(jù)用戶需求分頁(yè)顯示結(jié)

22、果 故障現(xiàn)象： 網(wǎng)頁(yè)頁(yè)面顯示非常慢，即使是網(wǎng)頁(yè)的第一頁(yè)，只取出很少的數(shù)據(jù) 分析思路： 分頁(yè)顯示時(shí)，用戶首先看到的首頁(yè)的前n行，而大部分時(shí)候翻頁(yè)動(dòng)作不會(huì)做很多次，因此需要讓前n頁(yè)的顯示盡可能快 結(jié)論： 在排序和過濾的字段上建立索引，并使用first_rows提示，將會(huì)提高分頁(yè)查詢的效率,李軼楠Mail:ora技術(shù)服務(wù)人生,案例17 組合索引的跳躍式索引掃描,場(chǎng)景： 9i，組合索引，查詢語句中沒有出現(xiàn)組合索引的前導(dǎo)字段 故障現(xiàn)象： 查詢速度非常慢，有大量的I/O 分析思路： RBO下，組合索引前導(dǎo)字段沒有出現(xiàn)時(shí)，走全表掃描 該組合索引的前導(dǎo)字段被大量查詢使用，但該查詢的where子句中沒有出現(xiàn)前導(dǎo)字段，只出現(xiàn)了其它字段 符合查詢條件的數(shù)據(jù)很少 結(jié)論： 在取數(shù)據(jù)少的情況下，索引效率更高 在CBO模式下，即使前導(dǎo)字段在wher