跟蹤某一會話發出的 sql 的方法來優化SQL

簡要說來，跟蹤一個會話發出的SQL 主要分成下面幾步：

1) 識別要跟蹤的客戶端程式到資料庫的連線(後面都用 session 代替)，主要找出能唯一識別一個session 的sid 與serial#.

2) 設定相應的引數，如開啟時間開關(可以知道一個 sql  執行了多長時間)，存放跟蹤資料的檔案的位置、最大值。

3)  啟動跟蹤功能

4)  讓系統執行一段時間，以便可以收集到跟蹤資料

5)  關閉跟蹤功能

6)  格式化跟蹤資料，得到我們易於理解的跟蹤結果。

 

現在就每一步，給出詳細的說明：

1)  識別要跟蹤的客戶端程式到資料庫的資料庫連線

查詢session 資訊(在sql*plus 中執行)：

set linesize 190

col machine format a30 wrap

col program for a40

col username format a15 wrap

set pagesize 500

select s.sid sid, s.SERIAL# "serial#", s.username, s.machine, s.program,

    p.spid ServPID, s.server

from v$session s, v$process p

where p.addr = s.paddr ;

 

如得到的一個查詢結果如下：

 

SID serial# USERNAME MACHINE              PROGRAM       SERVPID      SERVER

 

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

 

  8    3   SCOTT     WORKGROUP\SUNNYXU     SQLPLUS.EXE   388       DEDICATED

 

LOGON_TIME

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

 2005.06.28 18:50:11

 

上面的結果中比較有用的列為：

sid, serial# :  這兩個值聯合起來唯一標識一個session

username :  程式連線資料庫的使用者名稱

machine :  連線資料庫的程式所在的機器的機器名，可以 hostname 得到

program :  連線資料庫的程式名，所有用java jdbc thin 的程式的名字都一樣，

servpid :  與程式對應的伺服器端的伺服器程式的程式號，在 unix 下比較有用

server : 程式連線資料庫的模式：專用模式(dedicaed)、共享模式(shared)。

只有在專用模式下的資料庫連線，對其程式跟蹤才有效

logon_time :  程式連線資料庫的登陸時間

 

根據 machine, logon_time  可以方便的識別出一個資料庫連線對應的session，從而得到該session 的唯一標識sid, serial#,  為對該session 進行跟蹤做好準備

 

2)  設定相應的引數

引數說明：

timed_statistics :  收集跟蹤資訊時，是否將收集時間資訊，如果收集，則可以知道一個sql 的各個執行階段耗費的時間情況

user_dump_dest :  存放跟蹤資料的檔案的位置

max_dump_file_size :  放跟蹤資料的檔案的最大值，防止由於無意的疏忽，使跟蹤資料的檔案佔用整個硬碟，影響系統的正常執行

 

設定的方法：

SQL> exec sys.dbms_system.set_bool_param_in_session( -

   sid    => 8, -

   serial# => 3, -

   parnam   => 'timed_statistics', -

   bval    => true);

 

SQL> alter system set user_dump_dest='c:\temp';

--  注意這個語句會改變整個系統的跟蹤檔案存放的位置，所以我一般不改這個引數，而用系統的預設值，要檢視當前系統的該引數的值，可以用system 使用者登陸後：

SQL> show parameter user_dump_dest

 

SQL> exec sys.dbms_system.set_int_param_in_session( -

   sid    => 8, -

   serial# => 3, -

   parnam   => 'max_dump_file_size', -

   intval => 2147483647)

 

3)  啟動跟蹤功能

SQL> exec sys.dbms_system.set_sql_trace_in_session(8, 3, true);

注意，只有跟蹤的session 再次發出sql 語句後，才會產生trc 檔案

 

4)  讓系統執行一段時間，以便可以收集到跟蹤資料

 

5)  關閉跟蹤功能

SQL> exec sys.dbms_system.set_sql_trace_in_session(8,3,false);

 

6)  格式化跟蹤資料，得到我們易於理解的跟蹤結果。

對產生的trace 檔案進行格式化：

在命令提示符下，執行下面的命令

tkprof dsdb2_ora_18468.trc dsdb2_trace.out SYS=NO EXPLAIN=SCOTT/TIGER

其它使用tkprof 的例子：

(a)   tkprof  tracefile.trc  sort_1.prf  explain=apps/your_apps_password  print=10

sort='(prsqry,exeqry,fchqry,prscu,execu,fchcu)'

(b)   tkprof  tracefile.trc  sort_2.prf  explain=apps/your_apps_password  print=10

sort='(prsela,exeela,fchela)'

(c)   tkprof  tracefile.trc  sort_3.prf  explain=apps/your_apps_password  print=10

sort='(prscnt,execnt,fchcnt)'

(d) tkprof tracefile.trc normal.prf explain=apps/your_apps_password

 

現對tkprof 程式做進一步的說明：

    在開啟跟蹤功能後，oracle 將被跟蹤 session 中正在執行的SQL 的效能狀態資料都收集到一個跟蹤檔案中。這個跟蹤檔案提供了許多有用的資訊，例如一個 sql 的解析次數、執行次數、fetch 次數、物理讀次數、邏輯讀次數、CPU 使用時間等，利用這些資訊可以診斷你的 sql 的問題，從而用來優化你的系統。不幸的是，生成的跟蹤檔案中的資料是我們難以理解的，所以要用TKPROF 工具對其進行轉換，轉換成我們易於理解格式。tkprof 是oracle提供的實用工具，類似於 sql*plus，在安裝完oracle 客戶端後就自動安裝到系統中，直接在命令符下用就可以了。

 

當在開啟跟蹤功能時發生了recursive calls，則tkprof 也會產生這些 recursive calls 的統計資訊，並清楚的在格式化輸出檔案中標名它們為 recursive calls。

注意：recursive calls 的統計資料是包含在recursive calls 上的，並不包含在引起該recursive

calls 語句的sql 語句上面。所以計算一個sql 語句耗費的資源時，也要考慮該sql 語句引起recursive calls 語句花費的資源。通過將sys 引數設為 no 時，我們變可以在格式化的輸出

檔案中遮蔽掉這些 recursive calls 資訊。

 

如何得到tkprof 的幫助資訊：

執行tkprof 時，不帶任何引數，就可以得到該工具的幫助資訊。

 

執行計劃：

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

    一個語句的執行計劃是oracle 執行這個sql 語句的一系列指令。通過檢驗執行計劃，你可以更好的知道oracle 如何執行你的sql 語句，這個資訊可以幫助你決定是否你寫的sql 語

句已經使用了索引。如果在tkprof 中指定了EXPLAIN 引數，tkprof 使用 EXPLAIN PLAN 命令來為每個被跟蹤

的sql 語句產生執行計劃。

 

使用說明：

TKPROF 工具接受一個trace 檔案作為輸入檔案，利用提供給命令的多個引數對trace 檔案進行分析，然後將格式化好的結果放到一個輸出檔案中。

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25964700/viewspace-700311/，如需轉載，請註明出處，否則將追究法律責任。