SQL執行計劃之sql_trace
一，sql_trace的作用：用以描述SQL的執行過程的trace輸出。
- SQL是如何操作數據的
- SQL執行過程中產生了哪些等待事件
- SQL執行中消耗了多少資源
- SQL的實際執行計劃
- SQL產生的遞歸語句
二，set autotrace 與 sql_trace的區別
set autotrace用于輸出優化器產生的執行計劃，執行計劃展示的資源消耗情況是CBO的估算值，并不一定是SQL實際執行時資源消耗情況。比如，向表t 中插入10萬條數據，執行查詢的時候發現只返回少量數據，這可能就是表t還沒有被Oracle重分析，它并不知道表的數據已更新。
sql_trace是SQL實際的執行情況。
三，何時使用set autotrace 與 sql_trace
當需要分析執行計劃及CBO行為時，使用set autotrace；當要看一條SQL的真實運行效果時，使用sql_trace。
四，sql_trace的使用
（1）收集本回話的SQL執行過程
SQL>alter session set sql_trace=true
SQL>alter session set statistics_level = all;
SQL>alter session set timed_statistics = true;
SQL>alter session set max_dump_file_size = unlimited;
SQL>alter session set tracefile_identifier = liangjian
SQL>sql.....
SQL>alter session set sql_trace=false
SQL>exit
（2）收集其它會話的SQL執行過程
SQL1>select sid, serial# from v$session;
SQL2>execute dbms_system.set_sql_trace_in_session(sid, serial#, true);
SQL2>sql....
SQL2>execute dbms_system.set_sql_trace_in_session(sid, serial#, false);
五，trace文件內容
生成的trace文件的目錄由參數文件指定，可以通過v$diag_info視圖來查看，name=Default Trace File的值就是trace文件的默認路徑。下面是一個實際生成的trace文件：
trace文件內容：
trace文件中記錄的是Oracle按照時間先后順序來輸出的，它是Oracle執行SQL最底層的過程。上面第二個紅色方框中體現的就是 我們所發出SQL執行的過程，其意思是：首先打開一個標號為4的游標，關聯這條SQL語句，然后解析這條SQL語句（PARSE #4），其中mis=1表示共享區中沒有可共享已解析SQL，故它執行的是一次硬解析，然后執行SQL語句，再然后獲取數據，最后關閉游標。有時候游標標 號相同的輸出并不在一起，因為trace文件是按照時間的先后順序輸出的，這也最能反映Oracle內部的執行順序。
trace文件中有很多鍵值對信息，初次看trace文件也一定是看不懂的，下面介紹一下各個鍵所代表的含義：
六，tkprof格式化工具
trace文件并不直觀，為了讓讀者一目了然，Oracle提供了格式化trace文件的工具：tkprof
（1）tkprof命令
Usage: tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]
參數說明
tracefile：你要分析的trace文件的絕對路徑
outputfile：格式化后的文件絕對路徑
explain=user/password@connectstring，對每條SQL 語句確定其執行規劃，并將執行規劃寫到輸出文件中。如果不是有explain，在trace文件中我們看到的是SQL實際的執行路徑，不會有sql的執行 計劃（可以對比實際執行規劃和執行計劃的差別）
table=schema.tablename，在輸出到輸出文件前，用于存放臨時表的用戶名和表名。
PRINT:只列出輸出文件的最初N個SQL語句。默認為所有的SQL語句。
AGGREGATE:如果= NO，則不對多個相同的SQL進行匯總。如果為yes則對trace文件中的相同sql進行合并。
INSERT:SQL 語句的一種，用于將跟蹤文件的統計信息存儲到數據庫中。在TKPROF創建腳本后，在將結果輸入到數據庫中。
SYS:如果設置為yes，則所有sys用戶的操作(也包含用戶sql語句引發的遞歸sql)，這樣可以減少分析出來的文件的復雜度，便于查看。
sort:對trace文件的sql語句根據需要排序，其中比較有用的一個排序選項是fchela，即按照elapsed time fetching來對分析的結果排序（記住要設置初始化參數timed_statistics=true），生成的文件將把最消耗時間的sql放在最前面 顯示。
執行計劃分為兩部分，第一部分稱為行源操作(Row Source Operation )，是游標關閉且開啟跟蹤情況下寫到跟蹤文件中的執行計劃。這意味著如果應用程序不關閉游標而重用它們的話，不會有新的針對重用游標的執行計劃寫入到跟蹤 文件中。第二部分，叫做執行計劃 (Execution Plan)，是由指定了explain參數的TKPROF生成的。既然這是隨后生成的，所以和第一部分不一定完全匹配。萬一看到兩者不一致，前者是正確 的。兩個執行計劃都通過Rows列提供執行計劃中每個操作返回的行數（不是處理的--要注意）。 對于每個行源操作來說，可能還會提供如下的運行時統計：
cr是一致性模式下邏輯讀出的數據塊數。
pr是從磁盤物理讀出的數據塊數。
pw是物理寫入磁盤的數據塊數。
time是以微秒表示的總的消逝時間。要注意根據統計得到的值不總是精確的。實際上，為了減少開銷，可能用了采樣。
cost是操作的評估開銷。這個值只有在Oracle 11g才提供。
size是操作返回的預估數據量(字節數)。這個值只有在Oracle 11g才提供。
card是操作返回的預估行數。這個值只有在Oracle 11g才提供。
輸出文件的結尾給出了所有關于跟蹤文件的信息。首先可以看到跟蹤文件名稱、版本號、用于這個分析所使用的參數sort的值。然后，給出了所有會話數量與SQL語句數量。
Optimizer mode: ALL_ROWS表示優化器采用的是all_rows的模式
Parsing user id: 55 表示用戶id為55
（2）格式化后輸出文件的解釋
首先解釋輸出文件中列的含義：
CALL：每次SQL語句的處理都分成三個部分
Parse：這步將SQL語句轉換成執行計劃，包括檢查是否有正確的授權和所需要用到的表、列以及其他引用到的對象是否存在。
Execute：這步是真正的由Oracle來執行語句。對于insert、update、delete操作，這步會修改數據，對于select操作，這步就只是確定選擇的記錄。
Fetch：返回查詢語句中所獲得的記錄，這步只有select語句會被執行。
COUNT:這個語句被parse、execute、fetch的次數。
CPU：這個語句對于所有的parse、execute、fetch所消耗的cpu的時間，以秒為單位。
ELAPSED：這個語句所有消耗在parse、execute、fetch的總的時間。
DISK：從磁盤上的數據文件中物理讀取的塊的數量。
QUERY：在一致性讀模式下，所有parse、execute、fetch所獲得的buffer的數量。一致性模式的buffer是用于給一個長時間運行的事務提供一個一致性讀的快照，緩存實際上在頭部存儲了狀態。
CURRENT：在current模式下所獲得的buffer的數量。一般在current模式下執行insert、update、 delete操作都會獲取buffer。在current模式下如果在高速緩存區發現有新的緩存足夠給當前的事務使用，則這些buffer都會被讀入了緩 存區中。
ROWS: 所有SQL語句返回的記錄數目，但是不包括子查詢中返回的記錄數目。對于select語句，返回記錄是在fetch這步，對于insert、update、delete操作，返回記錄則是在execute這步。
（3）trace文件中的性能分析
1、如果分析數與執行數之比為1，說明每次執行這個查詢都要進行sql解析。如果分析數與執行數之比接近0，則意味著查詢執行了很多次軟解析，降低了系統的可伸縮性。
2、如果trace文件中顯示對所有或者幾乎所有的sql都執行一次，那有可能是因為沒有正確使用綁定變量。
3、如果一個(Fetch Count)/所獲得行數的比值接近1，且行數大于1，則應用程序不執行大批量取數操作，每種語言/API都有能力完成這個功能，即一次取多行。如果沒有 利用這個功能進行批量去，將有可能花費多得多的時間在客戶端與服務器端之間來回往返。這個過多的來回轉換出了產生很擁擠的網絡狀況之外，也會比一次調用獲 得很多行要慢得多，如何指示應用程序進行批量獲取將隨語言/API而定。
4、如果CPU時間與elasped時間有巨大差異，意味著有可能花了大量時間在等待某些事情上。如果花了一個CPU時間來執行，但它卻總共花了10秒的時間，這就意味著90%的運行時間在等待一個資源。例如被一個會話等待，或者大量查詢時的物理IO等待等
5、較長的CPU或經過時間往往是最消耗資源的sql，需要我們關注
6、可以通過磁盤IO所占邏輯IO的比例，disk/query+current來判斷磁盤IO的情況，太大的話有可能是db_buffer_size過小，當然這也跟SQL的具體特性有關
7、query+current/rows 平均每行所需的block數，太大的話（超過20）SQL語句效率太低，數據過于分散，可以考慮重組對象