SQL效能的度量 - 會話級別的SQL跟蹤sql_trace

透過初始化引數sql_trace開啟會話跟蹤，透過oradebug命令輸出到跟蹤檔案，提供更加詳細的資訊。但一般還需要藉助tkprof工具格式化跟蹤檔案便於閱讀。

在使用sql_trace之前，有兩個初始化引數可能需要注意一下，不過從9i開始都是預設按以下設定的。

初始化引數timed_statistics最好設為true，否則一些重要資訊不會被收集

show parameter timed_statistics;

NAME                                 TYPE        VALUE

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

timed_statistics                     boolean     TRUE

對跟蹤檔案的大小限制，可以KB、MB為單位設定，或者乾脆設定為unlimited

show parameter max_dump_file_size;

NAME                                 TYPE        VALUE

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

max_dump_file_size                   string      UNLIMITED

啟用自己的會話跟蹤

alter session set sql_trace = true;

或

exec dbms_session.set_sql_trace(true);

跟蹤指定的會話

exec dbms_system.set_sql_trace_in_session(sid, serial#, true|false);

如果要對其它使用者的會話進行設定，可以用dbms_system的另外兩個過程

exec dbms_system.set_bool_param_in_session(sid, serial#, 'timed_statistics', true);

exec dbms_system.set_int_param_in_session(sid, serial#, 'max_dump_file_size', 2147483647);

生成自己會話的跟蹤檔案

oradebug setmypid;

oradebug tracefile_name;

生成指定會話的跟蹤檔案

oradebug setospid ;

oradebug tracefile_name;

跟蹤檔案的命名規則：_ora_.trc，其中sid是例項名，spid是會話對應的作業系統伺服器程式ID。

跟蹤檔案的路徑由初始化引數user_dump_dest指定

show parameter user_dump_dest;

NAME                                 TYPE        VALUE

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

user_dump_dest                       string      c:\oracle\diag\rdbms\mes\mes\trace

以下查詢可以找到會話的sid、serial#、pid、spid幾個引數

select s.username, s.sid, s.serial#, p.pid, p.spid from v$session s, v$process p where s.paddr = p.addr and s.username is not null;

執行一些操作後關閉跟蹤，觀察跟蹤檔案的內容。跟蹤檔案的內容比較專業，不宜看懂，可以透過作業系統命令列工具tkprof格式化跟蹤檔案

tkprof tracefile outputfile [explain=] [table=] [print=] [insert=] [sys=] [sort=]

主要引數含義：

sys：yes或no，預設yes，指定輸出檔案中是否包含以SYS使用者執行的sql語句。

record：指定一個路徑下的檔案，用來生成在跟蹤檔案中找到的所有非遞迴的SQL。如：record=d:\test.log。

aggregate：no或yes，預設no，指定tkprof是否將同樣文字內容的sql聚合處理，如執行了十次select * from a，如果指定這個引數為no，那麼產生的輸出檔案會有十個這樣語句的執行資訊，如果指定的是yes，那麼tkprof會把這十次的執行資訊彙總顯示。

sort：指定tkprof輸出檔案裡sql語句按照什麼排序，預設是按照執行的先後順序排序的，可以指定它按照其他方式排序，比如磁碟讀取數，CPU時間等。這個引數最經常用的方式是：sort=prsela,exeela,fchela,也就是按照解析、執行、提取，這三個值加起來就是響應時間，tkprof會根據三個值的和進行排序，即按照響應時間排序。

print：經常搭配sort引數一起使用，用來指定tkprof輸出sql語句的數量。這兩個引數搭配使用起來就比較妙，如想知道一個跟蹤檔案裡響應時間排前十的SQL，那麼你就可以sort=prsela,exeela,fchela print=10來搭配使用。

explain：為每一個SQL提供一個執行計劃。使用方法是explain=使用者名稱/密碼。

wait：指定輸出檔案中是否包含等待事件，預設為包含。

如對於11g的跟蹤路徑

tkprof c:\oracle\diag\rdbms\mes\mes\trace\mes_ora_2372.trc d:\2372.txt sys=no sort=prsela,exeela,fchela record=d:\2372.log

而對於10g

tkprof c:\oracle\product\10.2.0\admin\mes\udump\mes_ora_3888.trc d:\3888.txt sys=no sort=prsela,exeela,fchela record=d:\3888.log

對於linux系統，在作業系統層面上利用top命令找到當前佔用CPU資源最高的Oracle程式的ID號，然後根據該ID號找到相應會話的sid和serial#

select s.username, s.sid, s.serial# from v$session s, v$process p where s.paddr = p.addr and p.spid = &spid;

有了sid和serial#，就可以對會話實施SQL跟蹤

exec dbms_system.set_sql_trace_in_session(sid, serial#, true);

最後透過tkprof工具轉換跟蹤檔案輸出，從而可以診斷分析該會話執行的操作。

tkprof輸出檔案中列的含義：

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這步。