Oracle中SQL語句的幾種用法

在診斷資料庫系統效能的過程中，總會涉及到跟蹤效率低下的SQL語句，Oracle資料庫10g包含一種新的公用程式trcsess，它可以讓您基於會話ID或模組名稱之類的條件，有選擇地從大量追蹤檔案中抽取出跟蹤資料，並將它們儲存到一個檔案中。該公用程式在共用伺服器配置中特別有用，因為發送器可能把每一個使用者請求傳遞給不同的共用伺服器處理序，從而為任何給定的會話產生多個追蹤檔案。與通過大量追蹤檔案發掘資訊不同，Oracle資料庫10g的trcsess可以讓您獲得關於單一使用者會話的整合後的跟蹤資訊。現在就如何跟蹤SQL語句來做一個簡單的總結。權做拋磚引玉之用。

如果我們可以修改應用系統的原始碼，則可以直接在程式中加入如下的語句：

1)alter session set timed_statistics=true;

/*適用於Oracle 8.1.7以後的版本*/

2)alter session set max_dump_file_size=unlimited ;

/*適用於Oracle 9i以後的版*/

3)alter session set tracefile_identifier='POX20031031a';

/*適用於Oracle 9i以後的版本*/

4)alter session set events '10046 trace name context forever， level 8';

/*在這裡編寫應用程式的代碼*/

5)alter session set events '10046 trace name context off';

在上述語句中，語句1是把該會話的時間統計開啟，該參數預設為false.在Oracle 9i之前的版本中，不能在會話級設定該參數，只能修改初始設定檔案然後重新啟動資料庫，這樣將在執行個體級開啟時間統計。

語句2是把追蹤檔案的大小設定成作業系統所允許的最大尺寸，這樣可以防止追蹤檔案在完成所需要的跟蹤之前被填充滿，此外需要注意的是確儲存放追蹤檔案的目錄要有足夠的空間，否則將會收到“檔案系統已滿”錯誤。

語句3的作用是使產生的追蹤檔案名稱中包含'POX20031031a'字串，這樣可以使你很容易的找到所需的追蹤檔案，該參數在Oracle 9i之後可用。

語句4和語句5的作用分別是開啟和關閉跟SQL跟蹤，你可以在這兩個語句之間寫入應用程式的代碼，這些代碼的執行情況都將被跟蹤。需要注意的是語句4)的level關鍵字，它用來指定追蹤層級，一共有0，1，2，4，8，12六個層級可以設定，0相當於關閉跟蹤;1是輸出一般的跟蹤資訊，不包括綁定變數和等待資訊;2和1相同;4是在層級1的基礎上增加綁定變數資訊;8是在層級1的基礎上增加等待資訊;層級12是輸出包含層級1，4，8的所有資訊。

如果應用程式的代碼無法修改或者是不想去修改，則可以在其他會話中開啟對特定會話的跟蹤，方法如下：

sys.dbms_system.set_bool_param_in_session(：sid， ：serial，'timed_statistics'， true);

sys.dbms_system.set_int_param_in_session( ：sid， ：serial，'max_dump_file_size'， 2147483647);

開啟和關閉跟蹤的第一種方法(oracle推薦)：

sys.dbms_support.start_trace_in_session(：sid， ：serial，waits=>true， binds=>false);

/* 在此期間運行要跟蹤的應用程式*/

sys.dbms_support.stop_trace_in_session(：sid， ：serial);

開啟和關閉跟蹤的第二種方法：

sys.dbms_system.set_ev(：sid， ：serial， 10046， 8， '');

/*在此期間運行要跟蹤的應用程式*/

sys.dbms_system.set_ev(：sid， ：serial， 10046， 0， '');

以上語句中的：sid和：serial分別代表所要跟蹤的會話的ID和序號，這些資訊可以從V$SESSION視圖的SID和SERIAL#列獲得。開啟關閉跟蹤中第一種方法的好處是你不用自己寫10046這個事件號，這樣可以減少錯誤，但是dbms_support程式包在你的資料庫中可能會不存在，這樣就只有用第二種方法了。

到現在為止，我們已經產生了所需要的追蹤檔案，然後我們需要找到該檔案來進行分析。追蹤檔案的存放位置只有兩種可能，Oracle參數USER_DUMP_DEST 或者BACKGROUND_DUMP_DEST指定的目錄;檔案名稱根據不同的平台會有所不同，但是在檔案名稱中都會包含會話所對應的作業系統進程(線程)號，也就是V$PROCESS的SPID列(V$PROCESS.SPID) ，該資訊可以通過 v$process.addr和v$session.paddr做表串連查詢得到。

例如：

select spid from v$process p，v$session s where p.addr=s.paddr and s.sid=：sid and s.serial#=：serial.

本文只對Oracle在使用專用伺服器處理序(Dedicated Server)模式做了總結，至於的共用伺服器(Shared Server)模式和在應用級使用串連池的情況下，收集SQL跟蹤資訊的方法會比較複雜，希望有相關經驗的高手來總結。

1. 如何高效刪除Oracle資料庫中的重複資料
2. 淺析Oracle監聽器安裝與配置
3. 將oracle 9i備份檔案匯入oracle 8i的方法簡介