1、ORACLE系列培訓(xùn)系列培訓(xùn)_SQL調(diào)優(yōu)調(diào)優(yōu)培訓(xùn)講師：初學(xué)者培訓(xùn)講師：初學(xué)者ORACLE-ORACLE-調(diào)優(yōu)范圍調(diào)優(yōu)范圍應(yīng)用程序級(jí)調(diào)優(yōu)： SQL語句調(diào)優(yōu) 管理變化調(diào)優(yōu)實(shí)例級(jí)調(diào)優(yōu) 內(nèi)存 數(shù)據(jù)結(jié)構(gòu) 實(shí)例配置操作系統(tǒng)交互 I/O SWAP ParametersORACLE-ORACLE-一般調(diào)整的思路一般調(diào)整的思路1.調(diào)整業(yè)務(wù)功能2.調(diào)整數(shù)據(jù)設(shè)計(jì)3.調(diào)整流程設(shè)計(jì)4.調(diào)整SQL語句5.調(diào)整物理結(jié)構(gòu)6.調(diào)整內(nèi)存分配7.調(diào)整I/O8.調(diào)整內(nèi)存競(jìng)爭(zhēng)9.調(diào)整操作系統(tǒng)ORACLE-ORACLE-調(diào)優(yōu)角色分配調(diào)優(yōu)角色分配ORACLE-SQLORACLE-SQL優(yōu)化衡量標(biāo)準(zhǔn)優(yōu)化衡量標(biāo)準(zhǔn)隨著軟件技術(shù)的不斷發(fā)展，系統(tǒng)性

2、能越來越重要。系統(tǒng)性能主要用：系統(tǒng)響應(yīng)時(shí)間系統(tǒng)響應(yīng)時(shí)間和并發(fā)性并發(fā)性來衡量。造成SQL語句性能不佳大致有兩個(gè)原因： 開發(fā)人員只關(guān)注查詢結(jié)果的正確性，忽視查詢語句的效率。 開發(fā)人員只關(guān)注SQL語句本身的效率，對(duì)SQL語句執(zhí)行原理、影響SQL執(zhí)行效率的主要因素不清楚。l 前者可以通過深入學(xué)習(xí)SQL語法及各種SQL調(diào)優(yōu)技巧進(jìn)行解決。SQL調(diào)優(yōu)是一個(gè)系統(tǒng)工程，熟悉SQL語法、掌握各種內(nèi)嵌函數(shù)、分析函數(shù)的用法只是編寫高效SQL的必要條件。l 后者從分析SQL語句執(zhí)行原理入手，指出SQL調(diào)優(yōu)應(yīng)在優(yōu)化SQL解析和優(yōu)化CBO上。ORACLE-SQLORACLE-SQL優(yōu)化過程優(yōu)化過程定位有問題的語句檢查執(zhí)行計(jì)

3、劃?rùn)z查執(zhí)行過程中優(yōu)化器的統(tǒng)計(jì)信息分析相關(guān)表的記錄數(shù)、索引情況改寫SQL語句、使用HINT、調(diào)整索引、表分析有些SQL語句不具備優(yōu)化的可能，需要優(yōu)化處理方式達(dá)到最佳執(zhí)行計(jì)劃ORACLE-SQLORACLE-SQL解析過程回顧解析過程回顧當(dāng)一個(gè)Oracle實(shí)例接收一條sql后1、Create a Cursor 創(chuàng)建游標(biāo)2、Parse the Statement 分析語句3、Describe Results of a Query 描述查詢的結(jié)果集4、Define Output of a Query 定義查詢的輸出數(shù)據(jù)5、Bind Any Variables 綁定變量6、Parallelize the

4、 Statement 并行執(zhí)行語句7、Run the Statement 運(yùn)行語句8、Fetch Rows of a Query 取查詢出來的行9、Close the Cursor 關(guān)閉游標(biāo) ORACLE-ORACLE-綁定變量的注意項(xiàng)綁定變量的注意項(xiàng)不要使用數(shù)據(jù)庫級(jí)的變量綁定參數(shù)cursor_sharing來強(qiáng)制綁定，無論其值為 force 還是similar有些帶 create table multiindexusage ( inda number , indb number , descr varchar2(10);Table created.SQL create index multi

5、ndex on multiindexusage(inda,indb);Index created.SQL set autotrace traceonlySQL select * from multiindexusage where inda = 1;Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF MULTIINDEXUSAGE 2 1 INDEX (RANGE SCAN) OF MULTINDEX (NON-UNIQUE)SQL select * from mult

6、iindexusage where indb = 1;Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF MULTIINDEXUSAGE 很明顯, 當(dāng)僅引用索引的第二個(gè)列時(shí),優(yōu)化器使用了全表掃描而忽略了索引避免在索引列上使用函數(shù)避免在索引列上使用函數(shù)WHEREWHERE子句中，如果索引列是函數(shù)的一部分優(yōu)化器將不子句中，如果索引列是函數(shù)的一部分優(yōu)化器將不使用索引而使用全表掃描使用索引而使用全表掃描舉例舉例: :低效：SELECT FROM DEPTWHERE SAL * 12 25000;高

7、效:SELECT FROM DEPTWHERE SAL 25000/12;避免使用前置通配符避免使用前置通配符WHERE子句中, 如果索引列所對(duì)應(yīng)的值的第一個(gè)字符由通配符(WILDCARD)開始, 索引將不被采用. SELECT USER_NO,USER_NAME,ADDRESSFROM USER_FILESWHERE USER_NO LIKE %109204421; 在這種情況下，ORACLE將使用全表掃描.避免在索引列上使用避免在索引列上使用NOTNOT通常，我們要避免在索引列上使用通常，我們要避免在索引列上使用NOT, NOTNOT, NOT會(huì)產(chǎn)生在和在會(huì)產(chǎn)生在和在索引列上使用函數(shù)相同的

8、影響索引列上使用函數(shù)相同的影響. . 當(dāng)當(dāng)ORACLE”O(jiān)RACLE”遇到遇到”NOT,NOT,他就他就會(huì)停止使用索引轉(zhuǎn)而執(zhí)行全表掃描會(huì)停止使用索引轉(zhuǎn)而執(zhí)行全表掃描. .舉例舉例: : 低效低效: (: (這里這里, ,不使用索引不使用索引) ) SELECT FROM DEPT WHERE DEPT_CODE NOT = 0; 高效高效: (: (這里這里, ,使用了索引使用了索引) ) SELECT FROM DEPT WHERE DEPT_CODE 0;避免在索引列上使用避免在索引列上使用 IS NULLIS NULL和和IS NOT NULLIS NOT NULL避免在索引中使用任何可

9、以為空的列，ORACLE將無法使用該索引 對(duì)于單列索引，如果列包含空值，索引中將不存在此記錄. 對(duì)于復(fù)合索引，如果每個(gè)列都為空，索引中同樣不存在此記錄.如果至少有一個(gè)列不為空，則記錄存在于索引中如果唯一性索引建立在表的A列和B列上, 并且表中存在一條記錄的A,B值為(123,null) , ORACLE將不接受下一條具有相同A,B值（123,null）的記錄(插入). 然而如果所有的索引列都為空，ORACLE將認(rèn)為整個(gè)鍵值為空而空不等于空. 因此你可以插入1000條具有相同鍵值的記錄,當(dāng)然它們都是空!因?yàn)榭罩挡淮嬖谟谒饕兄?所以WHERE子句中對(duì)索引列進(jìn)行空值比較將使ORACLE停用該索引.

10、任何在where子句中使用is null或is not null的語句優(yōu)化器是不允許使用索引的。避免出現(xiàn)索引列自動(dòng)轉(zhuǎn)換避免出現(xiàn)索引列自動(dòng)轉(zhuǎn)換當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時(shí)當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時(shí), ORACLE, ORACLE自動(dòng)對(duì)列進(jìn)行簡(jiǎn)單自動(dòng)對(duì)列進(jìn)行簡(jiǎn)單的類型轉(zhuǎn)換的類型轉(zhuǎn)換. .假設(shè)EMP_TYPE是一個(gè)字符類型的索引列.SELECT USER_NO,USER_NAME,ADDRESSFROM USER_FILESWHERE USER_NO = 109204421這個(gè)語句被ORACLE轉(zhuǎn)換為:SELECT USER_NO,USER_NAME,ADDRESSFROM USER_FILESWHE

11、RE TO_NUMBER(USER_NO) = 109204421 因?yàn)閮?nèi)部發(fā)生的類型轉(zhuǎn)換因?yàn)閮?nèi)部發(fā)生的類型轉(zhuǎn)換, , 這個(gè)索引將不會(huì)被用到這個(gè)索引將不會(huì)被用到! ! 在查詢時(shí)盡量少用格式轉(zhuǎn)換在查詢時(shí)盡量少用格式轉(zhuǎn)換v 如用如用 WHERE a.order_no = b.order_no WHERE a.order_no = b.order_no v 不用不用 WHERE TO_NUMBER (substr(a.order_no, instr(b.order_no, .) - 1) = TO_NUMBER (substr(a.order_no, instr(b.order_no, .) - 1

12、)3.減少訪問次數(shù)減少訪問數(shù)據(jù)庫的次數(shù)減少訪問數(shù)據(jù)庫的次數(shù)當(dāng)執(zhí)行每條SQL語句時(shí), ORACLE在內(nèi)部執(zhí)行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變量 , 讀數(shù)據(jù)塊等等. 由此可見, 減少訪問數(shù)據(jù)庫的次數(shù) , 就能實(shí)際上減少ORACLE的工作量.使用使用DECODEDECODE來減少處理時(shí)間來減少處理時(shí)間例如: SELECT COUNT( SELECT COUNT(* *) )，SUM(SAL)SUM(SAL) FROM FROMEMPEMP WHERE DEPT_NO = 0020 WHERE DEPT_NO = 0020 AND ENAME LIKE AND ENAME L

13、IKESMITH%;SMITH%; SELECT COUNT( SELECT COUNT(* *) )，SUM(SAL)SUM(SAL) FROM FROMEMPEMP WHERE DEPT_NO = 0030 WHERE DEPT_NO = 0030 AND ENAME LIKE AND ENAME LIKESMITH%;SMITH%;你可以用DECODE函數(shù)高效地得到相同結(jié)果SELECT COUNT(DECODE(DEPT_NO,0020,X,NULL) D0020_COUNT,SELECT COUNT(DECODE(DEPT_NO,0020,X,NULL) D0020_COUNT, CO

14、UNT(DECODE(DEPT_NO,0030,X,NULL) D0030_COUNT, COUNT(DECODE(DEPT_NO,0030,X,NULL) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL) D0020_SAL, SUM(DECODE(DEPT_NO,0020,SAL,NULL) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL) D0030_SAL SUM(DECODE(DEPT_NO,0030,SAL,NULL) D0030_SALFROM EMP WHERE ENAME LIKE SMITH

15、%;FROM EMP WHERE ENAME LIKE SMITH%;減少對(duì)表的查詢減少對(duì)表的查詢?cè)诤凶硬樵兊脑诤凶硬樵兊腟QLSQL語句中語句中, ,要特別注意減少對(duì)表的查詢要特別注意減少對(duì)表的查詢. .例如例如: : 低效低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) ANDDB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) 高效高效 SELECT TAB_

16、NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604) 4. 細(xì)節(jié)上的影響WHEREWHERE子句中的連接順序子句中的連接順序ORACLE采用自下而上的順序解析WHERE子句,根據(jù)這個(gè)原理, 當(dāng)在WHERE子句中有多個(gè)表聯(lián)接時(shí)，WHERE子句中排在最后的表應(yīng)當(dāng)是返回行數(shù)可能最少的表，有過濾條件的子句應(yīng)放在WHERE子句中的最后。v 如：設(shè)從emp表查到的數(shù)據(jù)比較少或該表的過濾條件比較確定，能大大縮小查詢范圍，則將最具有選擇性部分放在WHE

17、RE子句中的最后：select * from emp e,dept d where d.deptno 10 and e.deptno =30 ; v 如果dept表返回的記錄數(shù)較多的話，上面的查詢語句會(huì)比下面的查詢語句響應(yīng)快得多。select * from emp e,dept d where e.deptno =30 and d.deptno 10 ;WHEREWHERE子句子句 函數(shù)、表達(dá)式使用函數(shù)、表達(dá)式使用最好不要在WHERE子句中使用函或表達(dá)式，如果要使用的話，最好統(tǒng)一使用相同的表達(dá)式或函數(shù)，這樣便于以后使用合理的索引。Order byOrder by語句語句 vORDER BY語句

18、決定了Oracle如何將返回的查詢結(jié)果排序。Order by語句對(duì)要排序的列沒有什么特別的限制，也可以將函數(shù)加入列中（象聯(lián)接或者附加等）。任何在Order by語句的非索引項(xiàng)或者有計(jì)算表達(dá)式都將降低查詢速度。v仔細(xì)檢查order by語句以找出非索引項(xiàng)或者表達(dá)式，它們會(huì)降低性能。解決這個(gè)問題的辦法就是重寫order by語句以使用索引，也可以為所使用的列建立另外一個(gè)索引，同時(shí)應(yīng)絕對(duì)避免在order by子句中使用表達(dá)式。聯(lián)接列聯(lián)接列 對(duì)于有聯(lián)接的列，即使最后的聯(lián)接值為一個(gè)靜態(tài)值，優(yōu)化器是不會(huì)使用索引的。 select * from employss where first_name|last_

19、name =Beill Cliton; 系統(tǒng)優(yōu)化器對(duì)基于last_name創(chuàng)建的索引沒有使用。 當(dāng)采用下面這種SQL語句的編寫，Oracle系統(tǒng)就可以采用基于last_name創(chuàng)建的索引。 select * from employee where first_name =Beill and last_name =Cliton; 帶通配符（帶通配符（% %）的）的likelike語句語句 通配符（%）在搜尋詞首出現(xiàn)，Oracle系統(tǒng)不使用last_name的索引。select * from employee where last_name like %cliton%; 在很多情況下可能無法避免這

20、種情況，但是一定要心中有底，通配符如此使用會(huì)降低查詢速度。然而當(dāng)通配符出現(xiàn)在字符串其他位置時(shí)，優(yōu)化器就能利用索引。在下面的查詢中索引得到了使用： select * from employee where last_name like c%; 用用WhereWhere子句替換子句替換HAVINGHAVING子句子句避免使用HAVING子句, HAVING 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過濾. 這個(gè)處理需要排序,總計(jì)等操作. 如果能通過WHERE子句限制記錄的數(shù)目,那就能減少這方面的開銷.例如: 低效: SELECT REGIONSELECT REGION，AVG(LOG_SIZE)AVG

21、(LOG_SIZE) FROM LOCATION FROM LOCATION GROUP BY REGION GROUP BY REGION HAVING REGION REGION != SYDNEY HAVING REGION REGION != SYDNEY AND REGION != PERTH AND REGION != PERTH 高效 SELECT REGIONSELECT REGION，AVG(LOG_SIZE)AVG(LOG_SIZE) FROM LOCATION FROM LOCATION WHERE REGION REGION != SYDNEY WHERE REGION

22、 REGION != SYDNEY AND REGION != PERTH AND REGION != PERTH GROUP BY REGION GROUP BY REGION 順序 WHERE GROUP HAVING WHERE GROUP HAVING用用NOT EXISTS NOT EXISTS 替代替代 NOT INNOT IN在子查詢中,NOT IN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并. 無論在哪種情況下,NOT IN都是最低效的 (因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷). 使用NOT EXISTS 子句可以有效地利用索引。盡可能使用NOT EXISTS來代替NOT IN，盡管二者都

23、使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查詢效率更高。例如:n語句語句1 1 nSELECT dname, deptno FROM dept WHERE SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp); deptno NOT IN (SELECT deptno FROM emp); n語句語句2 2 nSELECT dname, deptno FROM dept WHERE SELECT dname, deptno FROM dept WHERE NOT

24、EXISTS NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno); (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno); 2 2要比要比1 1的執(zhí)行性能好很多。的執(zhí)行性能好很多。因?yàn)橐驗(yàn)? 1中對(duì)中對(duì)empemp進(jìn)行了進(jìn)行了full table scan,full table scan,這是很浪費(fèi)時(shí)間的操作。而且這是很浪費(fèi)時(shí)間的操作。而且1 1中中沒有用到?jīng)]有用到empemp的的indexindex， 因?yàn)闆]有因?yàn)闆]有wherewhere子句。而子句。而

25、2 2中的語句對(duì)中的語句對(duì)empemp進(jìn)行的是進(jìn)行的是縮小范圍的查詢?？s小范圍的查詢。用索引提高效率用索引提高效率索引是表的一個(gè)概念部分,用來提高檢索數(shù)據(jù)的效率，ORACLE使用了一個(gè)復(fù)雜的自平衡B-tree結(jié)構(gòu). 通常,通過索引查詢數(shù)據(jù)比全表掃描要快. 當(dāng)ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時(shí), ORACLE優(yōu)化器將使用索引. 同樣在聯(lián)結(jié)多個(gè)表時(shí)使用索引也可以提高效率. 另一個(gè)使用索引的好處是,它提供了主鍵(primary key)的唯一性驗(yàn)證。通常, 在大型表中使用索引特別有效. 當(dāng)然,你也會(huì)發(fā)現(xiàn), 在掃描小表時(shí),使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提高,

26、但是我們也必須注意到它的代價(jià). 索引需要空間來存儲(chǔ),也需要定期維護(hù), 每當(dāng)有記錄在表中增減或索引列被修改時(shí), 索引本身也會(huì)被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁盤I/O . 因?yàn)樗饕枰~外的存儲(chǔ)空間和處理,那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢.。定期的重構(gòu)索引是有必要的。避免在索引列上使用計(jì)算避免在索引列上使用計(jì)算WHERE子句中，如果索引列是函數(shù)的一部分優(yōu)化器將不使用索引而使用全表掃描 低效： SELECT FROM DEPT WHERE SAL * 12 25000; 高效: SELECT FROM DEPT WHE

27、RE SAL 25000/12;用用= = 替代替代 如果DEPTNO上有一個(gè)索引。 高效: SELECT SELECT * * FROM EMP FROM EMP WHERE DEPTNO =4 WHERE DEPTNO =4 低效: SELECT SELECT * * FROM EMP FROM EMP WHERE DEPTNO 3 WHERE DEPTNO 3通過使用通過使用=、=等，避免使用等，避免使用NOTNOT命令命令v 例子：nselect select * * from employee where from employee where salary 3000salary 3

28、000; ; v 對(duì)這個(gè)查詢，可以改寫為不使用NOT：nselect select * * from employee where from employee where salary3000 or salary3000salary3000; ; 雖然這兩種查詢的結(jié)果一樣，但是第二種查詢方案會(huì)比第一種查詢方案更快些。第二種查詢?cè)试SOracle對(duì)salary列使用索引，而第一種查詢則不能使用索引。如果有其它辦法，不要使用子查詢。外部聯(lián)接外部聯(lián)接+的用法的用法 v 外部聯(lián)接+按其在=的左邊或右邊分左聯(lián)接和右聯(lián)接。若不帶+運(yùn)算符的表中的一個(gè)行不直接匹配于帶+預(yù)算符的表中的任何行，則前者的行與后者中的一個(gè)空行相匹配并被返回。利用外部聯(lián)接+，可以替代效率十分低下的 not in 運(yùn)算，大大提高運(yùn)行速度。例如，下面這條命令執(zhí)行起來很慢：nselect a.empno from emp a where select a.empno from emp a where a.empno not ina.empno not in(select empn