oracle 调优3
?（5）CBO模式下表很久沒分析，表的增長明顯，優化器采取了全表掃描。
SQL> select * from test.testindex where a like '1%';
A???????????? B
---- ----------
1???????????? 2
1???????????? 1
10?????????? 10
11?????????? 11
12?????????? 12
13?????????? 13
14?????????? 14
15?????????? 15
16?????????? 16
17?????????? 17
18?????????? 18
19?????????? 19
100???????? 100
已選擇13行。
Execution Plan
----------------------------------------------------------
?? 0????? SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52)
?? 1?? 0? TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=13 Bytes=52)
(表一共102行，選擇比例為13/102>10%,優化器選擇了全表掃描)
――――――――――――――――――――――――――――――――――
增加表行數
SQL> declare i number;
? 2? begin
? 3? for i in 200 .. 1000 loop
? 4? insert into test.testindex values (to_char(i),i);
? 5? end loop;
? 6? end;
? 7? /
PL/SQL 過程已成功完成。
SQL> commit;
提交完成。
SQL> select count(*) from test.testindex;
? COUNT(*)
----------
903
SQL> select * from test.testindex where a like '1%';
A???????????? B
----? ----------
1???????????? 2
1???????????? 1
10?????????? 10
11?????????? 11
12?????????? 12
13?????????? 13
14?????????? 14
15?????????? 15
16?????????? 16
17?????????? 17
18?????????? 18
19?????????? 19
100????????? 100
1000???????? 1000
已選擇14行。
Execution Plan
----------------------------------------------------------
?? 0????? SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52)
?? 1? 0? TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=13 Bytes=52)
? (表一共903行，選擇比例為14/903<5%,優化器選擇了全表掃描,選擇路徑是錯誤的)
―――――――――――――――――――――――――――――
給表做分析
SQL> analyze table test.testindex compute statistics for table for all indexed c
olumns for all indexes;
表已分析。
SQL> select * from test.testindex where a like '1%';
A???????????? B
---- ----------
1???????????? 2
1???????????? 1
10?????????? 10
100???????? 100
1000?????? 1000
11?????????? 11
12?????????? 12
13?????????? 13
14?????????? 14
15?????????? 15
16?????????? 16
17?????????? 17
18?????????? 18
19?????????? 19
已選擇14行。
Execution Plan
----------------------------------------------------------
?? 0????? SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=24 Bytes=120)
?? 1?? 0? TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=4 Card=
????????? 24 Bytes=120)
?? 2? 1? INDEX (RANGE SCAN) OF 'IND_COLA' (NON-UNIQUE) (Cost=2 Ca
????????? rd=24)
（經過分析后優化器選擇了正確的路徑，使用了ind_cola索引）
---------------------------------------------------------------------------------------------------------------------------------------------------
--MartriWang@gmail.com?18/05/2007--
--PCTFREE和PCTUSED調整
?PCTFREE存儲參數?
PCTFREE存儲參數告訴ORACLE什么時候應該將數據塊從對象的空閑列表中移出。ORACLE的默認參數是
PCTFREE=10；也就是說，一旦一個INSERT操作使得數據塊的90%被使用，這個數據塊就從空閑列表(free?
list)中移出。?
?PCTUSED存儲參數?
PCTUSED存儲參數告訴ORACLE什么時候將以前滿的數據塊加到空閑列表中。當記錄從數據表中刪除時，
數據庫的數據塊就有空間接受新的記錄，但只有當填充的空間降到PCTUSED值以下時，該數據塊才被連接
到空閑列表中，才可以往其中插入數據。PCTUSED的默認值是PCTUSED=40。?
存儲參數規則小結?
（1）PCTUSED較高意味著相對較滿的數據塊會被放置到空閑列表中，從而有效的重復使用數據塊的空間，
但會導致I/O消耗。PCTUSED低意味著在一個數據塊快空的時候才被放置到空閑列表中，數據塊一次能接受很多
的記錄，因此可以減少I/O消耗，提高性能。?
（2）PCTFREE的值較大意味著數據塊沒有被利用多少就從空閑列表中斷開連接，不利于數據塊的充分使用。
PCTFREE過小的結果是，在更新時可能會出現數據記錄遷移(Migration)的情況。(注：數據記錄遷移(Migration
)是指記錄在是UPDATE操作擴展了一個VARCHAR2類型的列或BLOB列后,PCTFREE參數所指定的空間不夠擴展，從而
記錄被ORACLE強制遷移到新的數據塊，發生這種情況將較嚴重的影響ORACLE的性能，出現更新緩慢)。?
（3）在批量的插入、刪除或者更新操作之前，先刪除該表上的索引，在操作完畢之后在重新建立，這樣有
助于提高批量操作的整體速度，并且保證B樹索引在操作之后有良好的性能。
--同優化器下的調整；?
基于成本優化器(CBO)：?
（1）ORACLE 8i 以上版本更多地使用成本優化器，因為它更加智能；?
（2）通過optimizer_mode=all_rows 或 first_rows來選擇CBO；通過
alter session set optimizer_goal=all_rows 或 first_rows來選擇CBO;通過添加hint來選擇CBO;?
（3）使用基于成本優化的一個關鍵是：存在表和索引的統計資料。通過analyze table 獲得表
的統計資料；通過analyze index獲得索引的統計資料。?
（4）對于超過5個表的連接的查詢，建議不要使用成本優化器，而是在SQL語句中通過添加
/* + rule */提示或者通過指定的執行計劃來避免可能會在20分鐘以上的SQL解析時間。?
基于規則優化器(RBO)：?
（1）ORACLE 8i以及ORACLE的以前版本主要用(RBO),并且比較有效；?
（2）通過optimizer_mode=rule來選擇RBO；通過alter session set optimizer_goal=rule來選擇
RBO; 通過添加/* + rule */來選擇RBO;?
（3）在RBO中，from 子句的表的順序決定表的連接順序。From 子句的最后一個表是驅動表，這個
表應該是最小的表。?
（4）限定性最強的布爾表達式放在最底層。
--跟蹤、優化SQL語句的方法?
保證在實例級將TIMED_STATISTICS設置為TRUE(在 INIT.ORA中永久的設置它或執行 ALTER SYSTEM命
令臨時設置它)；?
保證將MAX_DUMP_FILE_SIZE設置的較高。此參數控制跟蹤文件的大小。?
決定USER_DUMP_DEST所指向的位置，并保證有足夠的磁盤空間。這是放置跟蹤文件的位置。?
在應用系統運行時，打開所懷疑的回話的SQL_TRACE.(在 INIT.ORA中通過SQL_TRACE=TRUE永久的設置
對所有的回話進行跟蹤或通過使用系統包DBMS_SYSTEM.set_sql_trace_in_session(sid，serial,true);命
令臨時設置它)?
執行業務相關操作；?
設置跟蹤結束(DBMS_SYSTEM.set_sql_trace_in_session(sid,serial,false)，如果沒有該步驟，可能
跟蹤文件中的信息不全，因為可能有一部分還在緩存中)；?
定位跟蹤文件；?
對步驟6的跟蹤文件進行TKPROF，生成報告文件；?
研究此報告文件，可以看到CPU、DISK、 QUERY、 COUNT等參數和execution plan(執行計劃)，優化開
銷最大的SQL；?
---------------------------------------------------------------------------------------------------------------------------------------------------
--MartriWang@gmail.com?20/05/2007--
這是因為當進行index full scan的時候 oracle定位到索引的root block，然后到branch block（如果有的話），
再定位到第一個leaf block, 然后根據leaf block的雙向鏈表順序讀取。它所讀取的塊都是有順序的，也是經過排序的。
而index fast full scan則不同，它是從段頭開始，讀取包含位圖塊，root block,所有的branch block, leaf block，
讀取的順序完全有物理存儲位置決定，并采取多塊讀，沒次讀取db_file_multiblock_read_count個塊。
索引是提高數據查詢最有效的方法，也是最難全面掌握的技術，因為正確的索引可能使效率提高10000倍，而無效的索引
可能是浪費了數據庫空間，甚至大大降低查詢性能。
索引的管理成本
1、 存儲索引的磁盤空間
2、 執行數據修改操作(INSERT、UPDATE、DELETE)產生的索引維護
3、 在數據處理時回需額外的回退空間。
實際數據修改測試:
一個表有字段A、B、C，同時進行插入10000行記錄測試
在沒有建索引時平均完成時間是2.9秒
在對A字段建索引后平均完成時間是6.7秒
在對A字段和B字段建索引后平均完成時間是10.3秒
在對A字段、B字段和C字段都建索引后平均完成時間是11.7秒
從以上測試結果可以明顯看出索引對數據修改產生的影響
索引按存儲方法分類
B*樹索引
B*樹索引是最常用的索引，其存儲結構類似書的索引結構，
有分支和葉兩種類型的存儲數據塊，分支塊相當于書的大目錄，葉塊相當于索引到的具體的書頁。一般索引及唯一約束索引都使用B*樹索引。
位圖索引
位圖索引儲存主要用來節省空間，減少ORACLE對數據塊的訪問，它采用位圖偏移方式來與表的行ID號對應，采用位圖索引一般是重復值
太多的表字段。位圖索引在實際密集型OLTP(數據事務處理)中用得比較少，因為OLTP會對表進行大量的刪除、修改、新建操作，ORACLE每次
進行操作都會對要操作的數據塊加鎖，所以多人操作很容易產生數據塊鎖等待甚至死鎖現象。在OLAP(數據分析處理)中應用位圖有優勢，因
為OLAP中大部分是對數據庫的查詢操作，而且一般采用數據倉庫技術，所以大量數據采用位圖索引節省空間比較明顯。
索引按功能分類
唯一索引
唯一索引有兩個作用，一個是數據約束，一個是數據索引，其中數據約束主要用來保證數據的完整性，唯一索引產生的索引記錄中每一
條記錄都對應一個唯一的ROWID。
主關鍵字索引
主關鍵字索引產生的索引同唯一索引，只不過它是在數據庫建立主關鍵字時系統自動建立的。
一般索引
一般索引不產生數據約束作用，其功能主要是對字段建立索引表，以提高數據查詢速度。
索引按索引對象分類
單列索引(表單個字段的索引)
多列索引(表多個字段的索引)
函數索引(對字段進行函數運算的索引)
建立函數索引的方法:
create index 收費日期索引 on GC_DFSS(trunc(sk_rq))
create index 完全客戶編號索引 on yhzl(qc_bh||kh_bh)
在對函數進行了索引后，如果當前會話要引用應設置當前會話的query_rewrite_enabled為TRUE。
alter session set query_rewrite_enabled=true
注:如果對用戶函數進行索引的話，那用戶函數應加上 deterministic參數，意思是函數在輸入值固定的情況下返回值也固定。例:
create or replace function trunc_add(input_date date)return date deterministic
as
begin
return trunc(input_date+1);
end trunc_add;
應用索引的掃描分類
INDEX UNIQUE SCAN(按索引唯一值掃描)
select * from zl_yhjbqk where hbs_bh='5420016000'
INDEX RANGE SCAN(按索引值范圍掃描)
select * from zl_yhjbqk where hbs_bh>'5420016000'
select * from zl_yhjbqk where qc_bh>'7001'
INDEX FAST FULL SCAN(按索引值快速全部掃描)
select hbs_bh from zl_yhjbqk order by hbs_bh
select count(*) from zl_yhjbqk
select qc_bh from zl_yhjbqk group by qc_bh
什么情況下應該建立索引
表的主關鍵字
自動建立唯一索引
如zl_yhjbqk(用戶基本情況)中的hbs_bh(戶標識編號)
表的字段唯一約束
ORACLE利用索引來保證數據的完整性
如lc_hj(流程環節)中的lc_bh+hj_sx(流程編號+環節順序)
直接條件查詢的字段
在SQL中用于條件約束的字段
如zl_yhjbqk(用戶基本情況)中的qc_bh(區冊編號)
select * from zl_yhjbqk where qc_bh=’7001’
查詢中與其它表關聯的字段
字段常常建立了外鍵關系
如zl_ydcf(用電成份)中的jldb_bh(計量點表編號)
select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’
查詢中排序的字段
排序的字段如果通過索引去訪問那將大大提高排序速度
select * from zl_yhjbqk order by qc_bh(建立qc_bh索引)
select * from zl_yhjbqk where qc_bh='7001' order by cb_sx(建立qc_bh+cb_sx索引，注:只是一個索引，其中包括qc_bh和cb_sx字段)
查詢中統計或分組統計的字段
select max(hbs_bh) from zl_yhjbqk
select qc_bh,count(*) from zl_yhjbqk group by qc_bh
什么情況下應不建或少建索引
表記錄太少
如果一個表只有5條記錄，采用索引去訪問記錄的話，那首先需訪問索引表，再通過索引表訪問數據表，一般索引表與數據表不在同一個
數據塊，這種情況下ORACLE至少要往返讀取數據塊兩次。而不用索引的情況下ORACLE會將所有的數據一次讀出，處理速度顯然會比用索引快。
如表zl_sybm(使用部門)一般只有幾條記錄，除了主關鍵字外對任何一個字段建索引都不會產生性能優化，實際上如果對這個表進行了統
計分析后ORACLE也不會用你建的索引，而是自動執行全表訪問。如:
select * from zl_sybm where sydw_bh='5401'(對sydw_bh建立索引不會產生性能優化)
經常插入、刪除、修改的表
對一些經常處理的業務表應在查詢允許的情況下盡量減少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等業務表。
數據重復且分布平均的表字段
假如一個表有10萬行記錄，有一個字段A只有T和F兩種值，且每個值的分布概率大約為50%，那么對這種表A字段建索引一般不會提高數據庫
的查詢速度。
經常和主字段一塊查詢但主字段索引值比較多的表字段
如gc_dfss(電費實收)表經常按收費序號、戶標識編號、抄表日期、電費發生年月、操作標志來具體查詢某一筆收款的情況，如果將所有的
字段都建在一個索引里那將會增加數據的修改、插入、刪除時間，從實際上分析一筆收款如果按收費序號索引就已經將記錄減少到只有幾條，如
果再按后面的幾個字段索引查詢將對性能不產生太大的影響。
如何只通過索引返回結果
一個索引一般包括單個或多個字段，如果能不訪問表直接應用索引就返回結果那將大大提高數據庫查詢的性能。對比以下三個SQL，其中對表
zl_yhjbqk的hbs_bh和qc_bh字段建立了索引:
1 select hbs_bh,qc_bh,xh_bz from zl_yhjbqk where qc_bh=’7001’
執行路徑:
SELECT STATEMENT, GOAL = CHOOSE 11 265 5565
TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK 11 265 5565
INDEX RANGE SCAN DLYX 區冊索引 1 265
平均執行時間(0.078秒)
2 select hbs_bh,qc_bh from zl_yhjbqk where qc_bh=’7001’
執行路徑:
SELECT STATEMENT, GOAL = CHOOSE 11 265 3710
TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK 11 265 3710
INDEX RANGE SCAN DLYX 區冊索引 1 265
平均執行時間(0.078秒)
3 select qc_bh from zl_yhjbqk where qc_bh=’7001’
執行路徑:
SELECT STATEMENT, GOAL = CHOOSE 1 265 1060
INDEX RANGE SCAN DLYX 區冊索引 1 265 1060
平均執行時間(0.062秒)
從執行結果可以看出第三條SQL的效率最高。執行路徑可以看出第1、2條SQL都多執行了TABLE ACCESS BY INDEX ROWID(通過ROWID訪問表)?
這個步驟，因為返回的結果列中包括當前使用索引(qc_bh)中未索引的列(hbs_bh,xh_bz)，而第3條SQL直接通過QC_BH返回了結果，這就是通過
索引直接返回結果的方法。
如何重建索引
alter index 表電量結果表主鍵 rebuild
如何快速新建大數據量表的索引
如果一個表的記錄達到100萬以上的話，要對其中一個字段建索引可能要花很長的時間，甚至導致服務器數據庫死機，因為在建索引的時候
ORACLE要將索引字段所有的內容取出并進行全面排序，數據量大的話可能導致服務器排序內存不足而引用磁盤交換空間進行，這將嚴重影響服
務器數據庫的工作。解決方法是增大數據庫啟動初始化中的排序內存參數，如果要進行大量的索引修改可以設置10M以上的排序內存(ORACLE缺省
大小為64K)，在索引建立完成后應將參數修改回來，因為在實際OLTP數據庫應用中一般不會用到這么大的排序內存。
--MartriWang@gmail.com?15/06/2007--
通過分析SQL語句的執行計劃優化SQL （三）?
第4章 ORACLE的優化器
優化器有時也被稱為查詢優化器，這是因為查詢是影響數據庫性能最主要的部分，不要以為只有SELECT語句是查詢。實際上，帶有任何
WHERE條件的DML(INSERT、UPDATE、DELETE)語句中都包含查詢要求，在后面的文章中，當說到查詢時，不一定只是指SELECT語句，也有可能
指DML語句中的查詢部分。優化器是所有關系數據庫引擎中的最神秘、最富挑戰性的部件之一，從性能的角度看也是最重要的部分，它性能的
高低直接關系到數據庫性能的好壞。
我們知道，SQL語句同其它語言(如C語言)的語句不一樣，它是非過程化(non-procedural)的語句，即當你要取數據時，不需要告訴數據
庫通過何種途徑去取數據，如到底是通過索引取數據，還是應該將表中的每行數據都取出來，然后再通過一一比較的方式取數據(即全表掃描)，
這是由數據庫的優化器決定的，這就是非過程化的含義，也就是說，如何取數據是由優化器決定，而不是應用開發者通過編程決定。在處理SQL
的SELECT、UPDATE、INSERT或DELETE語句時，Oracle 必須訪問語句所涉及的數據，Oracle的優化器部分用來決定訪問數據的有效路徑，使得語
句執行所需的I/O和處理時間最小。
為了實現一個查詢，內核必須為每個查詢定制一個查詢策略，或為取出符合條件的數據生成一個執行計劃(execution plan)。典型的，對于
同一個查詢，可能有幾個執行計劃都符合要求，都能得到符合條件的數據。例如，參與連接的表可以有多種不同的連接方法，這取決于連接條件
和優化器采用的連接方法。為了在多個執行計劃中選擇最優的執行計劃，優化器必須使用一些實際的指標來衡量每個執行計劃使用的資源(I/0次
數、CPU等)，這些資源也就是我們所說的代價(cost)。如果一個執行計劃使用的資源多，我們就說使用執行計劃的代價大。以執行計劃的代價大
小作為衡量標準，優化器選擇代價最小的執行計劃作為真正執行該查詢的執行計劃，并拋棄其它的執行計劃。
在ORACLE的發展過程中，一共開發過2種類型的優化器：基于規則的優化器和基于代價的優化器。這2種優化器的不同之處關鍵在于：取得代
價的方法與衡量代價的大小不同。現對每種優化器做一下簡單的介紹：
基于規則的優化器 -- Rule Based (Heuristic) Optimization(簡稱RBO)：
在ORACLE7之前，主要是使用基于規則的優化器。ORACLE在基于規則的優化器中采用啟發式的方法(Heuristic Approach)或規則(Rules)來生
成執行計劃。例如，如果一個查詢的where條件(where clause)包含一個謂詞(predicate，其實就是一個判斷條件，如”=”, “>”, ”<”等)，而且
該謂詞上引用的列上有有效索引，那么優化器將使用索引訪問這個表，而不考慮其它因素，如表中數據的多少、表中數據的易變性、索引的可選
擇性等。此時數據庫中沒有關于表與索引數據的統計性描述，如表中有多上行，每行的可選擇性等。優化器也不考慮實例參數，如multi block?
i/o、可用排序內存的大小等，所以優化器有時就選擇了次優化的計劃作為真正的執行計劃，導致系統性能不高。
如，對于select * from emp where deptno = 10這個查詢來說，如果是使用基于規則的優化器，而且deptno列上有有效的索引，則會通過
deptno列上的索引來訪問emp表。在絕大多數情況下，這是比較高效的，但是在一些特殊情況下，使用索引訪問也有比較低效的時候，現舉例說
明：
1) emp表比較小，該表的數據只存放在幾個數據塊中。此時使用全表掃描比使用索引訪問emp表反而要好。因為表比較小，極有可能數據全
在內存中，所以此時做全表掃描是最快的。而如果使用索引掃描，需要先從索引中找到符合條件記錄的rowid，然后再一一根據這些rowid從emp
中將數據取出來，在這種條件下，效率就會比全表掃描的效率要差一些。
2) emp表比較大時，而且deptno = 10條件能查詢出表中大部分的數據如(50%)。如該表共有4000萬行數據，共放在有500000個數據塊中，
每個數據塊為8k，則該表共有約4G，則這么多的數據不可能全放在內存中，絕大多數需要放在硬盤上。此時如果該查詢通過索引查詢，則是你夢
魘的開始。db_file_multiblock_read_count參數的值200。如果采用全表掃描，則需要500000/db_file_multiblock_read_count=500000/200=
2500次I/O。但是如果采用索引掃描，假設deptno列上的索引都已經cache到內存中，所以可以將訪問索引的開銷忽略不計。因為要讀出4000萬x?
50% = 2000萬數據，假設在讀這2000萬數據時，有99.9%的命中率，則還是需要20000次I/O,比上面的全表掃描需要的2500次多多了，所以在這種
情況下，用索引掃描反而性能會差很多。在這樣的情況下，用全表掃描的時間是固定的，但是用索引掃描的時間會隨著選出數據的增多使查詢時
間相應的延長。
上面是枯燥的假設數據，現在以具體的實例給予驗證：?
環境: oracle 817 + linux + 陣列柜，表SWD_BILLDETAIL有3200多萬數據；?
表的id列、cn列上都有索引?
經查看執行計劃，發現執行select count(id) from SWD_BILLDETAIL;使用全表掃描，執行完用了大約1.50分鐘(4次執行取平均，每次分別為
1.45 1.51 2.00 1.46)。而執行select count(id) from SWD_BILLDETAIL where cn <'6';卻用了2個小時還沒有執行完，經分析該語句使用了cn列
上的索引，然后利用查詢出的rowid再從表中查詢數據。我為什么不使用select count(cn) from SWD_BILLDETAIL where cn <'6';呢？
后面在分析執行路徑的索引掃描時時會給出說明。
下面就是基于規則的優化器使用的執行路徑與各個路徑對應的等級：?
RBO Path 1: Single Row by Rowid(等級最高)?
RBO Path 2: Single Row by Cluster Join?
RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key?
RBO Path 4: Single Row by Unique or Primary Key?
RBO Path 5: Clustered Join?
RBO Path 6: Hash Cluster Key?
RBO Path 7: Indexed Cluster Key?
RBO Path 8: Composite Index?
RBO Path 9: Single-Column Indexes?
RBO Path 10: Bounded Range Search on Indexed Columns?
RBO Path 11: Unbounded Range Search on Indexed Columns?
RBO Path 12: Sort Merge Join?
RBO Path 13: MAX or MIN of Indexed Column?
RBO Path 14: ORDER BY on Indexed Column?
RBO Path 15: Full Table Scan(等級最低)
上面的執行路徑中，RBO認為越往下執行的代價越大，即等級越低。在RBO生成執行計劃時，如果它發現有等級高的執行路徑可用，則肯定
會使用等級高的路徑，而不管任何其它影響性能的元素，即RBO通過上面的路徑的等級決定執行路徑的代價，執行路徑的等級越高，則使用該執
行路徑的代價越小。如上面2個例子所述，如果使用RBO，則肯定使用索引訪問表，也就是選擇了比較差的執行計劃，這樣會給數據庫性能帶來很
大的負面影響。為了解決這個問題，從ORACLE 7開始oracle引入了基于代價的優化器，下面給出了介紹。
基于代價的優化器 -- Cost Based Optimization(簡稱CBO)
Oracle把一個代價引擎(Cost Engine)集成到數據庫內核中，用來估計每個執行計劃需要的代價，該代價將每個執行計劃所耗費的資源進行
量化，從而CBO可以根據這個代價選擇出最優的執行計劃。一個查詢耗費的資源可以被分成3個基本組成部分：I/O代價、CPU代價、network代價。
I/O代價是將數據從磁盤讀入內存所需的代價。訪問數據包括將數據文件中數據塊的內容讀入到SGA的數據高速緩存中，在一般情況下，該代價是
處理一個查詢所需要的最主要代價，所以我們在優化時，一個基本原則就是降低查詢所產生的I/O總次數。CPU代價是處理在內存中數據所需要的
代價，如一旦數據被讀入內存，則我們在識別出我們需要的數據后，在這些數據上執行排序(sort)或連接(join)操作，這需要耗費CPU資源。
對于需要訪問跨節點(即通常說的服務器)數據庫上數據的查詢來說，存在network代價，用來量化傳輸操作耗費的資源。查詢遠程表的查詢
或執行分布式連接的查詢會在network代價方面花費比較大。
在使用CBO時，需要有表和索引的統計數據(分析數據)作為基礎數據，有了這些數據，CBO才能為各個執行計劃計算出相對準確的代價，從而
使CBO選擇最佳的執行計劃。所以定期的對表、索引進行分析是絕對必要的，這樣才能使統計數據反映數據庫中的真實情況。否則就會使CBO選擇
較差的執行計劃，影響數據庫的性能。分析操作不必做的太頻繁，一般來說，每星期一次就足夠了。切記如果想使用CBO，則必須定期對表和索引
進行分析。
對于分析用的命令，隨著數據庫版本的升級，用的命令也發生了變換，在oracle 8i以前，主要是用ANALYZE命令。在ORACLE 8I以后，又引入
了DBMS_STATS存儲包來進行分析。幸運的是從ORACLE 10G以后，分析工作變成自動的了，這減輕的DBA的負擔，不過在一些特殊情況下，還需要一
些手工分析。
如果采用了CBO優化器，而沒有對表和索引進行分析，沒有統計數據，則ORACLE使用缺省的統計數據(至少在ORACLE 9I中是這樣)，這可以從
oracle的文檔上找到。使用的缺省值肯定與系統的實際統計值不一致，這可能會導致優化器選擇錯誤的執行計劃，影響數據庫的性能。
要注意的是：雖然CBO的功能隨著ORACLE新版本的推出，功能越來越強，但它不是能包治百病的神藥，否則就不再需要DBA了，那我就慘了
實際上任何一個語句，隨著硬件環境與應用數據的不同，該語句的執行計劃可能需要隨之發生變化，這樣才能取得最好的性能。所以有時候不
在具體的環境下而進行SQL性能調整是徒勞的。
在ORACLE8I推出的時候，ORACLE極力建議大家使用CBO，說CBO有種種好處，但是在那是ORACLE開發的應用系統還是使用基于規則的優化器，
從這件事上我們可以得出這樣的結論：
1) 如果團隊的數據庫水平很高而且都熟悉應用數據的特點，RBO也可以取得很好的性能。
2）CBO不是很穩定，但是一個比較有前途的優化器，Oracle極力建議大家用是為了讓大家盡快發現它的BUG，以便進一步改善，但是
ORACLE為了對自己開發的應用系統負責，他們還是使用了比較熟悉而且成熟的RBO。從這個事情上給我們的啟發就是：我們在以后的開發中，
應該盡量采用我們熟悉并且成熟的技術，而不要一味的采用新技術，一味采用新技術并不一定能開發出好的產品。幸運的是從ORACLE 10G后，
CBO已經足夠的強大與智能，大家可以放心的使用該技術，因為ORACLE 10G后，Oracle自己開發的應用系統也使用CBO優化器了。而且ORACLE
規定，從ORACLE 10G開始，開始廢棄RBO優化器。這句話并不是指在ORACLE 10G中不能使用RBO，而是從ORACLE 10G開始開始，不再為RBO的
BUG提供修補服務。
在上面的第2個例子中，如果采用CBO優化器，它就會考慮emp表的行數，deptno列的統計數據，發現對該列做查詢會查詢出過多的數據，
并且考慮db_file_multiblock_read_count參數的設置，發現用全表掃描的代價比用索引掃描的代價要小，從而使用全表掃描從而取得良好
的執行性能。
判斷當前數據庫使用何種優化器：
主要是由optimizer_mode初始化參數決定的。該參數可能的取值為：first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows |
?choose | rule。具體解釋如下：?
RULE為使用RBO優化器。?
CHOOSE則是根據實際情況，如果數據字典中包含被引用的表的統計數據，即引用的對象已經被分析，則就使用CBO優化器，否則為RBO優
化器。?
ALL_ROWS為CBO優化器使用的第一種具體的優化方法，是以數據的吞吐量為主要目標，以便可以使用最少的資源完成語句。?
FIRST_ROWS為優化器使用的第二種具體的優化方法，是以數據的響應時間為主要目標，以便快速查詢出開始的幾行數據。?
FIRST_ROWS_[1 | 10 | 100 | 1000] 為優化器使用的第三種具體的優化方法，讓優化器選擇一個能夠把響應時間減到最小的查詢執行
計劃，以迅速產生查詢結果的前 n 行。該參數為ORACLE 9I新引入的。
從ORACLE V7以來，optimizer_mode參數的缺省設置應是"choose"，即如果對已分析的表查詢的話選擇CBO，否則選擇RBO。在此種設置中，
如果采用了CBO，則缺省為CBO中的all_rows模式。
注意：即使指定數據庫使用RBO優化器，但有時ORACLE數據庫還是會采用CBO優化器，這并不是ORACLE的BUG，主要是由于從ORACLE 8I后引
入的許多新特性都必須在CBO下才能使用，而你的SQL語句可能正好使用了這些新特性，此時數據庫會自動轉為使用CBO優化器執行這些語句。
什么是優化
優化是選擇最有效的執行計劃來執行SQL語句的過程，這是在處理任何數據的語句（SELECT,INSERT,UPDATE或DELETE）中的一個重要步驟。
對Oracle來說，執行這樣的語句有許多不同的方法，譬如說，將隨著以什么順序訪問哪些表或索引的不同而不同。所使用的執行計劃可以決定
語句能執行得有多快。Oracle中稱之為優化器（Optimizer）的組件用來選擇這種它認為最有效的執行計劃。
由于一系列因素都會會影響語句的執行，優化器綜合權衡各個因素，在眾多的執行計劃中選擇認為是最佳的執行計劃。然而，應用設計人
員通常比優化器更知道關于特定應用的數據特點。無論優化器多么智能，在某些情況下開發人員能選擇出比優化器選擇的最優執行計劃還要好
的執行計劃。這是需要人工干預數據庫優化的主要原因。事實表明，在某些情況下，確實需要DBA對某些語句進行手工優化。?
注：從Oracle的一個版本到另一個版本，優化器可能對同一語句生成不同的執行計劃。在將來的Oracle 版本中，優化器可能會基于它可以
用的更好、更理想的信息，作出更優的決策，從而導致為語句產生更優的執行計劃。
在物理層，oracle讀取數據，一次讀取的最小單位為數據庫塊(由多個連續的操作系統塊組成)，一次讀取的最大值由操作系統一次I/O的最大值
與multiblock參數共同決定，所以即使只需要一行數據，也是將該行所在的數據庫塊讀入內存。邏輯上，oracle用如下存取方法訪問數據：?
--MartriWang@gmail.com?15/06/2007--
1) 全表掃描（Full Table Scans, FTS）
為實現全表掃描，Oracle讀取表中所有的行，并檢查每一行是否滿足語句的WHERE限制條件。Oracle順序地讀取分配給表的每個數據塊，直
到讀到表的最高水線處(high water mark, HWM，標識表的最后一個數據塊)。一個多塊讀操作可以使一次I/O能讀取多塊數據塊
(db_block_multiblock_read_count參數設定)，而不是只讀取一個數據塊，這極大的減少了I/O總次數，提高了系統的吞吐量，所以利用多塊讀
的方法可以十分高效地實現全表掃描，而且只有在全表掃描的情況下才能使用多塊讀操作。在這種訪問模式下，每個數據塊只被讀一次。由于
HWM標識最后一塊被讀入的數據，而delete操作不影響HWM值，所以一個表的所有數據被delete后，其全表掃描的時間不會有改善，一般我們需要
使用truncate命令來使HWM值歸為0。幸運的是oracle 10G后，可以人工收縮HWM的值。
由FTS模式讀入的數據被放到高速緩存的Least Recently Used (LRU)列表的尾部，這樣可以使其快速交換出內存，從而不使內存重要的數據
被交換出內存。使用FTS的前提條件：在較大的表上不建議使用全表掃描，除非取出數據的比較多，超過總量的5% -- 10%，或你想使用并行查詢
功能時。?
使用全表掃描的例子：?
~~~~~~~~~~~~~~~~~~~~~~~~?
SQL> explain plan for select * from dual;?
Query Plan?
-----------------------------------------?
SELECT STATEMENT [CHOOSE] Cost=?
TABLE ACCESS FULL DUAL
2) 通過ROWID的表存取（Table Access by ROWID或rowid lookup）
行的ROWID指出了該行所在的數據文件、數據塊以及行在該塊中的位置，所以通過ROWID來存取數據可以快速定位到目標數據上，是Oracle存取
單行數據的最快方法。為了通過ROWID存取表，Oracle 首先要獲取被選擇行的ROWID，或者從語句的WHERE子句中得到，或者通過表的一個或多個索
引的索引掃描得到。Oracle然后以得到的ROWID為依據定位每個被選擇的行。
這種存取方法不會用到多塊讀操作，一次I/O只能讀取一個數據塊。我們會經常在執行計劃中看到該存取方法，如通過索引查詢數據。
使用ROWID存取的方法：?
SQL> explain plan for select * from dept where rowid = 'AAAAyGAADAAAAATAAF';?
Query Plan?
------------------------------------?
SELECT STATEMENT [CHOOSE] Cost=1?
TABLE ACCESS BY ROWID DEPT [ANALYZED]
3）索引掃描（Index Scan或index lookup）
我們先通過index查找到數據對應的rowid值(對于非唯一索引可能返回多個rowid值)，然后根據rowid直接從表中得到具體的數據，這種查找方式
稱為索引掃描或索引查找(index lookup)。一個rowid唯一的表示一行數據，該行對應的數據塊是通過一次i/o得到的，在此情況下該次i/o只會讀取
一個數據庫塊。
在索引中，除了存儲每個索引的值外，索引還存儲具有此值的行對應的ROWID值。索引掃描可以由2步組成：
(1) 掃描索引得到對應的rowid值。?
(2) 通過找到的rowid從表中讀出具體的數據。每步都是單獨的一次I/O，但是對于索引，由于經常使用，絕大多數都已經CACHE到內存中，所以第
1步的I/O經常是邏輯I/O，即數據可以從內存中得到。但是對于第2步來說，如果表比較大，則其數據不可能全在內存中，所以其I/O很有可能是物理I/O，
這是一個機械操作，相對邏輯I/O來說，是極其費時間的。所以如果多大表進行索引掃描，取出的數據如果大于總量的5% -- 10%，使用索引掃描會效率
下降很多。?
如下列所示：?
SQL> explain plan for select empno, ename from emp where empno=10;?
Query Plan?
------------------------------------?
SELECT STATEMENT [CHOOSE] Cost=1?
TABLE ACCESS BY ROWID EMP [ANALYZED]?
INDEX UNIQUE SCAN EMP_I1
注意TABLE ACCESS BY ROWID EMP部分，這表明這不是通過FTS存取路徑訪問數據，而是通過rowid lookup存取路徑訪問數據的。在此例中，所需要
的rowid是由于在索引查找empno列的值得到的，這種方式是INDEX UNIQUE SCAN查找，后面給予介紹，EMP_I1為使用的進行索引查找的索引名字。
但是如果查詢的數據能全在索引中找到，就可以避免進行第2步操作，避免了不必要的I/O，此時即使通過索引掃描取出的數據比較多，效率還是很
高的，因為這只會在索引中讀取。所以上面我在介紹基于規則的優化器時，使用了select count(id) from SWD_BILLDETAIL where cn <'6'，而沒有使
用select count(cn) from SWD_BILLDETAIL where cn <'6'。因為在實際情況中，只查詢被索引列的值的情況極為少，所以，如果我在查詢中使用count
(cn)，則不具有代表性。
SQL> explain plan for select empno from emp where empno=10; -- 只查詢empno列值?
Query Plan?
------------------------------------?
SELECT STATEMENT [CHOOSE] Cost=1?
INDEX UNIQUE SCAN EMP_I1
進一步講，如果sql語句中對索引列進行排序，因為索引已經預先排序好了，所以在執行計劃中不需要再對索引列進行排序
SQL> explain plan for select empno, ename from emp?
where empno > 7876 order by empno;?
Query Plan?
--------------------------------------------------------------------------------?
SELECT STATEMENT [CHOOSE] Cost=1?
TABLE ACCESS BY ROWID EMP [ANALYZED]?
INDEX RANGE SCAN EMP_I1 [ANALYZED]
從這個例子中可以看到：因為索引是已經排序了的，所以將按照索引的順序查詢出符合條件的行，因此避免了進一步排序操作。
根據索引的類型與where限制條件的不同，有4種類型的索引掃描：?
索引唯一掃描(index unique scan)?
索引范圍掃描(index range scan)?
索引全掃描(index full scan)?
索引快速掃描(index fast full scan)
(1) 索引唯一掃描(index unique scan)
通過唯一索引查找一個數值經常返回單個ROWID。如果該唯一索引有多個列組成(即組合索引)，則至少要有組合索引的引導列參
與到該查詢中，如創建一個索引：
create index idx_test on emp(ename, deptno, loc)。則select ename from emp where ename = 'JACK' and deptno = 'DEV'語
句可以使用該索引。如果該語句只返回一行，則存取方法稱為索引唯一掃描。而select ename from emp where deptno = 'DEV'語句
則不會使用該索引，因為where子句種沒有引導列。如果存在UNIQUE 或PRIMARY KEY 約束（它保證了語句只存取單行）的話，
Oracle經常實現唯一性掃描。?
使用唯一性約束的例子：?
SQL> explain plan for?
select empno,ename from emp where empno=10;?
Query Plan?
------------------------------------?
SELECT STATEMENT [CHOOSE] Cost=1?
TABLE ACCESS BY ROWID EMP [ANALYZED]?
INDEX UNIQUE SCAN EMP_I1
(2) 索引范圍掃描(index range scan)
使用一個索引存取多行數據，同上面一樣，如果索引是組合索引，如(1)所示，
而且select ename from emp where ename = 'JACK' and deptno = 'DEV'語句返回多行數據，雖然該語句還是使用該組合索引進行查
詢，可此時的存取方法稱為索引范圍掃描。在唯一索引上使用索引范圍掃描的典型情況下是在謂詞(where限制條件)中使用了范圍操作
符(如>、<、<>、>=、<=、between)
使用索引范圍掃描的例子：?
SQL> explain plan for select empno,ename from emp?
where empno > 7876 order by empno;?
Query Plan?
--------------------------------------------------------------------------------?
SELECT STATEMENT [CHOOSE] Cost=1?
TABLE ACCESS BY ROWID EMP [ANALYZED]?
INDEX RANGE SCAN EMP_I1 [ANALYZED]
在非唯一索引上，謂詞col = 5可能返回多行數據，所以在非唯一索引上都使用索引范圍掃描。?
使用index rang scan的3種情況：?
(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)?
(b) 在組合索引上，只使用部分列進行查詢，導致查詢出多行?
(c) 對非唯一索引列上進行的任何查詢。
(3) 索引全掃描(index full scan)
與全表掃描對應，也有相應的全索引掃描。在某些情況下，可能進行全索引掃描而不是范圍掃描，需要注意的是全索引掃描只在
CBO模式下才有效。CBO根據統計數值得知進行全索引掃描比進行全表掃描更有效時，才進行全索引掃描，而且此時查詢出的數據都必
須從索引中可以直接得到。?
全索引掃描的例子：?
An Index full scan will not perform single block i/o s and so it may prove to be inefficient.
e.g.?
Index BE_IX is a concatenated index on big_emp (empno, ename)
SQL> explain plan for select empno, ename from big_emp order by empno,ename;?
Query Plan?
--------------------------------------------------------------------------------?
SELECT STATEMENT [CHOOSE] Cost=26?
INDEX FULL SCAN BE_IX [ANALYZED]
(4) 索引快速掃描(index fast full scan)
掃描索引中的所有的數據塊，與 index full scan很類似，但是一個顯著的區別就是它不對查詢出的數據進行排序，即數據不是
以排序順序被返回。在這種存取方法中，可以使用多塊讀功能，也可以使用并行讀入，以便獲得最大吞吐量與縮短執行時間。
索引快速掃描的例子：?
BE_IX索引是一個多列索引：big_emp (empno,ename)
SQL> explain plan for select empno,ename from big_emp;?
Query Plan?
------------------------------------------?
SELECT STATEMENT [CHOOSE] Cost=1?
INDEX FAST FULL SCAN BE_IX [ANALYZED]
只選擇多列索引的第2列：
SQL> explain plan for select ename from big_emp;?
Query Plan?
------------------------------------------?
SELECT STATEMENT [CHOOSE] Cost=1?
INDEX FAST FULL SCAN BE_IX [ANALYZED]
--MartriWang@gmail.com?15/06/2007--
表之間的連接
Join是一種試圖將兩個表結合在一起的謂詞，一次只能連接2個表，表連接也可以被稱為表關聯。在后面的敘述中，我們將會使用”row
source”來代替”表”，因為使用row source更嚴謹一些，并且將參與連接的2個row source分別稱為row source1和row source 2。Join過程
的各個步驟經常是串行操作，即使相關的row source可以被并行訪問，即可以并行的讀取做join連接的兩個row source的數據，但是在將表中
符合限制條件的數據讀入到內存形成row source后，join的其它步驟一般是串行的。有多種方法可以將2個表連接起來，當然每種方法都有自己
的優缺點，每種連接類型只有在特定的條件下才會發揮出其最大優勢。
row source(表)之間的連接順序對于查詢的效率有非常大的影響。通過首先存取特定的表，即將該表作為驅動表，這樣可以先應用某些限
制條件，從而得到一個較小的row source，使連接的效率較高，這也就是我們常說的要先執行限制條件的原因。一般是在將表讀入內存時，應
用where子句中對該表的限制條件。
根據2個row source的連接條件的中操作符的不同，可以將連接分為等值連接(如WHERE A.COL3 = B.COL4)、非等值連接(WHERE A.COL3 >
B.COL4)、外連接(WHERE A.COL3 = B.COL4(+))。上面的各個連接的連接原理都基本一樣，所以為了簡單期間，下面以等值連接為例進行介紹。
在后面的介紹中，都已：?
SELECT A.COL1, B.COL2?
FROM A, B?
WHERE A.COL3 = B.COL4;
為例進行說明，假設A表為Row Soruce1，則其對應的連接操作關聯列為COL 3；B表為Row Soruce2，則其對應的連接操作關聯列為COL 4；
連接類型：
目前為止，無論連接操作符如何，典型的連接類型共有3種：?
排序 - - 合并連接(Sort Merge Join (SMJ) )?
嵌套循環(Nested Loops (NL) )?
哈希連接(Hash Join)
排序 - - 合并連接(Sort Merge Join, SMJ)
內部連接過程：?
1) 首先生成row source1需要的數據，然后對這些數據按照連接操作關聯列(如A.col3)進行排序。?
2) 隨后生成row source2需要的數據，然后對這些數據按照與sort source1對應的連接操作關聯列(如B.col4)進行排序。?
3) 最后兩邊已排序的行被放在一起執行合并操作，即將2個row source按照連接條件連接起來?
下面是連接步驟的圖形表示：?
MERGE?
/ /?
SORT SORT?
| |?
Row Source 1 Row Source 2
如果row source已經在連接關聯列上被排序，則該連接操作就不需要再進行sort操作，這樣可以大大提高這種連接操作的連接速度，因為
排序是個極其費資源的操作，特別是對于較大的表。 預先排序的row source包括已經被索引的列(如a.col3或b.col4上有索引)或row source已
經在前面的步驟中被排序了。盡管合并兩個row source的過程是串行的，但是可以并行訪問這兩個row source(如并行讀入數據，并行排序).
SMJ連接的例子：
SQL> explain plan for?
select /*+ ordered */ e.deptno, d.deptno?
from emp e, dept d?
where e.deptno = d.deptno?
order by e.deptno, d.deptno;
Query Plan?
-------------------------------------?
SELECT STATEMENT [CHOOSE] Cost=17?
MERGE JOIN?
SORT JOIN?
TABLE ACCESS FULL EMP [ANALYZED]?
SORT JOIN?
TABLE ACCESS FULL DEPT [ANALYZED]
排序是一個費時、費資源的操作，特別對于大表。基于這個原因，SMJ經常不是一個特別有效的連接方法，但是如果2個row source都已經
預先排序，則這種連接方法的效率也是蠻高的。
嵌套循環(Nested Loops, NL)
這個連接方法有驅動表(外部表)的概念。其實，該連接過程就是一個2層嵌套循環，所以外層循環的次數越少越好，這也就是我們為什么將
小表或返回較小row source的表作為驅動表(用于外層循環)的理論依據。但是這個理論只是一般指導原則，因為遵循這個理論并不能總保證使
語句產生的I/O次數最少。有時不遵守這個理論依據，反而會獲得更好的效率。如果使用這種方法，決定使用哪個表作為驅動表很重要。有時如
果驅動表選擇不正確，將會導致語句的性能很差、很差。
內部連接過程：?
Row source1的Row 1 -------------- -- Probe -> Row source 2?
Row source1的Row 2 -------------- -- Probe -> Row source 2?
Row source1的Row 3 -------------- -- Probe -> Row source 2?
…….?
Row source1的Row n -------------- -- Probe -> Row source 2
從內部連接過程來看，需要用row source1中的每一行，去匹配row source2中的所有行，所以此時保持row source1盡可能的小與高效的訪
問row source2(一般通過索引實現)是影響這個連接效率的關鍵問題。這只是理論指導原則，目的是使整個連接操作產生最少的物理I/O次數，
而且如果遵守這個原則，一般也會使總的物理I/O數最少。但是如果不遵從這個指導原則，反而能用更少的物理I/O實現連接操作，那盡管違反
指導原則吧！因為最少的物理I/O次數才是我們應該遵從的真正的指導原。
在上面的連接過程中，我們稱Row source1為驅動表或外部表。Row Source2被稱為被探查表或內部表。
在NESTED LOOPS連接中，Oracle讀取row source1中的每一行，然后在row sourc2中檢查是否有匹配的行，所有被匹配的行都被放到結果集
中，然后處理row source1中的下一行。這個過程一直繼續，直到row source1中的所有行都被處理。這是從連接操作中可以得到第一個匹配行
的最快的方法之一，這種類型的連接可以用在需要快速響應的語句中，以響應速度為主要目標。
如果driving row source(外部表)比較小，并且在inner row source(內部表)上有唯一索引，或有高選擇性非唯一索引時，使用這種方法
可以得到較好的效率。NESTED LOOPS有其它連接方法沒有的的一個優點是：可以先返回已經連接的行，而不必等待所有的連接操作處理完才返
回數據，這可以實現快速的響應時間。
如果不使用并行操作，最好的驅動表是那些應用了where 限制條件后，可以返回較少行數據的的表，所以大表也可能稱為驅動表，關鍵看
限制條件。對于并行查詢，我們經常選擇大表作為驅動表，因為大表可以充分利用并行功能。當然，有時對查詢使用并行操作并不一定會比查
詢不使用并行操作效率高，因為最后可能每個表只有很少的行符合限制條件，而且還要看你的硬件配置是否可以支持并行(如是否有多個CPU，
多個硬盤控制器)，所以要具體問題具體對待。
NL連接的例子：?
SQL> explain plan for?
select a.dname,b.sql?
from dept a,emp b?
where a.deptno = b.deptno;
Query Plan?
-------------------------?
SELECT STATEMENT [CHOOSE] Cost=5?
NESTED LOOPS?
TABLE ACCESS FULL DEPT [ANALYZED]?
TABLE ACCESS FULL EMP [ANALYZED]
哈希連接(Hash Join, HJ)
這種連接是在oracle 7.3以后引入的，從理論上來說比NL與SMJ更高效，而且只用在CBO優化器中。較小的row source被用來構建hash
table與bitmap，第2個row source被用來被hansed，并與第一個row source生成的hash table進行匹配，以便進行進一步的連接。Bitmap被用
來作為一種比較快的查找方法，來檢查在hash table中是否有匹配的行。特別的，當hash table比較大而不能全部容納在內存中時，這種查找
方法更為有用。這種連接方法也有NL連接中所謂的驅動表的概念，被構建為hash table與bitmap的表為驅動表，當被構建的hash table與
bitmap能被容納在內存中時，這種連接方式的效率極高。
HASH連接的例子：?
SQL> explain plan for?
select /*+ use_hash(emp) */ empno?
from emp, dept?
where emp.deptno = dept.deptno;
Query Plan?
----------------------------?
SELECT STATEMENT [CHOOSE] Cost=3?
HASH JOIN?
TABLE ACCESS FULL DEPT?
TABLE ACCESS FULL EMP
要使哈希連接有效，需要設置HASH_JOIN_ENABLED=TRUE，缺省情況下該參數為TRUE，另外，不要忘了還要設置hash_area_size參數，以使
哈希連接高效運行，因為哈希連接會在該參數指定大小的內存中運行，過小的參數會使哈希連接的性能比其他連接方式還要低。
總結一下，在哪種情況下用哪種連接方法比較好：
排序 - - 合并連接(Sort Merge Join, SMJ)：?
a) 對于非等值連接，這種連接方式的效率是比較高的。?
b) 如果在關聯的列上都有索引，效果更好。?
c) 對于將2個較大的row source做連接，該連接方法比NL連接要好一些。?
d) 但是如果sort merge返回的row source過大，則又會導致使用過多的rowid在表中查詢數據時，數據庫性能下降，因為過多的I/O。
嵌套循環(Nested Loops, NL)：?
a) 如果driving row source(外部表)比較小，并且在inner row source(內部表)上有唯一索引，或有高選擇性非唯一索引時，使用這種方
法可以得到較好的效率。?
b) NESTED LOOPS有其它連接方法沒有的的一個優點是：可以先返回已經連接的行，而不必等待所有的連接操作處理完才返回數據，這可以
實現快速的響應時間。
哈希連接(Hash Join, HJ)：?
a) 這種方法是在oracle7后來引入的，使用了比較先進的連接理論，一般來說，其效率應該好于其它2種連接，但是這種連接只能用在CBO
優化器中，而且需要設置合適的hash_area_size參數，才能取得較好的性能。?
b) 在2個較大的row source之間連接時會取得相對較好的效率，在一個row source較小時則能取得更好的效率。?
c) 只能用于等值連接中
笛卡兒乘積(Cartesian Product)
當兩個row source做連接，但是它們之間沒有關聯條件時，就會在兩個row source中做笛卡兒乘積，這通常由編寫代碼疏漏造成(即程序員
忘了寫關聯條件)。笛卡爾乘積是一個表的每一行依次與另一個表中的所有行匹配。在特殊情況下我們可以使用笛卡兒乘積，如在星形連接中，
除此之外，我們要盡量使用笛卡兒乘積，否則，自己想結果是什么吧！
注意在下面的語句中，在2個表之間沒有連接。?
SQL> explain plan for?
select emp.deptno,dept,deptno?
from emp,dept
Query Plan?
------------------------------?
SLECT STATEMENT [CHOOSE] Cost=5?
MERGE JOIN CARTESIAN?
TABLE ACCESS FULL DEPT?
SORT JOIN?
TABLE ACCESS FULL EMP
CARTESIAN關鍵字指出了在2個表之間做笛卡爾乘積。假如表emp有n行，dept表有m行，笛卡爾乘積的結果就是得到n * m行結果。
使用全套的hints：?
當使用hints時，在某些情況下，為了確保讓優化器產生最優的執行計劃，我們可能指定全套的hints。例如，如果有一個復雜的查詢，
包含多個表連接，如果你只為某個表指定了INDEX提示(指示存取路徑在該表上使用索引)，優化器需要來決定其它應該使用的訪問路徑和相
應的連接方法。因此，即使你給出了一個INDEX提示，優化器可能覺得沒有必要使用該提示。這是由于我們讓優化器選擇了其它連接方法和
存取路徑，而基于這些連接方法和存取路徑，優化器認為用戶給出的INDEX提示無用。為了防止這種情況，我們要使用全套的hints，如不
但指定要使用的索引，而且也指定連接的方法與連接的順序等。?
--MartriWang@gmail.com?15/06/2007--
使用全套hints的例子，ORDERED提示指出了連接的順序，而且為不同的表指定了連接方法：
SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b)?
USE_NL (glcc glf) USE_MERGE (gp gsb) */?
b.application_id, b.set_of_books_id ,?
b.personnel_id, p.vendor_id Personnel,?
p.segment1 PersonnelNumber, p.vendor_name Name?
FROM jl_br_journals j, jl_br_balances b,?
gl_code_combinations glcc, fnd_flex_values_vl glf,?
gl_periods gp, gl_sets_of_books gsb, po_vendors p?
WHERE ...
指示優化器的方法與目標的hints：
ALL_ROWS -- 基于代價的優化器，以吞吐量為目標?
FIRST_ROWS(n) -- 基于代價的優化器，以響應時間為目標?
CHOOSE -- 根據是否有統計信息，選擇不同的優化器?
RULE -- 使用基于規則的優化器
例子：?
SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id?
FROM employees?
WHERE department_id = 20;
SELECT /*+ CHOOSE */ employee_id, last_name, salary, job_id?
FROM employees?
WHERE employee_id = 7566;
SELECT /*+ RULE */ employee_id, last_name, salary, job_id?
FROM employees?
WHERE employee_id = 7566;
指示存儲路徑的hints：
FULL /*+ FULL ( table ) */?
指定該表使用全表掃描?
ROWID /*+ ROWID ( table ) */?
指定對該表使用rowid存取方法，該提示用的較少?
INDEX /*+ INDEX ( table [index]) */?
使用該表上指定的索引對表進行索引掃描?
INDEX_FFS /*+ INDEX_FFS ( table [index]) */?
使用快速全表掃描?
NO_INDEX /*+ NO_INDEX ( table [index]) */?
不使用該表上指定的索引進行存取，仍然可以使用其它的索引進行索引掃描
SELECT /*+ FULL(e) */ employee_id, last_name?
FROM employees e?
WHERE last_name LIKE :b1;
SELECT /*+ROWID(employees)*/ *?
FROM employees?
WHERE rowid > 'AAAAtkAABAAAFNTAAA' AND employee_id = 155;
SELECT /*+ INDEX(A sex_index) use sex_index because there are few?
male patients */ A.name, A.height, A.weight?
FROM patients A?
WHERE A.sex = 'm';
SELECT /*+NO_INDEX(employees emp_empid)*/ employee_id?
FROM employees?
WHERE employee_id > 200;
指示連接順序的hints:?
ORDERED /*+ ORDERED */?
按from 字句中表的順序從左到右的連接?
STAR /*+ STAR */?
指示優化器使用星型查詢
SELECT /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity?
FROM customers c, order_items l, orders o?
WHERE c.cust_last_name = :b1?
AND o.customer_id = c.customer_id?
AND o.order_id = l.order_id;
/*+ ORDERED USE_NL(FACTS) INDEX(facts fact_concat) */
指示連接類型的hints：?
USE_NL /*+ USE_NL ( table [,table, ...] ) */?
使用嵌套連接?
USE_MERGE /*+ USE_MERGE ( table [,table, ...]) */?
使用排序- -合并連接?
USE_HASH /*+ USE_HASH ( table [,table, ...]) */?
使用HASH連接?
注意：如果表有alias(別名)，則上面的table指的是表的別名，而不是真實的表名
--MartriWang@gmail.com?25/06/2007--
oracle最重要的9個動態性能視圖！
v$session + v$session_wait?
v$process
v$sql
v$sqltext
v$bh (更寧愿是x$bh)
v$lock
v$latch_children
v$sysstat
v$system_event
按組分的幾組重要的性能視圖
1。System 的 over view?
v$sysstat , v$system_event , v$parameter
2。某個session 的當前情況
v$process , v$session , v$session_wait ,v$session_event , v$sesstat
3。SQL 的情況
v$sql , v$sqlarea , v$SQL_PLAN , V$SQL_PLAN_STATISTICS, v$sqltext_with_newlines
3. Latch / lock /ENQUEUE
v$latch , v$latch_children , v$latch_holder , v$lock ,V$ENQUEUE_STAT ,V$ENQUEUE_LOCK
4. IO 方面的
v$segstat , v$filestat , v$tempstat ,v$datafile , v$tempfile
5.shared pool / Library cache
v$Librarycache , v$rowcache , x$ksmsp
6.幾個advice也不錯
v$db_cache_advice , v$PGA_TARGET_ADVICE, v$SHARED_POOL_ADVICE
轉載于:https://blog.51cto.com/2998729/772044
總結
以上是生活随笔為你收集整理的oracle 调优3的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： GDI文字旋转90度
- 下一篇： TCP的坚持定时器