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