ORACLE PLSQL優(yōu)化PPT課件

1、目的&預(yù)期結(jié)果目的： 1、一點(diǎn)經(jīng)驗(yàn)共享。 2、工作中指導(dǎo)作用。預(yù)期結(jié)果： 1、聽(tīng)后感覺(jué)到似乎懂了什么。 2、實(shí)際工作中確實(shí)能用上某點(diǎn)。第1頁(yè)/共43頁(yè)內(nèi)容PL/SQL優(yōu)化優(yōu)化 *索引索引索引誤區(qū)索引誤區(qū)索引優(yōu)化原則索引優(yōu)化原則索引如何選擇索引如何選擇 *執(zhí)行計(jì)劃執(zhí)行計(jì)劃如何選擇基表如何選擇基表(外表外表)如何查看執(zhí)行計(jì)劃如何查看執(zhí)行計(jì)劃 *常用常用sql PL/SQL注意事項(xiàng)注意事項(xiàng) SQL的使用規(guī)范的使用規(guī)范SQL優(yōu)化實(shí)例優(yōu)化實(shí)例(常用語(yǔ)句常用語(yǔ)句)第2頁(yè)/共43頁(yè) 索引誤區(qū)、原則、選擇索引索引第3頁(yè)/共43頁(yè)索引實(shí)現(xiàn)高效的實(shí)現(xiàn)高效的sql，建立合理的索引是非常必要的，建立合理的索引是非常必

2、要的索引種類索引種類1、B*tree2、反向索引、反向索引3、降序索引、降序索引4、位圖索引、位圖索引5、函數(shù)索引、函數(shù)索引第4頁(yè)/共43頁(yè)索引誤區(qū)索引越多越好？索引越多越好？5個(gè)個(gè) 一把雙刃劍，有利也有弊一把雙刃劍，有利也有弊對(duì)于一個(gè)單表的查詢，可以索引對(duì)于一個(gè)單表的查詢，可以索引1進(jìn)行過(guò)濾再使用進(jìn)行過(guò)濾再使用 索引索引2進(jìn)行過(guò)濾？進(jìn)行過(guò)濾？索引檢索一定比全表掃描快索引檢索一定比全表掃描快第5頁(yè)/共43頁(yè)索引優(yōu)化原則：原則：將索引和數(shù)據(jù)放在不同的文件組將索引和數(shù)據(jù)放在不同的文件組一定要有索引的引導(dǎo)項(xiàng)（第一個(gè)索引列）一定要有索引的引導(dǎo)項(xiàng)（第一個(gè)索引列）唯一索引的使用唯一索引的使用表關(guān)聯(lián)查詢時(shí)的

3、內(nèi)部表上關(guān)鍵列要有索引表關(guān)聯(lián)查詢時(shí)的內(nèi)部表上關(guān)鍵列要有索引如果該列的如果該列的“唯一鍵的數(shù)量唯一鍵的數(shù)量/表中記錄行數(shù)表中記錄行數(shù)”的的 比值越比值越 接近于接近于1， 則該列建立索引可能性較高則該列建立索引可能性較高 9i的跳躍索引的跳躍索引第6頁(yè)/共43頁(yè)索引選擇1、where從句中頻繁使用的關(guān)鍵字。從句中頻繁使用的關(guān)鍵字。2、和其他表關(guān)聯(lián)的關(guān)鍵字，考慮建立索引。、和其他表關(guān)聯(lián)的關(guān)鍵字，考慮建立索引。3、關(guān)鍵字的可選擇性比較高。、關(guān)鍵字的可選擇性比較高。4、建立組合索引的時(shí)候要選好前導(dǎo)列。、建立組合索引的時(shí)候要選好前導(dǎo)列。5、嵌套循環(huán)的內(nèi)表關(guān)聯(lián)字段一定要有索引。、嵌套循環(huán)的內(nèi)表關(guān)聯(lián)字段一定

4、要有索引。第7頁(yè)/共43頁(yè)我建立的索引有效嗎？為甚么要分析？CBO優(yōu)化分析的必要性。第8頁(yè)/共43頁(yè) 執(zhí)行計(jì)劃第9頁(yè)/共43頁(yè)SQL如何執(zhí)行？第10頁(yè)/共43頁(yè)內(nèi)表&外表RBO：外表是：外表是from后面的最右邊的表。后面的最右邊的表。CBO：外表自動(dòng)調(diào)節(jié)的：外表自動(dòng)調(diào)節(jié)的, 具體演示實(shí)例具體演示實(shí)例(看執(zhí)行計(jì)劃看執(zhí)行計(jì)劃)CBO為什么要分析表？為什么要分析表？RBO：基于規(guī)則的優(yōu)化CBO：基于代價(jià)的優(yōu)化參數(shù)調(diào)節(jié)：optimizer_mode: Rule 、choose、 all_rows、first_rows、first_rows_n表連接方式：排序 - - 合并連接(Sort Merge

5、Join (SMJ) )嵌套循環(huán)(Nested Loops (NL) )哈希連接(Hash Join)第11頁(yè)/共43頁(yè)如何查詢SQL效率？1、sql*plus2、 explain 3、工具（工具（plsql)第12頁(yè)/共43頁(yè)如何查詢SQL效率？第13頁(yè)/共43頁(yè)如何查詢SQL效率(續(xù))？使用Explain Plan$ORACLE_HOME/rdbms/admin/utlxplan.sql第14頁(yè)/共43頁(yè)如何查詢SQL效率(續(xù))？第15頁(yè)/共43頁(yè) PL塊的注意事項(xiàng)第16頁(yè)/共43頁(yè)P(yáng)L/SQL優(yōu)化PL/SQL（Procedural Language/ Structured Query L

6、anguage ）處理復(fù)雜的業(yè)務(wù)邏輯、快速開(kāi)發(fā)。處理復(fù)雜的業(yè)務(wù)邏輯、快速開(kāi)發(fā)。 與應(yīng)用程序分離，確保與應(yīng)用程序分離，確保sql語(yǔ)句的一致性。語(yǔ)句的一致性。高度安全高度安全 好的好的PL程序表面特征：程序表面特征：整潔、統(tǒng)一對(duì)其格式、關(guān)鍵字大寫。整潔、統(tǒng)一對(duì)其格式、關(guān)鍵字大寫。處理要完整。處理要完整。有針對(duì)性的捕獲異常。有針對(duì)性的捕獲異常。第17頁(yè)/共43頁(yè)下面程序需要做哪些優(yōu)化？沒(méi)有初始化缺少提交缺少異常第18頁(yè)/共43頁(yè)參數(shù)會(huì)傳嗎？盡量使用盡量使用%type使用使用%rowtype整體傳輸參數(shù)整體傳輸參數(shù)第19頁(yè)/共43頁(yè)數(shù)組的使用臨時(shí)表臨時(shí)表or數(shù)組？數(shù)組？為甚么使用數(shù)組？為甚么使用數(shù)組？

7、一個(gè)處理塊內(nèi)使用。一個(gè)處理塊內(nèi)使用。減少減少IO訪問(wèn)，快速訪問(wèn)，快速。第20頁(yè)/共43頁(yè)數(shù)組的使用（例）第21頁(yè)/共43頁(yè)SQL的使用規(guī)范第22頁(yè)/共43頁(yè)SQL的使用規(guī)范1、采用共享、采用共享sql 特別是動(dòng)態(tài)特別是動(dòng)態(tài)sql，必須使用變量綁定，避免，必須使用變量綁定，避免sql的重復(fù)解析。的重復(fù)解析。 2、 SELECT子句中避免使用子句中避免使用 * oracle需要轉(zhuǎn)換成表的列，通過(guò)查詢數(shù)據(jù)字典獲得，耗費(fèi)需要轉(zhuǎn)換成表的列，通過(guò)查詢數(shù)據(jù)字典獲得，耗費(fèi)時(shí)間時(shí)間3、使用表的別名使用表的別名(Alias) 明確的告訴明確的告訴oracle是哪個(gè)表上的列。不要將工作留給是哪個(gè)表上的列。不要將工作

8、留給oracle。第23頁(yè)/共43頁(yè)SQL的使用規(guī)范4、盡量避免反復(fù)訪問(wèn)同一張或幾張表，尤其是數(shù)據(jù)量較大、盡量避免反復(fù)訪問(wèn)同一張或幾張表，尤其是數(shù)據(jù)量較大 的表，可以考慮先根據(jù)條件提取數(shù)據(jù)到臨時(shí)表中，然的表，可以考慮先根據(jù)條件提取數(shù)據(jù)到臨時(shí)表中，然后再后再做連接。做連接。5、注意、注意where字句寫法，必須考慮語(yǔ)句順序，應(yīng)該根據(jù)索字句寫法，必須考慮語(yǔ)句順序，應(yīng)該根據(jù)索引順序、范圍大小來(lái)確定條件子句的前后順序，引順序、范圍大小來(lái)確定條件子句的前后順序，盡可能的讓字段順序與索引順序相一致，范圍從大到小。盡可能的讓字段順序與索引順序相一致，范圍從大到小。第24頁(yè)/共43頁(yè)SQL的使用規(guī)范6、不要在

9、、不要在where子句中的子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無(wú)法正確使用索引。盡量其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無(wú)法正確使用索引。盡量使用使用“=”，不要使用，不要使用“”。7、count(1)比比count(*)更有效率。更有效率。第25頁(yè)/共43頁(yè)SQL的使用規(guī)范8、注意表之間連接的數(shù)據(jù)類型，避免不同類型數(shù)據(jù)之間的、注意表之間連接的數(shù)據(jù)類型，避免不同類型數(shù)據(jù)之間的連接。注意存儲(chǔ)過(guò)程中參數(shù)和數(shù)據(jù)類型的關(guān)系連接。注意存儲(chǔ)過(guò)程中參數(shù)和數(shù)據(jù)類型的關(guān)系 。9、注意、注意insert、update操作的數(shù)據(jù)量，防止與其他應(yīng)用沖操作的數(shù)據(jù)量，防止

10、與其他應(yīng)用沖突。如果數(shù)據(jù)量超過(guò)突。如果數(shù)據(jù)量超過(guò)200個(gè)數(shù)據(jù)頁(yè)面（個(gè)數(shù)據(jù)頁(yè)面（400k），那么系統(tǒng)將），那么系統(tǒng)將會(huì)進(jìn)行鎖升級(jí)，頁(yè)級(jí)鎖會(huì)升級(jí)成表級(jí)鎖，不利于并行開(kāi)發(fā)。會(huì)進(jìn)行鎖升級(jí)，頁(yè)級(jí)鎖會(huì)升級(jí)成表級(jí)鎖，不利于并行開(kāi)發(fā)。第26頁(yè)/共43頁(yè)SQL優(yōu)化實(shí)例第27頁(yè)/共43頁(yè)SQL優(yōu)化1、選擇最有效率的表名順序：、選擇最有效率的表名順序：選擇記錄條數(shù)最少的表作為基礎(chǔ)表選擇記錄條數(shù)最少的表作為基礎(chǔ)表 說(shuō)明：以上說(shuō)明：以上oracle的的optimizer是是RBO的，的，CBO則無(wú)此限制，基表是怎么選則無(wú)此限制，基表是怎么選擇的？?jī)?nèi)表的關(guān)聯(lián)字段上要有索引。擇的？?jī)?nèi)表的關(guān)聯(lián)字段上要有索引。第28頁(yè)/共43

11、頁(yè)SQL優(yōu)化2、where子句的連接順序：子句的連接順序：ORACLE采用自下而上的順序解析采用自下而上的順序解析 WHERE子句子句,根據(jù)這個(gè)原理根據(jù)這個(gè)原理,表之間的連接必須寫在其他表之間的連接必須寫在其他WHERE條件之前條件之前, 那些可以過(guò)濾掉最大數(shù)量記錄的條件必須寫在那些可以過(guò)濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾子句的末尾 第29頁(yè)/共43頁(yè)SQL優(yōu)化3、減少對(duì)表的查詢、減少對(duì)表的查詢 第30頁(yè)/共43頁(yè)SQL優(yōu)化4、需要當(dāng)心的、需要當(dāng)心的WHERE子句（子句（在索引列上做運(yùn)算后，查詢將不走索引）在索引列上做運(yùn)算后，查詢將不走索引） 第31頁(yè)/共43頁(yè)SQL優(yōu)化5、用

12、、用EXISTS替代替代IN： 并不是絕對(duì)的并不是絕對(duì)的 第32頁(yè)/共43頁(yè)SQL優(yōu)化in適合內(nèi)外表都很大的情況，適合內(nèi)外表都很大的情況，exists適合外表結(jié)果集很小的情況。適合外表結(jié)果集很小的情況。 第33頁(yè)/共43頁(yè)SQL優(yōu)化6、用、用EXISTS替換替換DISTINCT（條件：外表與內(nèi)表是一對(duì)多的情況下使用）條件：外表與內(nèi)表是一對(duì)多的情況下使用） 第34頁(yè)/共43頁(yè)SQL優(yōu)化7、減少不必要的查詢、減少不必要的查詢 誤區(qū)：誤區(qū)：刪除數(shù)據(jù)前，先檢測(cè)是否存在，存在再刪除吧。刪除數(shù)據(jù)前，先檢測(cè)是否存在，存在再刪除吧。檢測(cè)是多此一舉，直接刪除！缺點(diǎn)：如果存在，多了一次交互，增加負(fù)擔(dān)。刪除不會(huì)出現(xiàn)異常，為什么要多一次查詢呢？第35頁(yè)/共43頁(yè)SQL優(yōu)化8、偽列的使用、偽列的使用 rowid 表里有重復(fù)數(shù)據(jù)，需要將其撿出并刪除。表里有重復(fù)數(shù)據(jù)，需要將其撿出并刪除。 最好將最好將rowid保留下來(lái)，做為后續(xù)處理的條件。保留下來(lái)，做為后續(xù)處理的條件。(行遷移？行遷移？)第36頁(yè)/共43頁(yè)SQL優(yōu)化9、執(zhí)行速度、執(zhí)行速度 當(dāng)處理大批量的數(shù)據(jù)的時(shí)候，處理時(shí)間可能較長(zhǎng)，有耐心等待嗎？當(dāng)處理大批量的數(shù)據(jù)的時(shí)候，處理時(shí)間可能較長(zhǎng)，有耐心等待嗎？ 時(shí)間允許等待嗎？該怎么辦？時(shí)間允許等待嗎？該怎么辦？多線程處理！第37頁(yè)/共43頁(yè)SQL優(yōu)化10、增加提示、增加提示(h