SQL優(yōu)化是數(shù)據(jù)優(yōu)化的重要方面，本文將分析Oracle自身的CBO優(yōu)化，即基于成本的優(yōu)化方法。Oracle為了自動的優(yōu)化sql語句需要各種統(tǒng)計數(shù)據(jù)作為優(yōu)化基礎(chǔ)。外面會通過sql的追蹤來分析sql的執(zhí)行過程，消耗的資源信息。對于數(shù)據(jù)庫的性能問題往往是在系統(tǒng)部署一段時間之后出現(xiàn)的，即大量用戶開始使用該系統(tǒng)，系統(tǒng)的數(shù)據(jù)處理量和各種計算復(fù)雜性增加的時候，這個時候往往會追溯到系統(tǒng)的初始設(shè)計階段，所以我們還是要在編碼階段就編寫高效的sql語句。我在網(wǎng)上看到了很多關(guān)于sql優(yōu)化的文章，但是不盡人意，有的很籠統(tǒng)的描述有的根本還是錯誤的方法，所以我重新將我的學(xué)習(xí)過程分享出來。
一、SQL查詢處理過程詳解
查詢處理與查詢優(yōu)化是兩個相關(guān)聯(lián)的概念，查詢處理時執(zhí)行SQL語句獲取數(shù)據(jù)的過程，而查詢優(yōu)化是通過分析SQL語句以及其他資源獲得最佳執(zhí)行計劃的過程。在這里最佳的執(zhí)行計劃。我指的是消耗資源最少的計劃，例如包含有數(shù)據(jù)庫服務(wù)器的CPU和系統(tǒng)I/O。一條SQL 的執(zhí)行分為3個階段：語法分析階段、語句優(yōu)化階段、查詢執(zhí)行階段。
1.1 語法分析階段
語法分析是在SGA中完成的，（SGA是指系統(tǒng)全局區(qū)，包括數(shù)據(jù)庫緩沖區(qū)、重做日志緩沖區(qū)、共享池、java池、大池、流池），在這里將sql語句分解為關(guān)系代數(shù)查詢，也就是通過這些關(guān)系代數(shù)查詢來驗(yàn)證這個sql的語法有沒有寫錯，關(guān)鍵字是否正確等。
1.2 語句優(yōu)化階段
這是這3個步驟中最關(guān)鍵的一個地方了，oracle默認(rèn)使用的是基于CBO來選擇最好的執(zhí)行計劃，你可能會問，啥是CBO？，好吧！CBO其實(shí)就是基于成本的優(yōu)化程序，也就是會將對成本消耗評估，將消耗的cpu執(zhí)行周期、內(nèi)存、I/O速率等資源轉(zhuǎn)換為時間成本。時間最少的當(dāng)然就是最好的了。例如Oracle的解析也分為硬解析和軟解析， 對于不同的oracle版本，硬解析的次數(shù)也不同，在oracle12中，硬解析的次數(shù)為19次，在oracle11g中硬解析的次數(shù)為59次。
在做這個階段，Oracle會將語法分析樹轉(zhuǎn)換為一個邏輯查詢，然后將邏輯查詢轉(zhuǎn)換為物理查詢計劃。而且這個物理查詢計劃還不止一種，因?yàn)閮?yōu)化器往往會生成好幾個有效的查詢計劃，然后會根據(jù)這些計劃來做出成本消耗評估。注意，這里只是做義工評估，并沒有把每一種計劃都去執(zhí)行一遍。那么oracle是依據(jù)什么來評估的呢？一般會按照如下因素進(jìn)行評估：a、查詢中涉及的連接操作以及連接順序 b、操作執(zhí)行的算法 c、數(shù)據(jù)讀取的方式，例如讀內(nèi)存還是磁盤 d、查詢各操作之間的數(shù)據(jù)傳遞方式。
一條sql語句進(jìn)來，到最終對sql語句生成執(zhí)行計劃之前，需要經(jīng)歷一個過程，如下圖所示（嗨呀，隨手畫的圖， 畫得比較丑呀�。�
1.3 查詢執(zhí)行
查詢執(zhí)行時最簡單的一個步驟了，只需要將剛才步驟2的物理查詢計劃進(jìn)行執(zhí)行即可，然后將處理的數(shù)據(jù)返回給用戶。
二、基于成本的優(yōu)化
2.1 優(yōu)化方式
優(yōu)化方式的含義是為滿足SQL優(yōu)化的目標(biāo)而選擇的優(yōu)化方式，在默認(rèn)情況下，是以SQL語句的吞吐量作為優(yōu)化的目標(biāo)。
下面提供三種優(yōu)化方式來滿足不同的查詢需求：
1、All_Rows：默認(rèn)方式，優(yōu)化的目標(biāo)是實(shí)現(xiàn)查詢的最大吞吐量
2、FIRST_ROWS_n：優(yōu)化輸出查詢的前n行數(shù)據(jù)，目標(biāo)是滿足快速的響應(yīng)需求
3、FIRST_ROWS：使用CBO的成本優(yōu)化盡快輸出查詢的前幾行數(shù)據(jù)，滿足最小響應(yīng)時間的需求
oracle提供了三種級別上的優(yōu)化：實(shí)例級、會話級、語句級。
查詢當(dāng)前數(shù)據(jù)庫的CBO優(yōu)化方式：
可以看出我當(dāng)前的數(shù)據(jù)庫的優(yōu)化方式是實(shí)現(xiàn)查詢的最大吞吐量。
2.2 優(yōu)化器工作過程
CBO通過4個步驟步驟完成SQL的優(yōu)化
1、根據(jù)統(tǒng)計數(shù)據(jù)轉(zhuǎn)換SQL語句 ： 也就是指CBO認(rèn)為轉(zhuǎn)換后的語句查詢會更高效，所以將你的sql語句轉(zhuǎn)換為另外一種形式，例如你寫的OR轉(zhuǎn)換為 UNION ALL，將between轉(zhuǎn)換為>=和<=等。
2、根據(jù)資源情況選訪問路徑：指訪問某個路徑的數(shù)據(jù)所消耗的資源。
3、根據(jù)統(tǒng)計數(shù)據(jù)選擇連接方法： 如果涉及多個表，CBO會根據(jù)統(tǒng)計數(shù)據(jù)以及表的鍵的信息來選擇連接的方法，在多個連接方法中選擇計算成本最低的一個作為最佳連接方法。
4、確定連接次序：指涉及的數(shù)據(jù)行的數(shù)目來確定最好的連接次序。
2.3 統(tǒng)計數(shù)據(jù)
--查看gather_stats_job的當(dāng)前運(yùn)行狀態(tài)
--查詢用戶scott擁有表的統(tǒng)計分析情況：sample_size表示采樣行數(shù)
select last_analyzed，table_name，owner，num_rows，sample_size from dba_tables where owner='SCOTT'；
--為模式scott的所有表統(tǒng)計數(shù)據(jù)(手工收集)
execute dbms_stats.gather_schema_stats(ownname => 'scott')；
三、主動優(yōu)化SQL語句
3.1 優(yōu)化查詢
1、優(yōu)化查詢：explain，對于使用索引查詢，使用like的時候只有%不在第一個位置才會有效，使用多列查詢的時候，只有查詢條件中使用了這些字段中的第一個字段時，索引才會被引用，or查詢條件時，前后兩個條件中的列都是索引時，查詢中才會使用索引。
2、優(yōu)化數(shù)據(jù)庫結(jié)構(gòu)，將字段很多的表分解為多個表，增加中間表，增加冗余字段，優(yōu)化插入速度，禁用唯一性檢查，使用批量插入，禁止外鍵檢查，禁止自動提交，優(yōu)化表optimize
3、優(yōu)化數(shù)據(jù)庫的服務(wù)器，硬件：內(nèi)存，io， 優(yōu)化參數(shù)。
4、使用綁定變量：我們都知道，在Oracle中是分為了硬解析和軟解析的，在SGA中，共享池就是存放解析后的SQL語句，此時的共享池包含SQL語句的最終執(zhí)行計劃。如果有相同的是SQL查詢語句，就不需要再次解析SQL語句了，而是直接從共享池中執(zhí)行SQL語句的執(zhí)行計劃。使用共享池就是為了避免硬解析的發(fā)生，因?yàn)槊看稳ミM(jìn)行硬解析的時候都需要重新去分析語句的語法語義，然后通過CBO優(yōu)化生成的最終執(zhí)行計劃，這樣就很消耗CPU的資源。使用綁定變量，也就是我們在java開發(fā)中常見的給一個sql語句加一個？來執(zhí)行，然后再傳入?yún)?shù)。
例如： select ename，job，sal from scott.emp where deptno=？
然后我們再把參數(shù)傳入，這樣不僅可以防止SQL注入，而且可以對SQL進(jìn)行優(yōu)化。
5、消除子查詢：對于一些嵌套的子查詢，將嵌套的sql語句，例如：
這樣的一條sql語句每次需要執(zhí)行N*M次操作，具體數(shù)值你可以使用下文中是sql跟蹤進(jìn)行性能分析。
優(yōu)化后的語句為：
優(yōu)化后的這條sql只需要進(jìn)行N+M此操作即可，其伸縮性更強(qiáng)，計算結(jié)果也不會呈指數(shù)增長。雖然初步看起來優(yōu)化后的sql語句似乎更長一點(diǎn)，如果你在質(zhì)疑到底對不對，你可以使用我們接下來講到的SQL語句分析工具來進(jìn)行對比，大家可以通過其執(zhí)行計劃來驗(yàn)證。
3.2 SQL語句優(yōu)化工具
使用explain plan for 指令來獲得SQL語句的執(zhí)行計劃，所以我們先來創(chuàng)建一個執(zhí)行這個指令所需要的表，在oracle的安裝目錄中，我們需要找到utlxplan.sql這個文件，然后執(zhí)行。我這里的這個文件的路徑位于E：\oracle\app\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql，執(zhí)行命令如下：
表已創(chuàng)建
查看這個表結(jié)構(gòu)：
然后我們通過這個命令來分析SQL語句的執(zhí)行：
SQL> explain plan for
2 select count(*) from scott.emp；
Explained
我們來查看一下plan_table表中的sql語句執(zhí)行計劃信息：
我們可以看到，這是一個全表掃描的，表明是emp。
如果我們想要更深入的對這條sql進(jìn)行分析怎么辦，例如想要知道這個的訪問對象、消耗的CPU等信息。那么我們可以啟用SQL追蹤。
1、使用autotrace指令
使用該指令可以跟蹤SQL語句并分析其執(zhí)行步驟，統(tǒng)計信息如物理讀數(shù)據(jù)量、磁盤和內(nèi)存排序數(shù)據(jù)量。
具體的操作命令如下：
來看一下這個生成好的文件（部分內(nèi)容，因?yàn)樯傻膬?nèi)容比較多，所以這里不完全貼上來，需要查看的朋友可以自己去執(zhí)行一個sql追蹤然后查看）：
在這段輸出中，可以看出，SQL語句被執(zhí)行了38次，總共耗時0.01秒，語句被執(zhí)行了48次，話費(fèi)時間是0.17秒，在解析和執(zhí)行期間沒有磁盤I/O和緩沖區(qū)讀取操作，fetch操作執(zhí)行了70次，耗時0.09秒，涉及了9次磁盤讀取以及171次緩沖區(qū)讀取操作，總共讀取了0個數(shù)據(jù)庫塊，涉及50行數(shù)據(jù)。
在庫緩存中丟失的命中次數(shù)是22次，說明有22次硬解析出現(xiàn)。最后說明是47個用戶SQL語句，42個內(nèi)部SQL語句總共涉及89個SQL語句。
四、被動優(yōu)化SQL
在程序打包后，或者系統(tǒng)運(yùn)行后如何來優(yōu)化SQL語句，一般就是建立或刪除索引、建立分區(qū)表等操作，下面指給出一些思路，具體的實(shí)現(xiàn)還是需要在實(shí)際工作中才能領(lǐng)會。
1、使用分區(qū)表
2、創(chuàng)建壓縮表：原理就是，將表中重復(fù)的數(shù)據(jù)去掉，采用算法來替換這些重復(fù)的值，在需要的時候，用算法去重建這些重復(fù)的數(shù)據(jù)，從而實(shí)現(xiàn)對表的壓縮。
語句為；
3、創(chuàng)建壓縮索引：原理同壓縮表，主要就是去掉索引中的重復(fù)值，尤其對于大表，可以減少存儲空間并增強(qiáng)查詢性能。
語句為：
4、保持CBO的穩(wěn)定性，創(chuàng)建存儲大綱，分為三種； 數(shù)據(jù)庫級別的存儲大綱、會話級別的存儲大綱、SQL語句級別的存儲大綱
5、使用V$SQL視圖
例如可以查詢消耗磁盤I/O最多的語句，緩沖區(qū)讀取次數(shù)最多的SQL語句等。
--查詢自實(shí)例啟動以來磁盤IO最多的sql語句
五、索引類型及使用時機(jī)
說到數(shù)據(jù)庫的優(yōu)化，不得不提的就是索引了，下面詳細(xì)來講解一下oracle的索引類型及其使用時機(jī)。
1、B-樹索引
B-樹索引是Oracle默認(rèn)的索引類型。葉子節(jié)點(diǎn)包含索引的實(shí)際值和該索引條目的行ID。分為根節(jié)點(diǎn)、分支節(jié)點(diǎn)、葉子節(jié)點(diǎn)3個部分，其中根節(jié)點(diǎn)位于索引的最頂端。在葉子節(jié)點(diǎn)中存儲了實(shí)際的索引列的值和該列對應(yīng)的記錄的行ID，它是唯一的Oracle指針，指向該行的物理位置，葉子節(jié)點(diǎn)其實(shí)就是一個雙向鏈表，每個葉子節(jié)點(diǎn)包含一個指向下一個和上一個葉子節(jié)點(diǎn)的指針，這樣在一定范圍內(nèi)便利用索引以搜索需要的記錄。
2、位圖索引
位圖索引使用位圖標(biāo)識索引的列值，它適用于沒有大量數(shù)據(jù)更新、刪除和插入操作的數(shù)據(jù)倉庫。因?yàn)槭褂梦粓D索引時，每個位圖索引項(xiàng)與表中大量的行有關(guān)聯(lián)，當(dāng)表中有大量的增刪改操作的時候，位圖索引頁需要相應(yīng)的改變，而且索引會占用一定的磁盤空間，并且索引在更新的時候受影響的索引行需要鎖定。
例如我們執(zhí)行如下語句：
SELECT EMPNO，ENAME，job，SAL FROM scott.emp WHERE JOB='SALESMAN'；
目的就是在emp中查出職位為salesman的員工信息，這里我們?yōu)槠浣⑽粓D索引，結(jié)構(gòu)如下圖所示（純手工繪圖）：
創(chuàng)建位圖索引的語句為：
create bitmap index emp_job_bitmap_idx on emp(job)；
3、反向鍵索引
是值在創(chuàng)建索引過程中對索引列創(chuàng)建的索引鍵值的字節(jié)反向，使用反向鍵索引的好處是將值連續(xù)插入到索引中時反向鍵能避免爭用。使用反向鍵索引使得每個鍵值被顛倒了順序，將索引的鍵值分散開。
例如：
46892 ----> 29864
Horoscope ---> eposcoroH
創(chuàng)建反向鍵索引需要使用reverse關(guān)鍵字。
create index emp_sal_reverse_idx on emp(sal) reverse；
4、基于函數(shù)的索引
用戶查詢時，如果查詢語句的where子句中有函數(shù)存在，oracle將使用函數(shù)索引加快查詢速度。
create index dept_dname_idx on dept9UPPER（dname)）；
如上所示，我們創(chuàng)建了一個基于表dept中列dname的函數(shù)索引，創(chuàng)建該索引時首先將列dname中的值轉(zhuǎn)換為大寫，然后對大寫的dname創(chuàng)建索引，放入索引表。資源當(dāng)用戶需要進(jìn)行如下查詢的時候就會極大的提高查詢速度。
select UPPER(dname) from scott.dept where UPPER(dname) ='SALES'；
六、SGA詳解
Oracle的SGA是指系統(tǒng)全局區(qū)，它包括數(shù)據(jù)庫緩沖區(qū)、重做日志緩沖區(qū)、共享池、java池、大池、流池。要優(yōu)化SGA就是要調(diào)整這些數(shù)據(jù)庫組件的參數(shù) ，這些組件就是實(shí)例優(yōu)化的操作對象，從而提高系統(tǒng)的運(yùn)行效率，如提高用戶查詢的響應(yīng)事件等。
數(shù)據(jù)庫緩沖區(qū)：存放用戶從庫中讀取的數(shù)據(jù)，用戶查找數(shù)據(jù)會先在這里進(jìn)行查找，如果沒有才會去讀數(shù)據(jù)庫文件，所以該區(qū)域的設(shè)置不能過小。
重做日志緩沖區(qū)：這里放置用戶改變的數(shù)據(jù)，所有變化了的數(shù)據(jù)和需要回滾的數(shù)據(jù)都暫時保存在這里。
共享池：包括數(shù)據(jù)字典高速緩存和庫高速緩存，庫高速緩存存放oracle解析的SQL語句、PL/SQL過程、包以及各種控制結(jié)構(gòu)，如表、庫緩沖句柄等。
java池：執(zhí)行java代碼的區(qū)域，是為運(yùn)行JVM分配的一段固定大小的內(nèi)存。
大池：該內(nèi)存區(qū)提供大型的內(nèi)存分配，在共享服務(wù)器連接模式下提供會話區(qū)，在使用RMAN備份是也使用該內(nèi)存區(qū)作為磁盤IO的數(shù)據(jù)緩沖區(qū)。
流池：流內(nèi)存，為oracle流專用的內(nèi)存池，流是指oracle數(shù)據(jù)庫中的一個數(shù)據(jù)共享。
對于數(shù)據(jù)庫的優(yōu)化是一個很深入的內(nèi)容了，例如還有可以優(yōu)化重做日志緩沖區(qū)、優(yōu)化共享池優(yōu)化PGA內(nèi)存等方面的內(nèi)容，
日志緩沖區(qū)中將緩沖寫入到日志文件中的方式有每隔3秒提交、數(shù)據(jù)大于1MB的時候、檢驗(yàn)點(diǎn)發(fā)生時、當(dāng)DBWR進(jìn)程將數(shù)據(jù)庫高速緩沖區(qū)中的數(shù)據(jù)寫到數(shù)據(jù)文件前，日志緩沖區(qū)的優(yōu)化就是調(diào)整log_buffer_pace或者將不同的文件放在不同的磁盤上以避免沖突。
PGA是一個程序全局區(qū)，可以作為大規(guī)模的數(shù)據(jù)排序，而不需要去使用虛擬內(nèi)存而占用操作系統(tǒng)的交換區(qū)。
更為詳細(xì)的內(nèi)容在本文就不再說明，感興趣的朋友可以自行查閱相關(guān)資料。學(xué)習(xí)一些SQL的底層，可以更好的修煉內(nèi)功。
核心關(guān)注：拓步ERP系統(tǒng)平臺是覆蓋了眾多的業(yè)務(wù)領(lǐng)域、行業(yè)應(yīng)用，蘊(yùn)涵了豐富的ERP管理思想，集成了ERP軟件業(yè)務(wù)管理理念，功能涉及供應(yīng)鏈、成本、制造、CRM、HR等眾多業(yè)務(wù)領(lǐng)域的管理，全面涵蓋了企業(yè)關(guān)注ERP管理系統(tǒng)的核心領(lǐng)域，是眾多中小企業(yè)信息化建設(shè)首選的ERP管理軟件信賴品牌。
轉(zhuǎn)載請注明出處：拓步ERP資訊網(wǎng)http://www.ezxoed.cn/
本文標(biāo)題：基于CBO的SQL優(yōu)化和Oracle實(shí)例優(yōu)化
本文網(wǎng)址：http://www.ezxoed.cn/html/support/11121824042.html
相關(guān)文章
