@
1、文章寫作前言簡介
之前曾經拜讀過《收穫，不止sql調優》一書，此書是國內DBA寫的一本很不錯的調優型別的書，是一些很不錯的調優經驗的分享。雖然讀了一遍，做了下讀書筆記，覺得很有所收穫，但是到實際的實踐中覺得還是很缺實踐。剛好最近又有一次sql調優培訓活動，去參加後，重新複習Oracle執行計劃，所以整理資料，做成筆記分享出來
2、什麼是執行計劃？
執行計劃是一條查詢語句在Oracle中的執行過程或訪問路徑的描述。
執行計劃描述了SQL引擎為執行SQL語句進行的操作；分析SQL語句相關的效能問題或僅僅質疑查詢優化器的決定時，必須知道執行計劃；所以執行計劃常用於sql調優。
3、怎麼檢視執行計劃？
檢視Oracle執行計劃有很多種，詳情參考我之前的讀書筆記，本部落格只介紹很常用的方法
oracle要使用執行計劃一般在sqlplus執行sql：
explain plan for select 1 from t
不過如果是使用PLSQL的話，那就可以使用PLSQL提供的查詢執行計劃了,也就是按F5
開啟PLSQL工具 -> 首選項 -> 視窗型別 -> 計劃視窗 ，在這裡加入執行計劃需要的引數
找個SQL，用PLSQL執行一下，這是plsql的簡單使用
解釋一下這些引數的意思:
- 基數(Rows)：Oracle估計的當前步驟的返回結果集行數
- 位元組(Bytes)：執行SQL對應步驟返回的位元組數
- 耗費(COST)、CPU耗費：Oracle估計的該步驟的執行耗費和CPU耗費
- 時間(Time)：Oracle估計的執行sql對於步驟需要的時間
4、檢視真實執行計劃
之前檢視執行計劃也喜歡按F5，不過最近去培訓，聽一名dba說，這種方法有時候不能獲取真實的執行計劃，收集的資訊也不全面，然後怎麼檢視sql執行過程的真實資訊？從培訓中學到的經驗做成筆記
sqlplus視窗執行：
- step1：set statistics_level
alter session set statistics_level=ALL;
- step2：執行業務sql
select /*+ monitor */ * from ... where ....;
- step3：為了樣式，設定linesize
set linesize 200 pagesize 300;
- step4：查詢真實執行計劃
select * from table(dbms_xplan.display_cursor(null, null, 'iostats last'));
sqlplus一般要資料庫管理員才可以使用，如果你不是dba，只能使用plsql developer的話，只能用下面的方法，方法是從培訓中學到的
使用儲存過程，SQL：
declare
b1 date;
begin
execute immediate 'alter session set statistics_level=ALL';
b1 := sysdate - 1;
for test in (
/*業務SQL(sql後面不需要加";")*/
select * from t) loop
null;
end loop;
for x in (select p.plan_table_output
from table(dbms_xplan.display_cursor(null,
null,
'advanced -bytes -PROJECTION allstats last')) p) loop
dbms_output.put_line(x.plan_table_output);
end loop;
rollback;
end;
/
兩種視窗：
- 1、SQL視窗的，執行SQL後只能去output檢視；
- 2、command window的，需要先設定
set serveroutput on size unlimited，然後再執行儲存過程
output或者命令視窗檢視的真實執行計劃和統計資訊：
SQL_ID abk3ghv9u1tvb, child number 0
-------------------------------------
SELECT /*+ monitor */ * FROM APPR_HANDLE_INFO
Plan hash value: 885170757
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 210 (100)| | 72059 |00:00:00.06 | 2460 |
| 1 | TABLE ACCESS FULL| APPR_HANDLE_INFO | 1 | 32752 | 210 (1)| 00:00:03 | 72059 |00:00:00.06 | 2460 |
------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / APPR_HANDLE_INFO@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "APPR_HANDLE_INFO"@"SEL$1")
END_OUTLINE_DATA
*/
關鍵資訊解釋：
- Starts：該SQL執行的次數
- E-Rows：為執行計劃預計的行數
- A-Rows：實際返回的行數，E-Rows和A-Rows作比較，就可以看出具體那一步執行計劃出問題了
- A-Time：每一步實際執行的時間，可以看出耗時的SQL
- Buffers：每一步實際執行的邏輯讀或一致性讀
5、看懂Oracle執行計劃
上面已經介紹瞭如何檢視執行計劃，現在簡單介紹一下一些基本方法和相關理論知識
5.1 檢視explain
找一條比較複雜的SQL，執行：
F5方式檢視：
set statistics_level=ALL方式：
SQL_ID 4qfq3t2ukm0y1, child number 0
-------------------------------------
SELECT /*+ monitor*/ A.USER_CODE, A.FULL_NAME, A.USER_PWD, C.UNIT_CODE,
C.UNIT_NAME FROM BASE_USER A LEFT JOIN (SELECT UR.USER_CODE,
UR.UNIT_CODE FROM APPR_USER_ROLE UR WHERE UR.USER_ROLE < 10) B ON
A.USER_CODE = B.USER_CODE LEFT JOIN LZCITY_APPROVE_UNIT_INFO C ON
B.UNIT_CODE = C.UNIT_CODE WHERE C.UNIT_CODE ='15803'
Plan hash value: 3288287052
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| | 16 |00:00:00.01 | 38 |
| 1 | NESTED LOOPS | | 1 | 1 | 3 (0)| 00:00:01 | 16 |00:00:00.01 | 38 |
| 2 | NESTED LOOPS | | 1 | 1 | 3 (0)| 00:00:01 | 16 |00:00:00.01 | 22 |
| 3 | NESTED LOOPS | | 1 | 1 | 2 (0)| 00:00:01 | 16 |00:00:00.01 | 5 |
| 4 | TABLE ACCESS BY INDEX ROWID| LZCITY_APPROVE_UNIT_INFO | 1 | 1 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 5 | INDEX UNIQUE SCAN | PK_LZCITY_APPROVE_UNIT_INFO | 1 | 1 | 0 (0)| | 1 |00:00:00.01 | 2 |
|* 6 | INDEX RANGE SCAN | PK_APPR_USER_ROLE | 1 | 1 | 1 (0)| 00:00:01 | 16 |00:00:00.01 | 2 |
|* 7 | INDEX UNIQUE SCAN | PK_BASE_USER | 16 | 1 | 0 (0)| | 16 |00:00:00.01 | 17 |
| 8 | TABLE ACCESS BY INDEX ROWID | BASE_USER | 16 | 1 | 1 (0)| 00:00:01 | 16 |00:00:00.01 | 16 |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$E3445A69
4 - SEL$E3445A69 / C@SEL$4
5 - SEL$E3445A69 / C@SEL$4
6 - SEL$E3445A69 / UR@SEL$2
7 - SEL$E3445A69 / A@SEL$3
8 - SEL$E3445A69 / A@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$E3445A69")
MERGE(@"SEL$2")
OUTLINE(@"SEL$A2E96217")
OUTER_JOIN_TO_INNER(@"SEL$E9F4A6F9" "B"@"SEL$1")
OUTER_JOIN_TO_INNER(@"SEL$E9F4A6F9" "C"@"SEL$4")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$E9F4A6F9")
MERGE(@"SEL$80808B20")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$80808B20")
MERGE(@"SEL$4")
MERGE(@"SEL$F1D6E378")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$F1D6E378")
MERGE(@"SEL$1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$E3445A69" "C"@"SEL$4" ("LZCITY_APPROVE_UNIT_INFO"."UNIT_CODE"))
INDEX(@"SEL$E3445A69" "UR"@"SEL$2" ("APPR_USER_ROLE"."UNIT_CODE" "APPR_USER_ROLE"."USER_CODE" "APPR_USER_ROLE"."AREA_SEQ"
"APPR_USER_ROLE"."USER_ROLE"))
INDEX(@"SEL$E3445A69" "A"@"SEL$3" ("BASE_USER"."USER_CODE"))
LEADING(@"SEL$E3445A69" "C"@"SEL$4" "UR"@"SEL$2" "A"@"SEL$3")
USE_NL(@"SEL$E3445A69" "UR"@"SEL$2")
USE_NL(@"SEL$E3445A69" "A"@"SEL$3")
NLJ_BATCHING(@"SEL$E3445A69" "A"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("C"."UNIT_CODE"='15803')
6 - access("UR"."UNIT_CODE"='15803' AND "UR"."USER_ROLE"<10)
filter("UR"."USER_ROLE"<10)
7 - access("A"."USER_CODE"="UR"."USER_CODE")
5.2 explain執行順序
所以不管是用F5方式還是set statistics_level=ALL方式，都有Operation引數，Operation表示sql執行過程，檢視怎麼執行的，有兩個規則：
- 根據Operation縮排判斷，縮排最多的最先執行；
- Operation縮排相同時，最上面的是最先執行的；
如圖執行計劃，根據規則，可以得出執行順序：INDEX UNIQUE SCAN->TABLE ACCESS BY INDEX ROWID->INDEX RANGE SCAN ->NESTED LOOPS ->INDEX UNIQUE SCAN->NESTED LOOPS ->TABLE ACCESS BY INDEX ROWID->NESTED LOOPS-> SELECT STATEMENT
5.3 訪問資料的方法
Oracle訪問表中資料的方法有兩種，一種是直接表中訪問資料，另外一種是先訪問索引，如果索引資料不符合目標SQL，就回表，符合就不回表，直接訪問索引就可以。
Oracle直接訪問表中資料的方法又分為兩種：
5.3.1 全表掃描（TABLE ACCESS FULL）
- 一種是全表掃描；（TABLE ACCESS FULL）
全表掃描是Oracle直接訪問資料的一種方法，全表掃描時從第一個區(EXTENT)的第一個塊(BLOCK)開始掃描，一直掃描的到表的高水位線(High Water Mark)，這個範圍內的資料塊都會掃描到
全表掃描是採用多資料塊一起掃的，並不是一個個資料庫掃的，然後我們經常說全表掃描慢是針對資料量很多的情況，資料量少的話，全表掃描並不慢的，不過隨著資料量越多，高水位線也就越高，也就是說需要掃描的資料庫越多，自然掃描所需要的IO越多，時間也越多
注意：資料量越多，全表掃描所需要的時間就越多，然後直接刪了表資料呢？查詢速度會變快？其實並不會的，因為即使我們刪了資料，高位水線並不會改變，也就是同樣需要掃描那麼多資料塊
5.3.2 ROWID掃描（TABLE ACCESS BY ROWID）
- 另一種是ROWID掃描（TABLE ACCESS BY ROWID）
ROWID也就是表資料行所在的物理儲存地址，所謂的ROWID掃描是通過ROWID所在的資料行記錄去定位。ROWID是一個偽列，資料庫裡並沒有這個列，它是資料庫查詢過程中獲取的一個實體地址，用於表示資料對應的行數。
用sql查詢：
select t.* , rowid from 表格
隨意獲取一個ROWID序列：AAAWSJAAFAAAWwUAAA，前6位表示物件編號(Data Object number)，其後3位檔案編號(Relative file number)，接著其後6位表示塊編號(Block number)， 再其後3位表示行編號(Row number)
ROWID編碼方法是：A ~ Z表示0到25；a ~ z表示26到51；0~9表示52到61；+表示62；/表示63；剛好64個字元。
這裡隨意找張表查一下檔案編號、區編號、行編號，查詢後會返回rowid的一系列實體地址和檔案編號(rowid_relative_fno(rowid))、塊編號(rowid_block_number(rowid))、行編號(rowid_row_number(rowid))
select t.seq,
rowid,
dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid),
dbms_rowid.rowid_row_number(rowid)
from t_info t
SQL查詢一下表格名稱為TABLE的物件編碼
select owner,object_id,data_object_id,status from dba_objects where object_name='TABLE';
相對檔案id和絕對檔案編碼
相對檔案id是指相對於表空間，在表空間唯一;絕對檔案編碼是指相當於全域性資料庫而言的，全域性唯一；下面SQL查詢一下相對檔案id和絕對檔案編碼
select file_name,file_id,relative_fno from dba_data_files;
訪問索引（TABLE ACCESS BY INDEX SCAN）的情況就比較多了，可以分為：
5.3.3 索引唯一掃描（INDEX UNIQUE SCAN）
- 索引唯一掃描（INDEX UNIQUE SCAN）
索引唯一性掃描(INDEX UNIQUE SCAN)是針對唯一性索引(UNIQUE INDEX)來說的，也就是建立唯一性索引才能索引唯一性掃描，唯一性掃描，其結果集只會返回一條記錄。
- 索引範圍掃描（INDEX RANGE SCAN）
索引範圍掃描(INDEX RANGE SCAN)適用於所有型別的B樹索引，一般不包括唯一性索引，因為唯一性索引走索引唯一性掃描。 當掃描的物件是非唯一性索引的情況，where謂詞條件為Between、=、<、>等等的情況就是索引範圍掃描，注意，可以是等值查詢，也可以是範圍查詢。如果where條件裡有一個索引鍵值列沒限定為非空的，那就可以走索引範圍掃描，如果改索引列是非空的，那就走索引全掃描
5.3.4 索引全掃描（INDEX FULL SCAN）
- 索引全掃描（INDEX FULL SCAN）
索引全掃描(INDEX FULL SCAN)適用於所有型別的B樹索引(包括唯一性索引和非唯一性索引)。
索引全掃描過程簡述：索引全掃描是指掃描目標索引所有葉子塊的索引行，但不意思著需要掃描所有的分支塊，索引全掃描時只需要訪問必要的分支塊，然後定位到位於改索引最左邊的葉子塊的第一行索引行，就可以利用改索引葉子塊之間的雙向指標連結串列，從左往右依次順序掃描所有的葉子塊的索引行
5.3.5 索引快速全掃描（INDEX FAST FULL SCAN）
- 索引快速全掃描（INDEX FAST FULL SCAN）
索引快速全掃描和索引全掃描很類似，也適用於所有型別的B樹索引(包括唯一性索引和非唯一性索引)。和索引全掃描類似，也是掃描所有葉子塊的索引行，這些都是索引快速全掃描和索引全掃描的相同點
索引快速全掃描和索引全掃描區別：
- 索引快速全掃描只適應於CBO(基於成本的優化器)
- 索引快速全掃描可以使用多塊讀，也可以並行執行
- 索引全掃描會按照葉子塊排序返回，而索引快速全掃描則是按照索引段記憶體儲塊順序返回
- 索引快速全掃描的執行結果不一定是有序的，而索引全掃描的執行結果是有序的，因為索引快速全掃描是根據索引行在磁碟的物理儲存順序來掃描的，不是根據索引行的邏輯順序來掃描的
5.3.6 索引跳躍式掃描（INDEX SKIP SCAN）
- 索引跳躍式掃描（INDEX SKIP SCAN）
索引跳躍式掃描(INDEX SKIP SCAN)適用於所有型別的複合B樹索引(包括唯一性索引和非唯一性索引)，索引跳躍式掃描可以使那些在where條件中沒有目標索引的前導列指定查詢條件但是有索引的非前導列指定查詢條件的目標SQL依然可以使用跳躍索引
如圖執行計劃就有INDEX RANGE SCAN、 INDEX UNIQUE SCAN 等等
5.4 表連線方法
如圖，執行計劃中有如下NESTED LOOPS等等這些，是什麼？這種其實就是Oracle中表連線的方法
兩個表之間的表連線方法有排序合併連線、巢狀迴圈連線、雜湊連線、笛卡爾連線
-
排序合併連線（merge sort join）
merge sort join是先將關聯表的關聯列各自做排序，然後從各自的排序表中抽取資料，到另一個排序表中做匹配 -
巢狀迴圈連線（Nested loop join）
Nested loops 工作方式是迴圈從一張表中讀取資料(驅動表outer table)，然後訪問另一張表（被查詢表 inner table,通常有索引）。驅動表中的每一行與inner表中的相應記錄JOIN。類似一個巢狀的迴圈。對於被連線的資料子集較小的情況，nested loop連線是個較好的選擇 -
雜湊連線（Hash join）
雜湊連線是CBO 做大資料集連線時的常用方式，優化器使用兩個表中較小的表（或資料來源）利用連線鍵在記憶體中建立雜湊表，然後掃描較大的表並探測雜湊表，找出與雜湊表匹配的行。 -
笛卡爾連線（Cross join）
如果兩個表做表連線而沒有連線條件，而會產生笛卡爾積，在實際工作中應該儘可能避免笛卡爾積
對於這些連線的詳細介紹可以檢視《收穫，不止sql調優》一書，或者檢視我做的讀書筆記
5.5 explain引數資訊
前面的學習，我們已經知道了執行計劃執行的順序、sql是做索引，還是全表掃描，或者是rowid掃描，但是如圖執行計劃還有很多引數，如圖，比如Starts，E-Rows，Cost (%CPU)等等，這些參數列示什麼含義？
執行計劃關鍵資訊介紹：
- Starts：該SQL執行的次數
- E-Rows：為執行計劃預計的行數
- Cost (%CPU)：CPU cost在整個cost中佔的百分比
- A-Rows：實際返回的行數，E-Rows和A-Rows作比較，就可以看出具體那一步執行計劃出問題了
- A-Time：每一步實際執行的時間，可以看出耗時的SQL
- Buffers：每一步實際執行的邏輯讀或一致性讀