http://blog.csdn.net/tianlesoftware/article/details/5827245
如果要分析某條SQL的性能問題，通常我們要先看SQL的執行計劃，看看SQL的每一步執行是否存在問題。 如果一條SQL平時執行的好好的，卻有一天突然性能很差，如果排除了系統資源和阻塞的原因，那麼基本可以斷定是執行計劃出了問題。
看懂執行計劃也就成了SQL優化的先決條件。 這裡的SQL優化指的是SQL性能問題的定位，定位後就可以解決問題。
一．檢視執行計劃的三種方法
1.1設定autotrace
| 序号 | 指令 | 解釋 |
| 1 | SET AUTOTRACE OFF | 此為預設值，即關閉Autotrace |
| 2 | SET AUTOTRACE ON EXPLAIN | 隻顯示執行計劃 |
| 3 | SET AUTOTRACE ON STATISTICS | 隻顯示執行的統計資訊 |
| 4 | SET AUTOTRACE ON | 包含2,3兩項内容 |
| 5 | SET AUTOTRACE TRACEONLY | 與ON相似，但不顯示語句的執行結果 |
SQL> set autotrace on
SQL> select * from dave;
ID NAME
---------- ----------
8 安慶
1 dave
2 bl
1 bl
2 dave
3 dba
4 sf-express
5 dmm
已選擇8行。
執行計劃
----------------------------------------------------------
Plan hash value: 3458767806
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 64 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DAVE | 8 | 64 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
609 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
1.2使用SQL
SQL> @?/rdbms/admin/utlxplan.sql
文法：
EXPLAIN PLAN [ SET STATEMENT_ID [=] < string literal > ] [ INTO < table_name > ] FOR < sql_statement >
其中：
STATEMENT_ID：是一個唯一的字元串，把目前執行計劃與存儲在同一PLAN中的其它執行計劃差別開來。
TABLE_NAME：是plan表名，它結構如前所示，你可以任意設定這個名稱。
SQL_STATEMENT：是真正的SQL語句。
select id,parent_id,operation,options,object_name,object_type
from plan_table
where statement_id='test'
order by id;
SQL> EXPLAIN PLAN FOR sql語句;
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
示例：
SQL> EXPLAIN PLAN FOR SELECT * FROM DAVE;
已解釋。
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
或者：
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3458767806
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 64 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DAVE | 8 | 64 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
已選擇8行。
執行計劃
----------------------------------------------------------
Plan hash value: 2137789089
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
25 recursive calls
12 db block gets
168 consistent gets
0 physical reads
0 redo size
974 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
8 rows processed
1.3使用Toad,PL/SQL Developer工具
二．Cardinality（基數）/ rows
Cardinality值表示CBO預期從一個行源（row source）傳回的記錄數，這個行源可能是一個表，一個索引，也可能是一個子查詢。在Oracle 9i中的執行計劃中，Cardinality縮寫成Card。 在10g中，Card值被rows替換。
這是9i的一個執行計劃，我們可以看到關鍵字Card：
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2Card=1 Bytes=402)
1 0 TABLE ACCESS (FULL) OF 'TBILLLOG8' (Cost=2Card=1 Bytes=402)
Oracle 10g的執行計劃，關鍵字換成了rows：
執行計劃
----------------------------------------------------------
Plan hash value: 2137789089
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Cardinality的值對于CBO做出正确的執行計劃來說至關重要。 如果CBO獲得的Cardinality值不夠準确（通常是沒有做分析或者分析資料過舊造成），在執行計劃成本計算上就會出現偏差，進而導緻CBO錯誤的制定出執行計劃。
在多表關聯查詢或者SQL中有子查詢時，每個關聯表或子查詢的Cardinality的值對主查詢的影響都非常大，甚至可以說，CBO就是依賴于各個關聯表或者子查詢Cardinality值計算出最後的執行計劃。
對于多表查詢，CBO使用每個關聯表傳回的行數（Cardinality）決定用什麼樣的通路方式來做表關聯（如Nested loops Join 或 hash Join）。
多表連接配接的三種方式詳解 HASH JOIN MERGE JOIN NESTED LOOP
http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5826546.aspx
對于子查詢，它的Cardinality将決定子查詢是使用索引還是使用全表掃描的方式通路資料。
三．SQL的執行計劃
生成SQL的執行計劃是Oracle在對SQL做硬解析時的一個非常重要的步驟，它制定出一個方案告訴Oracle在執行這條SQL時以什麼樣的方式通路資料：索引還是全表掃描，是Hash Join還是Nested loops Join等。 比如說某條SQL通過使用索引的方式通路資料是最節省資源的，結果CBO作出的執行計劃是全表掃描，那麼這條SQL的性能必然是比較差的。
Oracle SQL的硬解析和軟解析
http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx
示例：
SQL> SET AUTOTRACE TRACEONLY; -- 隻顯示執行計劃，不顯示結果集
SQL> select * from scott.emp a,scott.emp b where a.empno=b.mgr;
已選擇13行。
執行計劃
----------------------------------------------------------
Plan hash value: 992080948
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 988 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 13 | 988 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 532 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 13 | 494 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 13 | 494 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."EMPNO"="B"."MGR")
filter("A"."EMPNO"="B"."MGR")
5 - filter("B"."MGR" IS NOT NULL)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
2091 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13 rows processed
圖檔是Toad工具檢視的執行計劃。 在Toad 裡面，很清楚的顯示了執行的順序。 但是如果在SQLPLUS裡面就不是那麼直接。 但我們也可以判斷：一般按縮進長度來判斷，縮進最大的最先執行，如果有2行縮進一樣，那麼就先執行上面的。
3.1執行計劃中字段解釋：
ID: 一個序号，但不是執行的先後順序。執行的先後根據縮進來判斷。
Operation： 目前操作的内容。
Rows： 目前操作的Cardinality，Oracle估計目前操作的傳回結果集。
Cost（CPU）：Oracle 計算出來的一個數值（代價），用于說明SQL執行的代價。
Time：Oracle 估計目前操作的時間。
3.2謂詞說明：
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."EMPNO"="B"."MGR")
filter("A"."EMPNO"="B"."MGR")
5 - filter("B"."MGR" IS NOT NULL)
Access: 表示這個謂詞條件的值将會影響資料的通路路勁（表還是索引）。
Filter：表示謂詞條件的值不會影響資料的通路路勁，隻起過濾的作用。
在謂詞中主要注意access，要考慮謂詞的條件，使用的通路路徑是否正确。
3.3統計資訊說明：
db block gets ： 從buffer cache中讀取的block的數量
consistent gets： 從buffer cache中讀取的undo資料的block的數量
physical reads： 從磁盤讀取的block的數量
redo size： DML生成的redo的大小
sorts (memory) ：在記憶體執行的排序量
sorts (disk) ：在磁盤上執行的排序量
Physical Reads通常是我們最關心的，如果這個值很高，說明要從磁盤請求大量的資料到Buffer Cache裡，通常意味着系統裡存在大量全表掃描的SQL語句，這會影響到資料庫的性能，是以盡量避免語句做全表掃描，對于全表掃描的SQL語句，建議增 加相關的索引，優化SQL語句來解決。
關于physical reads，db block gets和consistent gets這三個參數之間有一個換算公式：
資料緩沖區的使用命中率=1 - ( physical reads / (db block gets + consistent gets) )。
用以下語句可以檢視資料緩沖區的命中率：
SQL>SELECT name, value FROM v$sysstat
WHERE name IN ('db block gets', 'consistent gets','physical reads');
查詢出來的結果Buffer Cache的命中率應該在90％以上，否則需要增加資料緩沖區的大小。
Recursive Calls：Number of recursive calls generated at both the user and system level.
Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call. In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls。
DB Block Gets：Number of times a CURRENT block was requested.
Current mode blocks are retrieved as they exist right now, not in a consistent read fashion. Normally, blocks retrieved for a query are retrieved as they existed when the query began. Current mode blocks are retrieved as they exist right now, not from a previous point in time. During a SELECT, you might see current mode retrievals due to reading the data dictionary to find the extent information for a table to do a full scan (because you need the "right now" information, not the consistent read). During a modification, you will access the blocks in current mode in order to write to them. (DB Block Gets:請求的資料塊在buffer能滿足的個數)
目前模式塊意思就是在操作中正好提取的塊數目，而不是在一緻性讀的情況下而産生的塊數。正常的情況下，一個查詢提取的塊是在查詢開始的那個時間點上存在的資料塊，目前塊是在這個時刻存在的資料塊，而不是在這個時間點之前或者之後的資料塊數目。
Consistent Gets： Number of times a consistent read was requested for a block.
This is how many blocks you processed in "consistent read" mode. This will include counts of blocks read from the rollback segment in order to roll back a block. This is the mode you read blocks in with a SELECT, for example. Also, when you do a searched UPDATE/DELETE, you read the blocks in consistent read mode and then get the block in current mode to actually do the modification. (Consistent Gets:資料請求總數在復原段Buffer中的資料一緻性讀所需要的資料塊)
這裡的概念是在處理你這個操作的時候需要在一緻性讀狀态上處理多少個塊，這些塊産生的主要原因是因為由于在你查詢的過程中，由于其他會話對資料塊進行操作，而對所要查詢的塊有了修改，但是由于我們的查詢是在這些修改之前調用的，是以需要對復原段中的資料塊的前映像進行查詢，以保證資料的一緻性。這樣就産生了一緻性讀。
Physical Reads：Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache. (Physical Reads:執行個體啟動後，從磁盤讀到Buffer Cache資料塊數量)
就是從磁盤上讀取資料塊的數量，其産生的主要原因是：
（1） 在資料庫高速緩存中不存在這些塊
（2） 全表掃描
（3） 磁盤排序
它們三者之間的關系大緻可概括為：
邏輯讀指的是Oracle從記憶體讀到的資料塊數量。一般來說是'consistent gets' + 'db block gets'。當在記憶體中找不到所需的資料塊的話就需要從磁盤中擷取，于是就産生了'physical reads'。
Sorts(disk):
Number of sort operations that required at least one disk write. Sorts that require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE.
bytes sent via SQL*Net to client:
Total number of bytes sent to the client from the foreground processes.
bytes received via SQL*Net from client:
Total number of bytes received from the client over Oracle Net.
SQL*Net roundtrips to/from client:
Total number of Oracle Net messages sent to and received from the client.
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)