執行計劃可以用來分析SQL的性能
一、檢視執行計劃的方法
1. 設定autotrace
set autotrace off: 此為預設值,即關閉autotrace
set autotrace on explain: 隻顯示執行計劃
set autotrace on statistics: 隻顯示執行的統計資訊
set autotrace on: 既顯示執行計劃,又顯示執行的統計資訊
set autotrace traceonly: 與on相似,但不顯示語句的執行結果
示例:
set autotrace on;
select 1 from dual;
注意:如果在執行set autotrace時出現以下錯誤提示:
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
可嘗試如下方式解決:
conn / as sysdba;
執行@$ORACLE_HOME/RDBMS/ADMIN/utlxplan.sql,或執行一下$ORACLE_HOM\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql檔案的内容.
執行@$ORACLE_HOME/sqlplus/admin/plustrce.sql,或執行一下$ORACLE_HOM\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql檔案的内容.
grant plustrace to public;
2. 使用SQL
執行:explain plan for <sql語句>
檢視:SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
或 select * from table(dbms_xplan.display);
示例:
explain plan for select 1 from dual;
select * from table(dbms_xplan.display);
3. 使用PL/SQL Developer、Toad等工具
在PL/SQL Developer中,選中SQL語句,然後點選菜單“工具”-“解釋計劃”或按快捷鍵F5即可。
二、執行計劃結果資訊說明
上面執行計劃示例在運作之後可能會輸出如下資訊,接下來對這些資訊進行進一步說明
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1388734953
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0) | 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0) | 00:00:01 |
--------------------------------------------------------------------------------
1. 執行計劃中字段的說明
Id: 一個序号,但不是執行的先後順序。執行的先後根據縮進來判斷。
Operation: 目前操作的内容。
Name: 操作的對象名稱。
Rows: 目前操作的基數,Oracle估計目前操作的傳回結果集。
Cost(%CPU): Oracle 計算出來的一個數值(代價),用于說明SQL執行的代價。
Time: Oracle估計目前操作的時間
2. 執行計劃中内容的說明
table access full: 全表掃描,對所有表中記錄進行掃描。使用多塊讀操作,一次I/O能讀取多塊資料塊。表字段不涉及索引時往往采用這種方式。
較大的表不建議使用全表掃描,除非結果資料超出全表資料總量的10%。
table access by index rowid: 通過ROWID的表存取,一次I/O隻能讀取一個資料塊。通過rowid讀取表字段,rowid可能是索引鍵值上的rowid。
4種類型的索引掃描(index scan)
index unique scan: 索引唯一掃描,如果表字段有UNIQUE 或PRIMARY KEY 限制,Oracle實作索引唯一掃描,這種掃描方式條件比較極端,出現比較少。
index range scan: 索引範圍掃描,最常見的索引掃描方式。在非唯一索引上都使用索引範圍掃描。
1 ) 在唯一索引列上使用了以下圈定範圍的操作符(> < <> >= <= between等)
2 ) 在組合索引上,隻使用部分列進行查詢,導緻查詢出多行
3 ) 對非唯一索引列上進行的任何查詢
index full scan: 索引全掃描,這種情況下,是查詢的資料都屬于索引字段,一般都含有排序操作。
index fast full scan: 索引快速掃描,如果查詢的資料都屬于索引字段,并且沒有進行排序操作,那麼是屬于這種情況。條件比較極端,出現比較少。
表之間的連接配接方式
nested loops: 嵌套循環,該連接配接過程就是一個2層嵌套循環,是以外層循環的次數越少越好。
如果driving row source(外部表)比較小,并且在inner row source(内部表)上有唯一索引,
或有高選擇性非唯一索引時,使用這種方法可以得到較好的效率。
hash join: 哈希連接配接,在2個較大的row source之間連接配接時會取得相對較好的效率,在一個row source較小時則能取得更好的效率。
sort merge join: 排序 - 合并連接配接,該種排序限制較大,出現比較少
内部連接配接過程:
1) 首先生成表1需要的資料,然後對這些資料按照連接配接操作關聯列進行排序;
2) 随後生成表2需要的資料,然後對這些資料按照與表1對應的連接配接操作關聯列進行排序;
3) 最後兩邊已排序的行被放在一起執行合并操作,即将2個表按照連接配接條件連接配接起來。
三、表連接配接方法
1. 排序 - - 合并連接配接(Sort Merge Join, SMJ):
a) 對于非等值連接配接,這種連接配接方式的效率是比較高的。
b) 如果在關聯的列上都有索引,效果更好。
c) 對于将2個較大的row source做連接配接,該連接配接方法比NL連接配接要好一些。
d) 但是如果sort merge傳回的row source過大,則又會導緻使用過多的rowid在表中查詢資料時,資料庫性能下降,因為過多的I/O.
2. 嵌套循環(Nested Loops, NL):
a) 如果driving row source(外部表)比較小,并且在inner row source(内部表)上有唯一索引,或有高選擇性非唯一索引時,使用這種方法可以得到較好的效率。
b) NESTED LOOPS有其它連接配接方法沒有的的一個優點是:可以先傳回已經連接配接的行,而不必等待所有的連接配接操作處理完才傳回資料,這可以實作快速的響應時間。
3. 哈希連接配接(Hash Join, HJ):
a) 這種方法是在oracle7後來引入的,使用了比較先進的連接配接理論,一般來說,其效率應該好于其它2種連接配接,但是這種連接配接隻能用在CBO優化器中,而且需要設定合适的hash_area_size參數,才能取得較好的性能。
b) 在2個較大的row source之間連接配接時會取得相對較好的效率,在一個row source較小時則能取得更好的效率。
c) 隻能用于等值連接配接中
四、執行計劃統計資訊
1. 統計資訊含義
recursive calls: 遞歸調用次數;
db block gets: 當期操作時從記憶體讀取的目前最新塊資料,并不是在一緻性讀的情況的塊數,即通過update/delete/select for update讀的塊數;
consistent gets: 當期操作時在一緻性讀狀态下讀取的塊數,即通過不帶for update的select 讀的塊數;
physical reads: 實體讀,Oracle從磁盤讀的資料塊數量, 其産生的主要原因是:在資料庫高速緩存中不存在這些塊;全表掃描;磁盤排序。其中邏輯讀指的是Oracle從記憶體讀到的資料塊數量。一般來說是'consistent gets' + 'db block gets'。當在記憶體中找不到所需的資料塊的話就需要從磁盤中擷取,于是就産生了'phsical reads'。
redo size: 執行SQL的過程中産生的重做日志;
519 bytes sent via SQL*Net to client: 通過網絡發送給用戶端的資料
524 bytes received via SQL*Net from client: 通過網絡從用戶端接收到的資料
SQL*Net roundtrips to/from client:通過網絡用戶端發送或接收的數量
sorts (memory): 在記憶體中發生的排序
sorts (disk): 在硬碟中發生的排序
rows processed:處理的行數
2. 統計資訊示例
參考資料:
http://www.cnblogs.com/jianggc/articles/2029854.html
http://www.cnblogs.com/fqw1987815/archive/2010/08/18/1802657.html
http://blog.csdn.net/zongrongna/article/details/51580415
http://blog.chinaunix.net/uid-21187846-id-3022916.html