【正文】 ORACLE培訓(xùn)SQL性能優(yōu)化內(nèi)容概述內(nèi)容概述課程主要討論：SQL語(yǔ)句執(zhí)行的過程、 ORACLE優(yōu)化器，表之間的關(guān)聯(lián)，如何得到 SQL執(zhí)行計(jì)劃，如何分析執(zhí)行計(jì)劃等內(nèi)容，從而由淺到深的方式了解 SQL優(yōu)化的過程，使大家逐步掌握 SQL優(yōu)化。目錄目錄1.優(yōu)化基礎(chǔ)知識(shí)2.性能調(diào)整綜述3.有效的應(yīng)用設(shè)計(jì)4.SQL語(yǔ)句的處理過程5.Oracle的優(yōu)化器6.Oracle的執(zhí)行計(jì)劃7.注意事項(xiàng)SQL語(yǔ)句優(yōu)化的過程語(yǔ)句優(yōu)化的過程n定位有問題的語(yǔ)句n檢查執(zhí)行計(jì)劃n檢查執(zhí)行過程中優(yōu)化器的統(tǒng)計(jì)信息n分析相關(guān)表的記錄數(shù)、索引情況n改寫 SQL語(yǔ)句、使用 HINT、調(diào)整索引、表分析n有些 SQL語(yǔ)句不具備優(yōu)化的可能，需要優(yōu)化處理方式n達(dá)到最佳執(zhí)行計(jì)劃什么是好的什么是好的 SQL語(yǔ)句？語(yǔ)句？n盡量簡(jiǎn)單，模塊化n易讀、易維護(hù)n節(jié)省資源168。內(nèi)存168。CPU168。掃描的數(shù)據(jù)塊要少168。少排序n不造成死鎖為什么要 bindvariables?n 字符級(jí)的比較 :168。SELECT*FROMUSER_FILESWHEREUSER_NO=‘10001234’。與168。SELECT*FROMUSER_FILESWHEREUSER_NO=:BV1。n 檢查：168。selectname,executions168。fromv$db_object_cache168。wherenamelike39。select*fromuser_files%39。什么叫做重編譯問題什么叫做重編譯？下面這個(gè)語(yǔ)句每執(zhí)行一次就需要在 SHAREPOOL硬解析一次，一百萬(wàn)用戶就是一百萬(wàn)次，消耗 CPU和內(nèi)存，如果業(yè)務(wù)量大，很可能導(dǎo)致宕庫(kù) ……如果綁定變量，則只需要硬解析一次，重復(fù)調(diào)用即可select*fromdConMsgwherecontract_no=32023484095139綁定變量解決重編譯問題未使用綁定變量的語(yǔ)句sprintf(sqlstr,insertinto(num1,num2)values(%d,%d),n_var1,n_var2)。EXECSQLEXECUTEIMMEDIATE:sqlstrEXECSQLCOMMIT。使用綁定變量的語(yǔ)句 strcpy(sqlstr,insertintotest(num1,num2)values(:v1,:v2))。EXECSQLPREPAREsql_stmtFROM:sqlstr。EXECSQLEXECUTEsql_stmtUSING:n_var1,:n_var2。EXECSQLCOMMIT。綁定變量的注意事項(xiàng)注意：不要使用數(shù)據(jù)庫(kù)級(jí)的變量綁定參數(shù)cursor_sharing來強(qiáng)制綁定，無論其值為 force還是 similar有些帶 的語(yǔ)句綁定變量后可能導(dǎo)致優(yōu)化器無法正確使用索引SQL語(yǔ)句的 四個(gè) 處理階段SQL語(yǔ)句的處理過程語(yǔ)句的處理過程解析 (PARSE)：SQL語(yǔ)句的處理過程語(yǔ)句的處理過程1. 在共享池中查找 SQL語(yǔ)句2. 檢查語(yǔ)法3. 檢查語(yǔ)義和相關(guān)的權(quán)限4. 合并 (MERGE)視圖定義和子查詢5. 確定執(zhí)行計(jì)劃綁定 (BIND)：SQL語(yǔ)句的處理過程語(yǔ)句的處理過程1. 在語(yǔ)句中查找綁定變量2. 賦值 (或重新賦值 )執(zhí)行 (EXECUTE)：SQL語(yǔ)句的處理過程語(yǔ)句的處理過程1. 應(yīng)用執(zhí)行計(jì)劃2. 執(zhí)行必要的 I/O和排序操作提取 (FETCH)：1. 從查詢結(jié)果中返回記錄2. 必要時(shí)進(jìn)行排序3. 使用 ARRAY FETCH機(jī)制共享游標(biāo)：好處共享游標(biāo)：好處1.減少解析2.動(dòng)態(tài)內(nèi)存調(diào)整3.提高內(nèi)存使用率書寫可共享的書寫可共享的 SQL綁定變量和共享游標(biāo)綁定變量和共享游標(biāo)ORACLE優(yōu)化器模式優(yōu)化器模式 概述概述Oracle的優(yōu)化器共有 3種模式： RULE(基于規(guī)則 )、 COST(基于成本 )、 CHOOSE(基于選擇 )。設(shè)置缺省的優(yōu)化器的方法，是在啟動(dòng)參數(shù)文件中針對(duì)OPTIMIZER_MODE參數(shù)的各種聲明進(jìn)行選擇，如 RULE、 COST、CHOOSE、 ALL_ROWS、 FIRST_ROWS。當(dāng)然也可以在 SQL語(yǔ)句級(jí)別或是會(huì)話級(jí)別對(duì)其進(jìn)行覆蓋。為了使用基于成本的優(yōu)化器 (CBO， Cost—BasedOptimizer) ，必須經(jīng)常運(yùn)行 analyze命令，以增加數(shù)據(jù)庫(kù)中的對(duì)象統(tǒng)計(jì)信息(objectstatistics)的準(zhǔn)確性。如果數(shù)據(jù)庫(kù)的優(yōu)化器模式 設(shè)置 為基于選擇，那么實(shí)際的優(yōu)化器模式將和是否運(yùn)行過 analyze命令有關(guān)。如果數(shù)據(jù)表已經(jīng)被analyze過，優(yōu)化器模式將自動(dòng)切換成 CBO，反之，數(shù)據(jù)庫(kù)將采用RULE形式的優(yōu)化器。在缺省情況下， Oracle采用 CHOOSE優(yōu)化器。為避免那些不必要的全表掃描，必須盡量避免使用CHOOSE優(yōu)化器，而直接采用基于規(guī)則或者基于成本的優(yōu)化器。訪問數(shù)據(jù)表的方式訪問數(shù)據(jù)表的方式① 全表掃描全表掃描就是順序地訪問表中每條記錄。 Oracle采用一次讀 入多個(gè)數(shù)據(jù)塊 (databaseblock)的方式優(yōu)化全表掃描。② 通過 ROWID訪問表ROWID包含了表中記錄的物理位置信息?？梢圆捎没赗OWID的訪問方式情況提高訪問表的效率。 Oracle采用索引實(shí)現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置 (ROWID)之間的聯(lián)系 通常索引提供 了快速訪問 ROWID的方法，因此那些基于索引列的查詢就可以得到性能的提高。數(shù)據(jù)庫(kù)性能n 影響數(shù)據(jù)庫(kù)系統(tǒng)性能的要素：168。 主機(jī) CPU,RAM,存儲(chǔ)系統(tǒng) 。168。OS參數(shù)配置 ,ORACLE參數(shù)配置 。168。 應(yīng)用方面：數(shù)據(jù)庫(kù)設(shè)計(jì)及 SQL編程的質(zhì)量n 一個(gè)性能優(yōu)秀的應(yīng)用系統(tǒng)需要：168。 良好的硬件配置；正確合理的數(shù)據(jù)庫(kù)及中間件參數(shù)配置；合理的數(shù)據(jù)庫(kù)設(shè)計(jì)；良好的 sql編程；運(yùn)行期的性能優(yōu)化SQLTunning的重點(diǎn)nSQL:insert,update,delete,select。n主要關(guān)注的是 selectn關(guān)注的是：如何用最小的硬件資源消耗、最少的響應(yīng)時(shí)間定位數(shù)據(jù)位置SQL優(yōu)化的一般性原則n 目標(biāo)：168。 減少服務(wù)器資源消耗（主要是磁盤 IO）；n 設(shè)計(jì)方面：168。 盡量依賴 oracle的優(yōu)化器，并為其提供條件；168。 合適的索引，索引的雙重效應(yīng)，列的選擇性；n 編碼方面：168。 利用索引，避免大表 FULLTABLESCAN；168。 合理使用臨時(shí)表；168。 避免寫過于復(fù)雜的 sql，不一定非要一個(gè) sql解決問題；168。 在不影響業(yè)務(wù)的前提下減小事務(wù)的粒度；優(yōu)化概括課程 Oracle數(shù)據(jù)庫(kù) SQL語(yǔ)句優(yōu)化的總體策略。以這些優(yōu)化策略為指導(dǎo)，通過經(jīng)驗(yàn)總結(jié)，我們可以不斷地豐富優(yōu)化方案，進(jìn)而指導(dǎo)我們進(jìn)行應(yīng)用系統(tǒng)的數(shù)據(jù)庫(kù)性能優(yōu)化。以下枚舉幾則被證明行之有效的優(yōu)化方案：●創(chuàng)建表的時(shí)候。應(yīng)盡量建立主鍵，盡量根據(jù)實(shí)際需要調(diào)整數(shù)據(jù)表的 PCTFREE和 PCTUSED參數(shù)；大數(shù)據(jù)表刪除，用 truncatetable代替 delete?！窈侠硎褂盟饕?，在 OLTP應(yīng)用中一張表的索引不要太多。數(shù)據(jù)重復(fù)量大的列不要建立二叉樹索引，可以采用位圖索引；組合索引的列順序盡量與查詢條件列順序保持一致；對(duì)于數(shù)據(jù)操作頻繁的表，索引需要定期重建，以減少失效的索引和碎片。優(yōu)化概括●查詢盡量用確定的列名，少用 *號(hào)。 selectcount(key)fromtabwherekey0性能優(yōu)于 selectcount(*)fromtab；盡量少嵌套子查詢，這種查詢會(huì)消耗大量的 CPU資源；對(duì)于有比較多or運(yùn)算的查詢，建議分成多個(gè)查詢，用 unionall聯(lián)結(jié)起來 .●盡量多用 mit語(yǔ)句提交事務(wù)，可以及時(shí)釋放資源、解鎖、釋放日志空間、減少管理花費(fèi)；在頻繁的、性能要求比較高的數(shù)據(jù)操作中，盡量避免遠(yuǎn)程訪問，如數(shù)據(jù)庫(kù)鏈等，訪問頻繁的表可以常駐內(nèi)存： altertable．．． cache；●在 Oracle中動(dòng)態(tài)執(zhí)行 SQL，盡量用 execute方式，不用dbms_sql包。sql語(yǔ)句的編寫原則和優(yōu)化 n在編寫 SQL語(yǔ)句時(shí)我們應(yīng)清楚優(yōu)化器根據(jù)何種原則來使用索引，這有助于寫出高性能的 SQL語(yǔ)句。nSQL語(yǔ)句的編寫原則和 SQL語(yǔ)句的優(yōu)化，請(qǐng)跟我一起學(xué)習(xí)以下幾方面：避免復(fù)雜的多表關(guān)聯(lián)select…fromuser_filesuf,df_money_filesdm,cw_charge_recordccwhere=and=and……andnotexists(select…)???很難優(yōu)化，隨著數(shù)據(jù)量的增加性能的風(fēng)險(xiǎn)很大。避免使用耗費(fèi)資源的操作帶有 DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL語(yǔ)句會(huì)啟動(dòng) SQL引擎執(zhí)行耗費(fèi)資源的排序 (SORT)功能 .DISTINCT需要一次排序操作 ,而其他的至少需要執(zhí)行兩次排序 .例如 ,一個(gè) UNION查詢 ,其中每個(gè)查詢都帶有 GROUPBY子句,GROUPBY會(huì)觸發(fā)嵌入排序 (NESTEDSORT)這樣 ,每個(gè)查詢需要執(zhí)行一次排序 ,然后在執(zhí)行 UNION時(shí) ,又一個(gè)唯一排序 (SORTUNIQUE)操作被執(zhí)行而且它只能在前面的嵌入排序結(jié)束后才能開始執(zhí)行 .嵌入的排序的深度會(huì)大大影響查詢的效率 .通常 ,帶有 UNION,MINUS,INTERSECT的 SQL語(yǔ)句都可以用其他方式重寫 .例如 :低效 :SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERE=高效 :SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT‘X’FRO