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