sql_trace生成及使用tkprof檢視trace file

1.Trace file簡介:

Trace file（追蹤檔案)是以trc為後續的文字檔案,它記錄了各種sql操作及所消耗的時間等,根據trace檔案我們就可以瞭解哪些sql導致了系統的效能瓶頸，進而採取恰當的方式調優.

2.怎麼生成trace file:

1. 首先用sqlplus登陸Oracle.

show parameter sql_trace

Name          Type        Value

-------------------------------------------

sql_trace     boolean      false

如果value是false表示系統當前不會產生trace檔案.採取如下操作讓系統產生trace檔案:

alter session set sql_trace=true;

或者:alter system set sql_trace=true;

2.執行一些sql語句後.停止產生trace檔案.alter session(或system)  set sql_trace=false.

3.trace檔案所在的預設路徑.SELECT VALUE  FROM V$PARAMETER WHERE NAME = 'user_dump_dest'

3.怎麼更改trace檔案的儲存目錄:

如果是oracle 11g 以下的版本則:alter system set user_dump_dest = 'd:\oracle\trace';(注意:trace檔案就直接生成在trace目錄下)

如果是oracle 11g.則alter system set user_diagnostic_dest = 'd:\oracle\trace';(注意:trace檔案不會直接生成在trace目錄下.trace目錄下會生成其他很多目錄.

trace檔案的具體目錄是:d:\oracle\trace\diag\rdbms\orli11r2\orli11r2\trace.其中的orli11r2是SID)

4.怎麼檢視trace檔案:

利用tkprof工具分析Trace檔案
可以利用tkprof工具分析Trace檔案，產生一個更加清晰合理的輸出結果。tkprof可以在$ORACLE_HOME/bin下面找到。

1）命令格式
命令格式為：
tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]
引數說明：
tracefile：要分析的trace檔案 
outputfile：格式化後的檔案
explain=user/password@connectstring 
table=schema.tablename 
    上述兩個引數是一起使用的，explain指示tkprof要為在跟蹤檔案中找到的每個SQL語句提供一個執行計劃。
    這是通過執行SQL語句EXPLAIN PLAN通過連線資料庫對在trace檔案中出現的每條sql語句檢視執行計劃，並將之輸出到outputfile中。
    指定的table名將提供給EXPLAIN PLAN語句。
print=n：只列出最初N個sql執行語句，預設是無限制的，只有在和引數sort一起使用的時候才有意義
insert=filename：會產生一個sql檔案，執行此檔案可將收集到的資料insert到資料庫表中
sys=no：sys使用者執行的SQL語句(例如，解析操作階段對資料字典的遞迴查詢)不輸出到輸出檔案中。
record=filename：可將非巢狀執行的sql語句過濾到指定的檔案中去 
waits=yes|no：是否統計任何等待事件，預設是yes 
aggregate=yes|no：是否將相同sql語句的執行資訊合計起來，預設為yes 
sort= option：設定排序選項，可以用逗號分隔多個選項。預設是跟蹤檔案中發現的SQL順序。具體選項可以檢視tkprof的命令幫助輸出得到。

例如：
tkprof <tracefile> <outputfile> sys=no sort=prsela,exeela,fchela
    prsela  elapsed time parsing
    exeela  elapsed time executing
    fchela  elapsed time fetching

只指定原TRC檔案及生成檔案位置：

tkprof /u01/diag/rdbms/bysrac/bysrac1/trace/bysrac1_ora_15241_TEST9_1.trc /home/oracle/test9_1.trc

2）輸出結果格式
輸出結果中，首先是頭部內容。
之後針對每個SQL語句提供如下資訊：SQL 語句文字、執行統計、關於解析的資訊、執行計劃以及等待事件。
執行計劃以及等待事件是可選的，只有儲存在跟蹤檔案中才會出現。
例如下面的輸出：

SQL ID: 6ns41jwh1fz2u
Plan Hash: 1544281142
select *
from
 bys.test9 where object_id <20000


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1129      0.09       0.65          0       1342          0       16914
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1131      0.09       0.65          0       1342          0       16914

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
  16914  TABLE ACCESS FULL TEST9 (cr=1342 pr=0 pw=0 time=92380 us cost=67 size=3484845 card=16835)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    1129        0.00          0.01
  Disk file operations I/O                        1        0.00          0.00
  ges message buffer allocation                  21        0.00          0.00
  gc cr multi block request                      25        0.04          0.44
  SQL*Net message from client                  1129        0.00          0.35

執行統計有如下的幾列：
count：表示執行的資料庫呼叫數量。
cpu：表示處理資料呼叫花去的CPU時間，以秒為單位。
elapsed：是處理資料庫呼叫花費的總的時間，以秒為單位，如果這個值比CPU時間高，下一節關於執行統計中的等待事件會提供在等待的資源或同步點。
disk：表示物理讀的資料塊數量。要當心，這不是物理I/O操作的數量，物理I/O運算元在關於等待事件一節給出。如果這個值大於邏輯讀的數量(disk > query +current)，這意味著資料塊填充進了臨時表空間。
query：是在一致性模式（consistent mode）下從快取記憶體邏輯讀取的塊數量。通常，這型別的邏輯讀用作查詢。 
current：代表在當前模式下從快取記憶體邏輯讀取的塊數量。通常，這類邏輯讀被INSERT、DELETE、MERGE以及UPDATE等語句所使用。
rows：代表處理的資料行數量。對於查詢來說，這就是獲取的行數量。對於INSERT、DELETE、MERGE以及UPDATE 等語句來說，這是所影響的行數量。

關於解析的資訊開始兩行Misses in library cache during parse和Misses in library cache during execute提供了發生在解析和執行呼叫階段的硬解析數量。
如果在執行呼叫時沒有硬解析發生，Misses in library cache during execute這一行將不存在。
接下來是優化器模式以及用於解析SQL語句的使用者。

執行計劃分為兩部分，第一部分稱為行源操作(Row Source Operation )，是遊標關閉且開啟跟蹤情況下寫到跟蹤檔案中的執行計劃。這意味著如果應用程式不關閉遊標而重用它們的話，不會有新的針對重用遊標的執行計劃寫入到跟蹤檔案中。第二部分，叫做執行計劃 (Execution Plan)，是由指定了explain引數的TKPROF生成的。既然這是隨後生成的，所以和第一部分不一定完全匹配。萬一你看到不一致，前者是正確的。
兩個執行計劃都通過Rows列提供執行計劃中每個操作返回的行數（不是處理的--要注意）。 
對於每個行源操作來說，可能還會提供如下的執行時統計： 
cr是一致性模式下邏輯讀出的資料塊數。
pr是從磁碟物理讀出的資料塊數。
pw是物理寫入磁碟的資料塊數。
time是以微秒錶示的總的消逝時間。要注意根據統計得到的值不總是精確的。實際上，為了減少開銷，可能用了取樣。
cost是操作的評估開銷。這個值只有在Oracle 11g才提供。
size是操作返回的預估資料量(位元組數)。這個值只有在Oracle 11g才提供。 
card是操作返回的預估行數。這個值只有在Oracle 11g才提供。

輸出檔案的結尾給出了所有關於跟蹤檔案的資訊。首先可以看到跟蹤檔名稱、版本號、用於這個分析所使用的引數sort的值。然後，給出了所有會話數量與SQL語句數量。