SQL語(yǔ)句性能調(diào)整的目標(biāo)是：
去掉不必要的大表全表掃描---不必要的大表全表掃描會(huì)造成不必要的輸入輸出，而且還會(huì)拖垮整個(gè)數(shù)據(jù)庫(kù)；
檢查優(yōu)化索引的使用---這對(duì)于提高查詢(xún)速度來(lái)說(shuō)非常重要
檢查子查詢(xún)---考慮SQL子查詢(xún)是否可以用簡(jiǎn)單連接的方式進(jìn)行重新書(shū)寫(xiě)；
調(diào)整PCTFREE和PCTUSED等存儲(chǔ)參數(shù)優(yōu)化插入、更新或者刪除等操作；
考慮數(shù)據(jù)庫(kù)的優(yōu)化器;
考慮數(shù)據(jù)表的全表掃描和在多個(gè)CPU的情況下考慮并行查詢(xún);
一、 索引(INDEX)使用的問(wèn)題
1． 索引(INDEX)，用還是不用？這是個(gè)的問(wèn)題。
是全表掃描還是索引范圍掃描主要考慮SQL的查詢(xún)速度問(wèn)題。這里主要關(guān)心讀取的記錄的數(shù)目。根據(jù)DONALD K .BURLESON的說(shuō)法,使用索引范圍掃描的原則是：
對(duì)于數(shù)據(jù)有原始排序的表，讀取少于表記錄數(shù)40%的查詢(xún)應(yīng)該使用索引范圍掃描。對(duì)讀取多于表記錄數(shù)40%的查詢(xún)應(yīng)全表掃描。
對(duì)于未排序的表，讀取少于表記錄數(shù)7%的查詢(xún)應(yīng)該使用索引范圍掃描,反之，對(duì)讀取多于表記錄數(shù)7%的查詢(xún)應(yīng)全表掃描。
注：在不同的書(shū)中，對(duì)是否使用索引的讀取記錄的百分比值不太一致，基本上是一個(gè)經(jīng)驗(yàn)值，但是讀取記錄的百分比越低，使用索引越有效。
2． 如果列上有建索引，什么SQL查詢(xún)是有用索引(INDEX)的？什么SQL查詢(xún)是沒(méi)有用索引(INDEX)的？
存在下面情況的SQL，不會(huì)用到索引：
存在數(shù)據(jù)類(lèi)型隱形轉(zhuǎn)換的,如：
select * from staff_member where staff_id=’123’；
列上有數(shù)學(xué)運(yùn)算的，如：
select * from staff_member where salary*2<10000>
使用不等于(<> )運(yùn)算的，如：
select * from staff_member where dept_no<>2001;
使用substr字符串函數(shù)的，如：
select * from staff_member where substr(last_name,1,4)=’FRED’;
‘%’通配符在第一個(gè)字符的，如:
select * from staff_member where first_name like ‘%DON’;
字符串連接(||)的，如：
select * from staff_member where first_name||’’=’DONALD’
3． 函數(shù)的索引
日期類(lèi)型也是很容易用到的，而且在SQL語(yǔ)句中會(huì)使用to_char函數(shù)以查詢(xún)具體的的范圍日期。如：select * from staff_member where TO_CHAR(birth_day,’YYYY’)=’2003’; 我們可以建立基于函數(shù)的索引如：CREATE INDEX Ind_emp_birth ON staff_member (to_char((birth_day,’YYYY’));
二、 SQL語(yǔ)句排序優(yōu)化
1． 排序發(fā)生的情況：
SQL中包含group by 子句
SQL 中包含order by 子句
SQL 中包含 distinct 子句
SQL 中包含 minus 或 union操作
創(chuàng)建索引時(shí)
2． 排序在內(nèi)存還是在磁盤(pán)中進(jìn)行？
在內(nèi)存執(zhí)行的排序速度要比在磁盤(pán)執(zhí)行的排序速度快14000倍。如果是專(zhuān)用連接，排序內(nèi)存根據(jù)INIT.ORA的sort_area_size進(jìn)行分配，如果是多線(xiàn)程服務(wù)連接，排序內(nèi)存根據(jù)large_pool_size進(jìn)行分配。
sort_area_size的增大可以減少磁盤(pán)排序，但是過(guò)大將使ORACLE性能降低，因?yàn)樗玫倪B接回話(huà)都會(huì)分配到一個(gè)sort_area_size大小的內(nèi)存，所以，為了提高有限的查詢(xún)速度，可能會(huì)浪費(fèi)大量的內(nèi)存。
增加sort_multiblock_read_count的值使每次讀取更多的內(nèi)容，減少運(yùn)行次數(shù)，提高性能。
三、SQL子查詢(xún)的調(diào)整
1、理解關(guān)聯(lián)子查詢(xún)和非關(guān)聯(lián)子查詢(xún)。
下面是一個(gè)非關(guān)聯(lián)子查詢(xún)：
select staff_name from staff_member where staff_id
in (select staff_id from staff_func);
而下面是一個(gè)關(guān)聯(lián)子查詢(xún)：
select staff_name from staff_member where staff_id in (select staff_id from staff_func where staff_member.staff_id=staff_func.staff_id);
以上返回的結(jié)果集是相同的，可是它們的執(zhí)行開(kāi)銷(xiāo)是不同的：
非關(guān)聯(lián)查詢(xún)的開(kāi)銷(xiāo)——非關(guān)聯(lián)查詢(xún)時(shí)子查詢(xún)只會(huì)執(zhí)行一次，而且結(jié)果是排序好的，并保存在一個(gè)ORACLE的臨時(shí)段中，其中的每一個(gè)記錄在返回時(shí)都會(huì)被父查詢(xún)所引用。在子查詢(xún)返回大量的記錄的情況下，將這些結(jié)果集排序，以及將臨時(shí)數(shù)據(jù)段進(jìn)行排序會(huì)增加大量的系統(tǒng)開(kāi)銷(xiāo)。
關(guān)聯(lián)查詢(xún)的開(kāi)銷(xiāo)——對(duì)返回到父查詢(xún)的的記錄來(lái)說(shuō)，子查詢(xún)會(huì)每行執(zhí)行一次。因此，我們必須保證任何可能的時(shí)候子查詢(xún)用到索引。
2、XISTS子句和IN子句
帶IN的關(guān)聯(lián)子查詢(xún)是多余的，因?yàn)镮N子句和子查詢(xún)中相關(guān)的操作的功能是一樣的。如：
select staff_name from staff_member where staff_id in (select staff_id from staff_func where staff_member.staff_id=staff_func.staff_id);
為非關(guān)聯(lián)子查詢(xún)指定EXISTS子句是不適當(dāng)?shù)模驗(yàn)檫@樣會(huì)產(chǎn)生笛卡乘積。如：
select staff_name from staff_member where staff_id
Exists (select staff_id from staff_func);
盡量不要使用NOT IN子句。使用MINUS 子句都比NOT IN 子句快，雖然使用MINUS子句要進(jìn)行兩次查詢(xún)：
select staff_name from staff_member where staff_id in (select staff_id from staff_member MINUS select staff_id from staff_func where func_id like ‘81%’);
3、 任何可能的時(shí)候，用標(biāo)準(zhǔn)連接或內(nèi)嵌視圖改寫(xiě)子查詢(xún)。
四、更新、插入、以及刪除等DML語(yǔ)句的調(diào)整
1、DML語(yǔ)句是指用來(lái)執(zhí)行更新、插入、以及刪除等操作類(lèi)型的語(yǔ)句。這些語(yǔ)句在結(jié)構(gòu)上是很簡(jiǎn)單的，可調(diào)整的余地較小。性能低下的情況有：
插入緩慢并占有過(guò)多的I/O資源——這種情況主要是空閑列表(free list)中的數(shù)據(jù)塊的空間過(guò)小，僅容的下較少的記錄。
更新緩慢——這種情況主要是UPDATE操作擴(kuò)展了一個(gè)VARCHAR2類(lèi)型的列，而ORACLE被強(qiáng)制將內(nèi)容遷移到其他數(shù)據(jù)塊時(shí)。
刪除緩慢——這種情況主要是記錄被刪除，ORACLE必須將數(shù)據(jù)塊重新放置到空閑列表(free list)時(shí)。
因此，對(duì)DML進(jìn)行調(diào)整，主要時(shí)利用對(duì)象存儲(chǔ)參數(shù)和SQL之間的關(guān)系進(jìn)行調(diào)整。
2、 CTFREE存儲(chǔ)參數(shù)
PCTFREE存儲(chǔ)參數(shù)告訴ORACLE什么時(shí)候應(yīng)該將數(shù)據(jù)塊從對(duì)象的空閑列表中移出。ORACLE的默認(rèn)參數(shù)是PCTFREE=10；也就是說(shuō)，一旦一個(gè)INSERT操作使得數(shù)據(jù)塊的90%被使用，這個(gè)數(shù)據(jù)塊就從空閑列表(free list)中移出。
PCTUSED存儲(chǔ)參數(shù)
PCTUSED存儲(chǔ)參數(shù)告訴ORACLE什么時(shí)候?qū)⒁郧皾M(mǎn)的數(shù)據(jù)塊加到空閑列表中。當(dāng)記錄從數(shù)據(jù)表中刪除時(shí)，數(shù)據(jù)庫(kù)的數(shù)據(jù)塊就有空間接受新的記錄，但只有當(dāng)填充的空間降到PCTUSED值以下時(shí)，該數(shù)據(jù)塊才被連接到空閑列表中，才可以往其中插入數(shù)據(jù)。PCTUSED的默認(rèn)值是PCTUSED=40。
存儲(chǔ)參數(shù)規(guī)則小結(jié)
（1）PCTUSED較高意味著相對(duì)較滿(mǎn)的數(shù)據(jù)塊會(huì)被放置到空閑列表中，從而有效的重復(fù)使用數(shù)據(jù)塊的空間，但會(huì)導(dǎo)致I/O消耗。PCTUSED低意味著在一個(gè)數(shù)據(jù)塊快空的時(shí)候才被放置到空閑列表中，數(shù)據(jù)塊一次能接受很多的記錄，因此可以減少I(mǎi)/O消耗，提高性能。
（2）PCTFREE的值較大意味著數(shù)據(jù)塊沒(méi)有被利用多少就從空閑列表中斷開(kāi)連接，不利于數(shù)據(jù)塊的充分使用。PCTFREE過(guò)小的結(jié)果是，在更新時(shí)可能會(huì)出現(xiàn)數(shù)據(jù)記錄遷移(Migration)的情況。(注：數(shù)據(jù)記錄遷移(Migration)是指記錄在是UPDATE操作擴(kuò)展了一個(gè)VARCHAR2類(lèi)型的列或BLOB列后,PCTFREE參數(shù)所指定的空間不夠擴(kuò)展，從而記錄被ORACLE強(qiáng)制遷移到新的數(shù)據(jù)塊，發(fā)生這種情況將較嚴(yán)重的影響ORACLE的性能，出現(xiàn)更新緩慢)。
（3）在批量的插入、刪除或者更新操作之前，先刪除該表上的索引，在操作完畢之后在重新建立，這樣有助于提高批量操作的整體速度，并且保證B樹(shù)索引在操作之后有良好的性能。
3、 同優(yōu)化器下的調(diào)整；
基于成本優(yōu)化器(CBO)：
（1）ORACLE 8i 以上版本更多地使用成本優(yōu)化器，因?yàn)樗又悄埽?
（2）通過(guò)optimizer_mode=all_rows 或 first_rows來(lái)選擇CBO；通過(guò)alter session set optimizer_goal=all_rows 或 first_rows來(lái)選擇CBO;通過(guò)添加hint來(lái)選擇CBO;
（3）使用基于成本優(yōu)化的一個(gè)關(guān)鍵是：存在表和索引的統(tǒng)計(jì)資料。通過(guò)analyze table 獲得表的統(tǒng)計(jì)資料；通過(guò)analyze index獲得索引的統(tǒng)計(jì)資料。
（4）對(duì)于超過(guò)5個(gè)表的連接的查詢(xún)，建議不要使用成本優(yōu)化器，而是在SQL語(yǔ)句中通過(guò)添加/* + rule */提示或者通過(guò)指定的執(zhí)行計(jì)劃來(lái)避免可能會(huì)在20分鐘以上的SQL解析時(shí)間。
基于規(guī)則優(yōu)化器(RBO)：
（1）ORACLE 8i以及ORACLE的以前版本主要用(RBO),并且比較有效；
（2）通過(guò)optimizer_mode=rule來(lái)選擇RBO；通過(guò)alter session set optimizer_goal=rule來(lái)選擇RBO; 通過(guò)添加/* + rule */來(lái)選擇RBO;
（3）在RBO中，from 子句的表的順序決定表的連接順序。From 子句的最后一個(gè)表是驅(qū)動(dòng)表，這個(gè)表應(yīng)該是最小的表。
（4）限定性最強(qiáng)的布爾表達(dá)式放在最底層。
4、跟蹤、優(yōu)化SQL語(yǔ)句的方法
保證在實(shí)例級(jí)將TIMED_STATISTICS設(shè)置為T(mén)RUE(在 INIT.ORA中永久的設(shè)置它或執(zhí)行 ALTER SYSTEM 命令臨時(shí)設(shè)置它)；
保證將MAX_DUMP_FILE_SIZE設(shè)置的較高。此參數(shù)控制跟蹤文件的大小。
決定USER_DUMP_DEST所指向的位置，并保證有足夠的磁盤(pán)空間。這是放置跟蹤文件的位置。
在應(yīng)用系統(tǒng)運(yùn)行時(shí)，打開(kāi)所懷疑的回話(huà)的SQL_TRACE.(在 INIT.ORA中通過(guò)SQL_TRACE=TRUE永久的設(shè)置對(duì)所有的回話(huà)進(jìn)行跟蹤或通過(guò)使用系統(tǒng)包DBMS_SYSTEM.set_sql_trace_in_session(sid，serial,true);命令臨時(shí)設(shè)置它)
執(zhí)行業(yè)務(wù)相關(guān)操作；
設(shè)置跟蹤結(jié)束(DBMS_SYSTEM.set_sql_trace_in_session(sid,serial,false)，如果沒(méi)有該步驟，可能跟蹤文件中的信息不全，因?yàn)榭赡苡幸徊糠诌€在緩存中)；
定位跟蹤文件；
對(duì)步驟6的跟蹤文件進(jìn)行TKPROF，生成報(bào)告文件；
研究此報(bào)告文件，可以看到CPU、DISK、 QUERY、 COUNT等參數(shù)和execution plan(執(zhí)行計(jì)劃)，優(yōu)化開(kāi)銷(xiāo)最大的SQL；
重復(fù)執(zhí)行步驟4)~9)直到達(dá)到所需的性能目標(biāo)；